Hive調優實用

1、前言

 

       毫不誇張的說，有沒有掌握hive調優，是判斷一個資料工程師是否合格的重要指標 

       hive調優涉及到壓縮和儲存調優，引數調優，sql的調優，資料傾斜調優，小檔案問題的調優等

 

2、資料的壓縮與儲存格式

 

 

1. map階段輸出資料壓縮 ，在這個階段，優先選擇一個低CPU開銷的演算法。

set hive.exec.compress.intermediate=trueset mapred.map.output.compression.codec= org.apache.hadoop.io.compress.SnappyCodecset mapred.map.output.compression.codec=com.hadoop.compression.lzo.LzoCodec;

2、 對最終輸出結果壓縮

​​​​​​​

set hive.exec.compress.output=true set mapred.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec ## 當然，也可以在hive建表時指定表的檔案格式和壓縮編碼

結論，一般選擇orcfile/parquet + snappy 方式

3、合理利用分割槽分桶

      分割槽是將表的資料在物理上分成不同的資料夾，以便於在查詢時可以精準指定所要讀取的分割槽目錄，從來降低讀取的資料量

分桶是將表資料按指定列的hash雜湊後分在了不同的檔案中，將來查詢時，hive可以根據分桶結構，快速定位到一行資料所在的分桶檔案，從來提高讀取效率

4、hive引數優化​​​​​​​

// 讓可以不走mapreduce任務的，就不走mapreduce任務hive> set hive.fetch.task.conversion=more; // 開啟任務並行執行 set hive.exec.parallel=true;// 解釋：當一個sql中有多個job時候，且這多個job之間沒有依賴，則可以讓順序執行變為並行執行（一般為用到union all的時候）  // 同一個sql允許並行任務的最大執行緒數 set hive.exec.parallel.thread.number=8; // 設定jvm重用// JVM重用對hive的效能具有非常大的 影響，特別是對於很難避免小檔案的場景或者task特別多的場景，這類場景大多數執行時間都很短。jvm的啟動過程可能會造成相當大的開銷，尤其是執行的job包含有成千上萬個task任務的情況。set mapred.job.reuse.jvm.num.tasks=10;  // 合理設定reduce的數目// 方法1：調整每個reduce所接受的資料量大小set hive.exec.reducers.bytes.per.reducer=500000000; （500M）// 方法2：直接設定reduce數量

set mapred.reduce.tasks = 20

// map端聚合，降低傳給reduce的資料量

set hive.map.aggr=true  

// 開啟hive內建的數傾優化機制

set hive.groupby.skewindata=true

5、sql優化

1、where條件優化

優化前（關聯式資料庫不用考慮會自動優化）

select m.cid,u.id from order m join customer u on( m.cid =u.id )where m.dt='20180808';

 

