【hive使用教程】3.2玩转Hive多维数据统计-grouping sets

最新推荐文章于 2026-02-25 01:19:48 发布

原创最新推荐文章于 2026-02-25 01:19:48 发布 · 3.5k 阅读

6 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

数据仓库

本文详细介绍了Hive中的GROUPING SETS、GROUPING__ID、CUBE、ROLLUP以及hive.new.job.grouping.set.cardinality参数的使用。通过实例展示了如何进行多维数据统计，强调了在使用CUBE和ROLLUP时对字段顺序的注意点，并分享了处理null值的实用技巧。

背景

一般的分组统计使用group by 完成，但是部分业务场景下，需要不同维度的数据存在在一张表中，即多维统计报表，如果使用group by进行计算，需要使用大量的union all完成，代码冗余比较多，Hive提供一种方便的方法，用来一次性完成这种统计

1、GROUPING SETS

grouping sets允许针对同一个数据集进行不同维度的统计，其原理类似于将不同的group by的结果通过union all 拼接在一起，下表即是使用grouping sets以及其对应的group by 等价写法

	GROUPING SETS	GROUP BY等价写法
1	SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS ( (a,b) )	SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b
2	SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS ( (a,b), a)	SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b UNION SELECT a, null, SUM( c ) FROM tab1 GROUP BY a
3	SELECT a,b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS (a,b)	SELECT a, null, SUM( c ) FROM tab1 GROUP BY a UNION SELECT null, b, SUM( c ) FROM tab1 GROUP BY b
4	SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS ( (a, b), a, b, ( ) )	SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b UNION SELECT a, null, SUM( c ) FROM tab1 GROUP BY a, null UNION SELECT null, b, SUM( c ) FROM tab1 GROUP BY null, b UNION SELECT null, null, SUM( c ) FROM tab1

2、GROUPING__ID

在我们使用grouping sets时，会产生null值，这个时候就会与数据记录中本身的null值混在一起而无法区分，Hive提供了一种标记的方法，可以用来识别这种情况，以具体的代码为例

SELECT nation_type,

gender,

GROUPING__ID,

count(1)

FROM zhidao.dwd_zhidao_carlife_asset_id_card_df

WHERE dt = '2018-07-25'

GROUP BY gender,

nation_type

GROUPING sets((nation_type,gender),(nation_type),(gender));

其中 grouping__id的生成机制是group by 后面内容的二进制对应的十进制值，即

(nation_type)=(nation_type,null)=(10)=2 代表group by 后面只有一个nation_type

(gender)=(null,gender)=(01)=1 代表group by 后面只有一个gender

(nation_type,gender)=(nation_type,gender)=(11)=3 代表group by 后面跟着nation_type,gender

3、Cube和Rollup

grouping sets实现了指定组合的多维分析，Cube则可以穷举全部可能，Rollup则是一种降维分析，代码演示如下：

SELECT nation_type,

gender,

GROUPING__ID,

count(1)

FROM zhidao.dwd_zhidao_carlife_asset_id_card_df

WHERE dt = '2018-07-25'

GROUP BY gender,

nation_type

WITH CUBE;

SELECT nation_type,

gender,

GROUPING__ID,

count(1)

FROM zhidao.dwd_zhidao_carlife_asset_id_card_df

WHERE dt = '2018-07-25'

GROUP BY gender,

nation_type

WITH ROLLUP;

可以看到，在group by 后面是gender,nation_type这种顺序下，CUBE实际实现的组合是((gender,nation_type),(gender),(nation_type),())，而Rollup实现的组合是((gender,nation_type),(gender),())，相当于Rollup没有了(nation_type)这种组合的统计，所以在使用Rollup时，一定要注意group by 后面字段的顺序

4、hive.new.job.grouping.set.cardinality参数

当维度过高，且统计语句中使用了 count distinct语句，就有可能出现如下报错

Error while compiling statement: FAILED: SemanticException [Error 10226]: An additional MR job is introduced since the cardinality of grouping sets is more than hive.new.job.grouping.set.cardinality. This functionality is not supported with distincts. Either set hive.new.job.grouping.set.cardinality to a high number (higher than the number of rows per input row due to grouping sets in the query), or rewrite the query to not use distincts. The number of rows per input row due to grouping sets is 256

此时可以通过以下语句突破这个限制

set hive.new.job.grouping.set.cardinality = 256;

其中256 为可以调节的数字，一般这个数字要大于你的维度的最高值，但是切记要慎用，一般维度最好不要超过7维，否则对集群压力会比较大