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 查询逻辑来表示。
如下一些常见的等价替换示例：
-- 语句1
select 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
-- 语句2
select 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,b
union
select a,null ,sum(c) from tbl group by a
-- 语句3
select a,b,sum(c) from tbl group by a,b grouping sets(a,b)
-- 相当于
select a,null,sum(c) from tbl group by a
union
select null ,b,sum(c) from tbl group by b
-- 语句4
select 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,b
union
select a,null,sum(c) from tbl group by a
union
select null,b,sum(c) from tbl group by b
union
select 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,c
grouping 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 s
grouping 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 查看其执行计划：
STAGE DEPENDENCIES:
Stage-1 is a root stage
Stage-0 depends on stages: Stage-1
STAGE 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 )
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;
explain
select gender,age,count(0) as num from temp.user_info_all
where ymd = '20230505'
and age 注：grouping sets后进行分组的列一定要在之前的group by中进行申明。
STAGE DEPENDENCIES:
Stage-1 is a root stage
Stage-0 depends on stages: Stage-1
STAGE 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 通过以上两个例子可以看出hive执行计划中没有具体的高级分组聚合如何实现分组方案。两者执行方式基本上差不多。
在数据扫描和查询上的确减少了多次数据扫描和数据io操作。在一定程度上节省了计算资源。
例3 使用cube替代grouping sets 。
set hive.map.aggr=true;
explain
select gender,age,count(0) as num from temp.user_info_all
where ymd = '20230505'
and age STAGE DEPENDENCIES:
Stage-1 is a root stage
Stage-0 depends on stages: Stage-1
STAGE 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 以上例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，随机赠送一本鲁边备注版珍藏大数据书籍。
服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.fwqtg.net
哈喽大家好，我是咸鱼 随着互联网技术的发展，分布式架构越来越被人们所采用。在分布式架构下，为了实现复杂的业务逻辑，应用程序需要分布式通信实现远程调用 而这时候就需要一种协议来支持远程过程调用，以便实现不同应用程序之间的数据交换和信息传递。其中常用的协议包括 H…
