hive使用技巧_巧用groupby实现去重统计

网站统计中常用的指标，pv ,uv , 独立IP,登录用户等，都涉及去重操作。全年的统计，PV超过100亿以上。即使是简单的去重统计也非常困难。

 

1、统计去重，原来SQL如下

 

select substr(day,1,4) year,count(*) PV,count(distinct cookieid) UV,count(distinct ip) IP,count(distinct userid) LOGIN
from dms.tracklog_5min a
where substr(day,1,4)='2015'
group by substr(day,1,4)
;

select substr(day,1,4) year,count(*) PV,count(distinct cookieid) UV,count(distinct ip) IP,count(distinct userid) LOGIN 
from dms.tracklog_5min a  
where substr(day,1,4)='2015'
group by substr(day,1,4)
;

 

统计中四个指示，三个都涉及了去重，任务跑了几个小时都未出结果。

 

2、利用group by 实现去重

 

select "2015","PV",count(*) from dms.tracklog_5min
where day>='2015' and day<'2016'
union all
select "201505","UV",count(*) from (
select  cookieid from dms.tracklog_5min
where day>='2015' and day<'2016'  group by cookieid ) a
union all
select "2015","IP",count(*) from (
select  ip from dms.tracklog_5min
where day>='2015' and day<'2016'  group by ip ) a
union all
select "2015","LOGIN",count(*) from (
select  userid from dms.tracklog_5min
where day>='2015' and day<'2016' group by userid) b;

select "2015","PV",count(*) from dms.tracklog_5min
where day>='2015' and day<'2016'
union all 
select "201505","UV",count(*) from (
select  cookieid from dms.tracklog_5min
where day>='2015' and day<'2016'  group by cookieid ) a 
union all 
select "2015","IP",count(*) from (
select  ip from dms.tracklog_5min
where day>='2015' and day<'2016'  group by ip ) a 
union all 
select "2015","LOGIN",count(*) from (
select  userid from dms.tracklog_5min
where day>='2015' and day<'2016' group by userid) b;

单独统计pv,uv,IP,login等指标，并union拼起来，任务跑了不到1个小时就去来结果了

 

 

3、参数的优化

 

SET mapred.reduce.tasks=50;
SET mapreduce.reduce.memory.mb=6000;
SET mapreduce.reduce.shuffle.memory.limit.percent=0.06;

SET mapred.reduce.tasks=50;
SET mapreduce.reduce.memory.mb=6000;
SET mapreduce.reduce.shuffle.memory.limit.percent=0.06;

 

 

涉及数据倾斜的话，主要是reduce中数据倾斜的问题，可能通过设置hive中reduce的并行数，reduce的内存大小单位为m，reduce中 shuffle的刷磁盘的比例，来解决。