if else condition in hive

[raj hive <ra...@gmail.com>]

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

RE: if else condition in hive

["Markovitz, Dudu" <dm...@paypal.com>]

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

Re: if else condition in hive

[Jörn Franke <jo...@gmail.com>]

I recommend you to rethink it as part of a bulk transfer potentially even using separate partitions. Will be much faster.

> On 21 Jun 2016, at 13:22, raj hive <ra...@gmail.com> wrote:
> 
> 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

Re: if else condition in hive

[Dmitry Tolpeko <dm...@gmail.com>]

Hi Raj,

Hive hpl/sql component can be used to achieve such functionality (see
www.hplsql.org for docs), now you can run it as a separate tool, and I hope
some day it will be available from Hive or Beeline CLI.

Thanks,
Dmitry

On Tue, Jun 21, 2016 at 2:22 PM, raj hive <ra...@gmail.com> wrote:

> 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
>