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.hiv...@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
