1、條件過濾
left join 中 on 后面加條件 where 和 and 的區別
- 1、 on條件是在生成臨時表時使用的條件，它不管and中的條件是否為真，都會保留左邊表中的全部記錄。
- 2、where條件是在臨時表生成好后，再對臨時表進行過濾的條件。這時已經沒有left join的含義（必須返回左表的記錄）了，條件不為真的就全部過濾掉。
條件加在where可能會導致主表/左表的最終記錄數變少的情況發生。
舉個例子：
有表a和表b
表a：
| id | name |
|---|---|
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
表b：
| id | name |
|---|---|
| 1 | A |
| 2 | B |
1、on 后面條件用and ，不管條件是否成立 都會把左表的數據全部展示
select * from a left join b on a.id = b.id and b.name = 'B';
此時會有4條記錄
| id | name | id2 | name2 |
|---|---|---|---|
| 1 | a | ||
| 2 | b | 2 | B |
| 3 | c | ||
| 4 | d |
2、on 后面條件用where ，在left join 生成的表上在做篩選，這時會把 where中不成立的篩選掉
select * from a left join b on a.id = b.id where b.`name` = 'B';
此時只有1條記錄
| id | name | id2 | name2 |
|---|---|---|---|
| 2 | b | 2 | B |
2、指定分區
結論：指定分區時優先采用寫法3，即 partition 的寫法。
1、left join xxx::xxx on 分區字段 = 其他表的分區字段
where 其他表的分區字段 = 2023110700
2、left join xxx::xxx on 分區字段 =2023110700
3、left join xxx::xxx partition(p_2023110700)t
4、left join (select xxx from xxx::xxx where 分區字段 =2023110700） on xxx
5、left join (select xxx from xxx::xxx partition(p_2023110700)t ） on xxx
寫法1 是最慢的，因為join兩邊的表都沒有提前過濾分區，所以執行時大概率導致全表掃描。
寫法2，3 一般情況下差異不大，寫法3是最規范的寫法。 寫法3必定是指定分區，寫法2大概率會優化為指定分區，小概率全表掃描。
寫法4，5是Hive的標準寫法，但是一般場景下沒有子查詢的必要，效果上與寫法2,3相同。
3、數據傾斜
1、優化大小表join，采用map join的方式優化
使用map join的必要條件：
a. 參與連接的小表的行數，以不超過2萬條為宜。
b.連接類型是inner join、right outer join（小表不能是右表）、left outer join（小表不能是左表）、left semi join。
使用方法示例：
INSERT OVERWRITE TABLE xxxSELECT /*+ MAPJOIN(aa) */ aa.pageid, u.age FROM page_view pvJOIN user uON (aa.userid = u.userid);注：當大表存在數據傾斜時，如果小表符合map join的要求，使用map join會極大加速計算。
/*+ MAPJOIN(pv) */ 參考資料
2、避免大小表join
再單獨清洗一張dwd，設定保留分區的個數，構造一張特定大小的總表，進行全表掃描再過濾字段。
