Hive中SQL數(shù)據(jù)傾斜及優(yōu)化的示例分析

這篇文章主要介紹了Hive中SQL數(shù)據(jù)傾斜及優(yōu)化的示例分析，具有一定借鑒價(jià)值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

1數(shù)據(jù)傾斜的原因

1.1操作：

1.2原因：

1)、key分布不均勻

2)、業(yè)務(wù)數(shù)據(jù)本身的特性

3)、建表時(shí)考慮不周

4)、某些SQL語(yǔ)句本身就有數(shù)據(jù)傾斜

1.3表現(xiàn)：

任務(wù)進(jìn)度長(zhǎng)時(shí)間維持在99%（或100%），查看任務(wù)監(jiān)控頁(yè)面，發(fā)現(xiàn)只有少量（1個(gè)或幾個(gè)）reduce子任務(wù)未完成。因?yàn)槠涮幚淼臄?shù)據(jù)量和其他reduce差異過(guò)大。

單一reduce的記錄數(shù)與平均記錄數(shù)差異過(guò)大，通常可能達(dá)到3倍甚至更多。 最長(zhǎng)時(shí)長(zhǎng)遠(yuǎn)大于平均時(shí)長(zhǎng)。

2數(shù)據(jù)傾斜的解決方案

2.1參數(shù)調(diào)節(jié)：

hive.map.aggr=true

Map 端部分聚合，相當(dāng)于Combiner

hive.groupby.skewindata=true

有數(shù)據(jù)傾斜的時(shí)候進(jìn)行負(fù)載均衡，當(dāng)選項(xiàng)設(shè)定為 true，生成的查詢(xún)計(jì)劃會(huì)有兩個(gè) MR Job。第一個(gè) MR Job 中，Map 的輸出結(jié)果集合會(huì)隨機(jī)分布到 Reduce 中，每個(gè) Reduce 做部分聚合操作，并輸出結(jié)果，這樣處理的結(jié)果是相同的 Group By Key 有可能被分發(fā)到不同的 Reduce 中，從而達(dá)到負(fù)載均衡的目的；第二個(gè) MR Job 再根據(jù)預(yù)處理的數(shù)據(jù)結(jié)果按照 Group By Key 分布到 Reduce 中（這個(gè)過(guò)程可以保證相同的 Group By Key 被分布到同一個(gè) Reduce 中），最后完成最終的聚合操作。

2.2 SQL語(yǔ)句調(diào)節(jié)：

如何Join：

關(guān)于驅(qū)動(dòng)表的選取，選用join key分布最均勻的表作為驅(qū)動(dòng)表

做好列裁剪和filter操作，以達(dá)到兩表做join的時(shí)候，數(shù)據(jù)量相對(duì)變小的效果。

大小表Join：

使用map join讓小的維度表（1000條以下的記錄條數(shù)） 先進(jìn)內(nèi)存。在map端完成reduce.

大表Join大表：

把空值的key變成一個(gè)字符串加上隨機(jī)數(shù)，把傾斜的數(shù)據(jù)分到不同的reduce上，由于null值關(guān)聯(lián)不上，處理后并不影響最終結(jié)果。

count distinct大量相同特殊值

count distinct時(shí)，將值為空的情況單獨(dú)處理，如果是計(jì)算count distinct，可以不用處理，直接過(guò)濾，在最后結(jié)果中加1。如果還有其他計(jì)算，需要進(jìn)行g(shù)roup by，可以先將值為空的記錄單獨(dú)處理，再和其他計(jì)算結(jié)果進(jìn)行union。

group by維度過(guò)?。?/strong>

采用sum() group by的方式來(lái)替換count(distinct)完成計(jì)算。

特殊情況特殊處理：

在業(yè)務(wù)邏輯優(yōu)化效果的不大情況下，有些時(shí)候是可以將傾斜的數(shù)據(jù)單獨(dú)拿出來(lái)處理。最后union回去。

3典型的業(yè)務(wù)場(chǎng)景

3.1空值產(chǎn)生的數(shù)據(jù)傾斜

