网址:https://www.iteye.com/blog/superlxw1234-1534779
hive 多字段同时count(distinct)优化
1. 需求与现状:
源表:pcup_3month_login_dtl_mes , 记录数12亿,文件数 300
统计SQL:
Sql代码
- insert overwrite table pcup_logininfo_tmp partition(data_type = 1)
- select popt_id,
- null as sndaid,
- count(distinct case when login_date>='2012-02-01' and login_date<'2012-05-01' then login_date else null end) as m3_login,
- null as m3_login_top5,
- count(distinct case when login_date>='2012-05-01' and login_date<='2012-05-09' then login_date else null end) as mn_login,
- null as mn_login_top5,
- null as m3_apptype,
- null as mn_apptype,
- 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,
- null as m3_g_login_top5,
- 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,
- null as m3_l_login_top5,
- 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,
- null as m3_s_login_top5,
- 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,
- null as m3_o_login_top5,
- 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,
- null as mn_g_login_top5,
- 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,
- null as mn_l_login_top5,
- 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,
- null as mn_s_login_top5,
- 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,
- null as mn_o_login_top5
- from pcup_3month_login_dtl_mes
- 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代码
- create table lxw_test3 as
- select popt_id,login_date,apptypeid
- from pcup_3month_login_dtl_mes
- where login_date>='2012-02-01' and login_date <= '2012-05-09'
- group by popt_id,login_date,apptypeid;
//利用rownumber 函数做去重标记
Sql代码
- add jar hdfs://nn.dc.sh-wgq.sdo.com:8020/group/p_sdo_data/udf/snda_udf.jar;
- CREATE TEMPORARY FUNCTION row_number AS 'com.snda.hive.udf.UDFrow_number';
- create table lxw_test4 as
- select type,popt_id,login_date,row_number(type,login_date,popt_id) as rn
- from (
- select type,popt_id,login_date
- from (
- select 'm3_login' as type,popt_id,login_date
- from lxw_test3
- where login_date>='2012-02-01' and login_date<'2012-05-01'
- union all
- select 'mn_login' as type,popt_id,login_date
- from lxw_test3
- where login_date>='2012-05-01' and login_date<='2012-05-09'
- union all
- select 'm3_g_login' as type,popt_id,login_date
- from lxw_test3
- where login_date>='2012-02-01' and login_date<'2012-05-01' and apptypeid='1'
- union all
- select 'm3_l_login' as type,popt_id,login_date
- from lxw_test3
- where login_date>='2012-02-01' and login_date<'2012-05-01' and apptypeid='2'
- union all
- select 'm3_s_login' as type,popt_id,login_date
- from lxw_test3
- where login_date>='2012-02-01' and login_date<'2012-05-01' and apptypeid='3'
- union all
- select 'm3_o_login' as type,popt_id,login_date
- from lxw_test3
- where login_date>='2012-02-01' and login_date<'2012-05-01' and apptypeid='4'
- union all
- select 'mn_g_login' as type,popt_id,login_date
- from lxw_test3
- where login_date>='2012-05-01' and login_date<='2012-05-09' and apptypeid='1'
- union all
- select 'mn_l_login' as type,popt_id,login_date
- from lxw_test3
- where login_date>='2012-05-01' and login_date<='2012-05-09' and apptypeid='2'
- union all
- select 'mn_s_login' as type,popt_id,login_date
- from lxw_test3
- where login_date>='2012-05-01' and login_date<='2012-05-09' and apptypeid='3'
- union all
- select 'mn_o_login' as type,popt_id,login_date
- from lxw_test3
- where login_date>='2012-05-01' and login_date<='2012-05-09' and apptypeid='4'
- ) x
- distribute by type,login_date,popt_id sort by type,login_date,popt_id
- ) y;
//用普通的聚合函数进行汇总
Sql代码
- insert overwrite table pcup_logininfo_tmp partition(data_type = 99)
- select popt_id,
- null as sndaid,
- sum(case when type = 'm3_login' and rn = 1 then 1 else 0 end) as m3_login,
- null as m3_login_top5,
- sum(case when type = 'mn_login' and rn = 1 then 1 else 0 end) as mn_login,
- null as mn_login_top5,
- null as m3_apptype,
- null as mn_apptype,
- sum(case when type = 'm3_g_login' and rn = 1 then 1 else 0 end) as m3_g_login,
- null as m3_g_login_top5,
- sum(case when type = 'm3_l_login' and rn = 1 then 1 else 0 end) as m3_l_login,
- null as m3_l_login_top5,
- sum(case when type = 'm3_s_login' and rn = 1 then 1 else 0 end) as m3_s_login,
- null as m3_s_login_top5,
- sum(case when type = 'm3_o_login' and rn = 1 then 1 else 0 end) as m3_o_login,
- null as m3_o_login_top5,
- sum(case when type = 'mn_g_login' and rn = 1 then 1 else 0 end) as mn_g_login,
- null as mn_g_login_top5,
- sum(case when type = 'mn_l_login' and rn = 1 then 1 else 0 end) as mn_l_login,
- null as mn_l_login_top5,
- sum(case when type = 'mn_s_login' and rn = 1 then 1 else 0 end) as mn_s_login,
- null as mn_s_login_top5,
- sum(case when type = 'mn_o_login' and rn = 1 then 1 else 0 end) as mn_o_login,
- null as mn_o_login_top5
- from lxw_test4
- group by popt_id