Hive JOIN使用詳解

轉自http://shiyanjun.cn/archives/588.html

 

Hive是基於Hadoop平臺的，它提供了類似SQL一樣的查詢語言HQL。有了Hive，如果使用過SQL語言，並且不理解Hadoop MapReduce執行原理，也就無法通過程式設計來實現MR，但是你仍然可以很容易地編寫出特定查詢分析的HQL語句，通過使用類似SQL的語法，將HQL查詢語句提交Hive系統執行查詢分析，最終Hive會幫你轉換成底層Hadoop能夠理解的MR Job。 對於最基本的HQL查詢我們不再累述，這裡主要說明Hive中進行統計分析時使用到的JOIN操作。在說明Hive JOIN之前，我們先簡單說明一下，Hadoop執行MR Job的基本過程（執行機制），能更好的幫助我們理解HQL轉換到底層的MR Job後是如何執行的。我們重點說明MapReduce執行過程中，從Map端到Reduce端這個過程（Shuffle）的執行情況，如圖所示（來自《Hadoop: The Definitive Guide》）：

基本執行過程，描述如下：

1. 一個InputSplit輸入到map，會執行我們實現的Mapper的處理邏輯，對資料進行對映操作。
2. map輸出時，會首先將輸出中間結果寫入到map自帶的buffer中（buffer預設大小為100M，可以通過io.sort.mb配置）。
3. map自帶的buffer使用容量達到一定門限（預設0.80或80%，可以通過io.sort.spill.percent配置），一個後臺執行緒會準備將buffer中的資料寫入到磁碟。
4. 這個後臺執行緒在將buffer中資料寫入磁碟之前，會首先將buffer中的資料進行partition（分割槽，partition數為Reducer的個數），對於每個的資料會基於Key進行一個in-memory排序。
5. 排序後，會檢查是否配置了Combiner，如果配置了則直接作用到已排序的每個partition的資料上，對map輸出進行化簡壓縮（這樣寫入磁碟的資料量就會減少，降低I/O操作開銷）。
6. 現在可以將經過處理的buffer中的資料寫入磁碟，生成一個檔案（每次buffer容量達到設定的門限，都會對應著一個寫入到磁碟的檔案）。
7. map任務結束之前，會對輸出的多個檔案進行合併操作，合併成一個檔案（若map輸出至少3個檔案，在多個檔案合併後寫入之前，如果配置了Combiner，則會執行來化簡壓縮輸出的資料，檔案個數可以通過min.num.splits.for.combine配置；如果指定了壓縮map輸出，這裡會根據配置對資料進行壓縮寫入磁碟），這個檔案仍然保持partition和排序的狀態。
8. reduce階段，每個reduce任務開始從多個map上拷貝屬於自己partition（map階段已經做好partition，而且每個reduce任務知道應該拷貝哪個partition；拷貝過程是在不同節點之間，Reducer上拷貝執行緒基於HTTP來通過網路傳輸資料）。
9. 每個reduce任務拷貝的map任務結果的指定partition，也是先將資料放入到自帶的一個buffer中（buffer預設大小為Heap記憶體的70%，可以通過mapred.job.shuffle.input.buffer.percent配置），如果配置了map結果進行壓縮，則這時要先將資料解壓縮後放入buffer中。
10. reduce自帶的buffer使用容量達到一定門限（預設0.66或66%，可以通過mapred.job.shuffle.merge.percent配置），或者buffer中存放的map的輸出的數量達到一定門限（預設1000，可以通過mapred.inmem.merge.threshold配置），buffer中的資料將會被寫入到磁碟中。
11. 在將buffer中多個map輸出合併寫入磁碟之前，如果設定了Combiner，則會化簡壓縮合並的map輸出。
12. 當屬於該reducer的map輸出全部拷貝完成，則會在reducer上生成多個檔案，這時開始執行合併操作，並保持每個map輸出資料中Key的有序性，將多個檔案合併成一個檔案（在reduce端可能存在buffer和磁碟上都有資料的情況，這樣在buffer中的資料可以減少一定量的I/O寫入操作開銷）。
13. 最後，執行reduce階段，執行我們實現的Reducer中化簡邏輯，最終將結果直接輸出到HDFS中（因為Reducer執行在DataNode上，輸出結果的第一個replica直接在儲存在本地節點上）。

通過上面的描述我們看到，在MR執行過程中，存在Shuffle過程的MR需要在網路中的節點之間（Mapper節點和Reducer節點）拷貝資料，如果傳輸的資料量很大會造成一定的網路開銷。而且，Map端和Reduce端都會通過一個特定的buffer來在記憶體中臨時快取資料，如果無法根據實際應用場景中資料的規模來使用Hive，尤其是執行表的JOIN操作，有可能很浪費資源，降低了系統處理任務的效率，還可能因為記憶體不足造成OOME問題，導致計算任務失敗。 下面，我們說明Hive中的JOIN操作，針對不同的JOIN方式，應該如何來實現和優化：

生成一個MR Job

多表連線，如果多個表中每個表都使用同一個列進行連線（出現在JOIN子句中），則只會生成一個MR Job，例如：

1	SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

三個表a、b、c都分別使用了同一個欄位進行連線，亦即同一個欄位同時出現在兩個JOIN子句中，從而只生成一個MR Job。

生成多個MR Job

多表連線，如果多表中，其中存在一個表使用了至少2個欄位進行連線（同一個表的至少2個列出現在JOIN子句中），則會至少生成2個MR Job，例如：

1	SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

