Hive数据倾斜优化：两个亿级表join

发布于 2018-10-11 15:34:10

5.3K0

发布于 2018-10-11 15:34:10

文章被收录于专栏：YG小书屋

问题

原始查询:

select a.strmd5,a.uiscoreporngroupcredit,b.dbitchclassifysexscore from 
(select strmd5,uiscoreporngroupcredit from user where dt=20180925) as a join  # 第一个子查询
(select strpicdownloadimgmd5,dbitchclassifysexscore from filter_log where dt=20180925 ) as b  # 第二个子查询
on a.strmd5 = b.strpicdownloadimgmd5;   # join条件

【现象】出现reduce阶段一直卡在99%的情况，猜测可能出现数据倾斜问题。【验证猜测】 1、查看user表strmd5个数，6亿左右，做distinct之后，只有4.5亿。大约有1.5亿重复数据。 2、查看filter_log表strpicdownloadimgmd5个数，6亿左右，做distinct之后，只有5亿。大约有1亿重复数据。 3、如果一个key在user表和filter_log表中都重复出现1k次，两个表join，总数据量为1k*1k=100w，也就说这一个key的结果就有100w条。这还只是1k次，如果是10w呢？

【解决方案】

利用row_number去重。第一个子查询：

select a.strmd5, a.uiscoreporngroupcredit from
(select strmd5,uiscoreporngroupcredit,row_number() over(partition by strmd5,uiscoreporngroupcredit) rn from user where dt=20180925) a 
where a.rn=1;

第二个子查询：

select b.strpicdownloadimgmd5, b.dbitchclassifysexscore from 
(select strpicdownloadimgmd5,dbitchclassifysexscore,row_number() over(partition by strpicdownloadimgmd5,dbitchclassifysexscore) rank from filter_log where uiappid=300047) b 
where b.rank=1;

再做join。

select c.strmd5,c.uiscoreporngroupcredit,d.dbitchclassifysexscore from 
(select a.strmd5, a.uiscoreporngroupcredit from (select strmd5,uiscoreporngroupcredit,row_number() over(partition by strmd5,uiscoreporngroupcredit) rn from user where dt=20180925) a where a.rn=1) as c #第一个子查询
join 
(select b.strpicdownloadimgmd5, b.dbitchclassifysexscore from (select strpicdownloadimgmd5,dbitchclassifysexscore,row_number() over(partition by strpicdownloadimgmd5,dbitchclassifysexscore) rank from filter_log where dt=20180925 and uiappid=300047) b where b.rank=1) as d  # 第二个子查询
on c.strmd5 = d.strpicdownloadimgmd5; # join条件

【结果】：原来6个小时都没查出来的join，现在25分钟就可以计算出结果了。

row_number与group by的区别

partition by 用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组，它和聚合函数不同的地方在于它能够返回一个分组中的多条记录，而聚合函数一般只有一个反映统计值的记录。

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2018.10.08 ，如有侵权请联系 cloudcommunity@tencent.com 删除

hive

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

hive

登录后参与评论

0 条评论

热度