当我们在利用大量增量表中的数据加工快照表时,往往需要在集市层的大宽表中扫描较大时间范围内的数据,
但有一种用于夜间生产的优化思路,可以不必扫描整张增量大宽表,
而是通过利用前一天该表的快照与当天的增量数据进行union,来完成当天的生产。
优化前
可以看到我们要取得每天所有用户的最新下单时间来制作快照,从增量交易大宽表中扫描了所有的历史交易订单。
select
id1,
id2,
max(last_trade_date) as last_trade_date,
'$now.date' as partition_date
from (
-- 增量数据使用 T-2日数据和T-1日增量合并,减少数据加工时间
select
t1.id1 as id1,
t2.id2 as id2,
t2.partition_date as last_trade_date
from table_id1_list t1
inner join cashier_pay_inc t2
on t1.partition_date = t2.partition_date
and t1.order_id = t2.order_id
where t1.partition_date <= '$now.date'
and t2.is_suc = 1
group by t1.id1,t2.id2,t2.partition_date
)
group by id1,id2
优化后
在夜间生产快照表时,可以通过将调用自身前一天的快照,与当天增量数据合并的方式,极大减少数据加工时间
select
id1,
id2,
max(last_trade_date) as last_trade_date,
'$now.date' as partition_date
from (
-- 增量数据使用 T-2日数据和T-1日增量合并,减少数据加工时间
select id1,id2,last_trade_date as last_trade_date
from table_id1_ss
where partition_date = '$now.delta(1).date'
union all
select
t1.id1 as id1,
t2.id2 as id2,
t2.partition_date as last_trade_date
from id2 t1
inner join cashier_pay_inc t2
on t1.partition_date = t2.partition_date
and t1.order_id = t2.order_id
where t1.partition_date = '$now.date'
and t2.is_suc = 1
group by t1.id1,t2.id2,t2.partition_date
)
group by id1,id2