(12)Hive调优——countdistinct去重优化

   离线数仓开发过程中经常会对数据去重后聚合统计，count distinct使得map端无法预聚合，容易引发reduce端长尾，以下是count distinct去重调优的几种方式。

解决方案一：group by 替代

原sql 如下：

#=====7日、14日的app点击的用户数（user_id去重统计） select group_id, app_id, -- 7日内UV count(distinct case when dt >= '${7d_before}' then user_id else null end) as 7d_uv, --14日内UV count(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去重统计） select group_id, app_id, -- 7日内UV sum(case when 7d_cnt > 0 then 1 else 0 end) as 7d_uv, --14日内UV sum(case when 14d_uv > 0 then 1 else 0 end) as 14d_uv from (select group_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_uv from tbl where 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') select cookie_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') select split(tkey, '_')[1] as cookie_id, event_query, #--- 求出最终的聚合值 sum(cnt) as cnt from ( select concat_ws('_', cast(ceiling(rand() * 99) as string), cookie_id) as tkey, event_query, #---将热点Key值：cookie_id 进行打散后，先局部聚合得到cnt count(*) as cnt from tblA where 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做最终聚合。