Hive Join，hivejoin

Hive Join，hivejoin

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins

LanguageManualJoins

Join Syntax

Hive支援下面的表join文法結構：

join_table:

    table_reference JOIN table_factor[join_condition]

  | table_reference {LEFT|RIGHT|FULL} [OUTER]JOIN table_reference join_condition

  | table_reference LEFT SEMI JOINtable_reference join_condition

  | table_reference CROSS JOIN table_reference[join_condition] (as of Hive 0.10)

 

table_reference:

    table_factor

  | join_table

 

table_factor:

    tbl_name [alias]

  | table_subquery alias

  | ( table_references )

 

join_condition:

    ON equality_expression ( ANDequality_expression )*

 

equality_expression:

    expression = expression

注釋：在Hive的joins，outerjoins和left semi joins只支援等式串連，不支援不等式串連，因為不等式串連很難轉化成map/reduce的job。

 

Version 0.13.0+: Implicit joinnotation（隱式串連符號）

從Hive0.13.0開始支援Implicit join notation，允許from子句去join以逗號分隔的表，省略掉join關鍵字，如下：

SELECT *
FROM table1t1, table2 t2, table3 t3
WHERE t1.id= t2.id AND t2.id = t3.id AND t1.zipcode = '02535';

 

Version 0.13.0+: Unqualified columnreferences

從Hive0.13.0開始支援非指定欄位的引用，如下：

CREATE TABLE a (k1 string, v1 string);
CREATE TABLE b (k2 string, v2 string);

SELECT k1, v1, k2, v2
FROM a JOIN b ON k1 = k2; 

如果一個欄位在多個表中出現，則Hive會指出它是一個歧義的引用。

Examples

下面有幾點關於Hive的join串連重要的地方：

1）  只支援等式join

SELECTa.* FROM a JOIN b ON (a.id = b.id);

SELECTa.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department);

2）  支援多張表join

SELECTa.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key =b.key2);

3）  產生一個MRJob：多表串連，如果多個表中每個表都使用同一個列進行串連（出現在JOIN子句中），則只會產生一個MR（map/reduce）Job比如：

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子句中，從而只產生一個MRJob。

產生多個MRJob：多表串連，如果多表中，其中存在一個表使用了至少2個欄位進行串連（同一個表的至少2個列出現在JOIN子句中），則會至少產生2個MRJob，如下的sql將轉化為兩個map/reduce任務：

SELECTa.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進行串連，對應著第一個MRJob；表a和b串連的結果，再和c進行串連，對應著第二個MRJob。

4）   表串連順序最佳化

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

SELECTa.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key =b.key1);

這個JOIN語句，會產生一個MRJob，在選擇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操作，這指定了將哪個表作為大表，從而得到最佳化。例如：

