1、条件过滤

left join 中 on 后面加条件 where 和 and 的区别

  • 1、 on条件是在生成临时表时使用的条件，它不管and中的条件是否为真，都会保留左边表中的全部记录。
  • 2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左表的记录）了，条件不为真的就全部过滤掉。

条件加在where可能会导致主表/左表的最终记录数变少的情况发生。

举个例子：
有表a和表b

表a：

idname
1a
2b
3c
4d

表b：

idname
1A
2B

1、on 后面条件用and ，不管条件是否成立 都会把左表的数据全部展示

select * from a left join b on a.id = b.idand b.name = 'B';

此时会有4条记录

idnameid2name2
1a
2b2B
3c
4d

2、on 后面条件用where ，在left join 生成的表上在做筛选，这时会把 where中不成立的筛选掉

select * from a left join b on a.id = b.idwhere b.`name` = 'B';

此时只有1条记录

idnameid2name2
2b2B

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 pv JOIN user u ON (aa.userid = u.userid); 注：当大表存在数据倾斜时，如果小表符合map join的要求，使用map join会极大加速计算。
/*+ MAPJOIN(pv) */

参考资料

2、避免大小表join
再单独清洗一张dwd，设定保留分区的个数，构造一张特定大小的总表，进行全表扫描再过滤字段。