Hive調(diào)優(yōu)集錦
Hive/HiveSQL常用優(yōu)化方法全面總結(jié)
關(guān)于Hive優(yōu)化的四種方法總結(jié)
HiveSQL優(yōu)化
Hive數(shù)據(jù)傾斜問題
Hive常見的數(shù)據(jù)傾斜及調(diào)優(yōu)技巧
HiveSQL排序

Hive作為大數(shù)據(jù)領(lǐng)域常用的數(shù)據(jù)倉庫組件笆包，在平時設(shè)計和查詢時要特別注意效率环揽。影響Hive效率的幾乎從不是數(shù)據(jù)量過大，而是數(shù)據(jù)傾斜庵佣、數(shù)據(jù)冗余歉胶、job或I/O過多、MapReduce分配不合理等等巴粪。對Hive的調(diào)優(yōu)既包含對HiveSQL語句本身的優(yōu)化通今，也包含Hive配置項和MR方面的調(diào)整。

減少數(shù)據(jù)量/掃描分區(qū)數(shù)肛根，避免全表掃描

列裁剪和分區(qū)裁剪

所謂列裁剪就是在查詢時只讀取需要的列辫塌，分區(qū)裁剪就是只讀取需要的分區(qū)。當(dāng)列很多或者數(shù)據(jù)量很大時派哲，如果select *或者不指定分區(qū)臼氨，全列掃描和全表掃描效率都很低。

謂詞下推

將SQL語句中的where謂詞邏輯都盡可能提前執(zhí)行芭届，減少下游處理的數(shù)據(jù)量储矩。
（先盡可能過濾數(shù)據(jù)，再進(jìn)行關(guān)聯(lián)）

避免數(shù)據(jù)傾斜

數(shù)據(jù)傾斜表現(xiàn)：

• 任務(wù)日志進(jìn)度長度為99%褂乍，在日志監(jiān)控進(jìn)度條顯示只有幾個reduce進(jìn)度一直沒有完成持隧。
• 某一reduce處理時長>平均處理時長
• job數(shù)過多

數(shù)據(jù)傾斜原因分析：

• key分布不均
• 業(yè)務(wù)數(shù)據(jù)本身存在不均勻情況
• 關(guān)聯(lián)字段重復(fù)數(shù)據(jù)較多

sort by代替order by

HiveSQL中的order by與其他SQL方言中的功能一樣，就是將結(jié)果按某字段全局排序逃片，這會導(dǎo)致所有map端數(shù)據(jù)都進(jìn)入一個reducer中屡拨，在數(shù)據(jù)量大時可能會長時間計算不完。
如果使用sort by褥实，那么還是會視情況啟動多個reducer進(jìn)行排序呀狼，并且保證每個reducer內(nèi)局部有序。為了控制map端數(shù)據(jù)分配到reducer的key性锭，往往還要配合distribute by一同使用赠潦。如果不加distribute by的話叫胖，map端數(shù)據(jù)就會隨機分配到reducer草冈。（如果兩個字段相同，相當(dāng)于cluster by）

select uid,upload_time,event_type,record_data   
from calendar_record_log    
where pt_date >= 20190201 and pt_date <= 20190224   
distribute by uid   
sort by upload_time desc,event_type desc;

用嵌套查詢的方式實現(xiàn)全局排序：

--非全局排序
select name,Company from table distribute by Company sort by Company;
--全局排序
select * from (select name,Company from table distribute by Company sort by Company) t order by Company;

group by代替distinct

當(dāng)要統(tǒng)計某一列的去重數(shù)時，如果數(shù)據(jù)量很大怎棱，count(distinct)就會非常慢哩俭，原因與order by類似，count(distinct)邏輯只會有很少的reducer來處理拳恋。這時可以用group by來改寫凡资，先group by再count(1)。
但是這樣寫會啟動兩個MR job（單純distinct只會啟動一個）谬运，所以要確保數(shù)據(jù)量大到啟動job的overhead遠(yuǎn)小于計算耗時隙赁，才考慮這種方法。當(dāng)數(shù)據(jù)集很小或者key的傾斜比較明顯時梆暖，group by還可能會比distinct慢伞访。

select count(id) from (select id from bigtable group by id) a;

如何用group by方式同時統(tǒng)計多個列：