SELECT/*+ STREAMTABLE(a) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOINc ON (c.key = b.key1);

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

如果STREAMTABLE省略掉了，那麼Hive會在join中streams最右邊的表。

5）  LEFT，RIGHT和FULLOUTER joins的存在是為了提供更多對on語句中沒有匹配的控制。

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

6）  基於條件的LEFTOUTER JOIN最佳化(邏輯同樣適合RIGHTand FULL joins)

表的join操作是在where語句之前執行的。

左串連時，左表中出現的欄位值都會保留，右表沒有串連上的欄位值都為空白。

例如：

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

WHERE a.ds='2015-06-21' ANDb.ds='2015-06-21';

執行順序為：首先a和b表join，然後結果再通過where條件過濾，這樣我們會發現在join過程中可能會輸出大量結果，再對這些結果進行過濾操作，比較耗時。

進行最佳化時，可以將where條件放在on語句中，如下：

SELECT a.val, b.val FROM a LEFT OUTERJOIN b

ON (a.key=b.key AND b.ds='2015-06-21' ANDa.ds='2015-06-21');

 

 

Join 是不能交換位置的。無論是LEFT 還是 RIGHT join，都是左串連的，例如：

 

SELECT a.val1,a.val2, b.val, c.val

FROM a

JOIN b ON (a.key = b.key)

LEFT OUTER JOIN c ON (a.key = c.key);

 

先join a表和b表，丟掉所有joinkey中不匹配的記錄，然後用這一中間結果和c表做join。當一個key在a表和c表都存在，但是b表中不存在的時候：整個記錄在第一次join，即ajoin b的時候都被丟掉了(包括a.val1,a.val2和a.key)，然後我們再和c表join的時候，就會得到這樣的結果：a.val1,a.val2, b.val, null。

如果使用RIGHT OUTERJOIN代替LEFT，那麼我們將得到這樣的結果：

NULL,NULL,NULL,c.val

 

樣本如下：

hive (hive)> select * from a;

a.id  a.name

1     jiangshouzhuang

2     zhangyun

 

hive (hive)> select * from b;

b.id b.name

1     jiangshouzhuang

3     baobao

 

hive (hive)> select * from c;

c.id  c.name

2     zhangyun

4     xiaosan

 

hive (hive)> SELECT a.name, b.name, c.name

           > FROM a

           > JOIN bON (a.id = b.id)

           > LEFTOUTER JOIN c ON (a.id = c.id);

jiangshouzhuang  jiangshouzhuang  NULL

 

hive (hive)> SELECT a.name, b.name, c.name

          > FROM a

           > JOIN bON (a.id = b.id)

           > rightOUTER JOIN c ON (a.id = c.id);

a.name   b.name   c.name

NULL     NULL     zhangyun

NULL     NULL     xiaosan

 

hive (hive)> SELECT a.name, b.name, c.name

           > FROM cLEFT OUTER JOIN a ON (c.id = a.id) LEFT OUTER JOIN b

a.name   b.name   c.name

zhangyun      NULL     zhangyun

NULL     NULL     xiaosan

7）  左半串連(leftsemi join)

左半串連可以更加有效地實現了類似in/exists的查詢語義，例如：

SELECTa.key, a.value

FROMa

WHEREa.key in

 (SELECT b.key FROM B);

可以用下面的語句替換：

SELECTa.key, a.val

FROMa LEFT SEMI JOIN b ON (a.key = b.key);

需要注意的是，在leftsemi join中，b表只能出現在on子句的後面，不能出現在select和where子句中。

關於子查詢，Hive支援情況如下：

·        在0.12版本，只支援FROM子句中的子查詢；

·        在0.13版本，也支援WHERE子句中的子查詢；

·        在0.13不包，IN/NOTIN/EXISTS/NOT EXISTS支援使用子查詢。

8）  MapSide Join

MapSide Join最佳化的出發點是，Map任務的輸出，不需要將資料拷貝到Reduce節點，從而降低了資料在網路節點之間傳輸的開銷。

對於多表串連，如果只有一個表比較大，其他表都很小，則join操作會轉化為一個只包含Map的job任務，例如：

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

FROMa JOIN b ON a.key = b.key;

對於a表資料的每一個map，都很夠完全讀取b表的資料。

注意：這裡，表a和表b不允許執行FULL/RIGHTOUTER JOIN操作。

 

補充：

Hive內建提供的最佳化機制之一就包括MapJoin:

在Hivev0.7之前，需要給出MapJoin的指示(hint)，Hive才會提供MapJoin的最佳化。

Hivev0.7之後的版本已經不需要給出MapJoin的指示就進行最佳化。

它是通過如下配置參數來控制的：

hive>set hive.auto.convert.join=true;

hive0.11之後，在表的大小符合設定時

hive.auto.convert.join.noconditionaltask=true

hive.auto.convert.join.noconditionaltask.size=10000000

hive.mapjoin.smalltable.filesize=25000000

預設會把join轉換為mapjoin(hive.ignore.mapjoin.hint=true,hive.auto.convert.join=true)

Hivev0.12.0版本，預設狀況下MapJoin最佳化是開啟的。

也就是hive.auto.convert.join=true。

Hive還提供另外一個參數--表檔案的大小作為開啟和關閉MapJoin的閾值。

hive.mapjoin.smalltable.filesize=25000000

 

9）  BucketMap Side Join

如果表進行join，同時join的列也是bucket列，並且一張表的bucket數是另外一張表的bucket數的倍數，那麼表之間的buckets可以進行join。

如果表A有4個buckets，表B也有4個buckets，那麼下面的join

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

FROMa JOIN b ON a.key = b.key;

只需在mapper階段完成。預設情況下，對於表a的每一個bucket，都會去擷取表b中的每一個bucket來進行join，這回造成一定的開銷，因為只有表b中滿足join條件的bucket才會真正與表a的bucket進行串連。

可以設定如下參數進行最佳化：

sethive.optimize.bucketmapjoin=true;

這樣，join的過程是：表b的bucket1隻會與表b的bucket1進行join，而不再考慮表b中的其他bucket2~4。

 

樣本：

建立表a：

CREATE TABLE a(key INT, value STRING)

CLUSTERED BY(key) INTO 6 BUCKETS

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\001'

COLLECTION ITEMS TERMINATED BY '\002'

MAP KEYS TERMINATED BY '\003'

STORED AS SEQUENCEFILE;

建立表b：

CREATE TABLE b(key INT, value STRING)

CLUSTERED BY(key) INTO 36 BUCKETS

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\001'

COLLECTION ITEMS TERMINATED BY '\002'

MAP KEYS TERMINATED BY '\003'

STORED AS SEQUENCEFILE;

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

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

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

SORTED BY(key)

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

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

set hive.optimize.bucketmapjoin = true;

set hive.optimize.bucketmapjoin.sortedmerge = true;

上面三個參數預設值為：

hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat

hive.optimize.bucketmapjoin=false

hive.optimize.bucketmapjoin.sortedmerge=false;

10）        MapJoin Restrictions

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

FROMa JOIN b ON a.key = b.key;

不需要reducer，對於表A的每個mapper，可以完全讀取表B。

下面列出的都是MapJoin不支援的：

 •UnionFollowed by a MapJoin

•LateralView Followed by a MapJoin

•ReduceSink (Group By/Join/Sort By/Cluster By/Distribute By) Followed by MapJoin

•MapJoinFollowed by Union

•MapJoinFollowed by Join

•MapJoinFollowed by MapJoin

配置參數hive.auto.convert.join=true，如果可能的話，自動將joins轉換為mapjoins，它應該取代使用mapjoinhint。

下面的查詢應該使用mapjoinhint：

       如果所有的inputs都被bucketed或者sorted，並且join應該被轉換為bucketizedmap-size join或者bucketized-mergejoin。

 

參考內容

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+JoinOptimization

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SubQueries

https://cwiki.apache.org/confluence/display/Hive/OuterJoinBehavior