sparksql broadcast join opt

select /*+ BROADCAST(t2) */
t2.trans_date
, t1.store_nbr
, t1.item_nbr
, sum(nvl(t1.warehouse_allowance,0.0)) as warehouse_allowance
, sum(nvl(t1.cts_transportation,0.0)) as cts_transportation
, sum(nvl(t1.cts_warehouse_management,0.0)) as cts_warehouse_management
, sum(nvl(t1.merch_sales,0.0)) as merch_sales
, to_utc_timestamp(current_timestamp,‘GMT-8’) as etl_load_time --数据入湖时间
, t2.trans_date as ts
from (
select
date_dt as trans_date
from dim.dim_common_calendar_f
where date_dt between ‘${las{t}_3{0}_{d}ay{s}_{s}tr}^{\prime }an{d}^{\prime }${yestoday_str}’
) t2
left join (
select
statis_dt – 统计日期
, item_nbr – 商品号
, store_nbr – 门店编号
, case when channel_id=‘82_1’ then ‘82’
when channel_id=‘15_1’ then ‘15’
else channel_id end as channel_id
, warehouse_allowance as warehouse_allowance
, cts_transportation as cts_transportation
, cts_warehouse_management as cts_warehouse_management – wa_cts
, merch_sales as merch_sales
from dm_secure.dm_fin_pnl_sams_item_pnl_di – sams商品日销售损益宽表
where channel_id in (‘82’,‘66’,‘67’,‘68’,‘15’,‘82_1’,‘15_1’)
and ts between date_sub(‘${las{t}_3{0}_{d}ay{s}_{s}tr}^{\prime },30)an{d}^{\prime }${yestoday_str}’
) t1
on t1.statis_dt between date_sub(t2.trans_date, 30) and t2.trans_date
group by t2.trans_date, t1.item_nbr, t1.store_nbr
;

优化前
± == Current Plan ==
CollectLimit (17)
± HashAggregate (16)
± ShuffleQueryStage (15)
± Exchange (14)
± * HashAggregate (13)
± * Project (12)
± * BroadcastNestedLoopJoin LeftOuter BuildRight (11)
:- * Project (4)
: ± * Filter (3)
: ± * ColumnarToRow (2)
: ± Scan orc dim.dim_common_calendar_f (1)
± BroadcastQueryStage (10)
± BroadcastExchange (9)
± * Project (8)
± * Filter (7)
± * ColumnarToRow (6)
± Scan orc dm_secure.dm_fin_pnl_sams_item_pnl_di (5)

优化后 添加 /*+ BROADCAST(t2) */
== Physical Plan ==
CommandResult (1)
± Execute InsertIntoHadoopFsRelationCommand (30)
± AdaptiveSparkPlan (29)
± == Final Plan ==
* HashAggregate (18)
± AQEShuffleRead (17)
± ShuffleQueryStage (16)
± Exchange (15)
± * HashAggregate (14)
± * Project (13)
± BroadcastNestedLoopJoin LeftOuter BuildLeft (12)
:- BroadcastQueryStage (7)
: ± BroadcastExchange (6)
: ± * Project (5)
: ± * Filter (4)
: ± * ColumnarToRow (3)
: ± Scan orc dim.dim_common_calendar_f (2)
± * Project (11)
± * Filter (10)
± * ColumnarToRow (9)
± Scan orc dm_secure.dm_fin_pnl_sams_item_pnl_di (8)