hive 多字段同时count(distinct)优化

网址：https://www.iteye.com/blog/superlxw1234-1534779

                                          hive 多字段同时count(distinct)优化

1.    需求与现状：
源表：pcup_3month_login_dtl_mes ， 记录数12亿，文件数 300
统计SQL:

Sql代码 

1. insert overwrite table pcup_logininfo_tmp partition(data_type = 1)  
2.   select popt_id,  
3.          null as sndaid,  
4.          count(distinct case when login_date>='2012-02-01' and login_date<'2012-05-01' then login_date else null end) as m3_login,  
5.          null as m3_login_top5,  
6.          count(distinct case when login_date>='2012-05-01' and login_date<='2012-05-09' then login_date else null end) as mn_login,  
7.          null as mn_login_top5,  
8.          null as m3_apptype,  
9.          null as mn_apptype,  
10.          count(distinct case when login_date>='2012-02-01' and login_date<'2012-05-01' and apptypeid='1' then login_date else null end) as m3_g_login,  
11.          null as m3_g_login_top5,  
12.          count(distinct case when login_date>='2012-02-01' and login_date<'2012-05-01' and apptypeid='2' then login_date else null end) as m3_l_login,  
13.          null as m3_l_login_top5,  
14.          count(distinct case when login_date>='2012-02-01' and login_date<'2012-05-01' and apptypeid='3' then login_date else null end) as m3_s_login,  
15.          null as m3_s_login_top5,  
16.          count(distinct case when login_date>='2012-02-01' and login_date<'2012-05-01' and apptypeid='4' then login_date else null end) as m3_o_login,  
17.          null as m3_o_login_top5,  
18.          count(distinct case when login_date>='2012-05-01' and login_date<='2012-05-09' and apptypeid='1' then login_date else null end) as mn_g_login,  
19.          null as mn_g_login_top5,  
20.          count(distinct case when login_date>='2012-05-01' and login_date<='2012-05-09' and apptypeid='2' then login_date else null end) as mn_l_login,  
21.          null as mn_l_login_top5,  
22.          count(distinct case when login_date>='2012-05-01' and login_date<='2012-05-09' and apptypeid='3' then login_date else null end) as mn_s_login,  
23.          null as mn_s_login_top5,  
24.          count(distinct case when login_date>='2012-05-01' and login_date<='2012-05-09' and apptypeid='4' then login_date else null end) as mn_o_login,  
25.          null as mn_o_login_top5  
26.   from pcup_3month_login_dtl_mes  
27.   group by popt_id;  

特点：group by 维度少，多字段count(distinct), reduce task非常少(7个)
耗时：1个半小时以上

2.    优化思路：

利用union all + group by + rownumber 代替所有的count(distinct)

根据文件大小设置合理的reduce task数量

3.    优化后的代码：耗时20分钟左右

SET mapred.reduce.tasks = 100;

/初步过滤+去重

Sql代码 

1. create table lxw_test3 as   
2. select popt_id,login_date,apptypeid   
3. from pcup_3month_login_dtl_mes   
4. where login_date>='2012-02-01' and login_date <= '2012-05-09'   
5. group by popt_id,login_date,apptypeid;  

//利用rownumber 函数做去重标记

Sql代码 

 

