hive查询使用详解

常见的hive查询操作有：count、group by、order by、join、distribute by、sort by、clusrer by、union all

一、常见的聚合函数

1、count计数

• count(*) 所有值不全为NULL时，加1操作；
• count(1) 不管有没有值，只要有这条记录，值就加1
• count(col) col列里面的值为null，值不会加1，这个列里面的值不为NULL，才加1 

2、sum求和

    sum(可转成数字的值)  返回bigint

3、avg求平均值

    avg(可转成数字的值)   返回double

4、distinct不同值个数

    count(distinct col)

二、order by按照某些字段排序

样例：select col1,other... from table where conditio order by col1,col2 [asc|desc]

注意：order by 后面可以有多列进行排序，默认按字典排序

order by为全局排序

order by需要reduce操作，且只有一个reduce，与配置无关。数据量很大时，慎用。

执行流程：

 从表中读取数据，执行where条件，以col1，col2列的值做成组合key，其他列值为value，然后在把数据传到同一个reduce中，根据需要的排序方式进行。

三、group by按照某些字段的值进行分组，有相同值放到一起。

    样例：

select col1[,col2],count(1),sel_expr(聚合操作) from table
where condition        --->Map端执行
group by col1[,col2]   --->Reduce端执行
[having]               --->Reduce端执行

    注意：

• select后面非聚合列，必须出现在group by中
• select后面除了普通列就是一些聚合操作
• group by后面也可以跟表达式，比如substr(col)

   特性

• 使用了Reduce操作，受限于reduce数量，设置reduce参数mapred，reduce，tasks
• 输出文件个数与reduce数相同，文件大小与reduce处理的数据量有关

   问题

• 网络负载过重
• 数据倾斜，优化参数hive.groupby.skewindata为true,会启动一个优化程序，避免数据倾斜。

四、join表连接

    两个表m,n之间按照on条件连接，m中的一条记录和n中的一条记录组成一条新记录。

    join等值连接（内连接），只有某个值在m和n中同时存在时。

    left outer join左外连接，左边表中的值无论是否在b中存在时，都输出：右边表中的值，只有在左边表中存在时才输出。

    right outer join和left outer join相反。

    left semi join类似exists。即查找a表中的数据，是否在b表中存在，找出存在的数据。

    mapjoin：在map端完成join操作，不需要用reduce，基于内存做join，属于优化操作。

五、Mapjoin

    在map端把小表加载到内存中，然后读取大表和内存中的小表完成连接操作。其中使用了分布式缓存技术。

    有点：不消耗集群的reduce资源；减少了reduce操作，加快程序执行；降低网络负载。

    缺点：占用部分内存，所以加载到内存中的表不能过大，因为每个计算节点都会加载一次；生成较多的小文件。

    执行方式：

• 自动方式，配置以下参数：hive自动根据sql，选择使用common join或者map join【set hive.auto.convert.join=true;hive.mapjoin.smalltable.filesize=25mb】
• 手动指定：select /*+mapjoin(n)*/ m.col,m.col2,n.col3 from m join n on m.col=n.col。注意/*+mapjoin(n)*/不能省略，只需替换表名n值即可。

    mapjoin的使用场景：

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

六、hive分桶JOIN

    对于每一个表或者分区，hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。

    hive是针对某一列进行分桶。hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶中。

    好处：获得更高的查询处理效率；使取样更高效。

create table bucketed_user(
   id  int,
   name  string
)clustered by (id) sorted by (name) into 4 buckets row format delimited fields terminated by '\t' stored as textfile;

set hive.enforce.bucketing=true;
分桶的使用
select * from bucketed_user tablesample(bucket 1 out of 2 on id)

BUCKET JOIN

set hive.optimize.bucketmapjoin=true;
set hive.optimize.bucketmapjoin.sortedmerge=true;
set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;

   连接两个在（包含连接列）相同列上划分了桶的表，可以使用Map端连接高效的实现。比如join操作。

   对于join操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那个将保存相同列值的痛进行join操作就可以，可以大大减少join的数据量。

    对于map端连接的情况，两个表以相同方式划分桶。处理左边表内某个桶的mpper知道右边表内相匹配的行在对应的桶内。因此，mapper只需要获取那个桶即可进行连接。这一优化方法并不一定要求表必须桶的个数相同，两个表的桶个数是倍数关系也可以。

七、distribute by、sort by

   distribute分散数据。distribute by col----按照col列把数据散列到不同的reduce。

   sort by排序，sort by col----按照col列把数据排序。

•  distribute by与group by对比。都是按照key值划分数据，都是用reduce操作，唯一不同的是distribute by只是单纯的分散数据，而group by把相同key的数据聚集到一起，后续必须是聚合操作。
• order by与sort by对比：order by是全局排序；sort by只是确保每个reduce上面输出的数据有序。如果只有一个reduce时，和order by作用一样。

八、cluster by

    把有相同值的数据聚集到一起，并排序。效果等价于distribute by col sort by col。cluster by col<==>distribute by col sort by col。