Hive的查詢注意事項以及優化總結
Hive是將符合SQL語法的字符串解析生成可以在Hadoop上執行的MapReduce的工具。使用Hive盡量按照分布式計算的一些特點來設計sql，和傳統關系型數據庫有區別，所以需要去掉原有關系型數據庫下開發的一些固有思維。
基本原則：
1：盡量盡早地過濾數據，減少每個階段的數據量,對于分區表要加分區，同時只選擇需要使用到的字段
- select ... from A
- join B
- on A.key = B.key
- where A.userid>10
- and B.userid<10
- and A.dt='20120417'
- and B.dt='20120417';
- 應該改寫為：
- select .... from (select .... from A
- where dt='201200417'
- and userid>10
- ) a
- join ( select .... from B
- where dt='201200417'
- and userid < 10
- ) b
- on a.key = b.key;
2、對歷史庫的計算經驗 (這項是說根據不同的使用目的優化使用方法)
歷史庫計算和使用，分區
3：盡量原子化操作，盡量避免一個SQL包含復雜邏輯
可以使用中間表來完成復雜的邏輯
4 jion操作 小表要注意放在join的左邊(目前TCL里面很多都小表放在join的右邊)。
否則會引起磁盤和內存的大量消耗
5：如果union all的部分個數大于2，或者每個union部分數據量大，應該拆成多個insert into 語句，實際測試過程中，執行時間能提升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 ...;
5：寫SQL要先了解數據本身的特點，如果有join ,group操作的話，要注意是否會有數據傾斜
如果出現數據傾斜，應當做如下處理：
- set hive.exec.reducers.max=200;
- set mapred.reduce.tasks= 200;---增大Reduce個數
set hive.groupby.mapaggr.checkinterval=100000 ;--這個是group的鍵對應的記錄條數超過這個值則會進行分拆,值根據具體數據量設置
set hive.groupby.skewindata=true; --如果是group by過程出現傾斜 應該設置為true
set hive.skewjoin.key=100000; --這個是join的鍵對應的記錄條數超過這個值則會進行分拆,值根據具體數據量設置
set hive.optimize.skewjoin=true;--如果是join 過程出現傾斜 應該設置為true
(1) 啟動一次job盡可能的多做事情，一個job能完成的事情,不要兩個job來做
通常來說前面的任務啟動可以稍帶一起做的事情就一起做了,以便后續的多個任務重用,與此緊密相連的是模型設計,好的模型特別重要.
(2) 合理設置reduce個數
reduce個數過少沒有真正發揮hadoop并行計算的威力，但reduce個數過多，會造成大量小文件問題，數據量、資源情況只有自己最清楚，找到個折衷點,
(3) 使用hive.exec.parallel參數控制在同一個sql中的不同的job是否可以同時運行，提高作業的并發
2、讓服務器盡量少做事情，走***的路徑，以資源消耗最少為目標
比如:
(1) 注意join的使用
若其中有一個表很小使用map join，否則使用普通的reduce join，注意hive會將join前面的表數據裝載內存,所以較小的一個表在較大的表之前,減少內存資源的消耗
(2)注意小文件的問題
在hive里有兩種比較常見的處理辦法
***是使用Combinefileinputformat，將多個小文件打包作為一個整體的inputsplit，減少map任務數
- set mapred.max.split.size=256000000;
- set mapred.min.split.size.per.node=256000000
- set Mapred.min.split.size.per.rack=256000000
- set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat
第二是設置hive參數，將額外啟動一個MR Job打包小文件
hive.merge.mapredfiles = false 是否合并 Reduce 輸出文件，默認為 False
hive.merge.size.per.task = 256*1000*1000 合并文件的大小
(3)注意數據傾斜
在hive里比較常用的處理辦法
***通過hive.groupby.skewindata=true控制生成兩個MR Job,***個MR Job Map的輸出結果隨機分配到reduce做次預匯總,減少某些key值條數過多某些key條數過小造成的數據傾斜問題
第二通過hive.map.aggr = true(默認為true)在Map端做combiner,假如map各條數據基本上不一樣, 聚合沒什么意義，做combiner反而畫蛇添足,hive里也考慮的比較周到通過參數hive.groupby.mapaggr.checkinterval = 100000 (默認)hive.map.aggr.hash.min.reduction=0.5(默認),預先取100000條數據聚合,如果聚合后的條數/100000>0.5，則不再聚合。
(4)善用multi insert,union all
multi insert適合基于同一個源表按照不同邏輯不同粒度處理插入不同表的場景，做到只需要掃描源表一次，job個數不變，減少源表掃描次數
union all用好，可減少表的掃描次數，減少job的個數,通常預先按不同邏輯不同條件生成的查詢union all后，再統一group by計算,不同表的union all相當于multiple inputs,同一個表的union all,相當map一次輸出多條
(5) 參數設置的調優
集群參數種類繁多,舉個例子比如
可針對特定job設置特定參數,比如jvm重用,reduce copy線程數量設置(適合map較快，輸出量較大)
如果任務數多且小，比如在一分鐘之內完成，減少task數量以減少任務初始化的消耗。可以通過配置JVM重用選項減少task的消耗
一、控制Hive中Map和reduce的數量
Hive中的sql查詢會生成執行計劃，執行計劃以MapReduce的方式執行，那么結合數據和集群的大小，map和reduce的數量就會影響到sql執行的效率。
除了要控制Hive生成的Job的數量，也要控制map和reduce的數量。
1、 map的數量，通常情況下和split的大小有關系，之前寫的一篇blog“map和reduce的數量是如何定義的”有描述。
hive中默認的hive.input.format是org.apache.hadoop.hive.ql.io.CombineHiveInputFormat，對于combineHiveInputFormat,它的輸入的map數量
由三個配置決定，
mapred.min.split.size.per.node， 一個節點上split的至少的大小
mapred.min.split.size.per.rack 一個交換機下split至少的大小
mapred.max.split.size 一個split***的大小
它的主要思路是把輸入目錄下的大文件分成多個map的輸入, 并合并小文件, 做為一個map的輸入. 具體的原理是下述三步:
a、根據輸入目錄下的每個文件,如果其長度超過mapred.max.split.size,以block為單位分成多個split(一個split是一個map的輸入),每個split的長度都大于mapred.max.split.size, 因為以block為單位, 因此也會大于blockSize, 此文件剩下的長度如果大于mapred.min.split.size.per.node, 則生成一個split, 否則先暫時保留.
b、現在剩下的都是一些長度效短的碎片,把每個rack下碎片合并, 只要長度超過mapred.max.split.size就合并成一個split, ***如果剩下的碎片比mapred.min.split.size.per.rack大, 就合并成一個split, 否則暫時保留.
c、把不同rack下的碎片合并, 只要長度超過mapred.max.split.size就合并成一個split, 剩下的碎片無論長度, 合并成一個split.
舉例: mapred.max.split.size=1000
- mapred.min.split.size.per.node=300
- mapred.min.split.size.per.rack=100
輸入目錄下五個文件,rack1下三個文件,長度為2050,1499,10, rack2下兩個文件,長度為1010,80. 另外blockSize為500.
經過***步, 生成五個split: 1000,1000,1000,499,1000. 剩下的碎片為rack1下:50,10; rack2下10:80
由于兩個rack下的碎片和都不超過100, 所以經過第二步, split和碎片都沒有變化.
第三步,合并四個碎片成一個split, 長度為150.
如果要減少map數量, 可以調大mapred.max.split.size, 否則調小即可.
其特點是: 一個塊至多作為一個map的輸入，一個文件可能有多個塊，一個文件可能因為塊多分給做為不同map的輸入， 一個map可能處理多個塊，可能處理多個文件。
2、 reduce數量
可以在hive運行sql的時，打印出來，如下：
- Number of reduce tasks not specified. Estimated from input data size: 1
- In order to change the average load for a reducer (in bytes):
- set hive.exec.reducers.bytes.per.reducer=<number>
- In order to limit the maximum number of reducers:
- set hive.exec.reducers.max=<number>
- In order to set a constant number of reducers:
- set mapred.reduce.tasks=<number>
reduce數量由以下三個參數決定，
mapred.reduce.tasks(強制指定reduce的任務數量)
hive.exec.reducers.bytes.per.reducer(每個reduce任務處理的數據量，默認為1000^3=1G)
hive.exec.reducers.max(每個任務***的reduce數，默認為999)
計算reducer數的公式很簡單N=min( hive.exec.reducers.max ，總輸入數據量/ hive.exec.reducers.bytes.per.reducer )
只有一個reduce的場景：
a、沒有group by 的匯總
b、order by
c、笛卡爾積
二、join和Group的優化
對于普通的join操作，會在map端根據key的hash值，shuffle到某一個reduce上去，在reduce端做join連接操作，內存中緩存join左邊的表，遍歷右邊的表，一次做join操作。所以在做join操作時候，將數據量多的表放在join的右邊。
當數據量比較大，并且key分布不均勻，大量的key都shuffle到一個reduce上了，就出現了數據的傾斜。
對于Group操作，首先在map端聚合，***在reduce端坐聚合，hive默認是這樣的，以下是相關的參數
· hive.map.aggr = true是否在 Map 端進行聚合，默認為 True
· hive.groupby.mapaggr.checkinterval = 100000在 Map 端進行聚合操作的條目數目
對于join和Group操作都可能會出現數據傾斜。
三、以下有幾種解決這個問題的常見思路
1、參數hive.groupby.skewindata = true,解決數據傾斜的***鑰匙，查詢計劃會有兩個 MR Job。***個 MR Job 中，Map 的輸出結果集合會隨機分布到 Reduce 中，每個 Reduce 做部分聚合操作，并輸出結果，這樣處理的結果是相同的 Group By Key 有可能被分發到不同的 Reduce 中，從而達到負載均衡的目的;第二個 MR Job 再根據預處理的數據結果按照 Group By Key 分布到 Reduce 中(這個過程可以保證相同的 Group By Key 被分布到同一個 Reduce 中)，***完成最終的聚合操作。
2、where的條件寫在join里面，使得減少join的數量(經過map端過濾，只輸出復合條件的)
3、mapjoin方式，無reduce操作，在map端做join操作(map端cache小表的全部數據)，這種方式下無法執行Full/RIGHT OUTER join操作
4、對于count(distinct)操作，在map端以group by的字段和count的字段聯合作為key，如果有大量相同的key，那么會存在數據傾斜的問題
5、數據的傾斜還包括，大量的join連接key為空的情況，空的key都hash到一個reduce上去了，解決這個問題，***把空的key和非空的key做區分
空的key不做join操作。
當然有的hive操作，不存在數據傾斜的問題，比如數據聚合類的操作，像sum、count，因為已經在map端做了聚合操作了，到reduce端的數據相對少一些，所以不存在這個問題。
四、小文件的合并
大量的小文件導致文件數目過多，給HDFS帶來壓力，對hive處理的效率影響比較大，可以合并map和reduce產生的文件
· hive.merge.mapfiles = true是否和并 Map 輸出文件，默認為 True
· hive.merge.mapredfiles = false是否合并 Reduce 輸出文件，默認為 False
· hive.merge.size.per.task = 256*1000*1000合并文件的大小
五、in/exists(not)
通過left semi join 實現 in操作，一個限制就是join右邊的表只能出現在join條件中
六、分區裁剪
通過在條件中指定分區，來限制數據掃描的范圍，可以極大提高查詢的效率
七、排序
order by 排序，只存在一個reduce，這樣效率比較低。
可以用sort by操作,通常結合distribute by使用做reduce分區鍵
