You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by yogesh dhari <yo...@gmail.com> on 2014/02/18 13:39:27 UTC
Hive Query :: Implementing case statement
Hello All,
I have a use case where a table say TABLE_SQL is geting updated like.
1st Update Command
update TABLE_SQL a
set BALANCE = b.prev
from TABLE_SQL_2 b
where a.key = b.key and a.code = "1"
2nd Update Command
update TABLE_SQL a
set BALANCE = b.prev
from TABLE_SQL_3 b
where a.key = b.key and a.code = "2"
same column is getting update twice in sql table,
I have a Table in Hive say TABLE_HIVE.
How to perform this kind operatation in HIVE,
Pls Help,
Thanks in Advance
Yogesh Kumar
Re: Hive Query :: Implementing case statement
Posted by yogesh dhari <yo...@gmail.com>.
Hello Stephen ,
Yes, actully I have used Left Outer Join instead of Join, there were left
outer joins in RDBMS Query instead of join.
Thanks again :)
On Thu, Feb 20, 2014 at 10:45 AM, Stephen Sprague <sp...@gmail.com>wrote:
> Hi Yogesh,
>
> i overlooked one thing and for completeness we should make note of it here.
>
> change:
>
>
> -- non-intersected rows
> select a.* from TABLE_SQL a join NEW_BALS b on (a.key=b.key) where
> b.NEW_BALANCE is null
>
> to
>
> -- non-intersected rows
> select a.* from TABLE_SQL a *LEFT OUTER* join NEW_BALS b on
> (a.key=b.key) where b.NEW_BALANCE is null
>
>
> This takes care of the rows where code != 1 and code != 2.
>
>
> But you knew that!
>
>
>
>
> On Wed, Feb 19, 2014 at 8:33 PM, yogesh dhari <yo...@gmail.com>wrote:
>
>> Thanks a lot Stephen Sprague :) :)
>>
>> It worked.. , just to remove the " ; " from here, bcoz it was throuig
>> sub query systax error...
>>
>>
>>
>> create table NEW_BALS as
>> select * from (
>> select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_2 b
>> on (a.key=b.key) where a.code='1';
>> UNION ALL
>> select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_3 b
>> on (a.key=b.key) where a.code='2';
>> ) z
>> ;
>>
>>
>>
>> to
>>
>> create table NEW_BALS as
>> select * from (
>> select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_2 b
>> on (a.key=b.key) where a.code='1'
>> UNION ALL
>> select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_3 b
>> on (a.key=b.key) where a.code='2'
>> ) z
>> ;
>>
>> Thanks & Regards
>> Yogesh Kumar
>>
>>
>> On Tue, Feb 18, 2014 at 9:18 PM, Stephen Sprague <sp...@gmail.com>wrote:
>>
>>> maybe consider something along these lines. nb. not tested.
>>>
>>> -- temp table holding new balances + key
>>>
>>> create table NEW_BALS as
>>> select * from (
>>> select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join
>>> TABLE_SQL_2 b on (a.key=b.key) where a.code='1';
>>> UNION ALL
>>> select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join
>>> TABLE_SQL_3 b on (a.key=b.key) where a.code='2';
>>> ) z
>>> ;
>>>
>>> -- i gave the table a new name but you could use TABLE_SQL instead of
>>> NEW_TABLE_SQL for the true update.
>>>
>>> insert overwrite table NEW_TABLE_SQL
>>>
>>> select * from (
>>>
>>> -- intersected rows - you'll have to line-up the columns correctly
>>> substituting b.NEW_BALANCE into the right position
>>> select a.col1, b.NEW_BALANCE, a.col2, ... from TABLE_SQL a join
>>> NEW_BALS b on (a.key=b.key)
>>>
>>> UNION ALL
>>>
>>> -- non-intersected rows
>>> select a.* from TABLE_SQL a join NEW_BALS b on (a.key=b.key) where
>>> b.NEW_BALANCE is null
>>>
>>> ) z
>>> ;
>>>
>>> there's probably some typos in there but hopefully you get the idea and
>>> can take it from here.
>>>
>>>
>>>
>>>
>>>
>>> On Tue, Feb 18, 2014 at 4:39 AM, yogesh dhari <yo...@gmail.com>wrote:
>>>
>>>> Hello All,
>>>>
>>>> I have a use case where a table say TABLE_SQL is geting updated like.
>>>>
>>>>
>>>> 1st Update Command
>>>>
>>>> update TABLE_SQL a
>>>> set BALANCE = b.prev
>>>> from TABLE_SQL_2 b
>>>> where a.key = b.key and a.code = "1"
>>>>
>>>>
>>>> 2nd Update Command
>>>>
>>>> update TABLE_SQL a
>>>> set BALANCE = b.prev
>>>> from TABLE_SQL_3 b
>>>> where a.key = b.key and a.code = "2"
>>>>
>>>>
>>>> same column is getting update twice in sql table,
>>>>
>>>> I have a Table in Hive say TABLE_HIVE.
>>>>
>>>> How to perform this kind operatation in HIVE,
>>>>
>>>> Pls Help,
>>>>
>>>> Thanks in Advance
>>>> Yogesh Kumar
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>
Re: Hive Query :: Implementing case statement
Posted by Stephen Sprague <sp...@gmail.com>.
Hi Yogesh,
i overlooked one thing and for completeness we should make note of it here.
change:
-- non-intersected rows
select a.* from TABLE_SQL a join NEW_BALS b on (a.key=b.key) where
b.NEW_BALANCE is null
to
-- non-intersected rows
select a.* from TABLE_SQL a *LEFT OUTER* join NEW_BALS b on
(a.key=b.key) where b.NEW_BALANCE is null
This takes care of the rows where code != 1 and code != 2.
But you knew that!
On Wed, Feb 19, 2014 at 8:33 PM, yogesh dhari <yo...@gmail.com> wrote:
> Thanks a lot Stephen Sprague :) :)
>
> It worked.. , just to remove the " ; " from here, bcoz it was throuig
> sub query systax error...
>
>
>
> create table NEW_BALS as
> select * from (
> select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_2 b on
> (a.key=b.key) where a.code='1';
> UNION ALL
> select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_3 b on
> (a.key=b.key) where a.code='2';
> ) z
> ;
>
>
>
> to
>
> create table NEW_BALS as
> select * from (
> select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_2 b on
> (a.key=b.key) where a.code='1'
> UNION ALL
> select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_3 b on
> (a.key=b.key) where a.code='2'
> ) z
> ;
>
> Thanks & Regards
> Yogesh Kumar
>
>
> On Tue, Feb 18, 2014 at 9:18 PM, Stephen Sprague <sp...@gmail.com>wrote:
>
>> maybe consider something along these lines. nb. not tested.
>>
>> -- temp table holding new balances + key
>>
>> create table NEW_BALS as
>> select * from (
>> select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join
>> TABLE_SQL_2 b on (a.key=b.key) where a.code='1';
>> UNION ALL
>> select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join
>> TABLE_SQL_3 b on (a.key=b.key) where a.code='2';
>> ) z
>> ;
>>
>> -- i gave the table a new name but you could use TABLE_SQL instead of
>> NEW_TABLE_SQL for the true update.
>>
>> insert overwrite table NEW_TABLE_SQL
>>
>> select * from (
>>
>> -- intersected rows - you'll have to line-up the columns correctly
>> substituting b.NEW_BALANCE into the right position
>> select a.col1, b.NEW_BALANCE, a.col2, ... from TABLE_SQL a join
>> NEW_BALS b on (a.key=b.key)
>>
>> UNION ALL
>>
>> -- non-intersected rows
>> select a.* from TABLE_SQL a join NEW_BALS b on (a.key=b.key) where
>> b.NEW_BALANCE is null
>>
>> ) z
>> ;
>>
>> there's probably some typos in there but hopefully you get the idea and
>> can take it from here.
>>
>>
>>
>>
>>
>> On Tue, Feb 18, 2014 at 4:39 AM, yogesh dhari <yo...@gmail.com>wrote:
>>
>>> Hello All,
>>>
>>> I have a use case where a table say TABLE_SQL is geting updated like.
>>>
>>>
>>> 1st Update Command
>>>
>>> update TABLE_SQL a
>>> set BALANCE = b.prev
>>> from TABLE_SQL_2 b
>>> where a.key = b.key and a.code = "1"
>>>
>>>
>>> 2nd Update Command
>>>
>>> update TABLE_SQL a
>>> set BALANCE = b.prev
>>> from TABLE_SQL_3 b
>>> where a.key = b.key and a.code = "2"
>>>
>>>
>>> same column is getting update twice in sql table,
>>>
>>> I have a Table in Hive say TABLE_HIVE.
>>>
>>> How to perform this kind operatation in HIVE,
>>>
>>> Pls Help,
>>>
>>> Thanks in Advance
>>> Yogesh Kumar
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>
Re: Hive Query :: Implementing case statement
Posted by yogesh dhari <yo...@gmail.com>.
Thanks a lot Stephen Sprague :) :)
It worked.. , just to remove the " ; " from here, bcoz it was throuig
sub query systax error...
create table NEW_BALS as
select * from (
select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_2 b on
(a.key=b.key) where a.code='1';
UNION ALL
select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_3 b on
(a.key=b.key) where a.code='2';
) z
;
to
create table NEW_BALS as
select * from (
select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_2 b on
(a.key=b.key) where a.code='1'
UNION ALL
select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_3 b on
(a.key=b.key) where a.code='2'
) z
;
Thanks & Regards
Yogesh Kumar
On Tue, Feb 18, 2014 at 9:18 PM, Stephen Sprague <sp...@gmail.com> wrote:
> maybe consider something along these lines. nb. not tested.
>
> -- temp table holding new balances + key
>
> create table NEW_BALS as
> select * from (
> select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join
> TABLE_SQL_2 b on (a.key=b.key) where a.code='1';
> UNION ALL
> select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join
> TABLE_SQL_3 b on (a.key=b.key) where a.code='2';
> ) z
> ;
>
> -- i gave the table a new name but you could use TABLE_SQL instead of
> NEW_TABLE_SQL for the true update.
>
> insert overwrite table NEW_TABLE_SQL
>
> select * from (
>
> -- intersected rows - you'll have to line-up the columns correctly
> substituting b.NEW_BALANCE into the right position
> select a.col1, b.NEW_BALANCE, a.col2, ... from TABLE_SQL a join
> NEW_BALS b on (a.key=b.key)
>
> UNION ALL
>
> -- non-intersected rows
> select a.* from TABLE_SQL a join NEW_BALS b on (a.key=b.key) where
> b.NEW_BALANCE is null
>
> ) z
> ;
>
> there's probably some typos in there but hopefully you get the idea and
> can take it from here.
>
>
>
>
>
> On Tue, Feb 18, 2014 at 4:39 AM, yogesh dhari <yo...@gmail.com>wrote:
>
>> Hello All,
>>
>> I have a use case where a table say TABLE_SQL is geting updated like.
>>
>>
>> 1st Update Command
>>
>> update TABLE_SQL a
>> set BALANCE = b.prev
>> from TABLE_SQL_2 b
>> where a.key = b.key and a.code = "1"
>>
>>
>> 2nd Update Command
>>
>> update TABLE_SQL a
>> set BALANCE = b.prev
>> from TABLE_SQL_3 b
>> where a.key = b.key and a.code = "2"
>>
>>
>> same column is getting update twice in sql table,
>>
>> I have a Table in Hive say TABLE_HIVE.
>>
>> How to perform this kind operatation in HIVE,
>>
>> Pls Help,
>>
>> Thanks in Advance
>> Yogesh Kumar
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
>
Re: Hive Query :: Implementing case statement
Posted by Stephen Sprague <sp...@gmail.com>.
maybe consider something along these lines. nb. not tested.
-- temp table holding new balances + key
create table NEW_BALS as
select * from (
select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_2
b on (a.key=b.key) where a.code='1';
UNION ALL
select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_3
b on (a.key=b.key) where a.code='2';
) z
;
-- i gave the table a new name but you could use TABLE_SQL instead of
NEW_TABLE_SQL for the true update.
insert overwrite table NEW_TABLE_SQL
select * from (
-- intersected rows - you'll have to line-up the columns correctly
substituting b.NEW_BALANCE into the right position
select a.col1, b.NEW_BALANCE, a.col2, ... from TABLE_SQL a join
NEW_BALS b on (a.key=b.key)
UNION ALL
-- non-intersected rows
select a.* from TABLE_SQL a join NEW_BALS b on (a.key=b.key) where
b.NEW_BALANCE is null
) z
;
there's probably some typos in there but hopefully you get the idea and can
take it from here.
On Tue, Feb 18, 2014 at 4:39 AM, yogesh dhari <yo...@gmail.com> wrote:
> Hello All,
>
> I have a use case where a table say TABLE_SQL is geting updated like.
>
>
> 1st Update Command
>
> update TABLE_SQL a
> set BALANCE = b.prev
> from TABLE_SQL_2 b
> where a.key = b.key and a.code = "1"
>
>
> 2nd Update Command
>
> update TABLE_SQL a
> set BALANCE = b.prev
> from TABLE_SQL_3 b
> where a.key = b.key and a.code = "2"
>
>
> same column is getting update twice in sql table,
>
> I have a Table in Hive say TABLE_HIVE.
>
> How to perform this kind operatation in HIVE,
>
> Pls Help,
>
> Thanks in Advance
> Yogesh Kumar
>
>
>
>
>
>
>
>
>
>
>
Re: Hive Query :: Implementing case statement
Posted by Navis류승우 <na...@nexr.com>.
If key is unique, you might overwrite values by using hbase handler.
2014-02-18 22:05 GMT+09:00 yogesh dhari <yo...@gmail.com>:
> Yes, Hive does not provide update statement, I am just looking for the
> work arround it, how to implement it
>
>
>
>
>
> On Tue, Feb 18, 2014 at 6:27 PM, Peter Marron <
> Peter.Marron@trilliumsoftware.com> wrote:
>
>> From https://cwiki.apache.org/confluence/display/Hive/Home
>>
>>
>>
>> "Hive is not designed for OLTP workloads and does not offer real-time
>> queries or row-level updates."
>>
>>
>>
>> As far as I am aware "UPDATE" isn't even in the Hive DML.
>>
>>
>>
>> Z
>>
>> *Peter Marron*
>> Senior Developer
>> Trillium Software, A Harte Hanks Company
>>
>> Theale Court, 1st Floor, 11-13 High Street
>> Theale
>> RG7 5AH
>>
>> +44 (0) 118 940 7609 office
>> +44 (0) 118 940 7699 fax
>>
>> [image:
>> https://4b2685446389bc779b46-5f66fbb59518cc4fcae8900db28267f5.ssl.cf2.rackcdn.com/trillium.png]<http://www.trilliumsoftware.com/>
>>
>> trilliumsoftware.com <http://www.trilliumsoftware.com/> / linkedin<http://www.linkedin.com/company/17710>
>> / twitter <https://twitter.com/trilliumsw> / facebook<http://www.facebook.com/HarteHanks>
>>
>>
>>
>> *From:* yogesh dhari [mailto:yogeshhive@gmail.com]
>> *Sent:* 18 February 2014 12:39
>> *To:* user@hive.apache.org
>> *Subject:* Hive Query :: Implementing case statement
>>
>>
>>
>> Hello All,
>>
>>
>>
>> I have a use case where a table say TABLE_SQL is geting updated like.
>>
>>
>>
>>
>>
>> 1st Update Command
>>
>>
>>
>> update TABLE_SQL a
>>
>> set BALANCE = b.prev
>>
>> from TABLE_SQL_2 b
>>
>> where a.key = b.key and a.code = "1"
>>
>>
>>
>>
>>
>> 2nd Update Command
>>
>>
>>
>> update TABLE_SQL a
>>
>> set BALANCE = b.prev
>>
>> from TABLE_SQL_3 b
>>
>> where a.key = b.key and a.code = "2"
>>
>>
>>
>>
>>
>> same column is getting update twice in sql table,
>>
>>
>>
>> I have a Table in Hive say TABLE_HIVE.
>>
>>
>>
>> How to perform this kind operatation in HIVE,
>>
>>
>>
>> Pls Help,
>>
>>
>>
>> Thanks in Advance
>>
>> Yogesh Kumar
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
>
Re: Hive Query :: Implementing case statement
Posted by yogesh dhari <yo...@gmail.com>.
Yes, Hive does not provide update statement, I am just looking for the work
arround it, how to implement it
On Tue, Feb 18, 2014 at 6:27 PM, Peter Marron <
Peter.Marron@trilliumsoftware.com> wrote:
> From https://cwiki.apache.org/confluence/display/Hive/Home
>
>
>
> "Hive is not designed for OLTP workloads and does not offer real-time
> queries or row-level updates."
>
>
>
> As far as I am aware "UPDATE" isn't even in the Hive DML.
>
>
>
> Z
>
> *Peter Marron*
> Senior Developer
> Trillium Software, A Harte Hanks Company
>
> Theale Court, 1st Floor, 11-13 High Street
> Theale
> RG7 5AH
>
> +44 (0) 118 940 7609 office
> +44 (0) 118 940 7699 fax
>
> [image:
> https://4b2685446389bc779b46-5f66fbb59518cc4fcae8900db28267f5.ssl.cf2.rackcdn.com/trillium.png]<http://www.trilliumsoftware.com/>
>
> trilliumsoftware.com <http://www.trilliumsoftware.com/> / linkedin<http://www.linkedin.com/company/17710>
> / twitter <https://twitter.com/trilliumsw> / facebook<http://www.facebook.com/HarteHanks>
>
>
>
> *From:* yogesh dhari [mailto:yogeshhive@gmail.com]
> *Sent:* 18 February 2014 12:39
> *To:* user@hive.apache.org
> *Subject:* Hive Query :: Implementing case statement
>
>
>
> Hello All,
>
>
>
> I have a use case where a table say TABLE_SQL is geting updated like.
>
>
>
>
>
> 1st Update Command
>
>
>
> update TABLE_SQL a
>
> set BALANCE = b.prev
>
> from TABLE_SQL_2 b
>
> where a.key = b.key and a.code = "1"
>
>
>
>
>
> 2nd Update Command
>
>
>
> update TABLE_SQL a
>
> set BALANCE = b.prev
>
> from TABLE_SQL_3 b
>
> where a.key = b.key and a.code = "2"
>
>
>
>
>
> same column is getting update twice in sql table,
>
>
>
> I have a Table in Hive say TABLE_HIVE.
>
>
>
> How to perform this kind operatation in HIVE,
>
>
>
> Pls Help,
>
>
>
> Thanks in Advance
>
> Yogesh Kumar
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
RE: Hive Query :: Implementing case statement
Posted by Peter Marron <Pe...@trilliumsoftware.com>.
>From https://cwiki.apache.org/confluence/display/Hive/Home
"Hive is not designed for OLTP workloads and does not offer real-time queries or row-level updates."
As far as I am aware "UPDATE" isn't even in the Hive DML.
Z
Peter Marron
Senior Developer
Trillium Software, A Harte Hanks Company
Theale Court, 1st Floor, 11-13 High Street
Theale
RG7 5AH
+44 (0) 118 940 7609 office
+44 (0) 118 940 7699 fax
[https://4b2685446389bc779b46-5f66fbb59518cc4fcae8900db28267f5.ssl.cf2.rackcdn.com/trillium.png]<http://www.trilliumsoftware.com/>
trilliumsoftware.com<http://www.trilliumsoftware.com/> / linkedin<http://www.linkedin.com/company/17710> / twitter<https://twitter.com/trilliumsw> / facebook<http://www.facebook.com/HarteHanks>
From: yogesh dhari [mailto:yogeshhive@gmail.com]
Sent: 18 February 2014 12:39
To: user@hive.apache.org
Subject: Hive Query :: Implementing case statement
Hello All,
I have a use case where a table say TABLE_SQL is geting updated like.
1st Update Command
update TABLE_SQL a
set BALANCE = b.prev
from TABLE_SQL_2 b
where a.key = b.key and a.code = "1"
2nd Update Command
update TABLE_SQL a
set BALANCE = b.prev
from TABLE_SQL_3 b
where a.key = b.key and a.code = "2"
same column is getting update twice in sql table,
I have a Table in Hive say TABLE_HIVE.
How to perform this kind operatation in HIVE,
Pls Help,
Thanks in Advance
Yogesh Kumar