[TOC] # 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 JOIN table_reference join_condition ~~~ Hive 支持等值連接（equality joins）、外連接（outer joins）和左右連接（`left/right joins`）。 Hive **不支持非等值的連接**，因為非等值連接非常難轉化到 map/reduce 任務。 另外，Hive 支持多于 2 個表的連接。 寫 join 查詢時，需要注意幾個關鍵點： ## 只支持等值join 例如： ~~~ SELECT a.* FROM a JOIN b ON (a.id = b.id) SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department) ~~~ 是正確的， 然而: ~~~ SELECT a.* FROM a JOIN b ON (a.id>b.id) ~~~ 是錯誤的。 ## 可以 join 多于 2 個表 例如 ~~~ SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2) ~~~ 如果join中多個表的 join key 是同一個，則 join 會被轉化為單個 `map/reduce` 任務，例如： ~~~ SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1) ~~~ 被轉化為單個 map/reduce 任務，因為 join 中只使用了 b.key1 作為 join key。 ~~~ SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2) ~~~ 而這一 join 被轉化為 2 個 map/reduce 任務。因為 b.key1 用于第一次 join 條件，而 b.key2 用于第二次 join。 ## join 時，每次 map/reduce 任務的邏輯 **reducer 會緩存 join 序列中除了最后一個表的所有表的記錄** ，再通過最后一個表將結果序列化到文件系統。這一實現有助于在 reduce 端減少內存的使用量。實踐中，**應該把最大的那個表寫在最后**（否則會因為緩存浪費大量內存）。例如： ~~~ 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（使用 1 次 map/reduce 任務計算）。Reduce 端會緩存 a 表和 b 表的記錄，然后每次取得一個 c 表的記錄就計算一次 join 結果，類似的還有： ~~~ SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2) ~~~ 這里用了 2 次 map/reduce 任務。第一次緩存 a 表，用 b 表序列化；第二次緩存第一次 map/reduce 任務的結果，然后用 c 表序列化。 ## LEFT，RIGHT 和 FULL OUTER 關鍵字用于處理 join 中空記錄的情況 full outer join是2個表全表匹配,沒匹配上就顯示null left outer join和left join一樣 left semi join只顯示左表數據不顯示右表的,只顯示左邊能和右邊匹配的數據 例如： ~~~ SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key) ~~~ 對應所有 a 表中的記錄都有一條記錄輸出。輸出的結果應該是 a.val, b.val，當 a.key=b.key 時，而當 b.key 中找不到等值的 a.key 記錄時也會輸出: a.val, NULL 所以 a 表中的所有記錄都被保留了； `"a RIGHT OUTER JOIN b"`會保留所有 b 表的記錄。 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' ~~~ 會 join a 表到 b 表（OUTER JOIN），列出 a.val 和 b.val 的記錄。WHERE 從句中可以使用其他列作為過濾條件。但是，如前所述，如果 b 表中找不到對應 a 表的記錄，b 表的所有列都會列出 NULL，包括 ds 列。也就是說，join 會過濾 b 表中不能找到匹配 a 表 join key 的所有記錄。這樣的話，LEFT OUTER 就使得查詢結果與 WHERE 子句無關了。解決的辦法是在 OUTER JOIN 時使用以下語法： ~~~ 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 階段過濾過的，所以不會存在上述問題。這一邏輯也可以應用于 RIGHT 和 FULL 類型的 join 中。 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 表，丟棄掉所有 join key 中不匹配的記錄，然后用這一中間結果和 c 表做 join。這一表述有一個不太明顯的問題，就是當一個 key 在 a 表和 c 表都存在，但是 b 表中不存在的時候：整個記錄在第一次 join，即 a JOIN b 的時候都被丟掉了（包括a.val1，a.val2和a.key），然后我們再和 c 表 join 的時候，如果 c.key 與 a.key 或 b.key 相等，就會得到這樣的結果：NULL, NULL, NULL, c.val ## 案列 建表 ~~~ hive> create table a(id int,name string) > row format delimited fields terminated by ','; OK Time taken: 0.178 seconds hive> create table b(id int,name string) > row format delimited fields terminated by ','; ~~~ 實驗： ~~~ //把對應匹配上的id都取出來 ** inner join select a.*,b.* from a inner join b on a.id=b.id; +-------+---------+-------+---------+--+ | a.id | a.name | b.id | b.name | +-------+---------+-------+---------+--+ | 2 | b | 2 | bb | | 3 | c | 3 | cc | | 7 | y | 7 | yy | +-------+---------+-------+---------+--+ ~~~ ~~~ //會取出a表中的全部數據,沒有匹配到p表中的就是null **left join select * from a left join b on a.id=b.id; +-------+---------+-------+---------+--+ | a.id | a.name | b.id | b.name | +-------+---------+-------+---------+--+ | 1 | a | NULL | NULL | | 2 | b | 2 | bb | | 3 | c | 3 | cc | | 4 | d | NULL | NULL | | 7 | y | 7 | yy | | 8 | u | NULL | NULL | +-------+---------+-------+---------+--+ ~~~ ~~~ //b表全部顯示,a表沒有匹配顯示null **right join select * from a right join b on a.id=b.id; +-------+---------+-------+---------+--+ | a.id | a.name | b.id | b.name | +-------+---------+-------+---------+--+ | 2 | b | 2 | bb | | 3 | c | 3 | cc | | 7 | y | 7 | yy | | NULL | NULL | 9 | pp | +-------+---------+-------+---------+--+ ~~~ ~~~ //2個表全部join顯示出來 ** select * from a full outer join b on a.id=b.id; +-------+---------+-------+---------+--+ | a.id | a.name | b.id | b.name | +-------+---------+-------+---------+--+ | 1 | a | NULL | NULL | | 2 | b | 2 | bb | | 3 | c | 3 | cc | | 4 | d | NULL | NULL | | 7 | y | 7 | yy | | 8 | u | NULL | NULL | | NULL | NULL | 9 | pp | +-------+---------+-------+---------+--+ ~~~ ~~~ //exists操作 // left semi join是exists操作的高效實現 **hive中的特別join select * from a left semi join b on a.id = b.id; +-------+---------+--+ | a.id | a.name | +-------+---------+--+ | 2 | b | | 3 | c | | 7 | y | +-------+---------+--+ 相當于 select * from a where a.id exists(select b.id from b); exists在hive中效率極低 ~~~