場(chǎng)景：如日志中，常會(huì)有信息丟失的問(wèn)題，比如日志中的 user_id，如果取其中的 user_id 和 用戶(hù)表中的user_id 關(guān)聯(lián)，會(huì)碰到數(shù)據(jù)傾斜的問(wèn)題。

解決方法1： user_id為空的不參與關(guān)聯(lián)（紅色字體為修改后）

select * from log a
  join users b
  on a.user_id is not null
  and a.user_id = b.user_id
union all
select * from log a
  where a.user_id is null;

解決方法2 ：賦與空值分新的key值

select *
  from log a
  left outer join users b
  on case when a.user_id is null then concat(‘hive’,rand() ) else a.user_id end = b.user_id;

結(jié)論：方法2比方法1效率更好，不但io少了，而且作業(yè)數(shù)也少了。解決方法1中 log讀取兩次，jobs是2。解決方法2 job數(shù)是1 。這個(gè)優(yōu)化適合無(wú)效 id (比如 -99 , ’’, null 等) 產(chǎn)生的傾斜問(wèn)題。把空值的 key 變成一個(gè)字符串加上隨機(jī)數(shù)，就能把傾斜的數(shù)據(jù)分到不同的reduce上 ,解決數(shù)據(jù)傾斜問(wèn)題。

3.2不同數(shù)據(jù)類(lèi)型關(guān)聯(lián)產(chǎn)生數(shù)據(jù)傾斜

場(chǎng)景：用戶(hù)表中user_id字段為int，log表中user_id字段既有string類(lèi)型也有int類(lèi)型。當(dāng)按照user_id進(jìn)行兩個(gè)表的Join操作時(shí)，默認(rèn)的Hash操作會(huì)按int型的id來(lái)進(jìn)行分配，這樣會(huì)導(dǎo)致所有string類(lèi)型id的記錄都分配到一個(gè)Reducer中。

解決方法：把數(shù)字類(lèi)型轉(zhuǎn)換成字符串類(lèi)型

select * from users a
  left outer join logs b
  on a.usr_id = cast(b.user_id as string)

3.3小表不小不大，怎么用 map join 解決傾斜問(wèn)題

使用 map join 解決小表(記錄數(shù)少)關(guān)聯(lián)大表的數(shù)據(jù)傾斜問(wèn)題，這個(gè)方法使用的頻率非常高，但如果小表很大，大到map join會(huì)出現(xiàn)bug或異常，這時(shí)就需要特別的處理。 以下例子:

select * from log a
  left outer join users b
  on a.user_id = b.user_id;

users 表有 600w+ 的記錄，把 users 分發(fā)到所有的 map 上也是個(gè)不小的開(kāi)銷(xiāo)，而且 map join 不支持這么大的小表。如果用普通的 join，又會(huì)碰到數(shù)據(jù)傾斜的問(wèn)題。

解決方法：

select /*+mapjoin(x)*/* from log a
  left outer join (
    select  /*+mapjoin(c)*/d.*
      from ( select distinct user_id from log ) c
      join users d
      on c.user_id = d.user_id
    ) x
  on a.user_id = b.user_id;

假如，log里user_id有上百萬(wàn)個(gè)，這就又回到原來(lái)map join問(wèn)題。所幸，每日的會(huì)員uv不會(huì)太多，有交易的會(huì)員不會(huì)太多，有點(diǎn)擊的會(huì)員不會(huì)太多，有傭金的會(huì)員不會(huì)太多等等。所以這個(gè)方法能解決很多場(chǎng)景下的數(shù)據(jù)傾斜問(wèn)題。

3.4GROUP BY替代COUNT(DISTINCT)達(dá)到優(yōu)化效果

　　計(jì)算 uv 的時(shí)候，經(jīng)常會(huì)用到 COUNT(DISTINCT)，但在數(shù)據(jù)比較傾斜的時(shí)候 COUNT(DISTINCT) 會(huì)比較慢。這時(shí)可以嘗試用 GROUP BY 改寫(xiě)代碼計(jì)算 uv。

