Hive作為大數(shù)據(jù)領(lǐng)域常用的數(shù)據(jù)倉庫組件相恃，在平時設(shè)計和查詢時要特別注意效率。影響Hive效率的幾乎從不是數(shù)據(jù)量過大笨觅，而是數(shù)據(jù)傾斜豆茫、數(shù)據(jù)冗余、job或I/O過多屋摇、MapReduce分配不合理等等。對Hive的調(diào)優(yōu)既包含對HiveQL語句本身的優(yōu)化幽邓，也包含Hive配置項和MR方面的調(diào)整炮温。
由于在寫的過程中發(fā)現(xiàn)篇幅過長，因此決定拆成上下兩篇發(fā)布牵舵。上篇包含從開頭到j(luò)oin優(yōu)化的內(nèi)容柒啤，下篇的傳送門是http://www.reibang.com/p/deb4a6f91d3b。祝食用愉快畸颅。

目錄

• 列裁剪和分區(qū)裁剪
• 謂詞下推
• sort by代替order by
• group by代替distinct
• group by配置調(diào)整
  • map端預(yù)聚合
  • 傾斜均衡配置項
• join基礎(chǔ)優(yōu)化
  • build table（小表）前置
  • 多表join時key相同
  • 利用map join特性
  • 分桶表map join
  • 傾斜均衡配置項
• 優(yōu)化SQL處理join數(shù)據(jù)傾斜
  • 空值或無意義值
  • 單獨處理傾斜key
  • 不同數(shù)據(jù)類型
  • build table過大
• MapReduce優(yōu)化
  • 調(diào)整mapper數(shù)
  • 調(diào)整reducer數(shù)
  • 合并小文件
  • 啟用壓縮
  • JVM重用
• 并行執(zhí)行與本地模式
• 嚴格模式
• 采用合適的存儲格式

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

最基本的操作担巩。所謂列裁剪就是在查詢時只讀取需要的列，分區(qū)裁剪就是只讀取需要的分區(qū)没炒。以我們的日歷記錄表為例：

select uid,event_type,record_data
from calendar_record_log
where pt_date >= 20190201 and pt_date <= 20190224
and status = 0;

當(dāng)列很多或者數(shù)據(jù)量很大時涛癌，如果select *或者不指定分區(qū)，全列掃描和全表掃描效率都很低送火。
Hive中與列裁剪優(yōu)化相關(guān)的配置項是hive.optimize.cp拳话，與分區(qū)裁剪優(yōu)化相關(guān)的則是hive.optimize.pruner，默認都是true种吸。在HiveQL解析階段對應(yīng)的則是ColumnPruner邏輯優(yōu)化器弃衍。

謂詞下推

在關(guān)系型數(shù)據(jù)庫如MySQL中，也有謂詞下推（Predicate Pushdown坚俗，PPD）的概念镜盯。它就是將SQL語句中的where謂詞邏輯都盡可能提前執(zhí)行岸裙，減少下游處理的數(shù)據(jù)量。
例如以下HiveQL語句：

select a.uid,a.event_type,b.topic_id,b.title
from calendar_record_log a
left outer join (
  select uid,topic_id,title from forum_topic
  where pt_date = 20190224 and length(content) >= 100
) b on a.uid = b.uid
where a.pt_date = 20190224 and status = 0;

對forum_topic做過濾的where語句寫在子查詢內(nèi)部速缆，而不是外部降允。Hive中有謂詞下推優(yōu)化的配置項hive.optimize.ppd，默認值true激涤，與它對應(yīng)的邏輯優(yōu)化器是PredicatePushDown拟糕。該優(yōu)化器就是將OperatorTree中的FilterOperator向上提，見下圖倦踢。

圖來自https://tech.meituan.com/2014/02/12/hive-sql-to-mapreduce.html

sort by代替order by

HiveQL中的order by與其他SQL方言中的功能一樣，就是將結(jié)果按某字段全局排序晤碘，這會導(dǎo)致所有map端數(shù)據(jù)都進入一個reducer中褂微，在數(shù)據(jù)量大時可能會長時間計算不完。
如果使用sort by园爷，那么還是會視情況啟動多個reducer進行排序宠蚂，并且保證每個reducer內(nèi)局部有序。為了控制map端數(shù)據(jù)分配到reducer的key童社，往往還要配合distribute by一同使用求厕。如果不加distribute by的話，map端數(shù)據(jù)就會隨機分配到reducer扰楼。
舉個例子呀癣，假如要以UID為key豺旬，以上傳時間倒序案糙、記錄類型倒序輸出記錄數(shù)據(jù)：

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;

group by代替distinct

當(dāng)要統(tǒng)計某一列的去重數(shù)時循榆，如果數(shù)據(jù)量很大并淋，count(distinct)就會非常慢沐寺，原因與order by類似色瘩，count(distinct)邏輯只會有很少的reducer來處理单绑。這時可以用group by來改寫：

select count(1) from (
  select uid from calendar_record_log
  where pt_date >= 20190101
  group by uid
) t;

但是這樣寫會啟動兩個MR job（單純distinct只會啟動一個）嘱函，所以要確保數(shù)據(jù)量大到啟動job的overhead遠小于計算耗時币厕，才考慮這種方法庆冕。當(dāng)數(shù)據(jù)集很小或者key的傾斜比較明顯時，group by還可能會比distinct慢劈榨。
那么如何用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;

group by配置調(diào)整

map端預(yù)聚合

group by時，如果先起一個combiner在map端做部分預(yù)聚合同辣，可以有效減少shuffle數(shù)據(jù)量拷姿。預(yù)聚合的配置項是hive.map.aggr惭载，默認值true，對應(yīng)的優(yōu)化器為GroupByOptimizer响巢，簡單方便描滔。
通過hive.groupby.mapaggr.checkinterval參數(shù)也可以設(shè)置map端預(yù)聚合的行數(shù)閾值，超過該值就會分拆job踪古，默認值100000含长。

