hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Markovitz, Dudu" <dmarkov...@paypal.com>
Subject RE: if else condition in hive
Date Tue, 21 Jun 2016 19:59:38 GMT
I understand that you’re looking for the functionality of the MERGE statement.

1)
MERGE is currently an open issue.
https://issues.apache.org/jira/browse/HIVE-10924

2)
UPDATE and DELETE (and MERGE in the future) work under a bunch of limitations, e.g. –
Currently only ORC tables are supported
https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions

3)
If we’re not working with transactional tables, we have no choice but create a temporary
target table (‘trg_tmp’) that will hold the new (updated and inserted) data and then replace
the original table/content (‘trg’) with the new one, in one of the following ways:

·         1

o   Drop table trg;

o   Alter table trg_tmp rename to trg;

·         2

o   Drop table trg_bck;

o   Alter table trg rename to trg_bck;

o   Alter table trg_tmp rename to trg;

·         3

o   Truncate table trg;

o   Insert into trg select * from trg_tmp;


I would recommend (2).

·         We keep the old table as a backup in case something goes wrong (in opposite of
(1)).

·         We have the minimum down time (in opposite of (3)).
The down sides are –

·         Renaming the ‘trg’ table requires that no one will touch the table at that
time

·         We preserve the storage of ‘trg’, ‘trg_bck’ and for some of the time –
‘trg_tmp’

One question regarding your specific case –
For matching rows (update operation), do we need any data from the target table or can we
take all the required columns from the source table?


Dudu



From: raj hive [mailto:raj.hiveql@gmail.com]
Sent: Tuesday, June 21, 2016 2:22 PM
To: user@hive.apache.org
Subject: if else condition in hive

Hi friends,
INSERT,UPDATE,DELETE commands are working fine in my Hive environment after changing the configuration
and all. Now, I have to execute a query like below sql  in hive.
If exists(select * from tablename where columnname=something)
  update table set column1=something where columnname=something
 else
  insert into tablename values ...
Can any one help me how to do it in Hive?
Thanks
Raj
Mime
View raw message