How to replace values in table a according another table b in Hive?

Question

Since Hive does not support update... So i wonder how can achieve it in Hive.

For say, I have table A, and Table B, which are linked with a key named user_id. Then there are some missing values in register_date in table A. Those missing cases can be filled with b_date in table B.

So, for each user_id, if the register_date (in table A) is missing, then I want to fill it with corresponding b_date value in table B.

Any ideas about it? Thanks a lot!

Answer 1

You can use case statement to do so : lets say you want to update table_a column name user_id .

If user_id is null then you want to update with the column of table B which b_date then you can do so by firing below query :

select case when a.user_id is null or len(trim(user_id)==0) then b.b_date else a.user_id end as user_id from table_A a join table_B b on a.id=b.id;

Answer 2

Go For the Update Hive 0.14.

Move the data of table A to table C. Then Do a Join on Table B and Table C(give your Condition and Pick Accordingly). Then Insert to table A. Then Drop Table C.

Insert into TableC Select * from Table A

Insert into TableA Select user_id,if(TableC.register_date IS NULL ,TableB.b_date) FROM TableB join TableC on TableB.user_id=TableC.user_id

Drop Table TableC;

Answer 3

UPDATE is available starting in Hive 0.14 as mentioned here. You have a good reason to upgrade now.

If you cannot upgrade Hive, you can create a new temp-table by joining table-A and table-B and then drop table-A and rename the temp-table to table-A.