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。