1. add jar hdfs://nn.dc.sh-wgq.sdo.com:8020/group/p_sdo_data/udf/snda_udf.jar;  
2. CREATE TEMPORARY FUNCTION row_number AS 'com.snda.hive.udf.UDFrow_number';  
3.   
4.                    create table lxw_test4 as   
5. select type,popt_id,login_date,row_number(type,login_date,popt_id) as rn   
6. from (  
7.        select type,popt_id,login_date   
8.        from (  
9.                 select 'm3_login' as type,popt_id,login_date    
10.                 from lxw_test3   
11.                 where login_date>='2012-02-01' and login_date<'2012-05-01'   
12.                 union all   
13.                 select 'mn_login' as type,popt_id,login_date   
14.                 from lxw_test3   
15.                 where login_date>='2012-05-01' and login_date<='2012-05-09'   
16.                 union all   
17.                 select 'm3_g_login' as type,popt_id,login_date   
18.                 from lxw_test3   
19.                 where login_date>='2012-02-01' and login_date<'2012-05-01' and apptypeid='1'   
20.                 union all   
21.                 select 'm3_l_login' as type,popt_id,login_date   
22.                 from lxw_test3   
23.                 where login_date>='2012-02-01' and login_date<'2012-05-01' and apptypeid='2'   
24.                 union all   
25.                 select 'm3_s_login' as type,popt_id,login_date   
26.                 from lxw_test3   
27.                 where login_date>='2012-02-01' and login_date<'2012-05-01' and apptypeid='3'   
28.                 union all   
29.                 select 'm3_o_login' as type,popt_id,login_date   
30.                 from lxw_test3   
31.                 where login_date>='2012-02-01' and login_date<'2012-05-01' and apptypeid='4'   
32.                 union all   
33.                 select 'mn_g_login' as type,popt_id,login_date   
34.                 from lxw_test3   
35.                 where login_date>='2012-05-01' and login_date<='2012-05-09' and apptypeid='1'   
36.                 union all   
37.                 select 'mn_l_login' as type,popt_id,login_date   
38.                 from lxw_test3   
39.                 where login_date>='2012-05-01' and login_date<='2012-05-09' and apptypeid='2'   
40.                 union all   
41.                 select 'mn_s_login' as type,popt_id,login_date   
42.                 from lxw_test3   
43.                 where login_date>='2012-05-01' and login_date<='2012-05-09' and apptypeid='3'   
44.                 union all   
45.                 select 'mn_o_login' as type,popt_id,login_date   
46.                 from lxw_test3   
47.                 where login_date>='2012-05-01' and login_date<='2012-05-09' and apptypeid='4'   
48.        ) x   
49.        distribute by type,login_date,popt_id sort by type,login_date,popt_id   
50. ) y;  

//用普通的聚合函数进行汇总

Sql代码 

 

1. insert overwrite table pcup_logininfo_tmp partition(data_type = 99)   
2. select popt_id,  
3. null as sndaid,  
4. sum(case when type = 'm3_login' and rn = 1 then 1 else 0 end) as m3_login,  
5. null as m3_login_top5,  
6. sum(case when type = 'mn_login' and rn = 1 then 1 else 0 end) as mn_login,  
7. null as mn_login_top5,  
8. null as m3_apptype,  
9. null as mn_apptype,  
10. sum(case when type = 'm3_g_login' and rn = 1 then 1 else 0 end) as m3_g_login,  
11. null as m3_g_login_top5,  
12. sum(case when type = 'm3_l_login' and rn = 1 then 1 else 0 end) as m3_l_login,  
13. null as m3_l_login_top5,  
14. sum(case when type = 'm3_s_login' and rn = 1 then 1 else 0 end) as m3_s_login,  
15. null as m3_s_login_top5,  
16. sum(case when type = 'm3_o_login' and rn = 1 then 1 else 0 end) as m3_o_login,  
17. null as m3_o_login_top5,  
18. sum(case when type = 'mn_g_login' and rn = 1 then 1 else 0 end) as mn_g_login,  
19. null as mn_g_login_top5,  
20. sum(case when type = 'mn_l_login' and rn = 1 then 1 else 0 end) as mn_l_login,  
21. null as mn_l_login_top5,  
22. sum(case when type = 'mn_s_login' and rn = 1 then 1 else 0 end) as mn_s_login,  
23. null as mn_s_login_top5,  
24. sum(case when type = 'mn_o_login' and rn = 1 then 1 else 0 end) as mn_o_login,  
25. null as mn_o_login_top5  
26. from lxw_test4   
27. group by popt_id