INSERT OVERWRITE TABLE s_dw_tanx_adzone_uv PARTITION (ds=20120329)

SELECT 20120329 AS thedate,adzoneid,COUNT(DISTINCT acookie) AS uv FROM s_ods_log_tanx_pv t WHERE t.ds=20120329 GROUP BY adzoneid

關(guān)于COUNT(DISTINCT)的數(shù)據(jù)傾斜問(wèn)題不能一概而論，要依情況而定，下面是我測(cè)試的一組數(shù)據(jù)：

測(cè)試數(shù)據(jù)：169857條

#統(tǒng)計(jì)每日IP 
CREATE TABLE ip_2014_12_29 AS SELECT COUNT(DISTINCT ip) AS IP FROM logdfs WHERE logdate='2014_12_29'; 
耗時(shí)：24.805 seconds 
#統(tǒng)計(jì)每日IP（改造） 
CREATE TABLE ip_2014_12_29 AS SELECT COUNT(1) AS IP FROM (SELECT DISTINCT ip from logdfs WHERE logdate='2014_12_29') tmp; 
耗時(shí)：46.833 seconds

　　測(cè)試結(jié)果表名：明顯改造后的語(yǔ)句比之前耗時(shí)，這是因?yàn)楦脑旌蟮恼Z(yǔ)句有2個(gè)SELECT，多了一個(gè)job，這樣在數(shù)據(jù)量小的時(shí)候，數(shù)據(jù)不會(huì)存在傾斜問(wèn)題。

3.5解決Hive對(duì)UNION ALL優(yōu)化的短板

Hive 對(duì) union all 的優(yōu)化的特性：對(duì) union all 優(yōu)化只局限于非嵌套查詢(xún)。

• 消滅子查詢(xún)內(nèi)的 group by

     示例 1：子查詢(xún)內(nèi)有 group by 

SELECT * FROM 
(SELECT * FROM t1 GROUP BY c1,c2,c3 UNION ALL SELECT * FROM t2 GROUP BY c1,c2,c3)t3 
GROUP BY c1,c2,c3

　　從業(yè)務(wù)邏輯上說(shuō)，子查詢(xún)內(nèi)的 GROUP BY 怎么都看顯得多余（功能上的多余，除非有 COUNT(DISTINCT)），如果不是因?yàn)?Hive Bug 或者性能上的考量（曾經(jīng)出現(xiàn)如果不執(zhí)行子查詢(xún) GROUP BY，數(shù)據(jù)得不到正確的結(jié)果的 Hive Bug）。所以這個(gè) Hive 按經(jīng)驗(yàn)轉(zhuǎn)換成如下所示：

SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2)t3 GROUP BY c1,c2,c3

　　調(diào)優(yōu)結(jié)果：經(jīng)過(guò)測(cè)試，并未出現(xiàn) union all 的 Hive Bug，數(shù)據(jù)是一致的。MapReduce 的 作業(yè)數(shù)由 3 減少到 1。 

     t1 相當(dāng)于一個(gè)目錄，t2 相當(dāng)于一個(gè)目錄，對(duì) Map/Reduce 程序來(lái)說(shuō)，t1，t2 可以作為 Map/Reduce 作業(yè)的 mutli inputs。這可以通過(guò)一個(gè) Map/Reduce 來(lái)解決這個(gè)問(wèn)題。Hadoop 的 計(jì)算框架，不怕數(shù)據(jù)多，就怕作業(yè)數(shù)多。

　　但如果換成是其他計(jì)算平臺(tái)如 Oracle，那就不一定了，因?yàn)榘汛蟮妮斎氩鸪蓛蓚€(gè)輸入， 分別排序匯總后 merge（假如兩個(gè)子排序是并行的話(huà)），是有可能性能更優(yōu)的（比如希爾排 序比冒泡排序的性能更優(yōu)）。

• 消滅子查詢(xún)內(nèi)的 COUNT(DISTINCT)，MAX，MIN。

