11-3菜鸟进口实时数仓2.0.pdf

1、2019.06 2019.7- CREATE VIEW tmp_table2 AS SELECT * FROM ( SELECT *,ROW_NUMBER() OVER(PARTITION BY lg_code ORDER BY PROCTIME() DESC) AS rn FROM sourcett_table2 ) aa WHERE rn = 1 ; INSERT INTO result_table AS SELECT t1.lg_code ,t1.property1 ,t2.property2 FROM tmp_table1 t1 LEFT OUTER JOIN tmp_table2 t

2、2 ON t1.lg_code = t2.lg_code ; Left Join Keyed State1 Keyed State2 物流订单流 履行日志流 Msg3: LP1,store1,tms1 Msg2: LP1,store1,null Msg1:LP2,store2,tms2 Msg2: LP2,time21,time22 Msg1: LP1,time11,time12 keykeyproperty1property1property2property2 LP1store1tms1 LP2store2tms2 keykeylogisticslogistics1 1logisticsl

3、ogistics2 2 LP1time11time12 LP2time21tms22 Msg3: LP1,store1,tms1 ,time11,time12 Msg2: LP1,store1,null,time11,time12 Msg1: LP2,store2,tms2,time21,time22 * 每份state的每个Key只存一份有效数据，而不是全量 2.多流Join的优化 . 伪代码 SELECT t1.lg_code ,t1.property1 ,t2.property2 ,t3.property3 ,t4.property4 ,t5.property5 ,t6.property6 ,t7.property7 ,t8.property8 ,t9.property9 ,t10.property10 FROM tmp_table1 t1 LEFT OUTER JOIN tmp_t