一、背景
odps是阿里集团的大数据计算平台，odps-sql语法类似于hive。
最近做了一个***项目，需求中用到了大量的维度交叉，等到需求实现后却发现新的问题——cube的交叉维度太多了（最初有17个），而且指标的计算逻辑比较复杂，造成数据加工太慢了，举例1天的增量表大概8个小时，需求中还有自然月和30天时间粒度的，根本没法实现，因此投入大量时间做了优化，最终结果：11个维度，1天表的加工时间是30分钟，7日表1个小时，30天表3个小时。
二、两种SQL方式
实现聚合查询有两种方式，一种是使用LATERAL VIEW + explode 将数据按某个维度扩张，另一种是使用grouping sets、rollup、cube的用法。
1.LATERAL VIEW + explode方法
explode函数用于列转行，explode(array)会将array数组的值拆成多行数据，配合LATERAL VIEW方法可以将源数据按某个维度复制，其中一份保持原样，另一份的指定维度置为固定值(比如“总计”)，这样再做group聚合时就可以得到聚合数据。使用本方法的优点是如果某个维度需要特殊处理，会比较方便；缺点就是代码比较冗余，而且执行效率不如grouping高。
使用本方法map输入数据可能会产生较多的小文件，可以通过调节参数来加速。
SELECT cont_type,sub_cont_type,
media_type_name,media_name,show_type,sold_type,
count(distinct case when is_new_cont = 1 then feed_id end) as cont_cnt_add,
count(distinct case when is_new_cont = 1 then media_id end) as media_cnt_add,
count(distinct feed_id) as cont_cnt_store,
count(distinct media_id) as media_cnt_store
FROM(
SELECT cont_type,sub_cont_type,
media_type_name,media_name,show_type,
IF (mask IN ('0') ,'总计',sold_type) AS sold_type,
is_new_cont,
feed_id,
media_id
FROM (
SELECT cont_type,sub_cont_type,
media_type_name,media_name,sold_type,
IF (mask IN ('0') ,'总计',show_type) AS show_type,
is_new_cont,
feed_id,
media_id
FROM (
SELECT cont_type,sub_cont_type,
IF (mask IN ('0') ,'总计',media_type_name) AS media_type_name,
IF (mask IN ('0', '1') ,'总计',media_name) AS media_name,
show_type,sold_type,
is_new_cont,
feed_id,
media_id
FROM (
SELECT
cont_type,
sub_cont_type,
coalesce(media_type_name,'其他') as media_type_name,
coalesce(media_name,'其他') as media_name,
coalesce(show_type,'其他') as show_type,
coalesce(sold_type,'其他') as sold_type,
case when feed_create_date = '${bizdate}' then 1 else 0 end as is_new_cont,
feed_id,
media_id
FROM dwd_tpp_cont_gyjc_feed_content_df
WHERE ds = '${bizdate}'
) a
LATERAL VIEW EXPLODE(SPLIT('0,1,2',',')) z AS mask
) a
LATERAL VIEW EXPLODE(SPLIT('0,1',',')) z AS mask
) a
LATERAL VIEW EXPLODE(SPLIT('0,1',',')) z AS mask
) a
GROUP BY cont_type,sub_cont_type,
media_type_name,media_name,show_type,sold_type
2.grouping方法
首先推荐一篇odps grouping技巧的文章，这篇文章介绍了odps中group聚合分析时常用的grouping sets、rollup、cube的用法，本文不再赘述 。https://blog.csdn.net/kangkangwanwan/article/details/109768648
SELECT feed_type
,IF(GROUPING(media_type_name)=0,media_type_name,'all') as media_type_name
,IF(GROUPING(media_name)=0,media_name,'all') as media_name
,IF(GROUPING(show_type)=0,show_type,'all') as show_type
,IF(GROUPING(sold_type)=0,sold_type,'all') as sold_type
,count(distinct case when is_new_cont = 1 then feed_id end) as cont_cnt_add
,count(distinct case when is_new_cont = 1 then media_id end) as media_cnt_add
,count(distinct feed_id) as cont_cnt_store
,count(distinct media_id) as media_cnt_store
FROM (
SELECT
feed_type,
coalesce(media_type_name,'其他') as media_type_name,
coalesce(media_name,'其他') as media_name,
coalesce(show_type,'其他') as show_type,
coalesce(sold_type,'其他') as sold_type,
case when feed_create_date = '${bizdate}' then 1 else 0 end as is_new_cont,
feed_id,
media_id
FROM dwd_tpp_cont_gyjc_feed_log_di
WHERE ds = '${bizdate}'
) a
GROUP BY feed_type
,cube(show_type,sold_type)
,rollup(media_type_name,media_name)
;
三、优化方法
用过kylin的同学应该对cube优化有一些基本的思路，比如将维度分为不同的组，组内交叉，组间隔离；如果有必选维度，则必选维度不参与交叉；尽量多的使用层级维度等等。如果维度之间毫无关系并且需要全部交叉的话，最终n个维度组成的cube有2^n个组，也就是数据膨胀了2^n倍，本例中200万条记录的表膨胀之后会变成400亿条！优化刻不容缓。
1.减少维度
第一个就是减少维度，看起来是废话，但是这是最根本的解决办法，odps支持的cube最大是10个，再多的维度造成的数据膨胀就是灾难性的，任何技巧在100亿条记录的时候都毫无用途。我们分析需求发现有一些维度是不太重要的，那为了保证数据的正常产出就必须精简。
举个例子，我们的30天表从11个维度降到7个维度之后加工时间从3小时降到了40分钟，非常明显。
2.多用rollup
rollup用于层级维度的情况，举个例子就是省、市、区，3个维度如果用cube会产生8个组合，但是由于存在层级关系，其实只需要4个就足够了。
group by rollup(a,b,c) = group by GROUPING SETS ((a,b,c),(a,b),(a),())
cube和rollup支持混合使用，写出来就是group by cube(a,b),rollup(c,d),rollup(e,f,g)，我在最初使用rollup时一直有个担心，就是rollup部分在cube之外，会造成交叉维度的不完整，即group by cube(a,b,rollup(c,d),rollup(e,f,g))会将rollup项也作为一个维度参与到分组中，其实由于rollup的那个空括号，这样的组合是重复的，以下是我的分析过程
----1----
group by cube(a,b),rollup(c,d),rollup(e,f,g)
等价于
group by cube(a,b), GROUPING SETS ((c,d),(c),()),GROUPING SETS ((e,f,g),(e,f),(e),())
等价于
group by GROUPING SETS (
(a,b,c,d,e,f,g),(a,b,c,d,e,f),(a,b,c,d,e),(a,b,c,d),(a,b,c,e,f,g),(a,b,c,e,f),(a,b,c,e),(a,b,c),(a,b,e,f,g),(a,b,e,f),(a,b,e),(a,b),
(a,c,d,e,f,g) ,(a,c,d,e,f) ,(a,c,d,e) ,(a,c,d) ,(a,c,e,f,g) ,(a,c,e,f) ,(a,c,e) ,(a,c) ,(a,e,f,g) ,(a,e,f) ,(a,e) ,(a) ,
(b,c,d,e,f,g) ,(b,c,d,e,f) ,(b,c,d,e) ,(b,c,d) ,(b,c,e,f,g) ,(b,c,e,f) ,(b,c,e) ,(b,c) ,(b,e,f,g) ,(b,e,f) ,(b,e) ,(b) ,
(c,d,e,f,g) ,(c,d,e,f) ,(c,d,e) ,(c,d) ,(c,e,f,g) ,(c,e,f) ,(c,e) ,(c) ,(e,f,g) ,(e,f) ,(e) ,()
)
----2----
group by cube(a,b,rollup(c,d),rollup(e,f,g) )
等价于
group by cube(a,b, GROUPING SETS ((c,d),(c),()),GROUPING SETS ((e,f,g),(e,f),(e),()) )
等价于
group by GROUPING SETS (
(a,b,c,d,e,f,g),(a,b,c,d,e,f),(a,b,c,d,e),(a,b,c,d),(a,b,c,e,f,g),(a,b,c,e,f),(a,b,c,e),(a,b,c),(a,b,e,f,g),(a,b,e,f),(a,b,e),(a,b),
(a,c,d,e,f,g) ,(a,c,d,e,f) ,(a,c,d,e) ,(a,c,d) ,(a,c,e,f,g) ,(a,c,e,f) ,(a,c,e) ,(a,c) ,(a,e,f,g) ,(a,e,f) ,(a,e) ,(a) ,
(b,c,d,e,f,g) ,(b,c,d,e,f) ,(b,c,d,e) ,(b,c,d) ,(b,c,e,f,g) ,(b,c,e,f) ,(b,c,e) ,(b,c) ,(b,e,f,g) ,(b,e,f) ,(b,e) ,(b) ,
(c,d,e,f,g) ,(c,d,e,f) ,(c,d,e) ,(c,d) ,(c,e,f,g) ,(c,e,f) ,(c,e) ,(c) ,(e,f,g) ,(e,f) ,(e) ,() ,
(a,b,c,d) ,(a,b,c) ,(a,b) , --重复
(a,c,d) ,(a,c) ,(a) ,
(b,c,d) ,(b,c) ,(b) ,
(c,d) ,(c) ,() ,
(a,b,e,f,g) ,(a,b,e,f) ,(a,b,e) ,(a,b) , --重复
(a,e,f,g) ,(a,e,f) ,(a,e) ,(a) ,
(b,e,f,g) ,(b,e,f) ,(b,e) ,(b) ,
(e,f,g) ,(e,f) ,(e) ,() ,
(a,b) , --重复
(a) ,
(b) ,
()
)
由此可见，group by cube(a,b),rollup(c,d),rollup(e,f,g)和group by cube(a,b,rollup(c,d),rollup(e,f,g))是等价的。
3.减少维度的取值
维度尽量使用枚举值，这样使得数据的基数变小，组合之后的总数据量也会变小。
4.参数调节
odps-sql的任务是伏羲系统，job分解为task，最常见的task有map、reduce和join三种形式，调节参数也从这三方面入手
- 如果发现map个数太少，每个map任务耗时太长，可能是输入数据都是小文件造成的，可以调小odps.sql.mapper.split.size值；
- 如果发现reduce个数太少，可以调大odps.sql.reducer.instances参数；
- 如果发现join个数太少，可以调大odps.sql.joiner.instances参数
注意：伏羲系统会自动调节参数，如果只是偶尔一次运行慢则不需要手动调节参数。
除此之外，还有一些小技巧，比如count(distinct)改为count(group by)，会增加reduce个数，提高加工速度。
