网站建设网络推广文章,欢迎访问中国建设银行官网,购物网站修改文案,wordpress首页关键词离线数仓开发过程中经常会对数据去重后聚合统计#xff0c;count distinct使得map端无法预聚合#xff0c;容易引发reduce端长尾#xff0c;以下是count distinct去重调优的几种方式。
解决方案一#xff1a;group by 替代
原sql 如下#xff1a;
#7日、14日的app点击的… 离线数仓开发过程中经常会对数据去重后聚合统计count distinct使得map端无法预聚合容易引发reduce端长尾以下是count distinct去重调优的几种方式。
解决方案一group by 替代
原sql 如下
#7日、14日的app点击的用户数user_id去重统计
selectgroup_id,app_id,
-- 7日内UVcount(distinct case when dt ${7d_before} then user_id else null end) as 7d_uv,
--14日内UVcount(distinct case when dt ${14d_before} then user_id else null end) as 14d_uv
from tbl
where dt ${14d_before}
group by group_id, --渠道app_id; --app
优化思路group by两阶段聚合
#7日、14日的app点击的用户数user_id去重统计
selectgroup_id,app_id,
-- 7日内UVsum(case when 7d_cnt 0 then 1 else 0 end) as 7d_uv,
--14日内UVsum(case when 14d_uv 0 then 1 else 0 end) as 14d_uvfrom (selectgroup_id,app_id,-- 7日内各渠道各app下的每个用户的点击量count(case when dt ${7d_before} then user_id else null end) as 7d_cnt,-- 14日内各渠道各app下的每个用户点击量count(case when dt ${14d_before} then user_id else null end) as 14d_uvfrom tblwhere dt ${14d_before}group by group_id,app_id,user_id) tmp1
group by group_id,app_id;
方案一弊端数据倾斜风险 解决方案一通过两阶段group by(分组聚合) 对count (distinct) 进行改造调优需要注意的是如果分组字段user_id在tbl 表中存在大量的重复值group by底层走shuffle会有数据倾斜的风险因此方案一还可以进一步优化。
解决方案二group by调优
1添加随机数两阶段聚合推荐
#优化前
insert overwrite table tblB partition (dt 2022-10-19)
selectcookie_id,event_query,count(*) as cnt
from tblA
where dt 20220718and dt 20221019and event_query is not null
group by cookie_id, event_query#优化后
insert overwrite table tblB partition (dt 2022-10-19)
selectsplit(tkey, _)[1] as cookie_id,event_query,#--- 求出最终的聚合值sum(cnt) as cnt
from (selectconcat_ws(_, cast(ceiling(rand() * 99) as string), cookie_id) as tkey,event_query,#---将热点Key值cookie_id 进行打散后先局部聚合得到cntcount(*) as cntfrom tblAwhere dt 20220718and dt 20221019and event_query is not null#--- 第一阶段添加[0-99]随机整数将热点Key值cookie_id 进行打散 M --Rgroup by concat_ws(_, cast(ceiling(rand() * 99) as string), cookie_id),event_query) temp#--- 第二阶段对拼接的key值进行切分还原原本的key值split(tkey, _)[1] cookie_id R --R
group by split(tkey, _)[1], event_que 优化思路为 第一阶段对需要聚合的Key值添加随机后缀进行打散基于加工后的key值进行初步聚合M--R1 第二阶段对加工后的key值进行切分还原对第一阶段的聚合值进行再次聚合求出最终结果值R1--R2
2开启Map端聚合
#--开启Map端聚合默认为true
set hive.map.aggr true;
#--在Map 端预先聚合操作的条数
set hive.groupby.mapaggr.checkinterval 100000;该参数可以将顶层的聚合操作放在 Map 阶段执行从而减轻shuffle清洗阶段的数据传输和 Reduce阶段的执行时间提升总体性能。
3数据倾斜时自动负载均衡
#---有数据倾斜的时候自动负载均衡默认是 false
set hive.groupby.skewindata true;开启该参数后当前程序会自动通过两个MapReduce来运行将M-R阶段 拆解成 M-R-R阶段
第一个MapReduce自动进行随机分布到Reducer中负载均衡每个Reducer做部分聚合操作输出结果第二个MapReduce将上一步聚合的结果再按照业务group by key进行处理保障相同的key分发到同一个reduce做最终聚合。
