I think you would probably want to take a look at the "MERGE" statement; https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML#LanguageManualDML-Merge but afaik it would need to only keep the last operation on the row to make it work; which is what you are also suggesting (I,U,D = D)... -- merge is only supported on transactional tables
set hive.support.concurrency=true;
set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
drop table if exists target;
drop table if exists upd;
create table target (id string, t timestamp)
stored as orc
tblproperties('transactional'='true');
create table upd (id string, iud string, t timestamp);
insert into target values ('1','2001-11-11'),
('10','2001-11-11');
insert into upd values ('1','D','2001-11-11'),
('2','U','2001-10-11'),
('2','D','2001-11-11'),
('2','I','2001-12-11'),
('3','U','2001-10-11'),
('3','D','2001-11-11'),
('4','I','2001-11-11'),
('10','D','2001-11-11');
-- the following will filter the "final" updates
select * from (
select *,LEAD(id,1) over (partition by id order by t) as lead
from upd
order by t
) tt where lead is null;
-- this merge statement will bake the updates into the target table
merge into target as ta using
(
select * from (
select *,LEAD(id,1) over (partition by id order by t) as lead
from upd
order by t
) tt where lead is null
) as up
ON ta.id=up.id
when matched and up.iud='D' then DELETE
when matched and up.iud='U' then update set t=up.t
when not matched and up.iud='I' then insert values (up.id,up.t);
for more info about using LEAD in this query: https://sqlsunday.com/2016/04/11/last-row-per-group/
... View more
