sql - 使用配置单元中的插入覆盖查询更改此合并查询的查询以给出相同的结果

Question

如何使用 hive 中的插入覆盖查询对此合并查询进行交替查询以给出相同的结果？

MERGE into cirt_16039.v_msdlp_incidentinformationcap_insight trg USING cirt_16039.v_msdlp_incidentinformationcap_stage src ON src.incidentnumber = trg.incidentnumber

WHEN MATCHED AND (trg.status != src.status AND src.archernumber IS NOT NULL) THEN UPDATE SET status =src.status, archernumber=src.archernumber, load_timestamp=src.load_timestamp

WHEN NOT MATCHED THEN INSERT VALUES ( src.archernumber, src.incidentnumber, src.sent, src.recipient, src.cc, src.bcc, src.policyname, src.policyrule, src.dismisalreason, src.status, src.country, src.load_timestamp, src.load_job, src.sent_yyyymm);

标签： sqlhive

Answer 1

您可以使用以下 SQL。评论将解释工作。

insert overwrite cirt_16039.v_msdlp_incidentinformationcap_insight
select  -- Existing records so UPDATE 
src.archernumber, src.incidentnumber, src.sent,src.recipient,src.cc, src.bcc, 
src.policyname, src.policyrule, src.dismisalreason, trg.status, src.country, 
trg.load_timestamp, src.load_job, src.sent_yyyymm
from 
cirt_16039.v_msdlp_incidentinformationcap_stage src,
cirt_16039.v_msdlp_incidentinformationcap_insight trg 
where
--trg.status != src.status AND -- Pls note you do not need this conditoin since if status is same then no harm in updating it from trg.
src.incidentnumber = trg.incidentnumber
union all 
select  --New records so INSERT
src.archernumber, src.incidentnumber, src.sent, src.recipient, src.cc, src.bcc, src.policyname, 
src.policyrule, src.dismisalreason, src.status, src.country, src.load_timestamp, src.load_job, src.sent_yyyymm
from cirt_16039.v_msdlp_incidentinformationcap_stage src
left join cirt_16039.v_msdlp_incidentinformationcap_insight trg on 
src.incidentnumber = trg.incidentnumber
where 
trg.incidentnumber is null  -- This condition will select only new records.