基于Hive的数据立方体实践-京东云开发者社区

### 1 引言

基于京东物流业务持续拓展、复杂化，业务侧人员（简称：业务侧）根据业务场景需要从数据角度做经营分析、运营分析或大促监控等。业务侧可以根据仓储、拣运、终端等场景涉及的业务动作、业务环节、业务过程等多维度分析一线操作动作是否标准、工作效率高低等。有些分析需要多个维度、有些分析甚至穷尽所有维度，统计的结果数据集，称之为数据立方体（Data Cube）。通常情况下，工程师思考的第一实现方式是union all，这很有可能会导致代码冗余。然而，通过Hive的高阶函数：with cube、grouping sets、with rollup，同样也可以达到union all的效果，且代码相对简单易维护。

### 2 方法简介

准备样例数据，创建表结构，并将数据加载到所创建的表中：

```sql
CREATE
	TABLE tmp.tmp_hivecube_test
	(
		`province` string COMMENT '省份',
		`city` string COMMENT '城市',
		`population` int COMMENT '人口数量'
	)
	ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
	STORED AS ORC tblproperties
	(
		'orc.compress' = 'SNAPPY'
	);
insert into tmp.tmp_hivecube_test
select '北京市','大兴区',260
union all
select '北京市','通州区',300
```

数据插入成功，查询数据

