Hive - distinct && group by 求 UV,PV 实战

给定数据表中包含用户 uid 和用户是否点击广告的标签 label，经常有需求统计用户的下发，打开 UV,PV，下面通过 Hive 实现统计并分析 distinct 与 group by 的性能与使用场景。

使用 distinct 计算用户打开的 PU,UV：

hive -e "
select dt,
sum(if(label='1',1,0)) as click_pv,
count(distinct case when label='1' then click.uid else NULL end) as click_uv,
count(label) as send_pv,
count(distinct click.uid) as send_uv from
(select label, dt, uid from $table where dt between '$st' and '$end') click
group by dt;
"

distinct 操作会将所有 uid shuffle 到同一个 reduce 上，当数据量很大时，任务执行会很慢。

任务开始时间为 2022-05-16 13:00:30，大概执行 1小时30分。

使用 group by 先将 click_pv，click_uv 划定到 id 维度，再通过 group by 分组 sum 聚合

hive -e "
select dt,
sum(click_pv) click_pv,
sum(case when click_pv>0 then 1 else 0 end) click_uv,
sum(send_pv) send_pv,
sum(case when send_pv>0 then 1 else 0 end) send_uv from
(select dt, uid,
sum(if(label='1',1,0)) as click_pv,
count(1) as send_pv
from $table
where dt between '$st' and '$end'
group by dt,uid
) click
group by dt;
"

group by 可以将数据分组再聚合，适合数据量比较大的场景，在数据很大的情况下效率优于 distinct。

任务开始时间为 2022-05-16 13:00:30，大概执行时间为 1小时，可以看到同样数据大小（大数据量）的情况下，groupby 的性能优于 distinct。

增加 case when 语法即可增加 group。

hive -e "
select dt,group,
sum(if(label='1',1,0)) as click_pv,
count(distinct case when label='1' then click.uid else NULL end) as click_uv,
count(label) as send_pv,
count(distinct click.uid) as send_uv from
(select label, dt, uid,
case
    when substr(uid,-2,1) in ('0', '1') then 'A'
    when substr(uid,-2,1) in ('2', '3') then 'B'
    when substr(uid,-2,1) in ('4', '5') then 'C'
    when substr(uid,-2,1) in ('6', '7') then 'D'
    when substr(uid,-2,1) in ('8', '9') then 'E'
end group
from $table where dt between '$st' and '$end') click
group by dt,group;
"

任务开始时间为 2022-05-16 14:54:34，执行耗时约为 30分，如果想获取上面同样的结果，时间由 1h30min 缩短为 30min，所以增加 group 对性能的提升很大。

使用 group by 分组时需要在 select 逻辑以及 group by 的逻辑内都增加 case when 语法获取不同 uid 的分组，这里和上面一样，将全部用户分成 5 个组。

hive -e "
select dt,group,
sum(click_pv) click_pv,
sum(case when click_pv>0 then 1 else 0 end) click_uv,
sum(send_pv) send_pv,
sum(case when send_pv>0 then 1 else 0 end) send_uv from
(select dt, uid,
sum(if(label='1',1,0)) as click_pv,
count(1) as send_pv,
case
    when substr(uid,-2,1) in ('0', '1') then 'A'
    when substr(uid,-2,1) in ('2', '3') then 'B'
    when substr(uid,-2,1) in ('4', '5') then 'C'
    when substr(uid,-2,1) in ('6', '7') then 'D'
    when substr(uid,-2,1) in ('8', '9') then 'E'
end group
from $table
where dt between '$st' and '$end'
group by dt,uid,
case
    when substr(uid,-2,1) in ('0', '1') then 'A'
    when substr(uid,-2,1) in ('2', '3') then 'B'
    when substr(uid,-2,1) in ('4', '5') then 'C'
    when substr(uid,-2,1) in ('6', '7') then 'D'
    when substr(uid,-2,1) in ('8', '9') then 'E'
end
) click
group by dt,group;
"

由于增加了分组 group，最后新增了 job1 实现不同 group 的汇总，任务开始时间为 2022-05-16 14:51:00，执行耗时约为 8分。如果想要获取上面 Group By && 未分组的同样结果，只需要将 Group By && 分组的结果相加即可，而执行的时间由 1小时 缩短为 8分钟，相差了接近7倍，所以在选择到合适的分组 group 大小时，任务的执行时间可以大大缩短。 

上面给出了 Group  By && Distinct 求 UV,PV 的方法，通过增加 group 和不增加 group 可以看到二者性能存在很大的不同，实战中可以使用二分法快速试验出合适的 group 数量再聚合，除此之外也可以手动指定 reduce 数量：

set mapred.reduce.tasks=100；

相关 Hive 参数可以参考 Hive 常用参数整理。

通过4组实验以及耗时可以得出大数据情况下：

A.Group By 效率优于 Distinct

B.Group By + group 效率优于 Group By