Hive 增强聚合 GROUPING SETS,CUBE,ROLLUP

2,202

在进入正文之前,先来理解两个概念:钻取与上卷

  • 钻取(Drill-down):也称作下钻,通过增加维度来查看更进一步的明细数据,如在查看不同年份的销售总额,可以钻取查看每年所有月份的销售额。钻取旨在增加维度得到明细数据
  • 上卷(Roll-up):也称作上钻,与钻取相反,通过减少维度来查看进一步汇总数据,如在查看所有月份的销售总额,可上卷查看每年的销售额。上卷旨在减少维度得到汇总数据

GROUPING SETSCUBEROLLUP 这几个增强聚合函数常用于 OLAP 分析中需要根据不同维度上卷和钻取的指标统计,比如计算分小时、天、周、月的 UV

GROUPING SETS

GROUP BY 中的 GROUPING SETS 语句允许在同一结果集中指定多个 GROUP BY 选项,所有 GROUPING SET 语句都可以根据 UNION 连接的几个 GROUP BY 查询进行逻辑表示

下面列举了几个 SQL 的对应关系,主要看 GROUP BY 后的字段

GROUPING SETS 聚合查询 对等的 GROUP BY 聚合查询
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
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
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
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

聪明的我们发现 GROUPING SETS 指定的维度有多少个(括号括起来的算 1 个),对应的就有多少个普通的 GROUP BYUNION。另外,空白括号 () 表示计算总体聚合

CUBE 和 ROLLUP

GROUPING SETS 指定组合的维度;CUBE 则在其维度中创建列集合的所有可能组合的小计,一旦我们在一组维度上计算出 CUBE,就可以得到这些维度上所有可能的聚合问题的答案;而 ROLLUP 用于在维的层次结构级别上计算聚合,比如 GROUP by a,b,c with ROLLUP 的层次结构是 a 向下钻取到 b 向下钻取到 c,通俗地讲就是能从维度最左侧能一直钻取到维度最右侧

需要注意的是 CUBEROLLUP 仅与 GROUP BY 一起使用

来看一下 CUBEROLLUPGROUPING SETS 的对应关系

CUBE/ROLLUP GROUPING SETS
GROUP BY a, b, c WITH CUBE GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (b, c), (a, c), (a), (b), (c), ())
GROUP BY a, b, c, WITH ROLLUP GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (a), ())

可以发现,相对于 CUBEROLLUP 来说,GROUPING SETS 更加灵活,可以自由定义维度组合;但是,对于同样的结果集,CUBEROLLUP 写法更加简洁

Grouping__ID

在使用 GROUPING SETSCUBEROLLUP 时,还有一个 Grouping__ID 函数,它会表示结果集属于哪一个分组,同时,还能解决这样一个问题:多个维度聚合时,一些维度在结果集中会表示为 NULL,但如果原始数据集中的维度本身就有 NULL 值,就有可能发生冲突;如果为 NULL 的维度在聚合维度中,Grouping__ID 为 1,否则为 0

看个例子

Column1(key) Column2(value)
1 NULL
1 1
2 2
2 3
3 3
3 NULL
4 5

执行 SQL

SELECT key, value, GROUPING__ID, count(*) cnt
FROM T1
GROUP BY key, value WITH ROLLUP;

结果如下

Column1(key) Column2(value) GROUPING__ID cnt
NULL NULL 3 6
1 NULL 0 2
1 NULL 1 1
1 1 0 1
2 NULL 1 1
2 2 0 1
3 NULL 0 2
3 NULL 1 1
3 3 0 1
4 NULL 1 1
4 5 0 1

SQL 采取的是 ROLLUP 聚合,相当于 GROUP BY key, value GROUPING SETS ((key, value), key, ()),所以结果集中会有 3 组数据,一组是总体聚合,一组按 key 聚合,一组按 key, value 聚合。在源数据中我们发现存在 (1, NULL)(3, NULL) 两行数据,可以想象当按 key 聚合和按 key, value 聚合时结果集中维度的显示时一样的,这就出现了我们开头所说的冲突。再看 SQL 结果集,(1, NULL)(3, NULL) 都各自对应着两行数据,那如何区分哪一行数据属于哪一组聚合呢。看看它们的 GROUPING__ID 就知道了,GROUPING__ID 为 1 表示为 NULL 的维度在聚合维度中,也就是例子中的按 key, value 聚合;GROUPING__ID 为 0 表示为 NULL 的维度不在聚合维度中,也就是例子中的按 key 聚合

SQL 案例推荐阅读:Hive分析窗口函数(五) GROUPING SETS,GROUPING__ID,CUBE,ROLLUP

hive.new.job.grouping.set.cardinality

这个参数的意思是对于 GROUPING SETSCUBEROLLUP 聚合是否启动一个新的 Map-Reduce Job

对于像这样的查询:select a, b, c, count(1) from T group by a, b, c with rollup,每行会创建 4 行:(a,b,c),(a,b,null),(a,null,null),(null,null,null),如果表 T 的基数很大,可能导致 Map-Reduce 作业崩溃,并且 Map 端聚合效果不好



参考资料: Apache Hive Wiki 文档