select t.a,sum(t.b),count(t.c),count(t.d) from (    
select a,b,null c,null d from some_table    
union all   
select a,0 b,c,null d from some_table group by a,c  
union all   
select a,0 b,null c,d from some_table group by a,d  
) t;

union all代替left join

同上

減少job數(shù)

join基礎(chǔ)優(yōu)化

• build table（小表）前置

• 多表join時key相同（將多個join合并為一個MR job來處理）
  這種情況會將多個join合并為一個MR job來處理，如果條件不同轰驳，就會拆成兩個MR job來計算厚掷。

• 大小表關(guān)聯(lián)：map join
  map join特別適合大小表join的情況。Hive會將build table和probe table在map端直接完成join過程级解，消滅了reduce冒黑，效率很高。

select /*+ MAPJOIN(time_dim) */ count(*) from store_sales join time_dim on ss_sold_time_sk = t_time_sk;

• 大大表關(guān)聯(lián)：處理空值
  大表與大表Join時勤哗，當(dāng)其中一張表的NULL值（或其他值）比較多時抡爹，容易導(dǎo)致這些相同值在reduce階段集中在某一個或幾個reduce上，發(fā)生數(shù)據(jù)傾斜問題芒划。
  優(yōu)化方法：
  （1）將NULL值提取出來最后合并豁延，這一部分只有map操作；非NULL值的數(shù)據(jù)分散到不同reduce上腊状，不會出現(xiàn)某個reduce任務(wù)數(shù)據(jù)加工時間過長的情況诱咏，整體效率提升明顯。這種方法由于有兩次Table Scan會導(dǎo)致map增多缴挖。

SELECT a.user_Id,a.username,b.customer_id
FROM user_info a
LEFT JOIN customer_info b
ON a.user_id = b.user_id
where a.user_id IS NOT NULL
UNION ALL
SELECT a.user_Id,a.username,NULL
FROM user_info a
WHERE a.user_id IS NULL

（2）在Join時直接把NULL值打散成隨機值來作為reduce的key值袋狞，不會出現(xiàn)某個reduce任務(wù)數(shù)據(jù)加工時間過長的情況，整體效率提升明顯映屋。這種方法解釋計劃只有一次map苟鸯，效率一般優(yōu)于第一種方法。

SELECT a.user_id,a.username,b.customer_id
FROM user_info a
LEFT JOIN customer_info b
ON (CASE WHEN a.user_id IS NULL THEN CONCAT ('dp_hive', RAND()) ELSE a.user_id END = b.user_id);

• 分桶表map join
  map join對分桶表還有特別的優(yōu)化棚点。由于分桶表是基于一列進(jìn)行hash存儲的早处，因此非常適合抽樣（按桶或按塊抽樣）。

• 傾斜均衡配置項
  這個配置與上面group by的傾斜均衡配置項異曲同工瘫析，通過hive.optimize.skewjoin來配置砌梆，默認(rèn)false默责。
  如果開啟了，在join過程中Hive會將計數(shù)超過閾值hive.skewjoin.key（默認(rèn)100000）的傾斜key對應(yīng)的行臨時寫進(jìn)文件中咸包，然后再啟動另一個job做map join生成結(jié)果桃序。通過hive.skewjoin.mapjoin.map.tasks參數(shù)還可以控制第二個job的mapper數(shù)量，默認(rèn)10000烂瘫。
  再重復(fù)一遍媒熊，通過自帶的配置項經(jīng)常不能解決數(shù)據(jù)傾斜問題。join是數(shù)據(jù)傾斜的重災(zāi)區(qū)坟比，后面還要介紹在SQL層面處理傾斜的各種方法芦鳍。

優(yōu)化SQL處理join數(shù)據(jù)傾斜

• 空值或無意義值
  若不需要空值數(shù)據(jù)，就提前寫where語句過濾掉葛账。需要保留的話怜校，將空值key用隨機方式打散，例如將用戶ID為null的記錄隨機改為負(fù)值注竿。
  解決方法為：空值null key變成字符串加上隨機數(shù)茄茁，可以把由于數(shù)據(jù)傾斜而導(dǎo)致的數(shù)據(jù)集中到一個reduce上處理的情形，打散到不同的reduce上巩割，生成多個reduce裙顽！

on (case when a.id is null then concat('hive',rand()) else a.id end = b.id)

