hive之于(yu)數(shu)(shu)據民工，就(jiu)如同鋤頭(tou)之于(yu)農民伯伯。hive用的好，才能從(cong)地(di)里(li)（數(shu)(shu)據庫）里(li)挖出更多的數(shu)(shu)據來(lai)。

用過hive的朋友，我想或多或少都有類似(si)的經歷：一天下(xia)來，沒跑幾次hive，就到下(xia)班時間了。

hive在極大(da)數(shu)據或(huo)者數(shu)據不(bu)平衡等(deng)情(qing)況下，表(biao)現(xian)往(wang)往(wang)一般，因此(ci)也出現(xian)了presto、spark-sql等(deng)替(ti)代品。今天不(bu)談其(qi)它，就(jiu)來說說關于(yu)hive，個人的一點心得(de)。

一. 表連接優化(hua) 

1.  將(jiang)大表放后頭

Hive假定查(cha)詢中(zhong)最(zui)后(hou)的(de)一個表(biao)是大表(biao)。它會(hui)將其它表(biao)緩存起來(lai)，然(ran)后(hou)掃描最(zui)后(hou)那個表(biao)。

因此通常(chang)需要將(jiang)小(xiao)表放前面(mian)，或(huo)者標記哪張表是大(da)表：/*streamtable(table_name) */

2. 使(shi)用相同的連接(jie)鍵

當對3個(ge)(ge)或者更多個(ge)(ge)表進(jin)行join連接(jie)時(shi)，如果每(mei)個(ge)(ge)on子(zi)句都(dou)使用相(xiang)同的(de)連接(jie)鍵的(de)話，那么只(zhi)會產生一個(ge)(ge)MapReduce job。

3. 盡量盡早地過濾數(shu)據

減(jian)少每個階段的數據量(liang),對于分區(qu)表要(yao)加分區(qu)，同時只(zhi)選擇需要(yao)使(shi)用(yong)到的字(zi)段。

4. 盡量原(yuan)子化操作

盡(jin)量避免一個SQL包含復雜邏(luo)(luo)輯，可以使用中間表來完成(cheng)復雜的邏(luo)(luo)輯

二. 用insert into替(ti)換union all

如果union all的部分(fen)(fen)個(ge)數(shu)大于2，或者每個(ge)union部分(fen)(fen)數(shu)據量(liang)大，應該拆(chai)成多個(ge)insert into 語句，實際測試過(guo)程中(zhong)，執行時間能提升50%

如：

insert overwite table tablename partition (dt= ....) 　

select ..... from ( select ... from A 

union all 　

select ... from B 　union all select ... from C ) R 　

where ...;

可以改寫為：

insert into table tablename partition (dt= ....) select .... from A WHERE ...; insert into table tablename partition (dt= ....) select .... from B 　WHERE ...; insert into table tablename partition (dt= ....) select .... from C WHERE ...;

三.  order by & sort by 

order by : 對查詢(xun)結果進(jin)行全(quan)局排序，消耗時間長。需要(yao) set hive.mapred.mode=nostrict

sort by : 局部排(pai)序，并非全(quan)局有序，提高效(xiao)率。

四. transform+python

一種嵌入在(zai)hive取數(shu)(shu)流程中(zhong)的自定義函(han)數(shu)(shu)，通過transform語(yu)句可(ke)以把(ba)在(zai)hive中(zhong)不方便實現(xian)的功能在(zai)python中(zhong)實現(xian)，然后(hou)寫入hive表中(zhong)。

語法：

select transform({column names1})

using '**.py'

as {column names2}

from {table name}

如果(guo)除python腳本外(wai)還(huan)有其它依賴資源，可以使用ADD ARVHIVE

五(wu). limit 語句快速出(chu)結果

一般(ban)情況下，Limit語句還是需要執行整(zheng)個(ge)查(cha)詢語句，然后再(zai)返回部(bu)分(fen)結(jie)果。

