首先我们要了解MAPJOIN优化原理，这里简要说明下
Spark Broadcast hash join(Hive map join同理)
1，把小表广播到所有大表分布的节点上，在每个节点上分别进行单机hash join
2，left join时只能广播右表
执行基本条件：小表必须小于参数spark.sql.autoBroadcastJoinThreshold, 默认为10M
sql 场景小表需要left join大表 小表150M左右 大表1T左右
原始sql(广播左表，未成功，因为left join只支持广播右表)
create table tmp.iapp_mkt_per_imei_pkg_repair_t2_not_default_20200812_1 as
select /*+MAPJOIN(a) */ a.imei,b.package,1 status,1 install_type,b.imei imei_b
from
(select imei from tmp.iapp_mkt_per_imei_only_repair_20200812 where install_type = 0 and tail != 'default') a
left join join
(select imei,package from edw.sys_app_list_fact where data_date = 20200812 and tail != 'default' and imei is not null and status in (1,2))b
on a.imei = b.imei
优化思路：使用with将小表提前处理，多次复用，将left join转化为可以进行广播变量的join，然后再加进行小表与小表right join进行数据补偿，此方法只适合小表left join 大表
优化后sql
create table tmp.lanfz_222 as
with iapp_mkt as (select imei from tmp.iapp_mkt_per_imei_only_repair_20200812 where install_type = 0 and tail != 'default')
select /*+BROADCAST(a) */
c.imei,b.package,1 status,1 install_type,b.imei imei_b
from
iapp_mkt a
join
(select imei,package from edw.sys_app_list_fact where data_date = 20200812 and tail != 'default' and imei is not null and status in (1,2))b
on a.imei = b.imei
right join
iapp_mkt c
on c.imei = b.imei;
结果数据量对比，完全一致
sql耗时对比：
原始sql：Time taken: 1369.239 seconds
优化后sql：Time taken: 680.239 seconds
