ToB企服应用市场:ToB评测及商务社交产业平台

标题: 什么是hive的高级分组聚合，它的用法和注意事项以及性能分析 [打印本页]

---

作者: 写过一篇    时间: 2023-6-30 01:18
标题: 什么是hive的高级分组聚合，它的用法和注意事项以及性能分析
hive的高级分组聚合是指在聚合时使用GROUPING SETS、CUBE和ROLLUP的分组聚合。
高级分组聚合在很多数据库类SQL中都有出现，并非hive独有，这里只说明hive中的情况。
使用高级分组聚合不仅可以简化SQL语句，而且通常情况下会提升SQL语句的性能。
1.Grouping sets 的使用

示例：

1. -- 使用方式
2. select a,b,sum(c) from tbl group by a,b grouping sets(a,b)

复制代码

Grouping sets的子句允许在一个group by 语句中，指定多个分组聚合列。所有含有Grouping sets 的子句都可以用union连接的多个group by 查询逻辑来表示。
如下一些常见的等价替换示例：

1. -- 语句1
2. select a, b sum(c) from tbl group by a,b grouping sets((a,b))
3. -- 相当于
4. select a,b,sum(c) from tbl group by a,b
6. -- 语句2
7. select a,b,sum(c) from tbl group by a,b grouping sets((a,b),a)
8. -- 相当于
9. select a,b,sum(c) from tbl group by a,b
10. union
11. select a,null ,sum(c) from tbl group by a
13. -- 语句3
14. select a,b,sum(c) from tbl group by a,b grouping sets(a,b)
15. -- 相当于
16. select a,null,sum(c) from tbl group by a
17. union
18. select null ,b,sum(c) from tbl group by b
20. -- 语句4
21. select a,b,sum(c) from tbl group by a,b grouping sets((a,b),a,b,())
22. -- 相当于
23. select a,b,sum(c) from tbl group by a,b
24. union
25. select a,null,sum(c) from tbl group by a
26. union
27. select null,b,sum(c) from tbl group by b
28. union
29. select null,null,sum(c) from tbl

复制代码

可以看到通过等价替换的改写之后，语句会变得简洁，性能我们之后分析。
2.cube 和rollup的使用

示例：

1. -- cube使用示例
2. select a,b,c,count(1) from tbl group by a,b,c with cube
3. -- rollup使用示例
4. select a,b,c,count(1) from tbl group by a,b,c with rollup

复制代码

用法说明：
以上两个高级分组函数都可以在一个group by 语句中完成多个分组聚合，它们都可以用grouping sets来等价替换。

• cube 会计算所有group by 列的所有组合
  

1. -- cube语句
2. select a,b,c,count(1) from tbl group by a,b,c with cube
3. -- 相当于
4. select a,b,c count(1) from tbl group by a,b,c
5. grouping sets((a,b,c),(a,b),(b,c),(a,c),(a),(b),(c),())

复制代码

• rollup 会按照group by 指定的列从左到右进行分组聚合
  

1. -- rollup语句 滚动式聚合
2. select a,b,c,count(1) from tbl group by a,b,c with rollup
3. -- 相当于
4. select a,b,c,count(1) from tbl group by a,b,c s
5. grouping sets((a,b,c),(a,b),(a),())

复制代码

3.使用高级分组聚合函数的性能分析

我们可以通过执行计划的执行来分析高级分组聚合SQL语句的执行过程，比对其优化的节点。
例1 含grouping sets关键词的SQL执行案例。

1. set hive.map.aggr=true;
2. explain
3. -- 小于30岁人群的不同性别平均年龄
4. select gender,avg(age) as avg_age from temp.user_info_all where ymd = '20230505'
5. and age < 30
6. group by gender;
8. -- 将以上语句改为grouping sets关键词执行语句
9. set hive.map.aggr=true;
10. explain
11. select gender,avg(age) as num from temp.user_info_all
12. where ymd = '20230505'
13. and age < 30
14. group by gender grouping sets((gender));

复制代码

查看其执行计划：

