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

离线数仓开发过程中经常会对数据去重后聚合统计，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做最终聚合。