set hive.map.aggr=true 时统计PV数据错误

最新推荐文章于 2024-03-24 09:56:22 发布

转载最新推荐文章于 2024-03-24 09:56:22 发布 · 561 阅读

1 ·

CC 4.0 BY-SA版权

原文链接：https://yq.aliyun.com/articles/622421

文章标签：

#大数据

本文介绍了一种使用Hive SQL进行数据聚合时遇到的问题及解决方案。通过对count(*)操作的优化，采用sum(1)替代，解决了map端聚合设置导致的数据不准确问题。此外，文中还分享了提高Hive查询效率的相关配置，如启用并行执行等。

从一个表里group by 之后计算累加值、去重值:

为了效率设置并行：set hive.exec.parallel=true（可选：set hive.exec.parallel.thread.number=16）、set hive.groupby.skewindata=true、set hive.map.aggr=true

select plat, pagetype, count(*) pv, count(distinct userkey) uv from client_pv_form where dt = '2015-08-19' group by plat, pagetype
union all
select plat, 'all' pagetype, count(*) pv, count(distinct userkey) uv from client_pv_form where dt = '2015-08-19' group by plat
union all
select 'all' plat, pagetype, count(*) pv, count(distinct userkey) uv from client_pv_form where dt = '2015-08-19' group by pagetype
union all
select 'all' plat, 'all' pagetype, count(*) pv, count(distinct userkey) uv from client_pv_form where dt = '2015-08-19'

坏就坏在：set hive.map.aggr=true，map端聚合的设置；

出来的pv数跟真实值对不上；

改成下边代码运行正确；

select plat, pagetype, sum(1) pv, count(distinct userkey) uv from client_pv_form where dt = '2015-08-19' group by plat, pagetype
union all
select plat, 'all' pagetype, sum(1) pv, count(distinct userkey) uv from client_pv_form where dt = '2015-08-19' group by plat
union all
select 'all' plat, pagetype, sum(1) pv, count(distinct userkey) uv from client_pv_form where dt = '2015-08-19' group by pagetype
union all
select 'all' plat, 'all' pagetype, sum(1) pv, count(distinct userkey) uv from client_pv_form where dt = '2015-08-19'