Hive 查詢優化總結

一、join優化

Join查找操作的基本原則：應該將條目少的表/子查詢放在 Join 操作符的左邊。原因是在 Join 操作的 Reduce 階段，位于 Join 操作符左邊的表的內容會被加載進內存，將條目少的表放在左邊，可以有效減少發生內存溢出錯誤的幾率。

Join查找操作中如果存在多個join，且所有參與join的表中其參與join的key都相同，則會將所有的join合并到一個mapred程序中。

案例：

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)  在一個mapre程序中執行join

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)   在兩個mapred程序中執行join

Map join的關鍵在于join操作中的某個表的數據量很小，案例：

SELECT /*+ MAPJOIN(b) */ a.key, a.value

  FROM a join b on a.key = b.key 

Mapjoin 的限制是無法執行a FULL/RIGHT OUTER JOIN b，和map join相關的hive參數：hive.join.emit.interval  hive.mapjoin.size.key  hive.mapjoin.cache.numrows

由于join操作是在where操作之前執行，所以當你在執行join時，where條件并不能起到減少join數據的作用；案例：

SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)

  WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'

最好修改為：

SELECT a.val, b.val FROM a LEFT OUTER JOIN b

  ON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07')

在join操作的每一個mapred程序中，hive都會把出現在join語句中相對靠后的表的數據stream化，相對靠前的變的數據緩存在內存中。當然，也可以手動指定stream化的表：SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

二、group by 優化

Map端聚合，首先在map端進行初步聚合，最后在reduce端得出最終結果，相關參數：

· hive.map.aggr = true是否在 Map 端進行聚合，默認為 True

· hive.groupby.mapaggr.checkinterval = 100000在 Map 端進行聚合操作的條目數目

數據傾斜聚合優化，設置參數hive.groupby.skewindata = true，當選項設定為 true，生成的查詢計劃會有兩個 MR Job。第一個 MR Job 中，Map 的輸出結果集合會隨機分布到 Reduce 中，每個 Reduce 做部分聚合操作，并輸出結果，這樣處理的結果是相同的 Group By Key 有可能被分發到不同的 Reduce 中，從而達到負載均衡的目的；第二個 MR Job 再根據預處理的數據結果按照 Group By Key 分布到 Reduce 中（這個過程可以保證相同的 Group By Key 被分布到同一個 Reduce 中），最后完成最終的聚合操作。

三、合并小文件

文件數目過多，會給 HDFS 帶來壓力，并且會影響處理效率，可以通過合并 Map 和 Reduce 的結果文件來消除這樣的影響：

· hive.merge.mapfiles = true是否和并 Map 輸出文件，默認為 True

· hive.merge.mapredfiles = false是否合并 Reduce 輸出文件，默認為 False

· hive.merge.size.per.task = 256*1000*1000合并文件的大小

四、Hive實現(not) in

通過left outer join進行查詢,（假設B表中包含另外的一個字段 key1 

select a.key from a left outer join b on a.key=b.key where b.key1 is null

通過left semi join 實現 in

SELECT a.key, a.val FROM a LEFT SEMI JOIN b on (a.key = b.key)

Left semi join 的限制：join條件中右邊的表只能出現在join條件中。

五、排序優化

Order by 實現全局排序，一個reduce實現，效率低

Sort by 實現部分有序，單個reduce輸出的結果是有序的，效率高，通常和DISTRIBUTE BY關鍵字一起使用（DISTRIBUTE BY關鍵字 可以指定map 到 reduce端的分發key）

CLUSTER BY col1 等價于DISTRIBUTE BY col1 SORT BY col1

六、使用分區

Hive中的每個分區都對應hdfs上的一個目錄，分區列也不是表中的一個實際的字段，而是一個或者多個偽列，在表的數據文件中實際上并不保存分區列的信息與數據。Partition關鍵字中排在前面的為主分區（只有一個），后面的為副分區

靜態分區：靜態分區在加載數據和使用時都需要在sql語句中指定

          案例：(stat_date='20120625',province='hunan')

動態分區：使用動態分區需要設置hive.exec.dynamic.partition參數值為true，默認值為false，在默認情況下，hive會假設主分區時靜態分區，副分區使用動態分區；如果想都使用動態分區，需要設置set hive.exec.dynamic.partition.mode=nostrick，默認為strick

          案例：(stat_date='20120625',province)

七、Distinct 使用

Hive支持在group by時對同一列進行多次distinct操作，卻不支持在同一個語句中對多個列進行distinct操作。

八、Hql使用自定義的mapred腳本

注意事項：在使用自定義的mapred腳本時，關鍵字MAP REDUCE 是語句SELECT TRANSFORM ( ... )的語法轉換，并不意味著使用MAP關鍵字時會強制產生一個新的map過程，使用REDUCE關鍵字時會產生一個red過程。

自定義的mapred腳本可以是hql語句完成更為復雜的功能，但是性能比hql語句差了一些，應該盡量避免使用，如有可能，使用UDTF函數來替換自定義的mapred腳本

九、UDTF

UDTF將單一輸入行轉化為多個輸出行，并且在使用UDTF時，select語句中不能包含其他的列，UDTF不支持嵌套，也不支持group by 、sort by等語句。如果想避免上述限制，需要使用lateral view語法，案例：

select a.timestamp, get_json_object(a.appevents, '$.eventid'), get_json_object(a.appenvets, '$.eventname') from log a;

select a.timestamp, b.*

from log a lateral view json_tuple(a.appevent, 'eventid', 'eventname') b as f1, f2;

其中，get_json_object為UDF函數，json_tuple為UDTF函數。

UDTF函數在某些應用場景下可以大大提高hql語句的性能，如需要多次解析json或者xml數據的應用場景。

十、聚合函數count和sum

Count和sum函數可能是在hql語句中使用的最為頻繁的兩個聚合函數了，但是在hive中count函數在計算distinct value時支持加入條件過濾。