HIVEsql使用总结

一、常用数据库命令

创新互联公司是一家专业提供岷县企业网站建设,专注与网站设计、成都网站建设H5高端网站建设、小程序制作等业务。10年已为岷县众多企业、政府机构等服务。创新互联专业网站建设公司优惠进行中。

1、查询有哪些数据库 show databases

 2、查询有哪些数据表:show tables

3、显示所有函数: show functions

4、使用use databasename;可以切换到某个数据库下

示例(切换到test数据库):use test

5、查看当前数据库:select current_database()

6、查询数据表有哪些字段及字段详情:describe tablename

示例(查询staged_employees数据表):describe staged_employees

可以简写为desc staged_employees

结果如下:

 

7、查看指定数据库里有哪些数据表:SHOW TABLES IN DbName;

示例:查看xiaoxiao数据库下面有哪些表

 SHOW TABLES IN xiaoxiao

8、获得表的建表语句:

示例(查看创建emailtest这个表使用的语句方法):show create table emailtest

9、查看数据库的描述信息和文件目录位置路径信息

示例(查看datetest数据库的描述信息和文件目录位置信息):describe databasedatetest;

二、创建数据库

创建xiaoxiao数据库:

create database xiaoxiao;

 

 

二、创建数据表


create   table staged_employees (

id int   comment 'id',

user_name   string comment 'user name')

 

 

 

 

三、删除数据库

 

删除数据库的时候,不允许删除有数据的数据库,如果数据库里面有数据则会报错。如果要忽略这些内容,则在后面增加CASCADE关键字,则忽略报错,删除数据库。

 

DROP DATABASE DbName CASCADE(可选);

DROP DATABASE IF EXISTS DbName CASCADE;

 

 

 

三、删除数据表

 

drop table staged_employees

 

 

 

四、删除数据表中所有内容

删除emaitest数据表中的所有内容。

insert overwrite table emailtest select * from emailtest where 1=0

 

 

 

五、更改表名

1、更改表名

-- 重命名表名 ALTER TABLE table_name RENAME TO new_table_name;

 

六、数据表添加字段:

alter table lemailtest add columns(time int comment 'now time')

 

 

七、HIVE统计函数

1、count(1)与count(*)得到的结果一致,包含null值。count(字段)不计算null值

2、集合统计函数

2.1 个数统计函数: count

语法: count(*), count(expr), count(DISTINCT expr[, expr_.])

返回值: int

说明: count(*)统计检索出的行的个数,包括NULL值的行;count(expr)返回指定字段的非空值的个数;count(DISTINCTexpr[, expr_.])返回指定字段的不同的非空值的个数

举例:

hive> select count(*) from lxw_dual;

20

hive> select count(distinct t) from lxw_dual;

10

2.2 总和统计函数: sum

语法: sum(col), sum(DISTINCT col)

返回值: double

说明: sum(col)统计结果集中col的相加的结果;sum(DISTINCT col)统计结果中col不同值相加的结果

举例:

hive> select sum(t) from lxw_dual;

100

hive> select sum(distinct t) from lxw_dual;

70

 

七、插入数据到emailtest数据表中(追加数据到原有的数据表中)

读取fx01数据表数据的一行,然后插入到emailtest数据表,对emailtest数据表原有的数据不会动的。

insert into table emailtest

select * from fx01 limit 1

 

八、插入数据倒emailtest数据表中(覆盖原有数据表的数据,相当于把原有数据表先清空,再写入新的数据)

从fx01数据表读取数据写入到emailtest数据表,对原有emailtest数据表数据清空处理。

INSERT OVERWRITE TABLE emailtest

SELECT email,y,m,d

FROMfx01 where m=06 and d=19

 

九、关键词匹配——like

 

1、从fx01表中查找列 content中包含"制度"的行数信息

Select * from fx01 where content like '%制度%'

 

十、根据某个关键字去匹配,然后去设置新的关键词case…when..方法

1、case用法一:CASE  条件判断函数 CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END

 

如果a等于b,那么返回c;如果a等于d,那么返回e;否则返回f

select policy,case policy when 'abc' then '测试' else 'ccc' end as policy from fx01 limit 6

 

2、case用法二:

假如要用到case when又要用到like这样的功能,即如果字符串包含‘语文’就怎么怎么样,包含‘数学’就怎么怎么样,包含‘英语’就怎么怎么样,like是用于where中的,放在case when里面是无效的,可以用instr()这个函数来查找这些字符出现的位置,代替like的功能,这样写就好呐。 

case when instr(t.str,’语文’) > 0 then 0 

when instr(t.str,’语文’) > 0 then 1 

when instr(t.str,’语文’) > 0 then 2 

else 3 end 

示例:

select t1.policy,case when instr(t1.policy,'信托') > 0 then '信托'

when instr(t1.policy,'张三') > 0  then '张三1'

when instr(t1.policy,'李四') > 0  then '李四1'

when instr(t1.policy,'小明') > 0  then '小明1' else '小红' end from (select distinct policy from fx01 limit 6) t1

 

 

十一、order by——TOP N

hive实现topN,使用order by和limit组合方式实现。Order by是进行降序排列,limit是选取多少。默认按升序排列,如果要按降序排列在末尾加上DESC,ASC是升序。

取排名TOP 5的数据。

select distinct company,count(company) as num from fx01 where m=05 group by company order by num DESC limit5

 

十二、数据表多表连接——join

 

多表连接——join两个以上的表

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2);

 

join支持left join(左连接)、right join(右连接)、full join(全连接)

 

1、两表相关联示例

select fx01_lanjie.company,fx01_lanjie.lanjie,fx01_yujing.yujing,fx01_lanjie.y,fx01_lanjie.m from fx01_lanjie left join fx01_yujing ON fx01_lanjie.company=fx01_yujing.company

 

2、三表相关联示例

select fx01_lanjie.company,fx01_lanjie.lanjie,fx01_yujing.yujing,fx01_fangxing.fangxing,fx01_lanjie.y,fx01_lanjie.m from fx01_lanjie left join fx01_yujing ON fx01_lanjie.company=fx01_yujing.company  join fx01_fangxing on fx01_lanjie.company=fx01_fangxing.company

 

十三、实现某一列字段关键词统计——split+explode函数

 

0  stu表数据:

 

stu:

id             name

hello,you zm2008

hello,me zm2015

 

 

1实现单词计数:     (列转行)   ---> split切分+explode(炸开)

 

1.0数据拆分成数组

select split(id,',') from  stu;    得到数组

[hello,you]

[hello,me]

 

1.1继续将数组拆分(hive explode函数会将数组继续拆分成单个字符)

select explode(split(id,','))  from stu;   窗体函数

hello

you

hello

me

 

 

1.2分组统计:

select t1.c1, count(1) from (select explode(split(id,',')) as c1  from stu)  t1 group by t1.c1;

hello 2

you 1

me 1

 

 

案例实现需求:统计“关键词”这一列每个关键词出现的次数,把数字去掉,只统计中文关键词

表名:Testtable

ID

keword

y

m

d

1

北京;广州;深圳;贵州

2017

2

8

2

重庆;河南

2017

2

5

7

12345555

2017

9

5

 

实现语句:

Select explode(split(keyword, ';')) as c1 from testtable  where keword' rlike '^[\\u4e00-\\u9fa5]+$'

 

 


标题名称:HIVEsql使用总结
文章源于:http://pwwzsj.com/article/pcoips.html