1. STAGE DEPENDENCIES:
2.   Stage-1 is a root stage
3.   Stage-0 depends on stages: Stage-1
5. STAGE PLANS:
6.   Stage: Stage-1
7.     Map Reduce
8.       Map Operator Tree:
9.           TableScan
10.             alias: user_info_all
11.             Statistics: Num rows: 32634295 Data size: 783223080 Basic stats: COMPLETE Column stats: NONE
12.             Filter Operator
13.               predicate: (age < 30) (type: boolean)
14.               Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE
15.               Group By Operator
16.                 aggregations: avg(age)
17.                 keys: gender (type: int), 0 (type: int)
18.                 mode: hash
19.                 outputColumnNames: _col0, _col1, _col2
20.                 Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE
21.                 Reduce Output Operator
22.                   key expressions: _col0 (type: int), _col1 (type: int)
23.                   sort order: ++
24.                   Map-reduce partition columns: _col0 (type: int), _col1 (type: int)
25.                   Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE
26.                   value expressions: _col2 (type: struct<count:bigint,sum:double,input:bigint>)
27.       Reduce Operator Tree:
28.         Group By Operator
29.           aggregations: avg(VALUE._col0)
30.           keys: KEY._col0 (type: int), KEY._col1 (type: int)
31.           mode: mergepartial
32.           outputColumnNames: _col0, _col2
33.           Statistics: Num rows: 5439049 Data size: 130537176 Basic stats: COMPLETE Column stats: NONE
34.           pruneGroupingSetId: true
35.           Select Operator
36.             expressions: _col0 (type: int), _col2 (type: double)
37.             outputColumnNames: _col0, _col1
38.             Statistics: Num rows: 5439049 Data size: 130537176 Basic stats: COMPLETE Column stats: NONE
39.             File Output Operator
40.               compressed: true
41.               Statistics: Num rows: 5439049 Data size: 130537176 Basic stats: COMPLETE Column stats: NONE
42.               table:
43.                   input format: org.apache.hadoop.mapred.SequenceFileInputFormat
44.                   output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
45.                   serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
47.   Stage: Stage-0
48.     Fetch Operator
49.       limit: -1
50.       Processor Tree:
51.         ListSink

复制代码

对以上内容进行关键字解读：
map阶段：

• Group By Operator ：Map端开启聚合操作
  
• aggregations：分组聚合的算法，该案例采取avg(age)
  
• keys: 这里是分组列+ 一个固定列 0
  
• mode:Hash
  
• outputColumnNames:最终输出三列。_col0, _col1, _col2
  
• Reduce Output Operator：该阶段为map阶段聚合后的操作
  
• key expressions：map端最终输出的key，该例为gender和0两列。
  
• sort order：输出两列都正序排序
  
• Map-reduce partition columns：表示Map阶段数据输出的分区列，该案例为gender和0两列进行分区。
  
• value expressions:map端最终输出value，为一个结构体。
  

Reduce阶段：

• Group By Operator：reduce阶段的分组聚合操作。
  
• aggregations: 分组聚合算法，avg(VALUE._col0)表示对map阶段输出的 value expressions的 _col0取平均值。
  
• keys:指定分组聚合的key，有两列。为map阶段输出的key。
  
• mode: mergepartial
  
• outputColumnNames: 表示最终输出的列，该例为gender和num。
  
• pruneGroupingSetId: 表示是否对最终输出的grouping id进行修剪，如果为true，则表示将keys最后一列抛弃。案例中为0列。
  
• Select Operator：进行列投影操作。
  
• expressions:输出的列。gender和num。
  

通过查看以上的执行计划，可以看出在使用含有grouping sets语句的SQL中，hive执行计划并没有给出具体的实现细节。
再执行具有多个聚合列的实例来看看：
例2 聚合年龄和聚合性别多列合并测试。

1. set hive.map.aggr=true;
2. explain
3. select gender,age,count(0) as num from temp.user_info_all
4. where ymd = '20230505'
5. and age < 30
6. group by gender,age grouping sets(gender,age);

复制代码

注：grouping sets后进行分组的列一定要在之前的group by中进行申明。

1. STAGE DEPENDENCIES:
2.   Stage-1 is a root stage
3.   Stage-0 depends on stages: Stage-1
5. STAGE PLANS:
6.   Stage: Stage-1
7.     Map Reduce
8.       Map Operator Tree:
9.           TableScan
10.             alias: user_info_all
11.             Statistics: Num rows: 32634295 Data size: 783223080 Basic stats: COMPLETE Column stats: NONE
12.             Filter Operator
13.               predicate: (age < 30) (type: boolean)
14.               Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE
15.               Group By Operator
16.                 aggregations: count(0)
17.                 keys: gender (type: int), age (type: bigint), 0 (type: int)
18.                 mode: hash
19.                 outputColumnNames: _col0, _col1, _col2, _col3
20.                 Statistics: Num rows: 21756196 Data size: 522148704 Basic stats: COMPLETE Column stats: NONE
21.                 Reduce Output Operator
22.                   key expressions: _col0 (type: int), _col1 (type: bigint), _col2 (type: int)
23.                   sort order: +++
24.                   Map-reduce partition columns: _col0 (type: int), _col1 (type: bigint), _col2 (type: int)
25.                   Statistics: Num rows: 21756196 Data size: 522148704 Basic stats: COMPLETE Column stats: NONE
26.                   value expressions: _col3 (type: bigint)
27.       Reduce Operator Tree:
28.         Group By Operator
29.           aggregations: count(VALUE._col0)
30.           keys: KEY._col0 (type: int), KEY._col1 (type: bigint), KEY._col2 (type: int)
31.           mode: mergepartial
32.           outputColumnNames: _col0, _col1, _col3
33.           Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE
34.           pruneGroupingSetId: true
35.           Select Operator
36.             expressions: _col0 (type: int), _col1 (type: bigint), _col3 (type: bigint)
37.             outputColumnNames: _col0, _col1, _col2
38.             Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE
39.             File Output Operator
40.               compressed: true
41.               Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE
42.               table:
43.                   input format: org.apache.hadoop.mapred.SequenceFileInputFormat
44.                   output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
45.                   serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
47.   Stage: Stage-0
48.     Fetch Operator
49.       limit: -1
50.       Processor Tree:
51.         ListSink

