【数仓开发】4-数仓多维模型构建
一.背景
数仓建设中经常会有多个维度灵活组合看数的需求，这种多维分析的场景一般有两种处理方式
-
即时查询
-
- 适合计算引擎很强，查询灵活，并发量不大的场景
- 数据链路：明细数据hive表-> MPP计算引擎
-
预计算
-
- 适合有固定模式的聚合查询。预计算的结果可以被不同下游复用
- 数据链路： 明细数据-> 离线计算引擎 -> 多维cube结果hive表 -> MPP计算引擎（presto）
二.维度爆炸&下游易用
“维度爆炸”指的是每增加一个维度，由于维度组合数翻倍，Cube的计算和存储量也会成倍增长。
作为典型的预计算MOLAP框架，kylin如何解决维度爆炸问题的呢
- 使用Partial Cube：通过聚合组进行剪枝，减少不必要的预计算组合
- 设置衍生维度：将能通过其他维度计算出来的维度（例如id/name）设置为衍生维度，减少不必要的预计算；
类似的，在Spark中可以对所有维度组合都进行预计算（ with cube 子句），
或者出于业务或者计算存储成本考虑只对一部分维度组合进行预计算（grouping sets 子句）。
grouping sets 方式剪枝后一定程度上可以避免维度爆炸，但还有以下痛点：
-
各方沟通成本高： brd、prd、技术方案、代码各自维护一套维度组合
-
- 需要大量时间和业务沟通、以确认维度组合
- 只能拿到无意义的groupingid， 在数据产品使用时，看不清目前具体有哪些维度组合，用户筛选不直观问题
-
维护成本高：SQL维护成本高，可能遗漏、重复
三.如何优化
示例：求各个维度组合的人数
维度组合： country 、 city 、 xian、zhen 、 is_good
- 统一使用-10000表示“全部”，不处理“全部“、”全国”等中文
select '中国' country , '北京' city , '昌平' xian ,'西北旺' zhen,1 is_good , 1000 p_num
1.grouping sets字句
8种维度组合
select
nvl(country,-1000) as country
,nvl(is_good,-1000) as is_good
,nvl(xian,-1000) as xian
,nvl(city,-1000) as city
,nvl(zhen,-1000) as zhen
,sum(p_num)
from
(
select '中国' country , '北京' city , '昌平' xian ,'西北旺' zhen,1 is_good , 1000 p_num
-- union all
-- select '中国' country , '河南' city , '光山' xian ,'十里' zhen,1 is_good , 50 p_num
) t
group by country,city,xian,zhen,is_good
grouping sets (
(country)
,(country,city)
,(country,xian)
,(country,xian,zhen)
,(country,is_good)
,(country,city,is_good)
,(country,xian,is_good)
,(country,xian,zhen,is_good)
)
运行结果：8条
| country | is_good | xian | city | zhen | _c5 |
|---|---|---|---|---|---|
| 中国 | 1 | 昌平 | -1000 | -1000 | 1000 |
| 中国 | -1000 | -1000 | -1000 | -1000 | 1000 |
| 中国 | 1 | -1000 | -1000 | -1000 | 1000 |
| 中国 | 1 | 昌平 | -1000 | 西北旺 | 1000 |
| 中国 | -1000 | -1000 | 北京 | -1000 | 1000 |
| 中国 | -1000 | 昌平 | -1000 | -1000 | 1000 |
| 中国 | 1 | -1000 | 北京 | -1000 | 1000 |
| 中国 | -1000 | 昌平 | -1000 | 西北旺 | 1000 |
2.lateral view + 自定义维度list
注：8种维度组合
通过lateral view 维度list 对数据做笛卡尔积，每条数据膨胀成8条维度组合不同的数据。
select
dims
,country
,is_good
,xian
,city
,zhen
,sum(p_num)
from
(
select
dims
,country --必选维度
,is_good
,if(find_in_set('县城', dims) > 0, xian, -10000) as xian
,if(find_in_set('城市', dims) > 0, city, -10000) as city
,if(find_in_set('城镇', dims) > 0, zhen, -10000) as zhen
,p_num
from
( select
array(is_good,-1000) as is_good_list
,country
,city
,xian
,zhen
,p_num
from
(
select '中国' country , '北京' city , '昌平' xian ,'西北旺' zhen,1 is_good , 1000 p_num
-- union all
-- select '中国' country , '河南' city , '光山' xian ,'十里' zhen,1 is_good , 50 p_num
)t1
)t2
lateral view explode(is_good_list) t1 as is_good
lateral view explode(array( '国家'
,'国家,县城'
,'国家,城市'
,'国家,城市,城镇'
)) t2 as dims
) tmp
group by dims,country,is_good,xian,city,zhen
运行结果：8条
| dims | country | is_good | xian | city | zhen | SUM(p_num) |
|---|---|---|---|---|---|---|
| 国家 | 中国 | 1 | -10000 | -10000 | -10000 | 1000 |
| 国家 | 中国 | -1000 | -10000 | -10000 | -10000 | 1000 |
| 国家_县城 | 中国 | 1 | 昌平 | -10000 | -10000 | 1000 |
| 国家_县城 | 中国 | -1000 | 昌平 | -10000 | -10000 | 1000 |
| 国家_城市 | 中国 | 1 | -10000 | 北京 | -10000 | 1000 |
| 国家_城市 | 中国 | -1000 | -10000 | 北京 | -10000 | 1000 |
| 国家城市城镇 | 中国 | 1 | -10000 | 北京 | 西北旺 | 1000 |
| 国家城市城镇 | 中国 | -1000 | -10000 | 北京 | 西北旺 | 1000 |
3.通过配置文件,维护维度list
配置表：conf_table
select
`version`
,dim1
,dim2
,dim3
,dim4
,dt
from (
select 'v1' as `version`,'国家' as dim1,'' as dim2 , '' dim3, '' dim4, '2022-07-05' as dt
union all
select 'v1' as `version`,'国家' as dim1,'县城' as dim2 , '' dim3, '' dim4, '2022-07-05' as dt
union all
select 'v1' as `version`,'国家' as dim1,'城市' as dim2 , '' dim3, '' dim4, '2022-07-05' as dt
union all
select 'v1' as `version`,'国家' as dim1,'城市' as dim2 , '城镇' dim3, '' dim4, '2022-07-05' as dt
) conf_table
sql
select
dims
,country
,is_good
,xian
,city
,zhen
,sum(p_num)
from
(
select
dims
,country --必选维度
,is_good
,if(find_in_set('县城', dims) > 0, xian, -10000) as xian
,if(find_in_set('城市', dims) > 0, city, -10000) as city
,if(find_in_set('城镇', dims) > 0, zhen, -10000) as zhen
,p_num
from
( select
array(is_good,-1000) as is_good_list
,country
,city
,xian
,zhen
,p_num
from
(
select '中国' country , '北京' city , '昌平' xian ,'西北旺' zhen,1 is_good , 1000 p_num
)t1
)t2
--配置表
left join (
select
concat_ws(',', array(dim1, dim2, dim3, dim4)) as dims
from (
select 'v1' as `version`,'国家' as dim1,'' as dim2 , '' dim3, '' dim4, '2022-07-05' as dt
union all
select 'v1' as `version`,'国家' as dim1,'县城' as dim2 , '' dim3, '' dim4, '2022-07-05' as dt
union all
select 'v1' as `version`,'国家' as dim1,'城市' as dim2 , '' dim3, '' dim4, '2022-07-05' as dt
union all
select 'v1' as `version`,'国家' as dim1,'城市' as dim2 , '城镇' dim3, '' dim4, '2022-07-05' as dt
) conf_table
where version = 'v1' and dt = '2022-07-05'
) conf_table
on 1 = 1 --笛卡尔积
lateral view explode(is_good_list) tmp as is_good
) tmp
group by dims,country,is_good,xian,city,zhen
;
| dims | country | is_good | xian | city | zhen | sum(p_num) |
|---|---|---|---|---|---|---|
| 国家,县城,, | 中国 | -1000 | 昌平 | -10000 | -10000 | 1000 |
| 国家,县城,, | 中国 | 1 | 昌平 | -10000 | -10000 | 1000 |
| 国家,城市,城镇, | 中国 | -1000 | -10000 | 北京 | 西北旺 | 1000 |
| 国家,城市,, | 中国 | 1 | -10000 | 北京 | -10000 | 1000 |
| 国家,,, | 中国 | -1000 | -10000 | -10000 | -10000 | 1000 |
| 国家,城市,, | 中国 | -1000 | -10000 | 北京 | -10000 | 1000 |
| 国家,,, | 中国 | 1 | -10000 | -10000 | -10000 | 1000 |
| 国家,城市,城镇, | 中国 | 1 | -10000 | 北京 | 西北旺 | 1000 |
