Hive查詢Join

Select a.val,b.val From a [Left|Right|Full Outer] Join b On (a.key==b.key);

現有兩張表：sales?列出了人名及其所購商品的?ID；things?列出商品的?ID?和名稱：

hive> select * from sales;
OK
Joe     2
Hank    4
Ali     0
Eve     3
Hank    2
Time taken: 0.085 seconds, Fetched: 5 row(s)
hive> select * from things;
OK
2       Tie
4       Coat
3       Hat
1       Scarf
Time taken: 0.069 seconds, Fetched: 4 row(s)

1.內連接

Hive只支持等值連接，這意味著在?ON?關鍵字后的表達式中只能使用等號。具體JAVA-API實現詳見：MR案例：內連接

hive> select sales.*,things.*> from sales JOIN things ON(sales.id = things.id);
Joe     2       2       Tie
Hank    4       4       Coat
Eve     3       3       Hat
Hank    2       2       Tie

此外還可以在Where子句中指定連接條件。?

hive> select sales.*,things.*                  > from sales,things> where sales.id = things.id;
OK
Joe     2       2       Tie
Hank    4       4       Coat
Eve     3       3       Hat
Hank    2       2       Tie

單個的連接用一個?MR?作業實現。但是，如果多個連接的連接條件中使用了相同的列，那么平均每個連接可以至少用一個?MR?作業來實現。可以在查詢前使用?Explain關鍵字 來查看?Hive將為某個查詢使用多少個?MR?作業：【此部分在以后詳述】

hive> explain> select sales.*,things.*> from sales join things on (sales.id=things.id);

2.外連接

外連接可以讓你找到連接表中不能匹配的數據行。前面的內連接，【Ali】那一行沒有出現在輸出中。因為她所購買商品的ID沒有在things表中出現。具體JAVA-API實現詳見MR案例：外連接

　　左外連接：就可以顯示左邊表的所有數據行：T_Name1 LEFT OUTER JOIN T_Name2 ON ()

hive> select sales.*,things.*                             > from sales LEFT OUTER JOIN things ON(sales.id = things.id);
OK
Joe     2       2       Tie
Hank    4       4       Coat
Ali     0       NULL    NULL
Eve     3       3       Hat
Hank    2       2       Tie
Time taken: 13.387 seconds, Fetched: 5 row(s)

　　右外連接：T_Name1 RIGHT?OUTER JOIN T_Name2 ON ()

hive> select sales.*,things.*                                    > from sales RIGHT OUTER JOIN things ON(sales.id = things.id);
OK
Joe     2       2       Tie
Hank    2       2       Tie
Hank    4       4       Coat
Eve     3       3       Hat
NULL    NULL    1       Scarf
Time taken: 14.54 seconds, Fetched: 5 row(s)

　　全外連接：T_Name1 FULL OUTER?JOIN T_Name2 ON ()

hive> select sales.*,things.*                                     > from sales FULL OUTER JOIN things ON(sales.id = things.id); 
OK
Ali     0       NULL    NULL
NULL    NULL    1       Scarf
Hank    2       2       Tie
Joe     2       2       Tie
Eve     3       3       Hat
Hank    4       4       Coat
Time taken: 44.671 seconds, Fetched: 6 row(s)

　　半連接：T_Name1 LEFT SEMI JOIN T_Name2 ON ()　

hive> select * from things> where things.id in   > (select id from sales);
OK
2       Tie
4       Coat
3       Hat
Time taken: 15.633 seconds, Fetched: 3 row(s)

　　In查詢可以轉化為?半連接查詢。必須遵循一個限制：右表(sales)只能在ON子句中出現。

hive> select * from things > LEFT SEMI JOIN sales ON(sales.id=things.id);
OK
2       Tie
4       Coat
3       Hat
Time taken: 13.169 seconds, Fetched: 3 row(s)

3.Map-side Join 具體JAVA-API實現詳見MR案例：Map-Join

• Join操作在 map 階段完成，因此無需 reduce 階段
• 適合 一個大表，一個小表 的 Join 操作
• 思想：小表復制到各個節點上，并加載到內存中；而對大表進行分片，每個分片與小表完成 Join 操作

select /*+ mapjoin(things) */ sales.*,things.*
from sales join things on sales.id=things.id;//等同于
select sales.*,things.*
from sales join things on sales.id=things.id;

• hive 0.6 的時候默認認為寫在select 后面的是大表，前面的是小表，或者使用 /*+mapjoin(map_table) */ 手工進行設定。
• hive 0.7 以后這個計算是自動完成，設置 hive.auto.convert.join=true ，hive會自動判斷哪個是小表，哪個是大表。判斷小表的依據是hive.smalltable.filesize=25000000L(默認是25M)，當小表超過這個大小，hive會自動轉化成common?join，即reduce-join。?

4.Reduce-side Join 具體JAVA-API實現詳見MR案例：Reduce-Join

• Join操作在reduce task中完成 【默認的join方式】
• 適合兩個大表連接操作
• 思想：map端按照連接字段進行hash，reduce 端完成連接操作

5.用于多于2個表的Join(有區別)

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

如果 Join 的 key 相同，不管有多少個表，都會則會合并為一個 Map-Reduce

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

如果 Join 的條件不相同，Map-Reduce 的任務數目和 Join 操作的次數是相對應.(本例2次)

6.Join每次MR任務的邏輯

reducer 會緩存 join 序列中除了最后一個表的所有表的記錄，?再通過最后一個表將結果序列化到文件系統。?這一實現有助于在 reduce 端減少內存的使用量。實踐中，應該把最大的那個表寫在最后（否則會因為緩存浪費大量內存）。例如：

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

使用 1 次MR任務，reduce 端會緩存 a 表和 b 表的記錄，然后每次取得一個 c 表的記錄就計算一次 join 結果。

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

使用 2 次MR任務，第一次緩存 a 表，用 b 表序列化；第二次緩存第一次 map/reduce 任務的結果，然后用 c 表序列化。

?