Hive的高级查询

发表于 | 分类于 | 阅读次数：

    本文主要是对Hive相关的高级查询做个总结，涉及排序相关关键字(order by(),sort by(),distribute by(),cluster by())的区别；group by col和partition by col的区别、distinct和group by去重的区别以及rank(),dense_rank()和row_number()的使用及他们的区别。

排序关键字的使用及区别

    order by 对查询结果集进行全局排序，所有的数据都通过一个Reduce处理，当数据量比较大，消耗较长的计算时间，可以指定desc降序，默认asc升序。在严格模式下使用order by 必须使用limit 进行限定。
    sort by不是全局排序，在数据进入Reducer之前进行排序，若Reduce Task > 1，只能保存每个Reducer的输出有序。可以指定desc降序，默认asc升序。
    distribute by是控制Map端如何拆分数据给Reduce端，hive会根据distribute by后面的列，对应Reduce的个数进行分发，默认采用hash算法。
    cluster by 具有distribute by和sort by的组合功能，但只能进行默认的升序排序。
    PS：1）distribute by和sort by配合使用，实现按某列降序排序。
      2) 全排序实现：先用sort by 保证每个reduce的输出有序，再进行order by对所有Reduce的输出进行归并，实现全局有序。

group by col和partition by col的区别

    group by col1在Reduce端运行，必须使用聚合函数，改变了数据的行数，只保留了分组聚合后的数据；partition by col不改变行数，配合order by 可以实现组内排序，不一定要使用聚合函数，如果col的值相同且使用了聚合函数，就重复显示聚合后的数据。如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- 创建表
create table result(
name string,
subject string,
score int
)
row format delimited
fields terminated by '\t';
-- 加载数据
load data local inpath 'sorce_data' into table result;
悟空    语文    87
悟空    数学    95
悟空    英语    68
八戒    语文    94
八戒    数学    56
八戒    英语    84
张飞    语文    64
张飞    数学    86
张飞    英语    84
小乔    语文    65
小乔    数学    85
小乔    英语    78
-- 计算每个人是三门成绩的和
select name,sum(score) from result group by name;
-- 查询结果
-- 八戒    234
-- 小乔    228
-- 张飞    234
-- 悟空    250
select name,sum(score) over(partition by name) from result;
-- 查询结果
-- 八戒    234
-- 八戒    234
-- 八戒    234
-- 小乔    228
-- 小乔    228
-- 小乔    228
-- 张飞    234
-- 张飞    234
-- 张飞    234
-- 悟空    250
-- 悟空    250
-- 悟空    250

distinct和group by去重时的区别

1
2
3
select count(distinct order_id) from order_snap;
Stage-Stage-1: Map: 402 Reduce: 1 Cumulative CPU: 7915.67 sec HDFS Read: 119072894175 HDFS Write: 10 SUCCESS
Time taken: 1820.864 seconds, Fetched: 1 row(s)

    默认情况下，distinct会将Map端所有数据shuffle到一个Reduce上进行去重，当数据量比较大时，耗时比较长。

1
2
3
4
select count(t.order_id) from (select order_id from order_snap group by order_id) t;
Stage-Stage-1: Map: 402 Reduce: 457 Cumulative CPU: 10056.7 sec HDFS Read: 119074266583 HDFS Write: 53469 SUCCESS
Stage-Stage-2: Map: 177 Reduce: 1 Cumulative CPU: 280.22 sec HDFS Read: 472596 HDFS Write: 10 SUCCESS
Time taken: 244.192 seconds, Fetched: 1 row(s)

    group by会将Map端所有数据shuffle多个Reduce上进行计算并行聚合，再通过count()聚合函数来进行统计。Hive在处理count()这种全聚和计算时，会忽略用户指定的Reduce Task数，强制使用 1 个Reduce Task。
总结：由以上两种查询方式所用的时间可知，先进行group by再聚合计算比distinct更加高效。

rank(),dense_rank()和row_number()的区别

    rank()函数，排序时，相同的值序号一样，下一个值跳着排序（并列名次占一位），最大序号 = 总数。实现组内排序。
    dense_rank()函数，排序时，相同的值序号一样的，下一个值跳着排序（并列名次不占位），最大序号 < 总数。实现组内排序。
    row_number()函数，排序时，相同的值序号连续，最大序号 = 总数。可以实现组内topk。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- 还是上面那个表
-- 计算每门科目的成绩排名(组内排序)
select *, rank() over(partition by subject order by score desc) rank,
dense_rank() over(partition by subject order by score desc) dense_rank,
row_number() over(partition by subject order by score desc) row_number
from result;
-- 查询结果
-- name  subject score  rank dense_rank row_number
-- 悟空    数学    95      1       1       1
-- 张飞    数学    86      2       2       2
-- 小乔    数学    85      3       3       3
-- 八戒    数学    56      4       4       4
-- 张飞    英语    84      1       1       1
-- 八戒    英语    84      1       1       2
-- 小乔    英语    78      3       2       3
-- 悟空    英语    68      4       3       4
-- 八戒    语文    94      1       1       1
-- 悟空    语文    87      2       2       2
-- 小乔    语文    65      3       3       3
-- 张飞    语文    64      4       4       4
-- 计算每门学科的前两名(组内topk)
select * from
(select *,row_number() over (partition by subject order by score desc) ranks from result) t
where t.ranks<=2;
-- 查询结果
-- name  subject score  ranks
-- 悟空    数学    95      1
-- 张飞    数学    86      2
-- 张飞    英语    84      1
-- 八戒    英语    84      2
-- 八戒    语文    94      1
-- 悟空    语文    87      2