![](//img1.jcloudcs.com/developer.jdcloud.com/a3a52fd9-1869-43f5-b108-a16e7ba7365120220829142358.png)

#### 2.1 with cube简介

所有参与粒度统计的字段都要写在group by后面，with cube函数会将所有可能的粒度结果统计出来：N维数据模型，通过cube操作，可产生2的N次方种聚合方式。

要求统计各个维度的人口数据？

1）使用union all方式统计，代码如下：

```sql
select
       province
      ,city
      ,sum(population) 
  from tmp.tmp_hivecube_test
 group by 
       province
      ,city
union all
select
       province
      ,null city
      ,sum(population)
  from tmp.tmp_hivecube_test
 group by
       province
union all
select
       null province
      ,city
      ,sum(population)
  from tmp.tmp_hivecube_test
 group by 
       city
union all
select
       null province
      ,null city
      ,sum(population)
  from tmp.tmp_hivecube_test
```

统计结果如下：

![](//img1.jcloudcs.com/developer.jdcloud.com/2b61dcc3-9d37-4f46-b646-1a3508f0154020220829142429.png)

2）使用with cube方式，代码如下：

```sql

select
       province
      ,city
      ,sum(population)
  from tmp.tmp_hivecube_test
 group by
       province
      ,city
 with cube
```

统计结果如下：

![](//img1.jcloudcs.com/developer.jdcloud.com/af608db1-bb2c-4eaa-9736-94f0f323dc9c20220829142700.png)

观察对比可知，union all与with cube统计结果一致，但with cube代码风格简洁易读、便于开发维护。

#### 2.2 grouping sets简介

grouping sets可以灵活配置需要聚合的列名，按照用户需求维护聚合列的组合配置即可。假设需要（col1），（col1,col2）两种维度，配置在group by之后即可，不需要的维度不用配置。

要求统计北京市及各区人口数量？

1）使用union all方式统计，代码如下：

统计结果如下：

![](//img1.jcloudcs.com/developer.jdcloud.com/2c815b66-097d-448d-8ebe-89fafce3033f20220829143453.png)

2）使用grouping sets方式，代码如下：

```sql
select
       province
      ,city
      ,sum(population)
  from tmp.tmp_hivecube_test
 group by
       province
      ,city
  grouping sets((province),(province,city))
```

统计结果如下：

![](//img1.jcloudcs.com/developer.jdcloud.com/b7066c41-f0d6-4e12-9c1f-02150d7059ab20220829143551.png)

观察对比可知，union all与grouping sets统计结果一致，但grouping sets代码风格简洁易读、便于开发人员灵活配置统计维度。

注意：

1. grouping sets中的所有字段，必须出现在group by之中；
2. grouping sets可包含多种粒度，每种粒度单独使用在英文括号内部，不同粒度之间用逗号间隔

#### 2.3 with rollup简介

with rollup主要是针对不同粒度数据的聚合处理，从右到左逐层递增（递减）组合计算，不允许左侧为null右侧非null的情况出现。

要求统计北京市及各区人口数量？

1）使用union all方式统计，代码如下：

```sql
select
       null province
      ,null city
      ,sum(population)
  from tmp.tmp_hivecube_test
union all
select
       province
      ,null city
      ,sum(population)
  from tmp.tmp_hivecube_test
 group by
       province   
union all
select
       province
      ,city
      ,sum(population) 
  from tmp.tmp_hivecube_test
 group by 
       province
      ,city
```

统计结果如下：

![](//img1.jcloudcs.com/developer.jdcloud.com/4a506259-b756-4096-933f-a6f45f0e0b9b20220829142919.png)

2）使用with rollup方式，代码如下：

```
select
       province
      ,city
      ,sum(population)
  from tmp.tmp_hivecube_test
 group by
       province
      ,city
  with rollup
```

统计结果如下：

![](//img1.jcloudcs.com/developer.jdcloud.com/f4a5dea6-4392-49d7-aa0d-c646693bdcf720220829142949.png)

观察对比可知，union all与with rollup统计结果一致，但with rollup代码风格简洁易读，更适合按照层级递增（递减）方式聚合。

注意：with rollup对group by后的字段排列顺序要求非常严格，顺序不一样，统计结果也会不一样。

### 3 实践避坑

前文已介绍数据cube实践方法，而在实际开发过程中，用户想要不同维度组合信息做统计。可能用户前期不确定想要统计哪些维度，可将历史数据所有维度组合的指标存储起来，用户想要查看数据时，可用grouping__id（grouping与id用2个“_”连接）筛选预期数据。grouping__id是hive内置变量，可以和上文方法配合使用。grouping__id 返回对应于与行关联的 grouping 位向量的数字，在功能上等同于获取多个 grouping函数的结果并将它们连接成一个位向量（一串 1 和 0）。使用 grouping__id = n 的单个条件来识别所需的行，可以避免使用多个 grouping 函数并使行过滤条件更易于表达。

通过实例展示grouping_id使用方法，参考下图：

![](//img1.jcloudcs.com/developer.jdcloud.com/1de44418-3c8a-47ee-a9da-32e11ef2eb8a20220829143018.png)

了解grouping__id使用方法后，使用with cube方式统计北京市及各区人口数量，grouping_id值为0或2的数据需要剔除，如下图：

![](//img1.jcloudcs.com/developer.jdcloud.com/958d7bf4-a57b-455e-b05f-858cf4cdb61820220829143028.png)

查询结果并非预期结果，好像grouping__id in (0,1,2,3)的所有数据都被剔除了。查看执行计划：

![](//img1.jcloudcs.com/developer.jdcloud.com/5be64e00-d156-4753-ab06-b14c46b6472e20220829143041.png)

- 子查询外的where条件在第一个map中被执行
- cube执行前，grouping__id已经被赋值默认为‘0’

通过分析可得，由于grouping__id默认值为‘0’，所以在做条件过滤grouping__id in (1, 3)判断结果为false，导致没有输出结果。其实，这是hive本身执行计划优化导致，称为谓词下推。其是为了将过滤条件提前到子查询中，以达到在map端尽量缩减数据的目的，碰巧遇到grouping__id先赋默认值的内置方法。可以通过增加参数（SET hive.optimize.ppd = false;--默认值为true），关闭谓词下推，可以得到预期结果，参考下图：

![](//img1.jcloudcs.com/developer.jdcloud.com/2dd326cf-e9c7-468e-9005-de96445c903e20220829143059.png)

4 总结

在开发数据立方体实践过程中，可根据实际场景选择grouping方法：
- 在不确定维度组合情况下，推荐使用with cube 和 grouping__id搭配使用，灵活聚合
- 在已确定维度组合情况下，推荐使用grouping sets，有效节省存储空间
- 如果存在层级鲜明、大小粒度不交叉的情况下，推荐使用with rollup，灵活上卷、下钻

------------

###### 自猿其说Tech-JDL京东物流技术与数据智能部
###### 作者：崔显飞