(12)Hive调优——count distinct去重优化

   离线数仓开发过程中经常会对数据去重后聚合统计，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 >= '20220718'and dt <= '20221019'and 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 >= '20220718'and dt <= '20221019'and event_query is not null#--- 第一阶段：添加[0-99]随机整数，将热点Key值：cookie_id 进行打散（ M -->R）group 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做最终聚合。