hive高級操作（優(yōu)化，數(shù)據(jù)傾斜優(yōu)化）

2019/2/21 星期四

hive高級操作（優(yōu)化，數(shù)據(jù)傾斜優(yōu)化）

分區(qū)表/桶表應用，skew，map-join //見hive的基本語法
行列轉(zhuǎn)換

hive 優(yōu)化
hive 優(yōu)化思想
Explain 的使用
經(jīng)典案例(distinct count)

數(shù)據(jù)傾斜的原因
操作：
關(guān)鍵詞 情形 后果
1、Join 其中一個表較小，但是key 集中分發(fā)到某一個或幾個Reduce 上的數(shù)據(jù)遠高于平均值 ；
2、大表與大表，但是分桶的判斷字段0 值或空值過多這些空值都由一個reduce 處理，非常慢；
3、group by group by 維度過小，某值的數(shù)量過多處理某值的reduce 非常耗時
4、Count Distinct 某特殊值過多處理此特殊值的reduce 耗時。
原因小結(jié)：
1)、key 分布不均勻
2)、業(yè)務數(shù)據(jù)本身的特性
3)、建表時考慮不周
4)、某些SQL 語句本身就有數(shù)據(jù)傾斜

表現(xiàn)：
任務進度長時間維持在99%（或100%），查看任務監(jiān)控頁面，發(fā)現(xiàn)只有少量（1個或幾個）reduce 子任務未完成。因為其處理的數(shù)據(jù)量和其他reduce 差異過大。單一reduce 的記錄數(shù)與平均記錄數(shù)差異過大，通?？赡苓_到3 倍甚至更多。最長時長遠大于平均時長。

數(shù)據(jù)傾斜的解決方案
1、參數(shù)調(diào)節(jié)：
hive.map.aggr=true
Map 端部分聚合，相當于Combiner
hive.groupby.skewindata=true
有數(shù)據(jù)傾斜的時候進行負載均衡，當選項設(shè)定為true，生成的查詢計劃會有兩個MR Job。第一個MR Job 中，Map 的輸出結(jié)果集合會隨機分布到Reduce 中，每個Reduce 做部分聚合操作，并輸出結(jié)果，這樣處理的結(jié)果是相同的Group By Key 有可能被分發(fā)到不同的Reduce 中，從而達到負載均衡的目的；第二個MR Job 再根據(jù)預處理的數(shù)據(jù)結(jié)果按照Group By Key 分布到Reduce 中（這個過程可以保證相同的Group By Key 被分布到同一個Reduce 中），最后完成最終的聚合操作。
2、SQL 語句調(diào)節(jié)：
如何Join：
關(guān)于驅(qū)動表的選取，選用join key 分布最均勻的表作為驅(qū)動表
做好列裁剪和filter 操作，以達到兩表做join 的時候，數(shù)據(jù)量相對變小的效果。
大小表Join：
使用map join 讓小的維度表（1000 條以下的記錄條數(shù)） 先進內(nèi)存。在map 端完成reduce.
大表Join 大表：
把空值的key 變成一個字符串加上隨機數(shù)，把傾斜的數(shù)據(jù)分到不同的reduce 上，由于null 值關(guān)聯(lián)不上，處理后并不影響最終結(jié)果。
count distinct（不同） 大量相同特殊值
count distinct 時，將值為空的情況單獨處理，如果是計算count distinct，可以不用處理，直接過濾，在最后結(jié)果中加1。如果還有其他計算，需要進行g(shù)roup by，可以先將值為空的記錄單獨處理，再和其他計算結(jié)果進行union。group by 維度過?。翰捎胹um() group by 的方式來替換count(distinct)完成計算。
特殊情況特殊處理：
在業(yè)務邏輯優(yōu)化效果的不大情況下，有些時候是可以將傾斜的數(shù)據(jù)單獨拿出來處理。最后union 回去。

典型的業(yè)務場景 //生產(chǎn)中遇到的問題解決方法總結(jié)
1、空值產(chǎn)生的數(shù)據(jù)傾斜
場景：如日志中，常會有信息丟失的問題，比如日志中的user_id，如果取其中的user_id 和用戶表中的user_id 關(guān)聯(lián)，會碰到數(shù)據(jù)傾斜的問題。
解決方法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_idunion allselect 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 中l(wèi)og 讀取兩次，jobs 是2。解決方法2 job 數(shù)是1 。
這個優(yōu)化適合無效id(比如-99 , ’’, null 等) 產(chǎn)生的傾斜問題。把空值的key 變成一個字符串加上隨機數(shù)，就能把傾斜的數(shù)據(jù)分到不同的reduce 上,解決數(shù)據(jù)傾斜問題。

