hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Markovitz, Dudu" <dmarkov...@paypal.com>
Subject RE: on duplicate update equivalent?
Date Fri, 23 Sep 2016 10:46:30 GMT
You may however use a code similar to the following.
The main idea is to work with 2 target tables.
Instead of merging the source table into a target table, we create an additional target table
based of the merge results.
A view is pointing all the time to the most updated target table.

Dudu


Initialize demo -

create table src (i int,c char(1));
insert into src values (2,'b'),(3,'c');

create table trg1 (i int,c char(1)) stored as orc;
insert into trg1 values (1,'X'),(2,'Y');

create view trg as select * from trg1;


Ongoing process -

create table if not exists trg2 as select coalesce (s.i,t.i) as i,coalesce (s.c,t.c) from
src as s full join trg as t on t.i = s.i;

alter view trg as select * from trg2;

drop table if exists trg1;


After some time passes and the source table contains new data -

create table if not exists trg1 as select coalesce (s.i,t.i) as i,coalesce (s.c,t.c) from
src as s full join trg as t on t.i = s.i;

alter view trg as select * from trg1;

drop table if exists trg2;


etc…




From: Markovitz, Dudu [mailto:dmarkovitz@paypal.com]
Sent: Friday, September 23, 2016 1:02 PM
To: user@hive.apache.org
Subject: RE: on duplicate update equivalent?

We’re not there yet…
https://issues.apache.org/jira/browse/HIVE-10924

Dudu

From: Vijay Ramachandran [mailto:vijay@linkedin.com]
Sent: Friday, September 23, 2016 11:47 AM
To: user@hive.apache.org<mailto:user@hive.apache.org>
Subject: on duplicate update equivalent?

Hello.
Is there a way to write a query with a behaviour equivalent to mysql's "on duplicate update"?
i.e., try to insert, and if key exists, update the row instead?
thanks,
Mime
View raw message