三個表基於2個欄位進行連線，這兩個欄位b.key1和b.key2同時出現在b表中。連線的過程是這樣的：首先a和b表基於a.key和b.key1進行連線，對應著第一個MR Job；表a和b連線的結果，再和c進行連線，對應著第二個MR Job。

表連線順序優化

多表連線，會轉換成多個MR Job，每一個MR Job在Hive中稱為JOIN階段（Stage）。在每一個Stage，按照JOIN順序中的最後一個表應該儘量是大表，因為JOIN前一階段生成的資料會存在於Reducer的buffer中，通過stream最後面的表，直接從Reducer的buffer中讀取已經緩衝的中間結果資料（這個中間結果資料可能是JOIN順序中，前面表連線的結果的Key，資料量相對較小，記憶體開銷就小），這樣，與後面的大表進行連線時，只需要從buffer中讀取快取的Key，與大表中的指定Key進行連線，速度會更快，也可能避免記憶體緩衝區溢位。例如：

1	SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

這個JOIN語句，會生成一個MR Job，在選擇JOIN順序的時候，資料量相比應該是b < c，表a和b基於a.key = b.key1進行連線，得到的結果（基於a和b進行連線的Key）會在Reducer上快取在buffer中，在與c進行連線時，從buffer中讀取Key（a.key=b.key1）來與表c的c.key進行連線。 另外，也可以通過給出一些Hint資訊來啟發JOIN操作，這指定了將哪個表作為大表，從而得到優化。例如：

1	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)

上述JOIN語句中，a表被視為大表，則首先會對錶b和c進行JOIN，然後再將得到的結果與表a進行JOIN。

基於條件的LEFT OUTER JOIN優化

左連線時，左表中出現的JOIN欄位都保留，右表沒有連線上的都為空。對於帶WHERE條件的JOIN語句，例如：

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

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

執行順序是，首先完成2表JOIN，然後再通過WHERE條件進行過濾，這樣在JOIN過程中可能會輸出大量結果，再對這些結果進行過濾，比較耗時。可以進行優化，將WHERE條件放在ON後，例如：

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

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

這樣，在JOIN的過程中，就對不滿足條件的記錄進行了預先過濾，可能會有更好的表現。

左半連線（LEFT SEMI JOIN）

左半連線實現了類似IN/EXISTS的查詢語義，使用關聯式資料庫子查詢的方式實現查詢SQL，例如：

1	SELECT a.key, a.value FROM a WHERE a.key IN (SELECT b.key FROM b);

使用Hive對應於如下語句：

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

需要注意的是，在LEFT SEMI JOIN中，表b只能出現在ON子句後面，不能夠出現在SELECT和WHERE子句中。 關於子查詢，這裡提一下，Hive支援情況如下：

• 在0.12版本，只支援FROM子句中的子查詢；
• 在0.13版本，也支援WHERE子句中的子查詢。

Map Side JOIN

Map Side JOIN優化的出發點是，Map任務輸出後，不需要將資料拷貝到Reducer節點，降低的資料在網路節點之間傳輸的開銷。 多表連線，如果只有一個表比較大，其他表都很小，則JOIN操作會轉換成一個只包含Map的Job，例如：

1	SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a JOIN b ON a.key = b.key

對於表a資料的每一個Map，都能夠完全讀取表b的資料。這裡，表a與b不允許執行FULL OUTER JOIN、RIGHT OUTER JOIN。

BUCKET Map Side JOIN

我們先看兩個表a和b的DDL，表a為：

1	CREATE TABLE a(key INT, othera STRING)

2	CLUSTERED BY(key) INTO 4 BUCKETS

3	ROW FORMAT DELIMITED

4	FIELDS TERMINATED BY '\001'

5	COLLECTION ITEMS TERMINATED BY '\002'

6	MAP KEYS TERMINATED BY '\003'

7	STORED AS SEQUENCEFILE;

表b為：

1	CREATE TABLE b(key INT, otherb STRING)

2	CLUSTERED BY(key) INTO 32 BUCKETS

3	ROW FORMAT DELIMITED

4	FIELDS TERMINATED BY '\001'

5	COLLECTION ITEMS TERMINATED BY '\002'

6	MAP KEYS TERMINATED BY '\003'

7	STORED AS SEQUENCEFILE;

現在要基於a.key和b.key進行JOIN操作，此時JOIN列同時也是BUCKET列，JOIN語句如下：

1	SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a JOIN b ON a.key = b.key

並且表a有4個BUCKET，表b有32個BUCKET，預設情況下，對於表a的每一個BUCKET，都會去獲取表b中的每一個BUCKET來進行JOIN，這回造成一定的開銷，因為只有表b中滿足JOIN條件的BUCKET才會真正與表a的BUCKET進行連線。 這種預設行為可以進行優化，通過改變預設JOIN行為，只需要設定變數：

1	set hive.optimize.bucketmapjoin = true

這樣，JOIN的過程是，表a的BUCKET 1只會與表b中的BUCKET 1進行JOIN，而不再考慮表b中的其他BUCKET 2~32。 如果上述表具有相同的BUCKET，如都是32個，而且還是排序的，亦即，在表定義中在CLUSTERED BY(key)後面增加如下約束：

1	SORTED BY(key)

則上述JOIN語句會執行一個Sort-Merge-Bucket (SMB) JOIN，同樣需要設定如下引數來改變預設行為，優化JOIN時只遍歷相關的BUCKET即可：

1	set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;

2	set hive.optimize.bucketmapjoin = true;

3	set hive.optimize.bucketmapjoin.sortedmerge = true;