2、不同數(shù)據(jù)類型關(guān)聯(lián)產(chǎn)生數(shù)據(jù)傾斜
場景：用戶表中user_id 字段為int，log 表中user_id 字段既有string 類型也有int 類型。當按照user_id 進行兩個表的Join 操作時，默認的Hash 操作會按int 型的id 來進行分配，這樣會導致所有string 類型id 的記錄都分配到一個Reducer 中。
解決方法：把數(shù)字類型轉(zhuǎn)換成字符串類型
select * from users a left outer join logs b on a.usr_id = cast(b.user_id as string)

3、小表不小不大，怎么用map join 解決傾斜問題
使用map join 解決小表(記錄數(shù)少)關(guān)聯(lián)大表的數(shù)據(jù)傾斜問題，這個方法使用的頻率非常高，但如果小表很大，大到map join 會出現(xiàn)bug 或異常，這時就需要特別的處理。以下例子:
select from log a left outer join users b on a.user_id = b.user_id;
users 表有600w+ 的記錄，把users 分發(fā)到所有的map 上也是個不小的開銷，而且map join 不支持這么大的小表。如果用普通的join，又會碰到數(shù)據(jù)傾斜的問題。
解決方法：
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 有上百萬個，這就又回到原來map join 問題。所幸，每日的會員uv 不會太多，有交易的會員不會太多，有點擊的會員不會太多，有傭金的會員不會太多等等。所以這個方法能解決很多場景下的數(shù)據(jù)傾斜問題。

總結(jié)：
//*****使map 的輸出數(shù)據(jù)更均勻的分布到reduce 中去，是我們的最終目標。
由于Hash算法的局限性，按key Hash 會或多或少的造成數(shù)據(jù)傾斜。大量經(jīng)驗表明數(shù)據(jù)傾斜的原因是人為的建表疏忽或業(yè)務邏輯可以規(guī)避的。
在此給出較為通用的步驟：
1、采樣log 表，哪些user_id 比較傾斜，得到一個結(jié)果表tmp1。由于對計算框架來說，所有的數(shù)據(jù)過來，他都是不知道數(shù)據(jù)分布情況的，所以采樣是并不可少的。
2、數(shù)據(jù)的分布符合社會學統(tǒng)計規(guī)則，貧富不均。傾斜的key 不會太多，就像一個社會的富人不多，奇特的人不多一樣。所以tmp1 記錄數(shù)會很少。把tmp1 和 users 做map join 生成tmp2,把tmp2 讀到distribute file cache。這是一個map 過程。
3、map 讀入users 和log，假如記錄來自log,則檢查user_id 是否在tmp2 里，如果是，輸出到本地文件a,否則生成<user_id,value>的key,value 對，假如記錄來自member,生成<user_id,value>的key,value 對，進入reduce 階段。
4、最終把a 文件，把Stage3 reduce 階段輸出的文件合并起寫到hdfs。

如果確認業(yè)務需要這樣傾斜的邏輯，考慮以下的優(yōu)化方案：
1、對于join，在判斷小表不大于1G 的情況下，使用map join
2、對于group by 或distinct，設(shè)定hive.groupby.skewindata=true
3、盡量使用上述的SQL 語句調(diào)節(jié)進行優(yōu)化

提示
1、大表與小表JOIN 時，需采用MapJoin。執(zhí)行MapJoin 時須重點關(guān)注JOIN的順序和過濾后小表的行數(shù)。過濾后小表的行數(shù)不能太大，通常允許的小表上限為20 萬條（僅提供參考），如果超過此界限不建議使用MapJoin。特別的，如果JOIN 的大表極大，Shuffle 的數(shù)據(jù)極多，考慮到大表與小表的大小的相對性，允許將可接受的小表行數(shù)上限提升至100 萬條。
2、重點關(guān)注Shuffle 和JOIN Stage 的數(shù)據(jù)量是否過大而使磁盤溢出，如果有請增加Reduce Task數(shù)目。必須盡量避免大表與大表直接JOIN，所以執(zhí)行之前要檢查分析一下SQL，如果有小表，先用小表或是過濾率較高的表過濾大表，即盡可能先做與小表有關(guān)的JOIN，再使大表參與進來。
3、實在要進行大表Join 大表：把空值的key 變成一個字符串加上隨機數(shù)，把傾斜的數(shù)據(jù)分到不同的reduce 上，由于null 值關(guān)聯(lián)不上，處理后并不影響最終結(jié)果。

正常的在業(yè)務上處理的就是表的join
總結(jié)：
1、首先需要對表做好列裁剪，經(jīng)量讓兩個表的數(shù)據(jù)量相對相等，處理后的數(shù)據(jù)量也變小
2、大小表join： 在map端 join 可以考慮讓小的維度表1000條數(shù)據(jù)先進內(nèi)存，也可以讓小表或者過濾率較高的表過濾大表，即盡可能先做與小表有關(guān)的JOIN，再使大表參與進來。
3、大表與大表join：可以把空值的key 變成一個字符串加上隨機數(shù)，把傾斜的數(shù)據(jù)分到不同的reduce 上，由于null 值關(guān)聯(lián)不上，處理后并不影響最終結(jié)果。也可以把大表進行成小表進行join