Hive - Cube, Rollup, GroupingId 示例与详解

一.引言

上篇文章讲到了 Grouping Sets 的使用方法，Grouping Sets 可以看做是将 group by 的内容进行 union 整合，这篇文章将基于同一思想进行扩展介绍两个方法 Cube 以及 Rollup，同时给出辅助函数 GroupingId 的生成方法与使用方法。

编辑

1 0 00s 100 20220505
2 1 90s 200 20220505
3 1 00s 300 20220505
4 1 70s 400 20220505
5 0 60s 500 20220505
6 0 80s 600 20220505
7 1 80s 700 20220505
8 0 60s 800 20220505
9 0 70s 900 20220505
10  1 10s 1000  20220505

下述示例基于上述 hive 表结构与内容。

二.Cube

1.Cube 解释

cube 立方体，也翻译为多维数据集，给定 group by key A,B,C，grouping sets 会根据 sets 中给定的 key 组合进行 union ，而 cube 则会将对应 key 的所有子集都进行 group by 并 union。

Cube 语法:

hive -e "select A,B,C,count(*) from table group by A,B,C with cube;"

等价 Grouping Sets 语法:

hive -e "select A,B,C,count(*) from table group by A,B,C grouping sets
((), (C), (B), (B, C), (A), (A, C), (A, B), (A, B, C))"

可以看到 grouping sets 内的组合可以看做 [A,B,C] 的全部子集，关于如何求列表子集可以参考：Python 求数组子集。

2.Cube 示例

对 user_gender，user_age 求 cube，等价于 grouping sets((), (user_gender), (user_age),(user_gender, user_age)) 即 subSet[A, B] = [[], [A], [B], [A,B]]：

hive -e "select user_gender,user_age,sum(buy_cost) from user_purchasing_info 
group by user_gender,user_age with cube;"

编辑

上图红框对应 ()，蓝框对应 (user_age)，绿框对应 (user_age, user_gender) ，其余对应 (user_gender)。

三.Rollup

1.Rollup 解释

rollup 译为归纳、卷曲，对应 hive 函数含义为对指定 group by 的 key 进行从右到左的递减 group by 随后进行 union，例如给定 group by key A,B,C， rollup 等价于 grouping sets ((A,B,C), (A,B), (A), ())。

Rollup 语法:

hive -e "select A,B,C,count(*) from table group by A,B,C with rollup;"

等价 Grouping Sets 语法:

hive -e "select A,B,C,count(*) from table group by A,B,C grouping sets
((A,B,C), (A,B), (A), ())"

需要注意递减顺序是从右向左。

2.Rollup 示例

对 user_gender, user_age 使用 rollup 方法，对应 grouping sets 为 ((user_gender, user_age), (user_gender), ())

hive -e "select user_gender,user_age,sum(buy_cost) from user_purchasing_info 
group by user_gender,user_age with rollup;"

编辑

其中红框对应 () 即全部求和，蓝框对应 (user_gender, user_age)，其余两行代表 (user_gender)。

四.Grouping__Id

1.Grouping__Id 解释与示例

GroupingId 顾名思义这个变量是用来表示每个组对应的 id，Grouping Sets、Cube、Rollup 都涉及到对多个 key 的组合进行 group by 随后 union，上面我们都是通过标颜色框的形式查看每一行归属于那一类，Grouping__Id 是 hive 内置变量，其可以搭配 Grouping Sets、Cube、Rollup 方法使用并指出对应内容的组别：

hive -e "select grouping__id,
user_gender,user_age,sum(buy_cost) from user_purchasing_info 
group by user_gender,user_age with cube;"

这里需要注意 grouping__id 中间的下划线为 '_' x 2，并不是单独的 '_'，否则会报语法错误：

FAILED: SemanticException [Error 10025]: Line 1:7 Expression not in GROUP BY 
key 'grouping_id'

              编辑               编辑

上图分别是 cube 还有 cube + grouping__id 得到的结果，可以看到相同 group 的内容拥有相同的 Grouping__Id。

2.Grouping__Id 生成方法

上述使用方法为 cube，其中 group by key 的顺序为 user_gender, user_age，下面看一下每个分组的 grouping__id 如何生成：

根据上图我们可以看出 Grouping__Id 的生成规则，将 group by 后的字段逆序，对于每一类 group by 得到的结果，如果该 key 参与 group by 则赋值1，否则赋值0，最后将本次 group by 的 key 对应的 0、1 赋值连起来组成二进制并转化为10进制即为该 group 的 Grouping__Id。

Grouping_Id 2 生成示例:

A.将 user_gender，user_age 逆序得到 user_age，user_gender

B.2组 group by 使用的 key 为 user_gender，分别赋值并连接得到 10

C.将 10 转换为 十进制: 1*2^{1} + 0*2{0} = 2 = Grouping__Id

Tips:

除了 Cube 之外， Rollup、Grouping Sets 也支持使用内置变量 Grouping__Id。