hive优化数据倾斜的解决方法

转自：http://blog.csdn.net/lpxuan151009/article/details/7980509

发生数据倾斜时，通常的现象是：

• 任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。
• 查看未完成的子任务，可以看到本地读写数据量积累非常大，通常超过10GB可以认定为发生数据倾斜。

数据倾斜一般是由于代码中的join或group by或distinct的key分布不均导致的，大量经验表明数据倾斜的原因是人为的建表疏忽或业务可以规避的。如果确认业务需要这样倾斜的逻辑.

 

• 对于join，使用map join，即在查询头添加map join hint，例如

select /*+MAPJOIN(b)*/ * from a join b on a.id =b.id

将会把join转换为mapjoin，且将b表作为小表处理。

• 对于group by或distinct，使用两次MR优化，即设定参数: hive.groupby.skewindata=true
• 随机数解决数据倾斜

一、随机函数解决倾斜

 

原始sql：

insertoverwrite table t_aa_click_log partition (pt=’${yyyymmddhh}’)

selecta.*

from(select * from t_aa_click_log1

wherept=’${yyyymmddhh}’

)a

leftouter join

(select* from t_aa_pv_info_log

wherept=’${yyyymmddhh}’) b

ona.pvid=b.pvid;

 

发现大量时间花费在reduce99%~100%这最后一步上，约占总时长20分钟的一半，

用以下sql检查下数据分布:

select*

from(

selectpvid,count(1) cnt

fromt_aa_click_log1

wherept=’${yyyymmddhh}’

groupby pvid) t

orderby cnt desc

limit50;

 

发现pvid=’NA’的占比最高，有100多万，而其他最多的也只有几十条，证实数据倾斜。

 

利用随机函数，将pvid=’NA’的数据随机分布到不同的reduce中：

insertoverwrite table t_aa_click_log partition (pt=’${yyyymmddhh}’)

selecta.*

from(select * from t_aa_click_log1

wherept=’${yyyymmddhh}’

)a

leftouter join

(select* from t_aa_pv_info_log

wherept=’${yyyymmddhh}’) b

–如果pvid长度<=2，包含pvid=NA或-1 等多种异常值，即用随机函数叠加处理，因为异常值本来就关联不到，所以加上随机函数对结果没有影响

oncase when length(a.pvid)<=2 then concat(a.pvid,rand()) else a.pvid end =b.pvid;

 

问题解决。

二、MAPJOIN 结合 UNIONALL

 

原始sql：

select a.*,coalesce(c.categoryid,’NA’) as app_category

from (select * from t_aa_pvid_ctr_hour_js_mes1

) a

left outer join

(select * fromt_qd_cmfu_book_info_mes

) c

on a.app_id=c.book_id;

 

速度很慢，老办法，先查下数据分布。

select *

from

(selectapp_id,count(1) cnt

fromt_aa_pvid_ctr_hour_js_mes1

group by app_id) t

order by cnt DESC

limit 50;

 

数据分布如下：

NA      617370129

2       118293314

1       40673814

d       20151236

b       1846306

s       1124246

5       675240

8       642231

6       611104

t       596973

4       579473

3       489516

7       475999

9       373395

107580  10508

 

我们可以看到除了NA是有问题的异常值，还有appid=1~9的数据也很多，而这些数据是可以关联到的，所以这里不能简单的随机函数了。而t_qd_cmfu_book_info_mes这张app库表，又有几百万数据，太大以致不能放入内存使用mapjoin。

 

解决方案：

select a.*,coalesce(c.categoryid,’NA’) as app_category

from –if app_id isnot number value or <=9,then not join

(select * fromt_aa_pvid_ctr_hour_js_mes1

where cast(app_id asint)>9

) a

left outer join

(select * fromt_qd_cmfu_book_info_mes

where cast(book_id asint)>9) c

on a.app_id=c.book_id

union all

select /*+ MAPJOIN(c)*/

a.*,coalesce(c.categoryid,’NA’) as app_category

from –if app_id<=9,use map join

(select * fromt_aa_pvid_ctr_hour_js_mes1

wherecoalesce(cast(app_id as int),-999)<=9) a

left outer join

(select * fromt_qd_cmfu_book_info_mes

where cast(book_id asint)<=9) c

–if app_id is notnumber value,then not join

on a.app_id=c.book_id

 

首先将appid=NA和1~9的数据存入一组，并使用mapjoin与维表（维表也限定appid=1~9，这样内存就放得下了）关联，而除此之外的数据存入另一组，使用普通的join，最后使用union

all 放到一起。