在starrocks支持动态分区覆盖后, 有一些数据加工的需要动态覆盖分区, 并且需要插入或者更新
需要支持merge into 才能高效的进行更新覆写
示例sql
insert overwrite ${lake}.ods_api_tb_trades_sold_increment_info_f
/* set_var.connector_io_tasks_per_scan_operator=1 */
("seller_nick","pic_path","payment","seller_rate","post_fee","receiver_name","receiver_state","receiver_address","receiver_zip","receiver_mobile","receiver_phone","consign_time","received_payment","receiver_country","receiver_town","order_tax_fee","tid","num","num_iid","status","title","type","price","discount_fee","total_fee","created","pay_time","modified","end_time","seller_flag","buyer_nick","has_buyer_message","credit_card_fee","step_trade_status","step_paid_fee","mark_desc","buyer_open_uid","shipping_type","adjust_fee","service_orders","buyer_rate","receiver_city","receiver_district","orders","shop_pick","rx_audit_status","post_gate_declare","cross_bonded_declare","order_tax_promotion_fee","service_type","is_o2o_passport","oaid","no_shipping","aid","origin_data","yuce_cube_shop_id","dt","yuce_tenant_id")
select case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."seller_nick" else coalesce(t1."seller_nick",t2."seller_nick") end as "seller_nick"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."pic_path" else coalesce(t1."pic_path",t2."pic_path") end as "pic_path"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."payment" else coalesce(t1."payment",t2."payment") end as "payment"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."seller_rate" else coalesce(t1."seller_rate",t2."seller_rate") end as "seller_rate"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."post_fee" else coalesce(t1."post_fee",t2."post_fee") end as "post_fee"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."receiver_name" else coalesce(t1."receiver_name",t2."receiver_name") end as "receiver_name"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."receiver_state" else coalesce(t1."receiver_state",t2."receiver_state") end as "receiver_state"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."receiver_address" else coalesce(t1."receiver_address",t2."receiver_address") end as "receiver_address"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."receiver_zip" else coalesce(t1."receiver_zip",t2."receiver_zip") end as "receiver_zip"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."receiver_mobile" else coalesce(t1."receiver_mobile",t2."receiver_mobile") end as "receiver_mobile"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."receiver_phone" else coalesce(t1."receiver_phone",t2."receiver_phone") end as "receiver_phone"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."consign_time" else coalesce(t1."consign_time",t2."consign_time") end as "consign_time"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."received_payment" else coalesce(t1."received_payment",t2."received_payment") end as "received_payment"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."receiver_country" else coalesce(t1."receiver_country",t2."receiver_country") end as "receiver_country"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."receiver_town" else coalesce(t1."receiver_town",t2."receiver_town") end as "receiver_town"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."order_tax_fee" else coalesce(t1."order_tax_fee",t2."order_tax_fee") end as "order_tax_fee"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."tid" else coalesce(t1."tid",t2."tid") end as "tid"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."num" else coalesce(t1."num",t2."num") end as "num"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."num_iid" else coalesce(t1."num_iid",t2."num_iid") end as "num_iid"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."status" else coalesce(t1."status",t2."status") end as "status"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."title" else coalesce(t1."title",t2."title") end as "title"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."type" else coalesce(t1."type",t2."type") end as "type"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."price" else coalesce(t1."price",t2."price") end as "price"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."discount_fee" else coalesce(t1."discount_fee",t2."discount_fee") end as "discount_fee"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."total_fee" else coalesce(t1."total_fee",t2."total_fee") end as "total_fee"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."created" else coalesce(t1."created",t2."created") end as "created"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."pay_time" else coalesce(t1."pay_time",t2."pay_time") end as "pay_time"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."modified" else coalesce(t1."modified",t2."modified") end as "modified"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."end_time" else coalesce(t1."end_time",t2."end_time") end as "end_time"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."seller_flag" else coalesce(t1."seller_flag",t2."seller_flag") end as "seller_flag"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."buyer_nick" else coalesce(t1."buyer_nick",t2."buyer_nick") end as "buyer_nick"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."has_buyer_message" else coalesce(t1."has_buyer_message",t2."has_buyer_message") end as "has_buyer_message"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."credit_card_fee" else coalesce(t1."credit_card_fee",t2."credit_card_fee") end as "credit_card_fee"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."step_trade_status" else coalesce(t1."step_trade_status",t2."step_trade_status") end as "step_trade_status"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."step_paid_fee" else coalesce(t1."step_paid_fee",t2."step_paid_fee") end as "step_paid_fee"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."mark_desc" else coalesce(t1."mark_desc",t2."mark_desc") end as "mark_desc"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."buyer_open_uid" else coalesce(t1."buyer_open_uid",t2."buyer_open_uid") end as "buyer_open_uid"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."shipping_type" else coalesce(t1."shipping_type",t2."shipping_type") end as "shipping_type"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."adjust_fee" else coalesce(t1."adjust_fee",t2."adjust_fee") end as "adjust_fee"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."service_orders" else coalesce(t1."service_orders",t2."service_orders") end as "service_orders"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."buyer_rate" else coalesce(t1."buyer_rate",t2."buyer_rate") end as "buyer_rate"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."receiver_city" else coalesce(t1."receiver_city",t2."receiver_city") end as "receiver_city"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."receiver_district" else coalesce(t1."receiver_district",t2."receiver_district") end as "receiver_district"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."orders" else coalesce(t1."orders",t2."orders") end as "orders"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."shop_pick" else coalesce(t1."shop_pick",t2."shop_pick") end as "shop_pick"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."rx_audit_status" else coalesce(t1."rx_audit_status",t2."rx_audit_status") end as "rx_audit_status"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."post_gate_declare" else coalesce(t1."post_gate_declare",t2."post_gate_declare") end as "post_gate_declare"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."cross_bonded_declare" else coalesce(t1."cross_bonded_declare",t2."cross_bonded_declare") end as "cross_bonded_declare"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."order_tax_promotion_fee" else coalesce(t1."order_tax_promotion_fee",t2."order_tax_promotion_fee") end as "order_tax_promotion_fee"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."service_type" else coalesce(t1."service_type",t2."service_type") end as "service_type"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."is_o2o_passport" else coalesce(t1."is_o2o_passport",t2."is_o2o_passport") end as "is_o2o_passport"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."oaid" else coalesce(t1."oaid",t2."oaid") end as "oaid"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."no_shipping" else coalesce(t1."no_shipping",t2."no_shipping") end as "no_shipping"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."aid" else coalesce(t1."aid",t2."aid") end as "aid"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."origin_data" else coalesce(t1."origin_data",t2."origin_data") end as "origin_data"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."yuce_cube_shop_id" else t1."yuce_cube_shop_id" end as "yuce_cube_shop_id"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."dt" else coalesce(substring(t1."end_time",1,7), '2999-12') end as "dt"
,case when coalesce(t1."modified",'') < coalesce(t2."modified",'') then t2."yuce_tenant_id" else t1."yuce_tenant_id" end as "yuce_tenant_id"
from (
select *
from (
select *,row_number() over(partition by "tid","yuce_cube_shop_id","yuce_tenant_id" order by "modified" desc,"yuce_data_create_time" desc) rk
from ${lake}.ods_api_tb_trades_sold_increment_info_du
where dt >= '${startTime}' and dt <= '${endTime}' and yuce_tenant_id = '#{yuce_tenant_id}'
) t1
where rk = 1
) t1
full join (
select *
from ${lake}.ods_api_tb_trades_sold_increment_info_f
where (
concat(yuce_tenant_id,'',dt) in (
select substring("end_time",1,7)
from ${lake}.ods_api_tb_trades_sold_increment_info_du
where dt >= '${startTime}' and dt <= '${endTime}' and yuce_tenant_id = '#{yuce_tenant_id}'
group by substring("end_time",1,7)
) or dt = '2999-12'
)
and yuce_tenant_id = '#{yuce_tenant_id}'
) t2
on coalesce(t1."tid",'yuce@#$123456qwer') = coalesce(t2."tid",'yuce@#$123456qwer')
and coalesce(t1."yuce_cube_shop_id",'yuce@#$123456qwer') = coalesce(t2."yuce_cube_shop_id",'yuce@#$123456qwer')
and coalesce(t1."yuce_tenant_id",'yuce@#$123456qwer') = coalesce(t2."yuce_tenant_id",'yuce@#$123456qwer')
;