复制代码

通过以上两个例子可以看出hive执行计划中没有具体的高级分组聚合如何实现分组方案。两者执行方式基本上差不多。
在数据扫描和查询上的确减少了多次数据扫描和数据io操作。在一定程度上节省了计算资源。
例3 使用cube替代grouping sets 。

1. set hive.map.aggr=true;
2. explain
3. select gender,age,count(0) as num from temp.user_info_all
4. where ymd = '20230505'
5. and age < 30
6. group by gender,age with cube;
8. -- 等价语句
9. select gender,age,count(0) as num from temp.user_info_all
10. where ymd = '20230505'
11. and age < 30
12. group by gender,age grouping sets((gender,age),(gender),(age),());

复制代码

1. STAGE DEPENDENCIES:
2.   Stage-1 is a root stage
3.   Stage-0 depends on stages: Stage-1
5. STAGE PLANS:
6.   Stage: Stage-1
7.     Map Reduce
8.       Map Operator Tree:
9.           TableScan
10.             alias: user_info_all
11.             Statistics: Num rows: 32634295 Data size: 783223080 Basic stats: COMPLETE Column stats: NONE
12.             Filter Operator
13.               predicate: (age < 30) (type: boolean)
14.               Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE
15.               Group By Operator
16.                 aggregations: count(0)
17.                 keys: gender (type: int), age (type: bigint), 0 (type: int)
18.                 mode: hash
19.                 outputColumnNames: _col0, _col1, _col2, _col3
20.                 Statistics: Num rows: 43512392 Data size: 1044297408 Basic stats: COMPLETE Column stats: NONE
21.                 Reduce Output Operator
22.                   key expressions: _col0 (type: int), _col1 (type: bigint), _col2 (type: int)
23.                   sort order: +++
24.                   Map-reduce partition columns: _col0 (type: int), _col1 (type: bigint), _col2 (type: int)
25.                   Statistics: Num rows: 43512392 Data size: 1044297408 Basic stats: COMPLETE Column stats: NONE
26.                   value expressions: _col3 (type: bigint)
27.       Reduce Operator Tree:
28.         Group By Operator
29.           aggregations: count(VALUE._col0)
30.           keys: KEY._col0 (type: int), KEY._col1 (type: bigint), KEY._col2 (type: int)
31.           mode: mergepartial
32.           outputColumnNames: _col0, _col1, _col3
33.           Statistics: Num rows: 21756196 Data size: 522148704 Basic stats: COMPLETE Column stats: NONE
34.           pruneGroupingSetId: true
35.           Select Operator
36.             expressions: _col0 (type: int), _col1 (type: bigint), _col3 (type: bigint)
37.             outputColumnNames: _col0, _col1, _col2
38.             Statistics: Num rows: 21756196 Data size: 522148704 Basic stats: COMPLETE Column stats: NONE
39.             File Output Operator
40.               compressed: true
41.               Statistics: Num rows: 21756196 Data size: 522148704 Basic stats: COMPLETE Column stats: NONE
42.               table:
43.                   input format: org.apache.hadoop.mapred.SequenceFileInputFormat
44.                   output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
45.                   serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
47.   Stage: Stage-0
48.     Fetch Operator
49.       limit: -1
50.       Processor Tree:
51.         ListSink

复制代码

以上例3 cube语句和例2语句输出数据完全是不一样的。但其输出执行计划内容基本和例2一致。可以看出hive的执行计划对高级分组聚合拆分执行计划的支持还不是很好。
使用高级分组聚合，要注意开启map端聚合模式。
使用高级分组聚合，如上案例，仅使用一个作业就能够实现union写法需要多个作业才能实现的逻辑。
从这点上来看能够减少多个作业在磁盘和网络I/O时的负担，是一种优化。
但是同时也要注意因过度使用高级分组聚合语句而导致的数据急速膨胀问题。

• 通常使用简单的group by 语句，一份数据只有一种聚合结果，一个分组聚合通常只有一个记录；
  
• 使用高级分组聚合，例如cube，在一个作业中一份数据会存在多种聚合情况，最终输出是，每种聚合情况各自对应一条数据。
  

注意事项：
如果使用高级分组聚合的语句处理的底表，在数据量很大的情况下容易导致Map或者Reduce任务因硬件资源不足而崩溃。
hive中使用hive.new.job.grouping.set.cardinality 配置项来应对以上情况。
如果SQL语句中处理分组聚合情况超过该配置项指定的值，默认值为（30），则会创建一个新的作业。
下一期：hive窗口分析函数解读以及带窗口分析函数的SQL性能分析
按例，欢迎点击此处关注我的个人公众号，交流更多知识。
后台回复关键字 hive，随机赠送一本鲁边备注版珍藏大数据书籍。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

---

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)

Powered by Discuz! X3.4