優化後(where條件在map端執行而不是在reduce端執行）

select m.cid,u.id from （select * from order where dt='20180818'） m join customer u on( m.cid =u.id);

 

2、union優化

儘量不要使用union （union 去掉重複的記錄）而是使用 union all 然後在用group by 去重

 

3、count distinct優化

不要使用count (distinct  cloumn) ,使用子查詢

select count(1) from (select id from tablename group by id) tmp;

 

4、用in 來代替join

 

如果需要根據一個表的欄位來約束另為一個表，儘量用in來代替join . in 要比join 快​​​​​​​

select id,name from tb1  a join tb2 b on(a.id = b.id); select id,name from tb1 where id in(select id from tb2);

5、優化子查詢

消滅子查詢內的 group by 、 COUNT(DISTINCT)，MAX，MIN。可以減少job的數量。

6、join 優化

Common/shuffle/Reduce JOIN 連線發生的階段，發生在reduce 階段， 適用於大表 連線 大表(預設的方式)

Map join ：連線發生在map階段 ， 適用於小表 連線 大表
                       大表的資料從檔案中讀取
                       小表的資料存放在記憶體中（hive中已經自動進行了優化，自動判斷小表，然後進行快取）

set hive.auto.convert.join=true;

 

SMB join
   Sort -Merge -Bucket Join  對大表連線大表的優化，用桶表的概念來進行優化。在一個桶內發生笛卡爾積連線（需要是兩個桶表進行join）

 set hive.auto.convert.sortmerge.join=true;   set hive.optimize.bucketmapjoin = true;   set hive.optimize.bucketmapjoin.sortedmerge = true;  set hive.auto.convert.sortmerge.join.noconditionaltask=true;

 

6、資料傾斜

 

表現：任務進度長時間維持在99%（或100%），檢視任務監控頁面，發現只有少量（1個或幾個）reduce子任務未完成。因為其處理的資料量和其他reduce差異過大。

原因：某個reduce的資料輸入量遠遠大於其他reduce資料的輸入量

 

1、sql本身導致的傾斜

 

1）group by

如果是在group by中產生了資料傾斜，是否可以講group by的維度變得更細，如果沒法變得更細，就可以在原分組key上新增隨機數後分組聚合一次，然後對結果去掉隨機數後再分組聚合

在join時，有大量為null的join key，則可以將null轉成隨機值，避免聚集

 

2）count(distinct)

情形：某特殊值過多

後果：處理此特殊值的 reduce 耗時；只有一個 reduce 任務

解決方式：count distinct 時，將值為空的情況單獨處理，比如可以直接過濾空值的行，

在最後結果中加 1。如果還有其他計算，需要進行 group by，可以先將值為空的記錄單獨處理，再和其他計算結果進行 union。

 

3）不同資料型別關聯產生資料傾斜

情形：比如使用者表中 user_id 欄位為 int，log 表中 user_id 欄位既有 string 型別也有 int 型別。當按照 user_id 進行兩個表的 Join 操作時。

後果：處理此特殊值的 reduce 耗時；只有一個 reduce 任務

預設的 Hash 操作會按 int 型的 id 來進行分配，這樣會導致所有 string 型別 id 的記錄都分配

到一個 Reducer 中。

解決方式：把數字型別轉換成字串型別

select * from users a

left outer join logs b

on a.usr_id = cast(b.user_id as string)

 

4）mapjoin

 

2、業務資料本身的特性(存在熱點key)

join的每路輸入都比較大，且長尾是熱點值導致的，可以對熱點值和非熱點值分別進行處理，再合併資料

3、key本身分佈不均

可以在key上加隨機數，或者增加reduceTask數量

開啟資料傾斜時負載均衡

set hive.groupby.skewindata=true;

思想：就是先隨機分發並處理，再按照 key group by 來分發處理。

操作：當選項設定為 true，生成的查詢計劃會有兩個 MRJob。

第一個 MRJob 中，Map 的輸出結果集合會隨機分佈到 Reduce 中，每個 Reduce 做部分聚合操作，並輸出結果，這樣處理的結果是相同的 GroupBy Key 有可能被分發到不同的Reduce 中，從而達到負載均衡的目的；

第二個 MRJob 再根據預處理的資料結果按照 GroupBy Key 分佈到 Reduce 中（這個過程可以保證相同的原始 GroupBy Key 被分佈到同一個 Reduce 中），最後完成最終的聚合操作。

 

4 、 控制空值分佈

將為空的 key 轉變為字串加隨機數或純隨機數，將因空值而造成傾斜的資料分不到多個 Reducer。

注：對於異常值如果不需要的話，最好是提前在 where 條件裡過濾掉，這樣可以使計算量大大減少

 

7、合併小檔案

 

小檔案的產生有三個地方，map輸入，map輸出，reduce輸出，小檔案過多也會影響hive的分析效率：

 

設定map輸入的小檔案合併​​​​​​​

set mapred.max.split.size=256000000;  //一個節點上split的至少的大小(這個值決定了多個DataNode上的檔案是否需要合併)set mapred.min.split.size.per.node=100000000;//一個交換機下split的至少的大小(這個值決定了多個交換機上的檔案是否需要合併)  set mapred.min.split.size.per.rack=100000000;//執行Map前進行小檔案合併set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

 

設定map輸出和reduce輸出進行合併的相關引數：​​​​​​​

//設定map端輸出進行合併，預設為trueset hive.merge.mapfiles = true//設定reduce端輸出進行合併，預設為falseset hive.merge.mapredfiles = true//設定合併檔案的大小set hive.merge.size.per.task = 256*1000*1000//當輸出檔案的平均大小小於該值時，啟動一個獨立的MapReduce任務進行檔案merge。set hive.merge.smallfiles.avgsize=16000000

 

8、檢視sql的執行計劃

 

explain sql 

學會檢視sql的執行計劃，優化業務邏輯 ，減少job的資料量。對調優也非常重要！