• 單獨處理傾斜key
  這其實是上面處理空值方法的拓展，不過傾斜的key變成了有意義的宣谈。一般來講傾斜的key都很少愈犹，我們可以將它們抽樣出來，對應(yīng)的行單獨存入臨時表中闻丑，然后打上一個較小的隨機數(shù)前綴（比如0~9）漩怎，最后再進(jìn)行聚合。
  例子：
  現(xiàn)有1TB文本文件words.txt嗦嗡，文件每行為若干個英文單詞勋锤，單詞間用空格分隔，文件中存在單詞word1占據(jù)了總單詞量的30%以上，其他單詞出現(xiàn)頻率較為平均。根據(jù)以上場景求摇，請描述mapreduce如何統(tǒng)計每個單詞出現(xiàn)的頻次。
  題中所述文本文件存在明顯的數(shù)據(jù)傾斜問題剩辟，word1出現(xiàn)頻次遠(yuǎn)大于其他單詞，因此需要對word1在map階段的輸出key值進(jìn)行構(gòu)造，從而將word1均分給多個reduce計算。
  map方法按行讀取文件吆录，每行文件按空格分隔為一個單詞列表，依次讀取每個單詞琼牧。
  若單詞為word1恢筝，則map階段的輸出為<word1_randomInt(50),1>,即"word1_"加0-50 之間的隨機整數(shù)哀卫。 其他單詞直接輸出<單詞,1>。
  注：只要答出map階段對單詞word1的輸出key值進(jìn)行構(gòu)造滋恬，以達(dá)到將word1均分為多個不同的key輸出的目的即可，具體方法可有所區(qū)別抱究。

• 轉(zhuǎn)換不同數(shù)據(jù)類型
  例如注冊表中ID字段為int類型恢氯，登錄表中ID字段即有string類型，也有int類型鼓寺。當(dāng)按照ID字段進(jìn)行兩表之間的join操作時勋拟，默認(rèn)的Hash操作會按int類型的ID來進(jìn)行分配，這樣會導(dǎo)致所有string類型ID的記錄統(tǒng)統(tǒng)都分配到一個Reduce里面去妈候！
  解決方法：把數(shù)字類型轉(zhuǎn)換成字符串類型

on a.ID = cast(b.ID as string)

• build table過大
  有時敢靡，build table會大到無法直接使用map join的地步，比如全量用戶維度表苦银，而使用普通join又有數(shù)據(jù)分布不均的問題啸胧。這時就要充分利用probe table的限制條件，削減build table的數(shù)據(jù)量幔虏，再使用map join解決纺念。代價就是需要進(jìn)行兩次join。

select /*+mapjoin(b)*/ a.uid,a.event_type,b.status,b.extra_info 
from calendar_record_log a  
left outer join (   
select /*+mapjoin(s)*/ t.uid,t.status,t.extra_info  
from (select distinct uid from calendar_record_log where pt_date = 20190228) s  
inner join user_info t on s.uid = t.uid 
) b on a.uid = b.uid    
where a.pt_date = 20190228;

配置優(yōu)化

group by配置優(yōu)化

• map端預(yù)聚合
  group by時想括，如果先起一個combiner在map端做部分預(yù)聚合陷谱，可以有效減少shuffle數(shù)據(jù)量。
  預(yù)聚合的配置項是hive.map.aggr瑟蜈，默認(rèn)值true烟逊，對應(yīng)的優(yōu)化器為GroupByOptimizer，簡單方便铺根。
  通過hive.groupby.mapaggr.checkinterval參數(shù)也可以設(shè)置map端預(yù)聚合的行數(shù)閾值宪躯，超過該值就會分拆job，默認(rèn)值100000位迂。
• 傾斜均衡配置項
  group by時如果某些key對應(yīng)的數(shù)據(jù)量過大眷唉，就會發(fā)生數(shù)據(jù)傾斜。Hive自帶了一個均衡數(shù)據(jù)傾斜的配置項hive.groupby.skewindata囤官，默認(rèn)值false冬阳。
  其實現(xiàn)方法是在group by時啟動兩個MR job。第一個job會將map端數(shù)據(jù)隨機輸入reducer党饮，每個reducer做部分聚合肝陪，相同的key就會分布在不同的reducer中。第二個job再將前面預(yù)處理過的數(shù)據(jù)按key聚合并輸出結(jié)果刑顺，這樣就起到了均衡的效果氯窍。
  但是饲常，配置項畢竟是死的，單純靠它有時不能根本上解決問題狼讨，因此還是建議自行了解數(shù)據(jù)傾斜的細(xì)節(jié)贝淤，并優(yōu)化查詢語句。

