① Hive的left Join，left Outer Join和left Semi Join三者的區別
以下為兩個測試數據表建表語句：
use test;
DROP TABLE IF EXISTS table1;
create table table1(
student_no bigint comment '學號',
student_name string comment '姓名'
)
COMMENT 'test 學生信息'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;
DROP TABLE IF EXISTS table2;
create table table2(
student_no bigint comment '學號',
class_no bigint comment '課程號'
)
COMMENT 'test 學生選課信息'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;
load data local inpath 'data_table1.txt' overwrite into table table1;
load data local inpath 'data_table2.txt' overwrite into table table2;
測試數據為：
hive left join測試數據
測試1：left join
語句：
select * from table1 left outer join table2 on(table1.student_no=table2.student_no);
結果：
FAILED: Parse Error: line 1:22 cannot recognize input near 『left』 『join』 『table2′ in join type specifier
我用的HIVE版本是0.8，不支持直接的left join寫法；
測試2：left outer join
語句：
select * from table1 left outer join table2 on(table1.student_no=table2.student_no);
結果：
1 name1 1 11
1 name1 1 12
1 name1 1 13
2 name2 2 11
2 name2 2 14
3 name3 3 15
3 name3 3 12
4 name4 4 13
4 name4 4 12
5 name5 5 14
5 name5 5 16
6 name6 NULL NULL
可以看到left outer join左邊表的數據都列出來了，如果右邊表沒有對應的列，則寫成了NULL值。
同時注意到，如果左邊的主鍵在右邊找到了N條，那麼結果也是會叉乘得到N條的，比如這里主鍵為1的顯示了右邊的3條。
測試3：left semi join
語句：
select * from table1 left semi join table2 on(table1.student_no=table2.student_no);
結果：
1 name1
2 name2
3 name3
4 name4
5 name5
可以看到，只列印出了左邊的表中的列，規律是如果主鍵在右邊表中存在，則列印，否則過濾掉了。
結論：
hive不支持』left join』的寫法；
hive的left outer join：如果右邊有多行和左邊表對應，就每一行都映射輸出；如果右邊沒有行與左邊行對應，就輸出左邊行，右邊表欄位為NULL；
hive的left semi join：相當於sql的in語句，比如上面測試3的語句相當於「select * from table1 where table1.student_no in (table2.student_no)」，注意，結果中是沒有B表的欄位的。
② sql中left join on 和where的執行順序
(8)SELECT (9)DISTINCT (11)<Top Num> <select list>
(1)FROM [left_table]
(3)<join_type> JOIN <right_table>
(2)ON <join_condition>
(4)WHERE <where_condition>
(5)GROUP BY <group_by_list>
(6)WITH <CUBE | RollUP>
(7)HAVING <having_condition>
(10)ORDER BY <order_by_list>
邏輯查詢處理階段簡介
FROM：對FROM子句中的前兩個表執行笛卡爾積（Cartesian proct)(交叉聯接），生成虛擬表VT1
ON：對VT1應用ON篩選器。只有那些使<join_condition>為真的行才被插入VT2。
OUTER(JOIN)：如 果指定了OUTER JOIN（相對於CROSS JOIN 或(INNER JOIN),保留表（preserved table：左外部聯接把左表標記為保留表，右外部聯接把右表標記為保留表，完全外部聯接把兩個表都標記為保留表）中未找到匹配的行將作為外部行添加到 VT2,生成VT3.如果FROM子句包含兩個以上的表，則對上一個聯接生成的結果表和下一個表重復執行步驟1到步驟3，直到處理完所有的表為止。
WHERE：對VT3應用WHERE篩選器。只有使<where_condition>為true的行才被插入VT4.
GROUP BY：按GROUP BY子句中的列列表對VT4中的行分組，生成VT5.
CUBE|ROLLUP：把超組(Suppergroups)插入VT5,生成VT6.
HAVING：對VT6應用HAVING篩選器。只有使<having_condition>為true的組才會被插入VT7.
SELECT：處理SELECT列表，產生VT8.
DISTINCT：將重復的行從VT8中移除，產生VT9.
ORDER BY：將VT9中的行按ORDER BY 子句中的列列表排序，生成游標（VC10).
TOP：從VC10的開始處選擇指定數量或比例的行，生成表VT11,並返回調用者。
③ hive left semi join map是在哪個階段
hive不支持』left
join』的寫法；
hive的left
outer
join：如果右邊有多行和左邊表對應,就每一行都映射輸出；如果右邊沒有行與左邊行對應,就輸出左邊行,右邊表欄位為NULL；
hive的left
semi
join：相當於SQL的in語句
以下為兩個測試數據表建表語句...
④ Hive的left join，left outer join和left semi join三者的區別
貼一直復制過來的。。
hive不支持『left
join』的寫法（我記得使用的時候好像報錯了，最後都改成了outer
join）
hive的left
outer
join：如果右邊有多行和左邊表對應，就每一行都映射輸出；如果右邊沒有行與左邊行對應，就輸出左邊行，右邊表欄位為NULL；
hive的left
semi
join：相當於SQL的in語句，比如上面測試3的語句相當於「select
*
from
table1
where
table1.student_no
in
(table2.student_no)」，注意，結果中是沒有B表的欄位的。
⑤ sql 使用left join後，選取其中兩列，進行欄位連接，但是出現了null，如何處理多謝！
on 後面跟的是兩個表的連接條件，
不匹配時，右表就為NULL
⑥ hive not in 通過什麼實現
目前hive不支持 in或not in 中包含查詢子句的語法，所以只能通過left join實現。
假設有一個登陸表login(當天登陸記錄,只有一個uid),和一個用戶注冊表regusers(當天注冊用戶，欄位只有一個uid)，這兩個表都包含一個欄位,uid。
in查詢
如果要查詢當天登陸的注冊用戶，需要用in查詢，hive sql如下:
select login.uid from login left outer join regusers on login.uid=regusers.uid where regusers.uid is not null
如果login表和regusers表按天分區，欄位是dt，那麼查詢2013年1月1號當天登陸的注冊用戶，hive sql如下:
select login.uid from login day_login left outer join
(select uid from regusers where dt='20130101') day_regusers
on day_login.uid=day_regusers.uid where day_login.dt='20130101' and day_regusers.uid is not null
not in查詢
如果要查詢當天登陸的老用戶(這里假設非當天注冊用戶就是老用戶)，需要用not in查詢，hive sql如下:
select login.uid from login left outer join regusers on login.uid=regusers.uid where regusers.uid is null;
如果login表和regusers表按天分區，欄位是dt，那麼查詢2013年1月1號當天登陸的老用戶，hive sql如下:
select login.uid from login day_login left outer join
(select uid from regusers where dt='20130101') day_regusers
on day_login.uid=day_regusers.uid where day_login.dt='20130101' and day_regusers.uid is null;
Hive join優化
========================================================
由
於 hive 與傳統關系型資料庫面對的業務場景及底層技術架構都有著很大差異，因此，傳統資料庫領域的一些技能放到 Hive 中可能已不再適用。關於
hive 的優化與原理、應用的文章，前面也陸陸續續的介紹了一些，但大多都偏向理論層面，本文就介紹一個實例，從實例中一步步加深對 hive
調優的認識與意識。
1、需求
需求我做了簡化，很簡單，兩張表做個 join，求指定城市，每天的 pv，用傳統的 RDBMS SQL 寫出來就這樣的：
?
1
2
3
4
5
6
7
8
9
10
11
SELECT t.statdate,
c.cname,
count(t.cookieid)
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON (t.area1= c.cname
OR t.area2 =c.cname
OR t.area3 = c.cname)
WHERE t.statdate>='20140818' and t.statdate<='20140824'
AND platform='pc'
GROUP BY t.statdate,
c.cname;
怎麼樣？根據 SQL 看懂需求沒問題吧？
2、非等值 join 問題
然後把這條 SQL 貼到 hive 中去執行，然後你會發現報錯了：
?
1
FAILED: SemanticException [Error 10019]: Line 5:32 OR not supported in JOIN currently 'cname'
這是因為 hive 受限於 MapRece 演算法模型，只支持 equi-joins（等值 join），要實現上述的非等值 join，你可以採用笛卡兒積（ full Cartesian proct ）來實現：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT t.statdate,
c.cname,
count(t.cookieid)
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
AND (t.area1= c.cname
OR t.area2 =c.cname
OR t.area3 = c.cname)
GROUP BY t.statdate,
c.cname;
然後再拿著這條語句執行下。
3、優化：rece side join VS Cartesian proct
如果你真的把這條語句放到 Hive 上執行，然後恰好你有張表還非常大，那麼恭喜你。。。集群管理員估計會找你的麻煩了。。。
友情提示：笛卡兒積這種語句在 Hive 下慎用，大數據場景下的 m * n 映射結果你懂的。。。對此，Hive 特意提供了一個環境變數：hive.mapred.mode=strict; 防止笛卡兒積的執行：
?
1
FAILED: SemanticException [Error 10052]: In strict mode, cartesian proct is not allowed. If you really want to perform the operation, set hive.mapred.mode=nonstrict
從 2 中的觀察得知我們在 on 後面跟 join
條件，走的是 rece side join，如果你在 where 後跟則是走 Cartesian proct，但是這里單條 sql
又沒法實現 rece side join，還有沒有其它辦法呢？
4、改寫非等值 join：union all
既然不允許非等值 join，那我們換一下思路，多個子查詢 union all，然後匯總：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SELECT dt,
name,
count(cid)
FROM
(SELECT t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area1 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
UNION ALL SELECT t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area2 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
UNION ALL SELECT t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area3 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc') tmp_trackflow
GROUP BY dt,
name;
5、優化：map side join
上述語句走的是 rece side join，從我們的需求及業務得知，tmpdb.city 是一張字典表，數據量很小，因此我們可以試試把上述的語句改寫成 mapjoin：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SELECT dt,
name,
count(cid)
FROM
(SELECT /*+ MAPJOIN(c) */ t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area1 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
UNION ALL SELECT /*+ MAPJOIN(c) */ t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area2 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
UNION ALL SELECT /*+ MAPJOIN(c) */ t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area3 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc') tmp_trackflow
GROUP BY dt,
name;
6、優化無極限：開啟 parallel 和 控制 rece 個數
上述語句執行時，你可以看到執行計劃和狀態信息，以及結合你的 union all 語句可知，三個 union 語句之間沒有依賴關系，其實是可以並行執行的：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
explain SQL...
...
STAGE DEPENDENCIES:
Stage-11 is a root stage
Stage-1 depends on stages: Stage-11
Stage-2 depends on stages: Stage-1
Stage-3 depends on stages: Stage-2, Stage-6, Stage-9
Stage-12 is a root stage
Stage-5 depends on stages: Stage-12
Stage-6 depends on stages: Stage-5
Stage-13 is a root stage
Stage-8 depends on stages: Stage-13
Stage-9 depends on stages: Stage-8
Stage-0 is a root stage
...
我們在 SQL 前加上如下環境變數選項：
?
1
2
set mapred.rece.tasks=60;
set hive.exec.parallel=true;
讓執行計劃中的 Stage-11、Stage-12、Stage-13 並行執行，並控制好 rece task 個數。
完整的語句如下：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
hive -e "
SET mapred.rece.tasks=60;
SET hive.exec.parallel=TRUE;
SELECT dt,
name,
count(cid)
FROM
(SELECT /*+ MAPJOIN(c) */ t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area1 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
UNION ALL SELECT /*+ MAPJOIN(c) */ t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area2 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
UNION ALL SELECT /*+ MAPJOIN(c) */ t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area3 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc') tmp_trackflow
GROUP BY dt,
name;
" > a1.txt
最後的優化效果是：2 中的語句三個小時沒出結果。。。5 比 4 快 8 倍左右，6 比 5 快 2 倍左右，最終 10min 出結果。
⑦ Hive的left join，left outer join和left semi join三者的區別
貼一直復制過來的。。hive不支持『left join』的寫法（我記得使用的時候好像報錯了，最後都改成了outer join）hive的left outer join：如果右邊有多行和左邊表對應，就每一行都映射輸出；如果右邊沒有行與左邊行對應，就輸出左邊行，右邊表欄位為NULL；hive的left semi join：相當於SQL的in語句，比如上面測試3的語句相當於「select * from table1 where table1.student_no in (table2.student_no)」，注意，結果中是沒有B表的欄位的。
⑧ Hive的left join，left outer join和left semi join三者的區別
hive不支持』left join』的寫法； hive的left outer join：如果右邊有多行和左邊表對應,就每一行都映射輸出；如果右邊沒有行與左邊行對應,就虎虎港臼蕃鉸歌歇攻忙輸出左邊行,右邊表欄位為NULL； hive的left semi join：相當於SQL的in語句 以下為兩個測試數據表建表語句...
⑨ Hive的left join，left outer join和left semi join三者的區別
Hive是基於Hadoop的一個數據倉庫工具，可以將結構化的數據文件映射為一張資料庫表，並提供簡單的sql查詢功能，可以將sql語句轉換為MapRece任務進行運行。
最常用的就是多表關聯查詢，主要講解下join、outer join和semi join的具體使用。
join是最簡單的關聯操作，兩邊關聯只取交集。Hive沒有left join !!!outer join分為left outer join、right outer join和full outer join。
left outer join是以左表驅動，右表不存在的key均賦值為null；right outer join是以右表驅動，左表不存在的key均賦值為null；full outer join全表關聯，將兩表完整的進行笛卡爾積操作，左右表均可賦值為null。
semi join最主要的使用場景就是解決exist in。Hive不支持where子句中的子查詢，SQL常用的exist in子句在Hive中是不支持的。SELECT a.key, a.valueFROM aWHERE a.key in (SELECT b.key FROM B);可以改寫為：SELECT a.key, a.valueFROM a LEFT OUTER JOIN b ON (a.key = b.key)WHERE b.key <> NULL;一個更高效的實現為：SELECT a.key, a.valueFROM a LEFT SEMI JOIN b on (a.key = b.key);
left semi join是0.5.0以上版本的特性。
⑩ hive中mapjoin和普通join的區別
hive支持』left join』寫； hiveleft outer join：右邊行左邊表應,每行都映射輸；右邊沒行與左邊行應,輸左邊行,右邊表欄位NULL； hiveleft semi join：相於SQLin語句 兩測試數據表建表語句...