SELECT * FROM 
(SELECT * FROM t1 
UNION ALL SELECT c1,c2,c3 COUNT(DISTINCT c4) FROM t2 GROUP BY c1,c2,c3) t3 
GROUP BY c1,c2,c3;

　　由于子查詢(xún)里頭有 COUNT(DISTINCT)操作，直接去 GROUP BY 將達(dá)不到業(yè)務(wù)目標(biāo)。這時(shí)采用 臨時(shí)表消滅 COUNT(DISTINCT)作業(yè)不但能解決傾斜問(wèn)題，還能有效減少 jobs。

INSERT t4 SELECT c1,c2,c3,c4 FROM t2 GROUP BY c1,c2,c3; 
SELECT c1,c2,c3,SUM(income),SUM(uv) FROM 
(SELECT c1,c2,c3,income,0 AS uv FROM t1 
UNION ALL 
SELECT c1,c2,c3,0 AS income,1 AS uv FROM t2) t3 
GROUP BY c1,c2,c3;

　　job 數(shù)是 2，減少一半，而且兩次 Map/Reduce 比 COUNT(DISTINCT)效率更高。

     調(diào)優(yōu)結(jié)果：千萬(wàn)級(jí)別的類(lèi)目表，member 表，與 10 億級(jí)得商品表關(guān)聯(lián)。原先 1963s 的任務(wù)經(jīng)過(guò)調(diào)整，1152s 即完成。

• 消滅子查詢(xún)內(nèi)的 JOIN

SELECT * FROM 
(SELECT * FROM t1 UNION ALL SELECT * FROM t4 UNION ALL SELECT * FROM t2 JOIN t3 ON t2.id=t3.id) x 
GROUP BY c1,c2;

　　上面代碼運(yùn)行會(huì)有 5 個(gè) jobs。加入先 JOIN 生存臨時(shí)表的話(huà) t5，然后 UNION ALL，會(huì)變成 2 個(gè) jobs。

INSERT OVERWRITE TABLE t5 
SELECT * FROM t2 JOIN t3 ON t2.id=t3.id; 
SELECT * FROM (t1 UNION ALL t4 UNION ALL t5);

　　調(diào)優(yōu)結(jié)果顯示：針對(duì)千萬(wàn)級(jí)別的廣告位表，由原先 5 個(gè) Job 共 15 分鐘，分解為 2 個(gè) job 一個(gè) 8-10 分鐘，一個(gè)3分鐘。

4總結(jié)

使map的輸出數(shù)據(jù)更均勻的分布到reduce中去，是我們的最終目標(biāo)。由于Hash算法的局限性，按key Hash會(huì)或多或少的造成數(shù)據(jù)傾斜。大量經(jīng)驗(yàn)表明數(shù)據(jù)傾斜的原因是人為的建表疏忽或業(yè)務(wù)邏輯可以規(guī)避的。在此給出較為通用的步驟：

1、采樣log表，哪些user_id比較傾斜，得到一個(gè)結(jié)果表tmp1。由于對(duì)計(jì)算框架來(lái)說(shuō)，所有的數(shù)據(jù)過(guò)來(lái)，他都是不知道數(shù)據(jù)分布情況的，所以采樣是并不可少的。

2、數(shù)據(jù)的分布符合社會(huì)學(xué)統(tǒng)計(jì)規(guī)則，貧富不均。傾斜的key不會(huì)太多，就像一個(gè)社會(huì)的富人不多，奇特的人不多一樣。所以tmp1記錄數(shù)會(huì)很少。把tmp1和users做map join生成tmp2,把tmp2讀到distribute file cache。這是一個(gè)map過(guò)程。

3、map讀入users和log，假如記錄來(lái)自log,則檢查user_id是否在tmp2里，如果是，輸出到本地文件a,否則生成<user_id,value>的key,value對(duì)，假如記錄來(lái)自member,生成<user_id,value>的key,value對(duì)，進(jìn)入reduce階段。

4、最終把a(bǔ)文件，把Stage3 reduce階段輸出的文件合并起寫(xiě)到hdfs。

