如何優(yōu)化Hive SQL ？？
點擊上方卡片進(jìn)入數(shù)據(jù)管道主頁
然后點擊右上角 “設(shè)為星標(biāo)”
比別人更快接收好文章
你們好，我是寶器！
Hive作為大數(shù)據(jù)平臺舉足輕重的框架，以其穩(wěn)定性和簡單易用性也成為當(dāng)前構(gòu)建企業(yè)級數(shù)據(jù)倉庫時使用最多的框架之一。
但是如果我們只局限于會使用Hive，而不考慮性能問題，就難搭建出一個完美的數(shù)倉，所以Hive性能調(diào)優(yōu)是我們大數(shù)據(jù)從業(yè)者必須掌握的技能。本文將給大家講解Hive參數(shù)與性能調(diào)優(yōu)的一些方法及技巧。
1. Limit 限制調(diào)整
一般情況下，limit語句還是需要執(zhí)行整個查詢語句，然后再返回部分結(jié)果。
有一個配置屬性可以開啟，避免這種情況：對數(shù)據(jù)源進(jìn)行抽樣。
hive.limit.optimize.enable=true -- 開啟對數(shù)據(jù)源進(jìn)行采樣的功能
hive.limit.row.max.size -- 設(shè)置最小的采樣容量
hive.limit.optimize.limit.file -- 設(shè)置最大的采樣樣本數(shù)
缺點：有可能部分?jǐn)?shù)據(jù)永遠(yuǎn)不會被處理到
2. JOIN優(yōu)化
1. 使用相同的連接鍵
當(dāng)對3個或者更多個表進(jìn)行join連接時，如果每個on子句都使用相同的連接鍵的話，那么只會產(chǎn)生一個MapReduce job。
2. 盡量盡早地過濾數(shù)據(jù)
減少每個階段的數(shù)據(jù)量,對于分區(qū)表要加分區(qū)，同時只選擇需要使用到的字段。
3. 盡量原子化操作
盡量避免一個SQL包含復(fù)雜邏輯，可以使用中間表來完成復(fù)雜的邏輯。
3. 小文件優(yōu)化
1) 小文件過多產(chǎn)生的影響
首先對底層存儲HDFS來說，HDFS本身就不適合存儲大量小文件，小文件過多會導(dǎo)致namenode元數(shù)據(jù)特別大, 占用太多內(nèi)存，嚴(yán)重影響HDFS的性能
對 Hive 來說，在進(jìn)行查詢時，每個小文件都會當(dāng)成一個塊，啟動一個Map任務(wù)來完成，而一個Map任務(wù)啟動和初始化的時間遠(yuǎn)遠(yuǎn)大于邏輯處理的時間，就會造成很大的資源浪費。而且，同時可執(zhí)行的Map數(shù)量是受限的。
2) 怎么解決小文件過多
1. 使用 hive 自帶的 concatenate 命令，自動合并小文件
使用方法：
#對于非分區(qū)表
alter?table?A?concatenate;
#對于分區(qū)表
alter?table?B?partition(day=20201224)?concatenate;
注意：
1、concatenate 命令只支持 RCFILE 和 ORC 文件類型。
2、使用concatenate命令合并小文件時不能指定合并后的文件數(shù)量，但可以多次執(zhí)行該命令。
3、當(dāng)多次使用concatenate后文件數(shù)量不在變化，這個跟參數(shù)mapreduce.input.fileinputformat.split.minsize=256mb的設(shè)置有關(guān)，可設(shè)定每個文件的最小size。
2. 調(diào)整參數(shù)減少Map數(shù)量
設(shè)置map輸入合并小文件的相關(guān)參數(shù)：
#執(zhí)行Map前進(jìn)行小文件合并
#CombineHiveInputFormat底層是?Hadoop的?CombineFileInputFormat?方法
#此方法是在mapper中將多個文件合成一個split作為輸入
set?hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;?--?默認(rèn)
#每個Map最大輸入大小(這個值決定了合并后文件的數(shù)量)
set?mapred.max.split.size=256000000;???--?256M
#一個節(jié)點上split的至少的大小(這個值決定了多個DataNode上的文件是否需要合并)
set?mapred.min.split.size.per.node=100000000;??--?100M
#一個交換機(jī)下split的至少的大小(這個值決定了多個交換機(jī)上的文件是否需要合并)
set?mapred.min.split.size.per.rack=100000000;??--?100M
設(shè)置map輸出和reduce輸出進(jìn)行合并的相關(guān)參數(shù):
#設(shè)置map端輸出進(jìn)行合并，默認(rèn)為true
set?hive.merge.mapfiles?=?true;
#設(shè)置reduce端輸出進(jìn)行合并，默認(rèn)為false
set?hive.merge.mapredfiles?=?true;
#設(shè)置合并文件的大小
set?hive.merge.size.per.task?=?256*1000*1000;???--?256M
#當(dāng)輸出文件的平均大小小于該值時，啟動一個獨立的MapReduce任務(wù)進(jìn)行文件merge
set?hive.merge.smallfiles.avgsize=16000000;???--?16M?
啟用壓縮:
#?hive的查詢結(jié)果輸出是否進(jìn)行壓縮
set?hive.exec.compress.output=true;
#?MapReduce?Job的結(jié)果輸出是否使用壓縮
set?mapreduce.output.fileoutputformat.compress=true;
3. 減少Reduce的數(shù)量
#reduce?的個數(shù)決定了輸出的文件的個數(shù)，所以可以調(diào)整reduce的個數(shù)控制hive表的文件數(shù)量，
#hive中的分區(qū)函數(shù)?distribute?by?正好是控制MR中partition分區(qū)的，
#然后通過設(shè)置reduce的數(shù)量，結(jié)合分區(qū)函數(shù)讓數(shù)據(jù)均衡的進(jìn)入每個reduce即可。
#設(shè)置reduce的數(shù)量有兩種方式，第一種是直接設(shè)置reduce個數(shù)
set?mapreduce.job.reduces=10;
#第二種是設(shè)置每個reduce的大小，Hive會根據(jù)數(shù)據(jù)總大小猜測確定一個reduce個數(shù)
set?hive.exec.reducers.bytes.per.reducer=5120000000;?--?默認(rèn)是1G，設(shè)置為5G
#執(zhí)行以下語句，將數(shù)據(jù)均衡的分配到reduce中
set?mapreduce.job.reduces=10;
insert?overwrite?table?A?partition(dt)
select?*?from?B
distribute?by?rand();
解釋：如設(shè)置reduce數(shù)量為10，則使用 rand()，?隨機(jī)生成一個數(shù) x % 10?，
這樣數(shù)據(jù)就會隨機(jī)進(jìn)入?reduce?中，防止出現(xiàn)有的文件過大或過小
4. 使用hadoop的archive將小文件歸檔
Hadoop Archive簡稱HAR，是一個高效地將小文件放入HDFS塊中的文件存檔工具，它能夠?qū)⒍鄠€小文件打包成一個HAR文件，這樣在減少namenode內(nèi)存使用的同時，仍然允許對文件進(jìn)行透明的訪問
#用來控制歸檔是否可用
set?hive.archive.enabled=true;
#通知Hive在創(chuàng)建歸檔時是否可以設(shè)置父目錄
set?hive.archive.har.parentdir.settable=true;
#控制需要歸檔文件的大小
set?har.partfile.size=1099511627776;
#使用以下命令進(jìn)行歸檔
ALTER?TABLE?A?ARCHIVE?PARTITION(dt='2022-02-24',?hr='12');
#對已歸檔的分區(qū)恢復(fù)為原文件
ALTER?TABLE?A?UNARCHIVE?PARTITION(dt='2022-02-24',?hr='12');
注意:
歸檔的分區(qū)可以查看不能 insert overwrite，必須先 unarchive
4. 本地模式
有時hive的輸入數(shù)據(jù)量是非常小的。在這種情況下，為查詢出發(fā)執(zhí)行任務(wù)的時間消耗可能會比實際job的執(zhí)行時間要多的多。對于大多數(shù)這種情況，hive可以通過本地模式在單臺機(jī)器上處理所有的任務(wù)。對于小數(shù)據(jù)集，執(zhí)行時間會明顯被縮短。
set hive.exec.mode.local.auto=true;
當(dāng)一個job滿足如下條件才能真正使用本地模式：
job的輸入數(shù)據(jù)大小必須小于參數(shù)：
hive.exec.mode.local.auto.inputbytes.max(默認(rèn)128MB)job的map數(shù)必須小于參數(shù)：
hive.exec.mode.local.auto.tasks.max(默認(rèn)4)job的reduce數(shù)必須為0或者1
可用參數(shù) hive.mapred.local.mem (默認(rèn)0)控制child jvm使用的最大內(nèi)存數(shù)。
5. strict模式
開啟嚴(yán)格模式對分區(qū)表進(jìn)行查詢，在where子句中沒有加分區(qū)過濾的話，將禁止提交任務(wù)(默認(rèn)：nonstrict)
set hive.mapred.mode=strict 開啟嚴(yán)格模式
注：使用嚴(yán)格模式可以禁止以下三種類型的查詢：
1. 對分區(qū)表的查詢必須使用到分區(qū)相關(guān)的字段
分區(qū)表的數(shù)據(jù)量通常都比較大，對分區(qū)表的查詢必須使用到分區(qū)相關(guān)的字段，不允許掃描所有分區(qū)，想想也是如果掃描所有分區(qū)的話那么對表進(jìn)行分區(qū)還有什么意義呢。
當(dāng)然某些特殊情況可能還是需要掃描所有分區(qū)，這個時候就需要記得確保嚴(yán)格模式被關(guān)閉。
2. order by必須帶limit
因為要保證全局有序需要將所有的數(shù)據(jù)拉到一個Reducer上，當(dāng)數(shù)據(jù)集比較大時速度會很慢。個人猜測可能是設(shè)置了limit N之后就會有一個很簡單的優(yōu)化算法：每個Reducer排序取N然后再合并排序取N即可，可大大減少數(shù)據(jù)傳輸量。
3. 禁止笛卡爾積查詢（join必須有on連接條件）
Hive不會對where中的連接條件優(yōu)化為on，所以join必須帶有on連接條件，不允許兩個表直接相乘。
6. 并行執(zhí)行優(yōu)化
Hive會將一個查詢轉(zhuǎn)化成一個或者多個階段。這樣的階段可以是MapReduce階段、抽樣階段、合并階段、limit階段。或者Hive執(zhí)行過程中可能需要的其他階段。默認(rèn)情況下，Hive一次只會執(zhí)行一個階段。不過，某個特定的job可能包含眾多的階段，而這些階段可能并非完全互相依賴的，也就是說有些階段是可以并行執(zhí)行的，這樣可能使得整個job的執(zhí)行時間縮短。如果有更多的階段可以并行執(zhí)行，那么job可能就越快完成。
通過設(shè)置參數(shù)hive.exec.parallel值為true，就可以開啟并發(fā)執(zhí)行。在共享集群中，需要注意下，如果job中并行階段增多，那么集群利用率就會增加。
set?hive.exec.parallel=true;?//打開任務(wù)并行執(zhí)行
set?hive.exec.parallel.thread.number=16;?//同一個sql允許最大并行度，默認(rèn)為8。
當(dāng)然得是在系統(tǒng)資源比較空閑的時候才有優(yōu)勢，否則沒資源，并行也起不來。
7. JVM優(yōu)化
JVM重用是Hadoop調(diào)優(yōu)參數(shù)的內(nèi)容，其對Hive的性能具有非常大的影響，特別是對于很難避免小文件的場景或task特別多的場景，這類場景大多數(shù)執(zhí)行時間都很短。
Hadoop的默認(rèn)配置通常是使用派生JVM來執(zhí)行map和Reduce任務(wù)的。這時JVM的啟動過程可能會造成相當(dāng)大的開銷，尤其是執(zhí)行的job包含有成百上千task任務(wù)的情況。JVM重用可以使得JVM實例在同一個job中重新使用N次。N的值可以在Hadoop的mapred-site.xml文件中進(jìn)行配置。通常在10-20之間，具體多少需要根據(jù)具體業(yè)務(wù)場景測試得出。
<property>
??<name>mapreduce.job.jvm.numtasksname>
??<value>10value>
??<description>How?many?tasks?to?run?per?jvm.?If?set?to?-1,?there?is
??no?limit.?
??description>
property>
我們也可以在Hive中設(shè)置：
set mapred.job.reuse.jvm.num.tasks=10 設(shè)置jvm重用
這個功能的缺點是，開啟JVM重用將一直占用使用到的task插槽，以便進(jìn)行重用，直到任務(wù)完成后才能釋放。如果某個“不平衡的”job中有某幾個reduce task執(zhí)行的時間要比其他Reduce task消耗的時間多的多的話，那么保留的插槽就會一直空閑著卻無法被其他的job使用，直到所有的task都結(jié)束了才會釋放。
8. 推測執(zhí)行優(yōu)化
在分布式集群環(huán)境下，因為程序bug（包括Hadoop本身的bug），負(fù)載不均衡或者資源分布不均等原因，會造成同一個作業(yè)的多個任務(wù)之間運行速度不一致，有些任務(wù)的運行速度可能明顯慢于其他任務(wù)（比如一個作業(yè)的某個任務(wù)進(jìn)度只有50%，而其他所有任務(wù)已經(jīng)運行完畢），則這些任務(wù)會拖慢作業(yè)的整體執(zhí)行進(jìn)度。為了避免這種情況發(fā)生，Hadoop采用了推測執(zhí)行（Speculative Execution）機(jī)制，它根據(jù)一定的法則推測出“拖后腿”的任務(wù)，并為這樣的任務(wù)啟動一個備份任務(wù)，讓該任務(wù)與原始任務(wù)同時處理同一份數(shù)據(jù)，并最終選用最先成功運行完成任務(wù)的計算結(jié)果作為最終結(jié)果。
設(shè)置開啟推測執(zhí)行參數(shù)：Hadoop的mapred-site.xml文件中進(jìn)行配置：
<property>
??<name>mapreduce.map.speculativename>
??<value>truevalue>
??<description>If?true,?then?multiple?instances?of?some?map?tasks?
???????????????may?be?executed?in?parallel.description>
property>
<property>
??<name>mapreduce.reduce.speculativename>
??<value>truevalue>
??<description>If?true,?then?multiple?instances?of?some?reduce?tasks?
???????????????may?be?executed?in?parallel.description>
property>
Hive本身也提供了配置項來控制reduce-side的推測執(zhí)行:
set hive.mapred.reduce.tasks.speculative.execution=true
關(guān)于調(diào)優(yōu)這些推測執(zhí)行變量，還很難給一個具體的建議。如果用戶因為輸入數(shù)據(jù)量很大而需要執(zhí)行長時間的map或者reduce task的話，那么啟動推測執(zhí)行造成的浪費是非常巨大的。
9. 數(shù)據(jù)傾斜優(yōu)化
數(shù)據(jù)傾斜的原理都知道，就是某一個或幾個key占據(jù)了整個數(shù)據(jù)的90%，這樣整個任務(wù)的效率都會被這個key的處理拖慢，同時也可能會因為相同的key會聚合到一起造成內(nèi)存溢出。
Hive的數(shù)據(jù)傾斜一般的處理方案：
常見的做法，通過參數(shù)調(diào)優(yōu)：
set?hive.map.aggr=true;??
set?hive.groupby.skewindata?=?ture;
當(dāng)選項設(shè)定為true時，生成的查詢計劃有兩個MapReduce任務(wù)。
在第一個MapReduce中，map的輸出結(jié)果集合會隨機(jī)分布到reduce中，每個reduce做部分聚合操作，并輸出結(jié)果。
這樣處理的結(jié)果是，相同的Group By Key有可能分發(fā)到不同的reduce中，從而達(dá)到負(fù)載均衡的目的；
第二個MapReduce任務(wù)再根據(jù)預(yù)處理的數(shù)據(jù)結(jié)果按照Group By Key分布到reduce中（這個過程可以保證相同的Group By Key分布到同一個reduce中），最后完成最終的聚合操作。
但是這個處理方案對于我們來說是個黑盒，無法把控。
那么在日常需求的情況下如何處理這種數(shù)據(jù)傾斜的情況呢：
sample采樣，獲取哪些集中的key；
將集中的key按照一定規(guī)則添加隨機(jī)數(shù)；
進(jìn)行join，由于打散了，所以數(shù)據(jù)傾斜避免了；
在處理結(jié)果中對之前的添加的隨機(jī)數(shù)進(jìn)行切分，變成原始的數(shù)據(jù)。
10. 動態(tài)分區(qū)調(diào)整
動態(tài)分區(qū)屬性：設(shè)置為true表示開啟動態(tài)分區(qū)功能（默認(rèn)為false）
hive.exec.dynamic.partition=true;
動態(tài)分區(qū)屬性：設(shè)置為nonstrict,表示允許所有分區(qū)都是動態(tài)的（默認(rèn)為strict） 設(shè)置為strict，表示必須保證至少有一個分區(qū)是靜態(tài)的
hive.exec.dynamic.partition.mode=strict;
動態(tài)分區(qū)屬性：每個mapper或reducer可以創(chuàng)建的最大動態(tài)分區(qū)個數(shù)
hive.exec.max.dynamic.partitions.pernode=100;
動態(tài)分區(qū)屬性：一個動態(tài)分區(qū)創(chuàng)建語句可以創(chuàng)建的最大動態(tài)分區(qū)個數(shù)
hive.exec.max.dynamic.partitions=1000;
動態(tài)分區(qū)屬性：全局可以創(chuàng)建的最大文件個數(shù)
hive.exec.max.created.files=100000;
11. 其他參數(shù)調(diào)優(yōu)
開啟CLI提示符前打印出當(dāng)前所在的數(shù)據(jù)庫名
set hive.cli.print.current.db=true;
讓CLI打印出字段名稱
hive.cli.print.header=true;
設(shè)置任務(wù)名稱，方便查找監(jiān)控
set mapred.job.name=P_DWA_D_IA_S_USER_PROD;
決定是否可以在 Map 端進(jìn)行聚合操作
set hive.map.aggr=true;
有數(shù)據(jù)傾斜的時候進(jìn)行負(fù)載均衡
set hive.groupby.skewindata=true;
對于簡單的不需要聚合的類似SELECT col from table LIMIT n語句，不需要起MapReduce job，直接通過Fetch task獲取數(shù)據(jù)
set hive.fetch.task.conversion=more;
最后
代碼優(yōu)化原則：
理透需求原則，這是優(yōu)化的根本；
把握數(shù)據(jù)全鏈路原則，這是優(yōu)化的脈絡(luò)；
堅持代碼的簡潔原則，這讓優(yōu)化更加簡單；
沒有瓶頸時談?wù)搩?yōu)化，這是自尋煩惱。
推薦閱讀
歡迎長按掃碼關(guān)注「數(shù)據(jù)管道」