hive 数据倾斜 实例

任务长时间执行未果 (10000+ second)

查看日志：

[34m###*[Log]      [TASK PING]          : taskId=<111510244>, 6162 seconds[0m
[34m###*[Log]      [TASK PING]          : taskId=<111510244>, 6163 seconds[0m
INFO  : Map 1: 1/1	Map 10: 1/1	Map 11: 2/2	Map 17: 1/1	Map 18: 2/2	Map 24: 1/1	Map 25: 1/1	Map 28: 2/2	Map 30: 2/2	Reducer 12: 199(+1)/200	Reducer 13: 0(+200)/200	Reducer 14: 0/200	Reducer 15: 0/200	Reducer 16: 0/1	Reducer 19: 199(+1)/200	Reducer 2: 200/200	Reducer 20: 0(+200)/200	Reducer 21: 0/200	Reducer 22: 0/200	Reducer 23: 0/1	Reducer 26: 200/200	Reducer 29: 200/200	Reducer 3: 200/200	Reducer 31: 200/200	Reducer 4: 200/200	Reducer 5: 200/200	Reducer 6: 1/1	Reducer 8: 0/200	Reducer 9: 0/200	
INFO  : Map 1: 1/1	Map 10: 1/1	Map 11: 2/2	Map 17: 1/1	Map 18: 2/2	Map 24: 1/1	Map 25: 1/1	Map 28: 2/2	Map 30: 2/2	Reducer 12: 199(+1)/200	Reducer 13: 0(+200)/200	Reducer 14: 0/200	Reducer 15: 0/200	Reducer 16: 0/1	Reducer 19: 199(+1)/200	Reducer 2: 200/200	Reducer 20: 0(+200)/200	Reducer 21: 0/200	Reducer 22: 0/200	Reducer 23: 0/1	Reducer 26: 200/200	Reducer 29: 200/200	Reducer 3: 200/200	Reducer 31: 200/200	Reducer 4: 200/200	Reducer 5: 200/200	Reducer 6: 1/1	Reducer 8: 0/200	Reducer 9: 0/200	
[34m###*[Log]      [TASK PING]          : taskId=<111510244>, 6164 seconds[0m
[34m###*[Log]      [TASK PING]          : taskId=<111510244>, 6165 seconds[0m

发现reducer 12 13 19 20 很久没有执行完

explain sql 代码，查看reducer12是什么任务：

原来在with as 就出问题了：

with trfc as (
    select distinct
        t1.topicdate
        ,t1.cid
        ,case when t2.sex is null or lower(t2.sex) in ('','null') then 'unknown'
        else lower(t2.sex) end as gender 
        ,case when t2.platform is null or lower(t2.platform) in ('','null') then 'unknown'
        else lower(t2.platform) end as platfm 
        ,case when t2.province_cn in ('上海','北京','天津','重庆') then concat(t2.province_cn, '市')
        when t2.province_cn is not null then t2.province_cn
        else 'unknown' end as prov 
        ,case when t2.city_cn in ('上海','北京','天津','重庆') then concat(t2.city_cn, '市')
        when t2.city_cn is not null then t2.city_cn
        else 'unknown' end as ct
        ,case when t2.model is null or lower(t2.model) in ('','null') then 'unknown'
        when t2.model_cn is not null then t2.model_cn
        else lower(t2.model) end as mdl 
        ,t1.wid
    from d_extra.dw_trfc_saashw_base t1  
    inner join d_extra.dm_usr_hw_wid_msg t2 on ( t1.cid = t2.cid and t1.wid = t2.wid )
    where t1.topicdate <= '{4}' and t1.topicdate >= '{6}'
    and t1.wid is not null

)

然后看看是哪个表出问题：

select 
nvl(cid,'nnn')as cid,
count(*) as cnt
from d_extra.dw_trfc_saashw_base t1  
where topicdate = '2018-05-16'
and wid is not null
group by nvl(cid,'nnn')
order by cnt desc

limit 50

结果无异常。

select 
cid, wid,
count(*) as cnt
from d_extra.dm_usr_hw_wid_msg 
group by cid, wid
order by cnt desc

limit 50

结果异常：