优化三：多表关联-数据倾斜-空值

现象

原hql，用时6093.318 seconds

create table temp.day_user_play_uuid_split_20180226_test1 as
select 
t.datess,
t.device_id,
t.version_id,
t.province_id,
t.city_id,
t.telecom_id,
t.terminal_id,
t.ip,
case
when play_type_id='02' then 
case when t.uuid is not null and t.uuid !='' and t.uuid !='0' then t.uuid when t2.uuid is not null then t2.uuid when t3.uuid is not null then t3.uuid
else 'unknow' end
when play_type_id='03' then
case when t.uuid is not null and t.uuid !='' and t.uuid !='0' then t.uuid when t1.premiere_channel_uuid is not null then t1.premiere_channel_uuid else 'unknow' end
else t.uuid end,
t.play_type_id,
t.play_type,
t.program_id,
t.program_serise_id,
t.client_time,
t.server_time,
t.open_time,
t.play_time,
t.buffer_count,
t.buffer_aver_time,
t.url_first,
t.url,
t.terminal_type 
from 
    (select * from dm_bas.day_user_play_uuid where play_type<>'vod_replay') t 
left join 
         dim.cms_program t1 on t.program_id=t1.program_id 
left join 
         dim.cms_playbill_channel t2 on t.program_id=t2.channel_id 
left join 
         dim.cms_history_playbill_channel t3 on t.program_id=t3.playbill_id ;

分析：

查看主表关联字段分布，可以看出空值占总量的66.6%的百分比，这导致大部分计算分配到一个reduce导致整个任务计算缓慢，体现为reduce进度长时间处在99%。

--总数据量
select count(1) as num from dm_bas.day_user_play_uuid
where  play_type<>'vod_replay';
390270887
--关联字段分布情况
select program_id,count(1) as num from dm_bas.day_user_play_uuid
where  play_type<>'vod_replay' group by program_id order by num desc limit 6;
    259816668
0   46610174
1   1284378
70564740    357546
70564743    251696
70564694    212326

方案：

关联前将左表为空关联字段设置为一个随机数，再去关联右表，这么做的目的是即使是左表的未关联记录，它的key也分布得十分均匀

create table temp.day_user_play_uuid_split_20180226_test3 as
select 
t.datess,
t.device_id,
t.version_id,
t.province_id,
t.city_id,
t.telecom_id,
t.terminal_id,
t.ip,
case
when play_type_id='02' then 
case when t.uuid is not null and t.uuid !='' and t.uuid !='0' then t.uuid when t2.uuid is not null then t2.uuid when t3.uuid is not null then t3.uuid
else 'unknow' end
when play_type_id='03' then
case when t.uuid is not null and t.uuid !='' and t.uuid !='0' then t.uuid when t1.premiere_channel_uuid is not null then t1.premiere_channel_uuid else 'unknow' end
else t.uuid end,
t.play_type_id,
t.play_type,
t.program_id,
t.program_serise_id,
t.client_time,
t.server_time,
t.open_time,
t.play_time,
t.buffer_count,
t.buffer_aver_time,
t.url_first,
t.url,
t.terminal_type 
from 
   (select 
   datess,device_id,version_id,province_id,city_id,telecom_id,terminal_id,ip,uuid,play_type_id,play_type,
   case when program_id is not null and trim(program_id) != '' then program_id else RAND() end as program_id,
   program_serise_id,client_time,server_time,open_time,play_time,buffer_count,
   buffer_aver_time,url_first,url,terminal_type
   from dm_bas.day_user_play_uuid where play_type<>'vod_replay') t 
left join 
        dim.cms_program t1 on t.program_id=t1.program_id 
left join 
        dim.cms_playbill_channel t2 on t.program_id=t2.channel_id 
left join 
        dim.cms_history_playbill_channel t3 on t.program_id=t3.playbill_id ;

把program_id为null和空字符串的转换为随机数即可，用时856.342 seconds，速度提升了7倍。

误区：

刚开始使用的是coalesce(program_id,RAND()) as program_id替换的program_id，速度还是提升不上去，后来才反应过来coalesce只替换数据为null的而空字符串的数据还是保留原样。