有一個配置(zhi)屬性可以開啟，避免(mian)這種情(qing)況---對數據源進(jin)行抽樣

hive.limit.optimize.enable=true --- 開啟對數據源進行(xing)采(cai)樣的(de)功能

hive.limit.row.max.size --- 設置最小的采樣容量(liang)

hive.limit.optimize.limit.file --- 設置(zhi)最大的采樣(yang)(yang)樣(yang)(yang)本數

缺點(dian)：有可能部分(fen)數(shu)據永遠(yuan)不會被(bei)處理到

六. 本地模式

對(dui)于小數(shu)據集(ji)，為查詢觸發執行任務(wu)消耗(hao)的(de)時(shi)間>實際執行job的(de)時(shi)間，因此可以通過(guo)本地模式，在(zai)單臺(tai)機器(qi)上（或某些時(shi)候(hou)在(zai)單個進程上）處(chu)理所有的(de)任務(wu)。

set oldjobtracker=${hiveconf:mapred.job.tracker}; 

set mapred.job.tracker=local; 　

set marped.tmp.dir=/home/edward/tmp; sql 語句 　set mapred.job.tracker=${oldjobtracker};

-- 可以(yi)通過設置屬性hive.exec.mode.local.auto的值為true，來讓hve在適(shi)當的時候自動啟動這個(ge)優化(hua)，也(ye)可以(yi)將這個(ge)配置寫在$HOME/.hiverc文件中(zhong)。

-- 當一個job滿足如(ru)下(xia)條(tiao)件才(cai)能真正使用本(ben)地(di)模(mo)式：

1.job的輸入(ru)數(shu)據(ju)大(da)小(xiao)必須小(xiao)于參數(shu)：hive.exec.mode.local.auto.inputbytes.max(默認128MB)

2.job的map數(shu)必須小于參數(shu)：hive.exec.mode.local.auto.tasks.max(默認4)

3.job的reduce數必須為0或者1

可(ke)用參數(shu)(shu)hive.mapred.local.mem(默(mo)認0)控(kong)制(zhi)child jvm使用的最大內存數(shu)(shu)。

七. 并行執行

hive會將(jiang)一個(ge)查詢轉化為(wei)一個(ge)或多個(ge)階(jie)(jie)段，包括：MapReduce階(jie)(jie)段、抽(chou)樣(yang)階(jie)(jie)段、合并階(jie)(jie)段、limit階(jie)(jie)段等。默認情況下，一次(ci)只執行一個(ge)階(jie)(jie)段。 不過(guo)，如(ru)果某(mou)些階(jie)(jie)段不是互相依賴，是可以并行執行的。

set hive.exec.parallel=true,可以開啟并發(fa)執(zhi)行。

set hive.exec.parallel.thread.number=16; //同一個sql允(yun)許最大并行度，默認(ren)為8。

會比較耗系統資源。

八. 調整(zheng)mapper和(he)reducer的個數

1 Map階段優化

map個(ge)(ge)數的(de)主(zhu)要的(de)決定(ding)因素有： input的(de)文件(jian)總個(ge)(ge)數，input的(de)文件(jian)大(da)小(xiao)，集群設置的(de)文件(jian)塊大(da)小(xiao)（默認(ren)128M，不可(ke)自定(ding)義）。

舉例：

a) 假設(she)input目(mu)錄(lu)下有1個(ge)(ge)文件(jian)a,大小為780M,那么hadoop會(hui)將該文件(jian)a分隔(ge)成(cheng)7個(ge)(ge)塊(kuai)（6個(ge)(ge)128m的(de)塊(kuai)和1個(ge)(ge)12m的(de)塊(kuai)），從(cong)而產生7個(ge)(ge)map數(shu)

