問題背景

最近遇到一個比較棘手的事情：hive sql優(yōu)化：

lib表（id,h,soj,noj,sp,np） ? ? ? ? --一個字典表

mitem表（md,mt,soj,noj,sp,np）--一天的數(shù)據(jù)，包含小時分區(qū)的表。

業(yè)務(wù)：

1）需要先把lib表與mitem表進(jìn)行關(guān)聯(lián)(關(guān)聯(lián)條件是lib.soj=mitem.soj and lib.noj=mitem.noj)，關(guān)聯(lián)后的結(jié)果按照soj,md,mt,id,h進(jìn)行分組；

2）對1）中的結(jié)果在分組的時候需要統(tǒng)計差值的平均值記為svalue;

3）對關(guān)聯(lián)后的分區(qū)的統(tǒng)計后的數(shù)據(jù)，進(jìn)行一次分組排序：按照soj,md,mt分組，按照svalue排序，只保留同一個分組內(nèi)排序第一的記錄。

其中表lib有3億條記錄，mitem表包含記錄數(shù)50~150億左右，lib與mitem關(guān)聯(lián)后的記錄數(shù)在6000億條記錄，之后對這個關(guān)聯(lián)后的結(jié)果進(jìn)行進(jìn)行分組卻執(zhí)行了6小時后拋出異常問題。

嘗試解決方案

?瓶頸主要體現(xiàn)在在對第一次關(guān)聯(lián)后的記錄包含了6000億條記錄進(jìn)行分組時，耗費(fèi)資源，資源不足導(dǎo)致的問題。

嘗試過的解決方案：

1）創(chuàng)建索引：《hive：創(chuàng)建索引》

針對該6000億條記錄進(jìn)行創(chuàng)建索引，耗費(fèi)了20小時后依然是在stage2失敗了，此方案推翻。

2）對mitem數(shù)據(jù)按照小時粒度進(jìn)行數(shù)據(jù)拆分，之后每一個小時的mitem與lib進(jìn)行關(guān)聯(lián)，結(jié)果耗費(fèi)時間為20多個小時，依然是拋出異常。

3）對mitem數(shù)據(jù)按照小時粒度進(jìn)行分區(qū)，同時對lib表按照字段soj進(jìn)行分頁（分10頁，一頁中包含的lib記錄數(shù)約3000w條)《hive:某張表進(jìn)行分頁》

create table lib_soj as select soj?from lib group by soj;－－記錄數(shù)約為8000條記錄

create table lib_soj_page as select row_number()over(order by soj)rnum,soj from lib_soj;

create table lib_1_1000 as select t10.* from lib t10 inner join?lib_soj_page t11 on t10.soj=t11.soj where t11.rnum between 1 and 1000;－－記錄數(shù)約為3000w條記錄。

此時，拿一個小時的select * from?mitem where hour='2017102412' 與一個分頁中的soj進(jìn)行關(guān)聯(lián)，數(shù)據(jù)終于出來了，可是耗費(fèi)的時間為１小時２０分，那么該總體時間為1.33*24*10小時。時間實(shí)際上太長了。

４）針對２）、３）的方案我們得知，如果把ｍitem查分帶來的效果實(shí)際上是不大，而查分ｌｉｂ的效果特別明顯，于是想到如果把ｌｉｂ查分的粒度更細(xì)與一天的ｍｉｔｅｍ進(jìn)行關(guān)聯(lián)是否可行(這里是查分為２０份，一份數(shù)據(jù)約為１５００ｗ)。

測試結(jié)果，耗時３小時２０分，那么總體的時間約為3.33*20小時。如果并行執(zhí)行多個分頁的數(shù)據(jù)相信時間上會縮短。

?但是目前這個方案應(yīng)該是shuffle時出現(xiàn)了數(shù)據(jù)偏移問題：

調(diào)優(yōu)：

https://tech.meituan.com/spark-tuning-pro.html

提高ｓｈｕｆｆｌｅ并行度：

http://blog.csdn.net/u013939918/article/details/60956620

?

。

總結(jié)

以上是生活随笔為你收集整理的Hive:表１inner join表２结果group by优化的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。