MapReduce配置優(yōu)化

• 調(diào)整mapper數(shù)
  mapper_num = MIN(split_num, MAX(default_num, mapred.map.tasks))
  mapper數(shù)量與輸入文件的split數(shù)息息相關(guān)政供。一般來講播聪，如果輸入文件是少量大文件，就減少mapper數(shù)布隔；如果輸入文件是大量非小文件离陶，就增大mapper數(shù)；至于大量小文件的情況衅檀，得參考下面“合并小文件”一節(jié)的方法處理招刨。

• 調(diào)整reducer數(shù)
  reducer_num = MIN(total_input_size / reducers.bytes.per.reducer, reducers.max)
  reducer數(shù)量與輸出文件的數(shù)量相關(guān)。如果reducer數(shù)太多哀军，會產(chǎn)生大量小文件沉眶，對HDFS造成壓力。如果reducer數(shù)太少杉适，每個reducer要處理很多數(shù)據(jù)沦寂，容易拖慢運行時間或者造成OOM。

• 合并小文件
  輸入階段合并淘衙、輸出階段合并

• 啟用壓縮
  壓縮job的中間結(jié)果數(shù)據(jù)和輸出數(shù)據(jù)传藏，可以用少量CPU時間節(jié)省很多空間。

• JVM重用
  在MR job中彤守，默認(rèn)是每執(zhí)行一個task就啟動一個JVM毯侦。如果task非常小而碎，那么JVM啟動和關(guān)閉的耗時就會很長具垫〕蘩耄可以通過調(diào)節(jié)參數(shù)mapred.job.reuse.jvm.num.tasks來重用。例如將這個參數(shù)設(shè)成5筝蚕，那么就代表同一個MR job中順序執(zhí)行的5個task可以重復(fù)使用一個JVM卦碾，減少啟動和關(guān)閉的開銷。但它對不同MR job中的task無效起宽。

• 并行執(zhí)行
  Hive中互相沒有依賴關(guān)系的job間是可以并行執(zhí)行的洲胖，最典型的就是多個子查詢union all。在集群資源相對充足的情況下坯沪，可以開啟并行執(zhí)行绿映。

• 本地模式
  Hive也可以不將任務(wù)提交到集群進(jìn)行運算，而是直接在一臺節(jié)點上處理。因為消除了提交到集群的overhead叉弦，所以比較適合數(shù)據(jù)量很小丐一，且邏輯不復(fù)雜的任務(wù)。

• 嚴(yán)格模式
  所謂嚴(yán)格模式淹冰，就是強制不允許用戶執(zhí)行3種有風(fēng)險的HiveSQL語句库车，一旦執(zhí)行會直接失敗。
  1樱拴、查詢分區(qū)表時不限定分區(qū)列的語句
  2柠衍、兩表join產(chǎn)生了笛卡爾積的語句
  3、用order by來排序但沒有指定limit的語句

• 推測執(zhí)行

• 采用合適的存儲格式
  在HiveSQL的create table語句中疹鳄，可以使用stored as ...指定表的存儲格式拧略。Hive表支持的存儲格式有TextFile芦岂、SequenceFile瘪弓、RCFile、Avro禽最、ORC腺怯、Parquet等。
  存儲格式一般需要根據(jù)業(yè)務(wù)進(jìn)行選擇川无，在我們的實操中呛占，絕大多數(shù)表都采用TextFile與Parquet兩種存儲格式之一。
  TextFile是最簡單的存儲格式懦趋，它是純文本記錄晾虑，也是Hive的默認(rèn)格式。雖然它的磁盤開銷比較大仅叫，查詢效率也低帜篇，但它更多地是作為跳板來使用。RCFile诫咱、ORC笙隙、Parquet等格式的表都不能由文件直接導(dǎo)入數(shù)據(jù)，必須由TextFile來做中轉(zhuǎn)坎缭。
  Parquet和ORC都是Apache旗下的開源列式存儲格式竟痰。列式存儲比起傳統(tǒng)的行式存儲更適合批量OLAP查詢，并且也支持更好的壓縮和編碼掏呼。我們選擇Parquet的原因主要是它支持Impala查詢引擎坏快，并且我們對update、delete和事務(wù)性操作需求很低憎夷。