什么是hive的高级分组聚合，它的用法和注意事项以及性能分析

hive的高级分组聚合是指在聚合时使用GROUPING SETS、CUBE和ROLLUP的分组聚合。

高级分组聚合在很多数据库类SQL中都有出现，并非hive独有，这里只说明hive中的情况。

使用高级分组聚合不仅可以简化SQL语句，而且通常情况下会提升SQL语句的性能。

1.Grouping sets 的使用

示例：

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

Grouping sets的子句允许在一个group by 语句中，指定多个分组聚合列。所有含有Grouping sets 的子句都可以用union连接的多个group by 查询逻辑来表示。

如下一些常见的等价替换示例：

-- 语句1select a, b sum(c) from tbl group by a,b grouping sets((a,b))-- 相当于 select a,b,sum(c) from tbl group by a,b-- 语句2select a,b,sum(c) from tbl group by a,b grouping sets((a,b),a)-- 相当于select a,b,sum(c) from tbl group by a,bunionselect a,null ,sum(c) from tbl group by a-- 语句3select a,b,sum(c) from tbl group by a,b grouping sets(a,b)-- 相当于select a,null,sum(c) from tbl group by aunionselect null ,b,sum(c) from tbl group by b-- 语句4select a,b,sum(c) from tbl group by a,b grouping sets((a,b),a,b,())-- 相当于select a,b,sum(c) from tbl group by a,bunionselect a,null,sum(c) from tbl group by aunionselect null,b,sum(c) from tbl group by bunionselect null,null,sum(c) from tbl

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

2.cube 和rollup的使用

示例：

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

用法说明：

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

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

-- cube语句select a,b,c,count(1) from tbl group by a,b,c with cube-- 相当于select a,b,c count(1) from tbl group by a,b,cgrouping sets((a,b,c),(a,b),(b,c),(a,c),(a),(b),(c),())

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

-- rollup语句 滚动式聚合select a,b,c,count(1) from tbl group by a,b,c with rollup-- 相当于select a,b,c,count(1) from tbl group by a,b,c sgrouping sets((a,b,c),(a,b),(a),())

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

我们可以通过执行计划的执行来分析高级分组聚合SQL语句的执行过程，比对其优化的节点。

例1 含grouping sets关键词的SQL执行案例。

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

查看其执行计划：

STAGE DEPENDENCIES:  Stage-1 is a root stage  Stage-0 depends on stages: Stage-1STAGE PLANS:  Stage: Stage-1    Map Reduce      Map Operator Tree:          TableScan            alias: user_info_all            Statistics: Num rows: 32634295 Data size: 783223080 Basic stats: COMPLETE Column stats: NONE            Filter Operator              predicate: (age < 30) (type: boolean)              Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE              Group By Operator                aggregations: avg(age)                keys: gender (type: int), 0 (type: int)                mode: hash                outputColumnNames: _col0, _col1, _col2                Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE                Reduce Output Operator                  key expressions: _col0 (type: int), _col1 (type: int)                  sort order: ++                  Map-reduce partition columns: _col0 (type: int), _col1 (type: int)                  Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE                  value expressions: _col2 (type: struct)      Reduce Operator Tree:        Group By Operator          aggregations: avg(VALUE._col0)          keys: KEY._col0 (type: int), KEY._col1 (type: int)          mode: mergepartial          outputColumnNames: _col0, _col2          Statistics: Num rows: 5439049 Data size: 130537176 Basic stats: COMPLETE Column stats: NONE          pruneGroupingSetId: true          Select Operator            expressions: _col0 (type: int), _col2 (type: double)            outputColumnNames: _col0, _col1            Statistics: Num rows: 5439049 Data size: 130537176 Basic stats: COMPLETE Column stats: NONE            File Output Operator              compressed: true              Statistics: Num rows: 5439049 Data size: 130537176 Basic stats: COMPLETE Column stats: NONE              table:                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat                  serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe  Stage: Stage-0    Fetch Operator      limit: -1      Processor Tree:        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 聚合年龄和聚合性别多列合并测试。

set hive.map.aggr=true;explainselect gender,age,count(0) as num from temp.user_info_all where ymd = '20230505'and age < 30 group by gender,age grouping sets(gender,age);

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

