背景
数据需求,要求计算各个维度下的用户数,并将数据落在目标表中,结果需要支持任意维度的筛选。
维度包括:平台(platform),是否新用户(is_new),年龄(age),人生阶段(life_stage),城市类型(city_class),省份名称(province_name),使用年限(use_age),当前APP版本(app_version_final),启动播放(start_play)。 启动播放维度涵盖:启动用户,播放用户,启动无播放用户,播放无启动用户。
数据举例:
platform | is_new | age | life_stage | city_class | province_name | use_age | app_version_final | start_play | device_id |
---|---|---|---|---|---|---|---|---|---|
gphone | 0 | - | - | - | - | 1 | 9.12.6 | 播放用户 | 2 |
gphone | 0 | 19-24岁 | 中学生 | C | 青海省 | 10 | 9.12.6 | 启动用户 | 3 |
gphone | 0 | 36-40岁 | - | A- | 广东省 | 78 | 9.12.6 | 播放用户 | 6 |
iphone | 0 | 31-35岁 | - | A- | 江苏省 | 726 | 9.12.6 | 启动用户 | 7 |
iphone | 0 | 31-35岁 | 工作 | C | 江苏省 | 184 | 9.12.6 | 启动用户 | 8 |
iphone | 0 | 31-35岁 | 工作 | D | 湖南省 | 849 | 9.12.6 | 启动用户 | 9 |
iphone | 0 | 0-18岁 | 中学生 | A | 广东省 | - | 9.12.6 | 播放用户 | 10 |
iphone | 0 | 0-18岁 | 中学生 | A | 广东省 | - | 9.12.6 | 播放无启动用户 | 10 |
iphone | 0 | 31-35岁 | 工作 | A- | 辽宁省 | 199 | 9.7.0 | 启动用户 | 11 |
iphone | 0 | 31-35岁 | 工作 | C | 湖北省 | 496 | 9.12.6 | 启动用户 | 12 |
gphone | 0 | 36-40岁 | 工作 | D | 安徽省 | - | 9.12.0 | 启动用户 | 13 |
gphone | 0 | - | - | - | - | - | 9.12.0 | 启动用户 | 14 |
gphone | 0 | - | - | - | - | - | 9.12.0 | 启动无播放用户 | 14 |
gphone | 0 | 36-40岁 | - | D | 黑龙江省 | 21 | 9.12.6 | 播放用户 | 19 |
gphone | 0 | - | - | - | - | 4 | 9.12.6 | 启动用户 | 20 |
iphone | 0 | 25-30岁 | 工作 | E | 贵州省 | 1093 | 9.5.8 | 播放用户 | 1 |
iphone | 0 | 25-30岁 | 工作 | E | 贵州省 | 1093 | 9.5.8 | 播放无启动用户 | 1 |
分析需求之后,思考应该使用with cube语法,以实现支持任意维度的筛选,但是维度很多,使用with cube之后可能会出现数据量很大的情况,做测试后发现可以运行。
分析数据来源,base_data_tbl包含了除去start_play以外的所有数据,做一次join即可。
warning:除去启动播放这个维度,其他维度都是没有重叠的数据的,也就是一个用户不可能又属于新用户又不属于新用户,但是一个用户可能既是播放用户,也是播放无启动用户,所以加入这个维度之后,需要在with cube的聚合字段中加入distinct,才能保证数据的正确性
如下图标黄数据所示:
代码分析
首先按照我们最基础的思路写一下代码
WITH demo2 AS --创建临时表demo2,处理start_play维度信息,为join做准备
(
SELECT device_id
,'有启动无播放' AS start_play
FROM data_source_tbl
WHERE vv is null
AND start_num is not null
UNION ALL
SELECT device_id
,'有播放无启动' AS start_play
FROM data_source_tbl
WHERE vv is not null
AND start_num is null
UNION ALL
SELECT device_id
,'启动用户' AS start_play
FROM data_source_tbl
WHERE (vv is null AND start_num is not null) or (vv is not null AND start_num is not null)
UNION ALL
SELECT device_id
,'播放用户' AS start_play
FROM data_source_tbl
WHERE (vv is not null AND start_num is null) or (vv is not null AND start_num is not null)
)
INSERT OVERWRITE TABLE 目标表 partition(dt='2021-07-24')
SELECT nvl(A.platform,'ALL') AS platform
,nvl(A.is_new,'ALL') AS is_new
,nvl(A.age,'ALL') AS age
,nvl(A.life_stage,'ALL') AS life_stage
,nvl(A.city_class,'ALL') AS city_class
,nvl(A.province_name,'ALL') AS province_name
,nvl(A.use_age,'ALL') AS use_age
,nvl(A.app_version_final,'ALL') AS app_version_final
,nvl(B.start_play,'ALL') AS start_play
,COUNT(distinct A.device_id) AS dau -- 所有字段使用nvl是为了最终建表完成后做查询时候的清晰显示
FROM
( -- 这个子查询是为了将除了start_play以外的数据进行去重,使用了group by所有字段的方法
SELECT nvl(A.platform,'-') AS platform
,nvl(A.is_new,'-') AS is_new
,nvl(A.age,'-') AS age
,nvl(A.life_stage,'-') AS life_stage
,nvl(A.city_class,'-') AS city_class
,nvl(A.province_name,'-') AS province_name
,nvl(A.use_age,'-') AS use_age
,nvl(A.app_version_final,'-') AS app_version_final
,nvl(A.device_id,'-') AS device_id
FROM base_data_tbl A
WHERE A.dt='2021-07-24'
GROUP BY platform
,is_new
,age
,life_stage
,city_class
,province_name
,use_age
,app_version_final
,device_id
) A
LEFT JOIN demo2 B ON A.device_id = B.device_id
GROUP BY platform
,is_new
,age
,life_stage
,city_class
,province_name
,use_age
,app_version_final
,start_play WITH cube
这段代码看上去没有问题,但是运行就会发现,在一个reduce阶段会卡在76%不动,查了一下原因,发现是一个reducer要处理过多地distinct数据了,处理速度很慢,我等了4个小时,代码都没跑完…
这时候我尝试更改配置项优化,不是一个reduce不行么,我配置成10个,发现还是慢,我最后配了40个,代码如下:
-- 在 Map 端进行聚合操作的条目数目
set hive.groupby.mapaggr.checkinterval = 100000;
-- 有数据倾斜的时候进行负载均衡(默认是 false)
set hive.groupby.skewindata = true;
-- 必须设置,否则无法开启这么大的grouping sets
SET hive.new.job.grouping.set.cardinality=1024;
-- 设置map和reduce的数量
set mapred.reduce.tasks=40;
set mapred.map.tasks= 9;
其余代码不变,再次运行3个小时可以跑出结果,但是还是太慢了,想要刷数据的话一天才能刷几次,不太行
代码优化
下一步就需要进行代码层面的优化了,能否将distinct去掉?
最常见的使用group by代替distinct方法在当前场景下不能使用,因为是在做with cube聚合
那就把不写distinct可能出错的情景拿出来,然后过滤掉就可以了
这里详细解释一下:为什么当前结果不用distinct会有问题?因为start_play这个筛选维度一个用户可能对应多个元素,在我们进行join操作之后,可能有一个用户存在多行的可能,例如出现
868894031715839 1 NULL 2021-07-20 播放用户
868894031715839 2 NULL 2021-07-20 有播放无登录
这样两条数据,在进行with cube计算的时候,如果不进行处理,计算结果就会有误。
例如我在做platform维度和is_new维度的聚合,其他维度设置为nvl中的默认值ALL,如果不处理,start_play维度中的ALL就对应着很多重复数据,所以我们要将这种可能性进行过滤
我第一次尝试是不写with cube,直接手写所有可能,然后发现不太现实,因为可能性太多了,写两页都写不完。第二次尝试成功了,代码如下
use qube_pps;
--在 Map 端进行聚合操作的条目数目
set hive.groupby.mapaggr.checkinterval = 100000;
--有数据倾斜的时候进行负载均衡(默认是 false)
set hive.groupby.skewindata = true;
SET hive.new.job.grouping.set.cardinality=1024;
WITH demo2 AS
(
-- 注意这里将ALL作为一个当前维度的标签,将所有用户设置为这个标签
select device_id, 'ALL' as start_play
from data_source_tbl
union all
SELECT device_id
,'有启动无播放' AS start_play
FROM data_source_tbl
WHERE vv is null
AND start_num is not null
UNION ALL
SELECT device_id
,'有播放无启动' AS start_play
FROM data_source_tbl
WHERE vv is not null
AND start_num is null
UNION ALL
SELECT device_id
,'启动用户' AS start_play
FROM data_source_tbl
WHERE (vv is null AND start_num is not null) or (vv is not null AND start_num is not null)
UNION ALL
SELECT device_id
,'播放用户' AS start_play
FROM data_source_tbl
WHERE (vv is not null AND start_num is null) or (vv is not null AND start_num is not null)
)
INSERT OVERWRITE TABLE 目标表 partition(dt='2021-07-24')
select * from (
SELECT nvl(A.platform,'ALL') AS platform
,nvl(A.is_new,'ALL') AS is_new
,nvl(A.age,'ALL') AS age
,nvl(A.life_stage,'ALL') AS life_stage
,nvl(A.city_class,'ALL') AS city_class
,nvl(A.province_name,'ALL') AS province_name
,nvl(A.use_age,'ALL') AS use_age
,nvl(A.app_version_final,'ALL') AS app_version_final
,nvl(A.start_play,'ERR') AS start_play -- 一遇到start_play为ERR的情况就过滤掉,为了不让数据不全,上面临时表中加上了ALL字段以保证最终结果的正确性
,COUNT(A.device_id) AS dau
FROM
(
SELECT nvl(A.platform,'-') AS platform
,nvl(A.is_new,'-') AS is_new
,nvl(A.age,'-') AS age
,nvl(A.life_stage,'-') AS life_stage
,nvl(A.city_class,'-') AS city_class
,nvl(A.province_name,'-') AS province_name
,nvl(A.use_age,'-') AS use_age
,nvl(A.app_version_final,'-') AS app_version_final
,nvl(A.device_id,'-') AS device_id
,nvl(B.start_play,'-') as start_play
FROM base_data_tbl A
left join demo2 B on B.device_id=A.device_id
WHERE A.dt='2021-07-24'
) A
GROUP BY platform
,is_new
,age
,life_stage
,city_class
,province_name
,use_age
,app_version_final
,start_play
WITH cube
) t where start_play!='ERR' -- 过滤操作
最终代码运行时间为20-40分钟,极大优化了效率。
总结
通过对with cube的深入理解,删去了distinct造成的性能瓶颈,分析问题产生的原因然后进行了性能优化