hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Markovitz, Dudu" <>
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.

MERGE is currently an open issue.

UPDATE and DELETE (and MERGE in the future) work under a bunch of limitations, e.g. –
Currently only ORC tables are supported

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

·         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

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

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?


From: raj hive []
Sent: Tuesday, June 21, 2016 2:22 PM
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
  insert into tablename values ...
Can any one help me how to do it in Hive?
View raw message