b) 假設(she)input目錄(lu)下有(you)3個(ge)文件a,b,c,大小(xiao)分(fen)(fen)別為10m，20m，130m，那么hadoop會分(fen)(fen)隔成4個(ge)塊（10m,20m,128m,2m）,從(cong)而產生4個(ge)map數

即，如(ru)果文(wen)件(jian)大于塊大小(xiao)(128m),那(nei)么會拆分(fen)，如(ru)果小(xiao)于塊大小(xiao)，則(ze)把該文(wen)件(jian)當成一個塊。

map執行時間(jian)(jian)：map任務啟(qi)動(dong)和(he)初始化的時間(jian)(jian)+邏輯處理的時間(jian)(jian)。

1）減少map數

若有大量小(xiao)文(wen)件（小(xiao)于128M），會產生多個map，處理方法是：

set mapred.max.split.size=100000000; set mapred.min.split.size.per.node=100000000; set mapred.min.split.size.per.rack=100000000;  

-- 前面三個參數(shu)確定合并文件(jian)塊(kuai)的(de)大(da)(da)小，大(da)(da)于(yu)文件(jian)塊(kuai)大(da)(da)小128m的(de)，按照(zhao)128m來分隔，小于(yu)128m,大(da)(da)于(yu)100m的(de)，按照(zhao)100m來分隔，把(ba)那些小于(yu)100m的(de)（包(bao)括小文件(jian)和分隔大(da)(da)文件(jian)剩下的(de)）進(jin)行合并

　set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; -- 執行(xing)前進行(xing)小文件合并(bing) 2）增加map數

當(dang)input的文(wen)件都(dou)很大，任(ren)務(wu)邏輯復雜，map執行(xing)非常慢的時候，可以考慮增加Map數，來使得(de)每個map處(chu)理的數據量減少，從而(er)提高任(ren)務(wu)的執行(xing)效(xiao)率。

set mapred.reduce.tasks=?

2 Reduce階段優化

調整方式：

-- set mapred.reduce.tasks=?

-- set hive.exec.reducers.bytes.per.reducer = ?

一般(ban)根(gen)據輸入文件的總大小(xiao),用它的estimation函數(shu)來自動(dong)計算reduce的個數(shu)：reduce個數(shu) = InputFileSize / bytes per reducer

九.嚴格模式

set hive.marped.mode=strict ------ 防止用戶(hu)執行那(nei)些可(ke)能意想不(bu)到(dao)的(de)不(bu)好(hao)的(de)影響的(de)查詢

-- 分區表(biao)，必須選定分區范圍(wei)

-- 對于使用order by的查詢，要求必須使用limit語句。因為(wei)order by為(wei)了執行(xing)排序過程會(hui)將所有的結果數據分發到同(tong)一(yi)個reducer中進行(xing)處理(li)。

-- 限(xian)制笛卡爾積查詢：兩張表join時必須有(you)on語(yu)句

十.數據傾斜

表現(xian)：任務(wu)進(jin)度長(chang)時間維持在99%（或100%），查(cha)看任務(wu)監控頁面(mian)，發(fa)現(xian)只(zhi)有少量(liang)（1個(ge)或幾個(ge)）reduce子任務(wu)未(wei)完成。因為其處理的數據量(liang)和其他reduce差(cha)異(yi)過大。

單(dan)一reduce的記錄數與平均記錄數差異過大(da)，通(tong)常可能達到(dao)3倍甚(shen)至更(geng)多(duo)。 最(zui)長時長遠大(da)于平均時長。

原因

1)、key分布不(bu)均勻

2)、業(ye)務數(shu)據本身的特性

3)、建(jian)表(biao)時考慮不周

4)、某些SQL語句本身就有數據(ju)傾斜

解決方案：

參數調節

hive.map.aggr=true

參考文獻：

1. 《hive編程指南》Edward Capriolo

對數(shu)據感興趣的小伙伴，歡迎(ying)交(jiao)流，微信公共(gong)號(hao):一(yi)白侃數(shu)