多维度架构 多维度布局

目录

• 一.背景
• 二.维度爆炸&下游易用
• 三.如何优化

• 1.grouping sets字句
• 2.lateral view + 自定义维度list
• 3.通过配置文件,维护维度list

一.背景

数仓建设中经常会有多个维度灵活组合看数的需求，这种多维分析的场景一般有两种处理方式

• 即时查询

• 适合计算引擎很强，查询灵活，并发量不大的场景
• 数据链路：明细数据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