hive hql文档_hive之hql

1. 简单的聚合操作

count计数【 count()--只计算不为空的行 count(1)--会把空行也放进去 count(col)--类似于count()】、

sum求和，返回bigint类型

sum(col)+1:这种写法会报错，需要把1转换成bigint类型，即sum(col)+cast(1 as bigint)

avg求平均值，返回double

distinct

2. 查询操作

order by【其中，where在map中实现，order by在reduce中实现】

38.png

按照某些字段排序，asc升序，desc降序，默认按字典排序【不写asc和desc的话】

注：order by是全局排序；后面可以跟多列；需要reduce操作，且只有一个reduce，与配置

无关；所有的数据都集中在一个reduce中操作，数据量大的情况下不要轻易尝试order by

例：

select * from test_select order by name asc,salary desc;

39.png

group by ，having【其中，where在map中实现，group by having 在reduce中实现】

40.png

注：select后面非聚合的列必须出现在group by中；

特性：

使用reduce操作，受限于reduce数量，可设置reduce参数mapred.reduce.tasks;

输出文件个数与reduce数相同，文件大小与reduce处理的数据量有关

问题：

网络负载过重；数据倾斜【某个reduce处理数据过多，数据在多个reduce中分布不均

衡】，优化参数hive.groupby.skewindata=true【设置这个参数之后，会再启动一个job，

第一个job分配每个reduce的计算数量，第二个job处理第一个job的结果】

例：

查看当前reduce数量为1

41.png

设置reduce数量等于5

set mapred.reduce.tasks=5;

首先实验一下order by，order by只用一个reduce【reduce数量还是等于1】，设置多个对它来讲没什么用处

42.png

然后尝试group by，group by受限于reduce数量【此时reduce的数量等于5】，计算表test_select_2中每一个name的记录数量

select name,count(name) from test_select_2 group by name;

43.png

44.png

45.png

假设当前数据量很大，设置参数set hive.groupby.skewindata=true来优化group by【当前job数量等于2】

46.png

join【on条件不支持>,>=,

下面的例子的数据为

test_select

47.png

test_select_2

48.png

(1)join，类似于MySQL中的inner join

49.png

50.png

例：

select t1.name name1,t1.salary salary1,t2.name name2,t2.salary salary2 from test_select

t1 join test_select_2 t2 on t1.name=t2.name;

51.png

(2)left outer join，类似于MySQL中的left join，在另一张表找到关联的一条数据之后还要继续遍历剩余数据，如果找到其他能匹配的数据，也输出。

例：

select t1.name name1,t1.salary salary1,t2.name name2,t2.salary salary2 from test_select

t1 left join test_select_2 t2 on t1.name=t2.name;

52.png

(3)right outer join，类似于MySQL中的right join

例：

select t1.name name1,t1.salary salary1,t2.name name2,t2.salary salary2 from test_select

t1 right join test_select_2 t2 on t1.name=t2.name;

53.png

(4)left semi join，类似于exists，在另一张表中找到关联的一条数据就不会再遍历剩余数据了

例：

select t1.name name1,t1.salary salary1 from test_select t1 left semi join test_select_2 t2

on t1.name=t2.name;

54.png

输出test_select中的每一条与test_select_2能关联上的数据，

注：与MySQL中的exists类似， left semi join只能输出主表的字段，不能输出test_select_2 中的字段，会报以下错误

55.png

(5)mapjoin，相当于join的一个优化操作，在map端完成join操作，不需要用reduce，基于内存做join，属于优化操作

56.png

使用场景：

关联操作中有一张表非常小；不等值的连接操作

配置方式：

第一种自动：hive根据SQL，自动选择common join还是mapjoin

需要配置两个参数：

set hive.auto.convert.join=true;

set hive.mapjoin.smalltable.filesize

第二种方式手动：

select /*+mapjoin(n)*/ m.col,m.col2,n.col3 from m join n on m.col=n.col

上面这种写法表示把n加载到内存中，也可以选择把m加载到内存中

特性：

在map端加载小表【这个表可以自己指定】到内存，然后读取大表，和内存中的小表完成连接记录；使用了分布式缓存作用

优点：

不消耗集群的reduce资源【reduce资源相对紧缺】；减少了reduce操作，加快程序运行

缺点：

占用部分内存，所以加载到内存中的小表数据量不宜过大，因为每个计算节点都会加载一次；生成较多的小文件【map任务越多，生成的小文件越多】

distribute by和sort by

distribute by col1,col2 -- 按照col1和col2将数据分散到不同的reduce

sort by col1 -- 按照某列排序

两者结合使用：确保每个reduce的输出都是有序的，sort by对每个reduce内数据进行排序【局部排序】

基本语法

select col1,col2 from M

distribute by col1

sort by col1 asc,col2 desc

使用场景：map或者reduce输出文件大小不均；小文件过多【通过设置reduce个数，输出文件个数等于reduce个数】；文件过大

例：把大文件放到小文件【数据和join例子中的一样】

现将reduce设置成3，可输出3个文件

set mapred.reduce.tasks=3

insert overwrite table test_select select name,salary from test_select_2 distribute by name;

57.png

查看该表所在目录，该目录下有三个输出文件，分别对应设置的三个reduce

hadoop fs -ls /user/hive/warehouse/test_select

58.png

例：将test_select下面的三个文件合并，并插入到表test_select_3

因为要合并三个文件，所以将reduce设置成1，即只输出1个文件

set mapred.reduce.tasks=1;

insert overwrite table test_select_3 select name,salary from test_select distribute by name;

59.png

查看表test_select_3，该目录下只有一个文件，包含了test_select表的所有内容，合并完成

hadoop fs -ls /user/hive/warehouse/test_select_3

hadoop fs -cat /user/hive/warehouse/test_select_3/000000_0

60.png

union all【hive不支持union】

将多个表的数据合并成一个表，用法与MySQL中的union all类似，也不走reduce

基本语法：

select col

from

(select col1 from table1

union all

select col1 from table2

) tmp;

注：union两边的字段col1--名字要一样、字段类型要一样、字段个数要一样