Hive - distinct && group by 求 UV,PV 实战-阿里云开发者社区

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

2022-08-22 235

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

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

一.引言

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

一.Distinct & 未分组

使用 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 && 未分组

使用 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。

三.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 & 分组

使用 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 大小时，任务的执行时间可以大大缩短。