STAGE DEPENDENCIES:  Stage-1 is a root stage  Stage-0 depends on stages: Stage-1STAGE PLANS:  Stage: Stage-1    Map Reduce      Map Operator Tree:          TableScan            alias: user_info_all            Statistics: Num rows: 32634295 Data size: 783223080 Basic stats: COMPLETE Column stats: NONE            Filter Operator              predicate: (age < 30) (type: boolean)              Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE              Group By Operator                aggregations: count(0)                keys: gender (type: int), age (type: bigint), 0 (type: int)                mode: hash                outputColumnNames: _col0, _col1, _col2, _col3                Statistics: Num rows: 21756196 Data size: 522148704 Basic stats: COMPLETE Column stats: NONE                Reduce Output Operator                  key expressions: _col0 (type: int), _col1 (type: bigint), _col2 (type: int)                  sort order: +++                  Map-reduce partition columns: _col0 (type: int), _col1 (type: bigint), _col2 (type: int)                  Statistics: Num rows: 21756196 Data size: 522148704 Basic stats: COMPLETE Column stats: NONE                  value expressions: _col3 (type: bigint)      Reduce Operator Tree:        Group By Operator          aggregations: count(VALUE._col0)          keys: KEY._col0 (type: int), KEY._col1 (type: bigint), KEY._col2 (type: int)          mode: mergepartial          outputColumnNames: _col0, _col1, _col3          Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE          pruneGroupingSetId: true          Select Operator            expressions: _col0 (type: int), _col1 (type: bigint), _col3 (type: bigint)            outputColumnNames: _col0, _col1, _col2            Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE            File Output Operator              compressed: true              Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE              table:                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat                  serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe  Stage: Stage-0    Fetch Operator      limit: -1      Processor Tree:        ListSink

通过以上两个例子可以看出hive执行计划中没有具体的高级分组聚合如何实现分组方案。两者执行方式基本上差不多。

在数据扫描和查询上的确减少了多次数据扫描和数据io操作。在一定程度上节省了计算资源。

例3 使用cube替代grouping sets 。

set hive.map.aggr=true;explainselect gender,age,count(0) as num from temp.user_info_all where ymd = '20230505'and age < 30 group by gender,age with cube;-- 等价语句select gender,age,count(0) as num from temp.user_info_all where ymd = '20230505'and age < 30 group by gender,age grouping sets((gender,age),(gender),(age),());

STAGE DEPENDENCIES:  Stage-1 is a root stage  Stage-0 depends on stages: Stage-1STAGE PLANS:  Stage: Stage-1    Map Reduce      Map Operator Tree:          TableScan            alias: user_info_all            Statistics: Num rows: 32634295 Data size: 783223080 Basic stats: COMPLETE Column stats: NONE            Filter Operator              predicate: (age < 30) (type: boolean)              Statistics: Num rows: 10878098 Data size: 261074352 Basic stats: COMPLETE Column stats: NONE              Group By Operator                aggregations: count(0)                keys: gender (type: int), age (type: bigint), 0 (type: int)                mode: hash                outputColumnNames: _col0, _col1, _col2, _col3                Statistics: Num rows: 43512392 Data size: 1044297408 Basic stats: COMPLETE Column stats: NONE                Reduce Output Operator                  key expressions: _col0 (type: int), _col1 (type: bigint), _col2 (type: int)                  sort order: +++                  Map-reduce partition columns: _col0 (type: int), _col1 (type: bigint), _col2 (type: int)                  Statistics: Num rows: 43512392 Data size: 1044297408 Basic stats: COMPLETE Column stats: NONE                  value expressions: _col3 (type: bigint)      Reduce Operator Tree:        Group By Operator          aggregations: count(VALUE._col0)          keys: KEY._col0 (type: int), KEY._col1 (type: bigint), KEY._col2 (type: int)          mode: mergepartial          outputColumnNames: _col0, _col1, _col3          Statistics: Num rows: 21756196 Data size: 522148704 Basic stats: COMPLETE Column stats: NONE          pruneGroupingSetId: true          Select Operator            expressions: _col0 (type: int), _col1 (type: bigint), _col3 (type: bigint)            outputColumnNames: _col0, _col1, _col2            Statistics: Num rows: 21756196 Data size: 522148704 Basic stats: COMPLETE Column stats: NONE            File Output Operator              compressed: true              Statistics: Num rows: 21756196 Data size: 522148704 Basic stats: COMPLETE Column stats: NONE              table:                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat                  serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe  Stage: Stage-0    Fetch Operator      limit: -1      Processor Tree:        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，随机赠送一本鲁边备注版珍藏大数据书籍。