傾斜均衡配置項

group by時如果某些key對應(yīng)的數(shù)據(jù)量過大，就會發(fā)生數(shù)據(jù)傾斜伏穆。Hive自帶了一個均衡數(shù)據(jù)傾斜的配置項hive.groupby.skewindata拘泞，默認值false。
其實現(xiàn)方法是在group by時啟動兩個MR job枕扫。第一個job會將map端數(shù)據(jù)隨機輸入reducer陪腌，每個reducer做部分聚合，相同的key就會分布在不同的reducer中烟瞧。第二個job再將前面預(yù)處理過的數(shù)據(jù)按key聚合并輸出結(jié)果诗鸭，這樣就起到了均衡的效果。
但是参滴，配置項畢竟是死的强岸，單純靠它有時不能根本上解決問題，因此還是建議自行了解數(shù)據(jù)傾斜的細節(jié)砾赔，并優(yōu)化查詢語句请唱。

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

join優(yōu)化是一個復(fù)雜的話題，下面先說5點最基本的注意事項过蹂。

build table（小表）前置

在最常見的hash join方法中，一般總有一張相對小的表和一張相對大的表聚至，小表叫build table酷勺，大表叫probe table。如下圖所示扳躬。

圖來自http://hbasefly.com/2017/03/19/sparksql-basic-join/

Hive在解析帶join的SQL語句時脆诉，會默認將最后一個表作為probe table，將前面的表作為build table并試圖將它們讀進內(nèi)存贷币。如果表順序?qū)懛椿魇ぃ琾robe table在前面，引發(fā)OOM的風(fēng)險就高了役纹。
在維度建模數(shù)據(jù)倉庫中偶摔，事實表就是probe table，維度表就是build table促脉。假設(shè)現(xiàn)在要將日歷記錄事實表和記錄項編碼維度表來join：

select a.event_type,a.event_code,a.event_desc,b.upload_time
from calendar_event_code a
inner join (
  select event_type,upload_time from calendar_record_log
  where pt_date = 20190225
) b on a.event_type = b.event_type;

多表join時key相同

這種情況會將多個join合并為一個MR job來處理辰斋，例如：

select a.event_type,a.event_code,a.event_desc,b.upload_time
from calendar_event_code a
inner join (
  select event_type,upload_time from calendar_record_log
  where pt_date = 20190225
) b on a.event_type = b.event_type
inner join (
  select event_type,upload_time from calendar_record_log_2
  where pt_date = 20190225
) c on a.event_type = c.event_type;

如果上面兩個join的條件不相同策州，比如改成a.event_code = c.event_code，就會拆成兩個MR job計算宫仗。
負責(zé)這個的是相關(guān)性優(yōu)化器CorrelationOptimizer够挂，它的功能除此之外還非常多，邏輯復(fù)雜藕夫，參考Hive官方的文檔可以獲得更多細節(jié)：https://cwiki.apache.org/confluence/display/Hive/Correlation+Optimizer孽糖。

利用map join特性

map join特別適合大小表join的情況。Hive會將build table和probe table在map端直接完成join過程毅贮，消滅了reduce办悟，效率很高。

select /*+mapjoin(a)*/ a.event_type,b.upload_time
from calendar_event_code a
inner join (
  select event_type,upload_time from calendar_record_log
  where pt_date = 20190225
) b on a.event_type < b.event_type;

上面的語句中加了一條map join hint嫩码，以顯式啟用map join特性誉尖。早在Hive 0.8版本之后，就不需要寫這條hint了铸题。map join還支持不等值連接铡恕，應(yīng)用更加靈活。
map join的配置項是hive.auto.convert.join丢间，默認值true探熔，對應(yīng)邏輯優(yōu)化器是MapJoinProcessor。
還有一些參數(shù)用來控制map join的行為烘挫，比如hive.mapjoin.smalltable.filesize诀艰，當(dāng)build table大小小于該值就會啟用map join，默認值25000000（25MB）饮六。還有hive.mapjoin.cache.numrows其垄，表示緩存build table的多少行數(shù)據(jù)到內(nèi)存，默認值25000卤橄。

分桶表map join

map join對分桶表還有特別的優(yōu)化绿满。由于分桶表是基于一列進行hash存儲的，因此非常適合抽樣（按桶或按塊抽樣）窟扑。
它對應(yīng)的配置項是hive.optimize.bucketmapjoin喇颁，優(yōu)化器是BucketMapJoinOptimizer。但我們的業(yè)務(wù)中用分桶表較少嚎货，所以就不班門弄斧了橘霎，只是提一句。

傾斜均衡配置項

這個配置與上面group by的傾斜均衡配置項異曲同工殖属，通過hive.optimize.skewjoin來配置姐叁，默認false。
如果開啟了，在join過程中Hive會將計數(shù)超過閾值hive.skewjoin.key（默認100000）的傾斜key對應(yīng)的行臨時寫進文件中七蜘，然后再啟動另一個job做map join生成結(jié)果谭溉。通過hive.skewjoin.mapjoin.map.tasks參數(shù)還可以控制第二個job的mapper數(shù)量，默認10000橡卤。
再重復(fù)一遍扮念，通過自帶的配置項經(jīng)常不能解決數(shù)據(jù)傾斜問題。join是數(shù)據(jù)傾斜的重災(zāi)區(qū)碧库，后面還要介紹在SQL層面處理傾斜的各種方法柜与。

下篇見：http://www.reibang.com/p/deb4a6f91d3b