如果確認(rèn)業(yè)務(wù)需要這樣傾斜的邏輯，考慮以下的優(yōu)化方案：

1、對(duì)于join，在判斷小表不大于1G的情況下，使用map join

2、對(duì)于group by或distinct，設(shè)定 hive.groupby.skewindata=true

3、盡量使用上述的SQL語(yǔ)句調(diào)節(jié)進(jìn)行優(yōu)化

hadoop處理數(shù)據(jù)的過(guò)程，有幾個(gè)顯著的特征:
不怕數(shù)據(jù)多，就怕數(shù)據(jù)傾斜。
對(duì)jobs數(shù)比較多的作業(yè)運(yùn)行效率相對(duì)比較低，比如即使有幾百行的表，如果多次關(guān)聯(lián)多次匯總，產(chǎn)生十幾個(gè)jobs，沒(méi)半小時(shí)是跑不完的。map reduce作業(yè)初始化的時(shí)間是比較長(zhǎng)的。
對(duì)sum，count來(lái)說(shuō)，不存在數(shù)據(jù)傾斜問(wèn)題。
對(duì)count(distinct ),效率較低，數(shù)據(jù)量一多，準(zhǔn)出問(wèn)題，如果是多count(distinct )效率更低。

優(yōu)化可以從幾個(gè)方面著手：
好的模型設(shè)計(jì)事半功倍。
解決數(shù)據(jù)傾斜問(wèn)題。
減少job數(shù)。
設(shè)置合理的map reduce的task數(shù)，能有效提升性能。(比如，10w+級(jí)別的計(jì)算，用160個(gè)reduce，那是相當(dāng)?shù)睦速M(fèi)，1個(gè)足夠)。
自己動(dòng)手寫(xiě)sql解決數(shù)據(jù)傾斜問(wèn)題是個(gè)不錯(cuò)的選擇。set hive.groupby.skewindata=true;這是通用的算法優(yōu)化，但算法優(yōu)化總是漠視業(yè)務(wù)，習(xí)慣性提供通用的解決方法。 Etl開(kāi)發(fā)人員更了解業(yè)務(wù)，更了解數(shù)據(jù)，所以通過(guò)業(yè)務(wù)邏輯解決傾斜的方法往往更精確，更有效。
對(duì)count(distinct)采取漠視的方法，尤其數(shù)據(jù)大的時(shí)候很容易產(chǎn)生傾斜問(wèn)題，不抱僥幸心理。自己動(dòng)手，豐衣足食。
對(duì)小文件進(jìn)行合并，是行至有效的提高調(diào)度效率的方法，假如我們的作業(yè)設(shè)置合理的文件數(shù)，對(duì)云梯的整體調(diào)度效率也會(huì)產(chǎn)生積極的影響。
優(yōu)化時(shí)把握整體，單個(gè)作業(yè)最優(yōu)不如整體最優(yōu)。

細(xì)節(jié)上就是：
去除查詢(xún)中不需要的column
Where條件判斷等在TableScan階段就進(jìn)行過(guò)濾
利用Partition信息，只讀取符合條件的Partition
Map端join，以大表作驅(qū)動(dòng)，小表載入所有mapper內(nèi)存中
調(diào)整Join順序，確保以大表作為驅(qū)動(dòng)表
對(duì)于數(shù)據(jù)分布不均衡的表Group by時(shí)，為避免數(shù)據(jù)集中到少數(shù)的reducer上，分成兩個(gè)map-reduce階段。第一個(gè)階段先用Distinct列進(jìn)行shuffle，然后在reduce端部分聚合，減小數(shù)據(jù)規(guī)模，第二個(gè)map-reduce階段再按group-by列聚合。
在map端用hash進(jìn)行部分聚合，減小reduce端數(shù)據(jù)處理規(guī)模。

感謝你能夠認(rèn)真閱讀完這篇文章，希望小編分享的“Hive中SQL數(shù)據(jù)傾斜及優(yōu)化的示例分析”這篇文章對(duì)大家有幫助，同時(shí)也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識(shí)等著你來(lái)學(xué)習(xí)!