Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Hive update a column from another table

Hive update a column from another table

Explorer

Hi,

I have a clustered bucketted table, with ACID enabled

Does hive supports updates from another table?

This is something I am trying to achieve, updating a column values from another table

UPDATE transaction_bak

SET fl_t_order = tt.t_order

FROM trans_temp tt

WHERE transaction_bak.id = tt.id;

I tried with left join, but no success, getting ParseException line 2:0 missing SET at 'left'

UPDATE transaction_bak

LEFT OUTER JOIN trans_temp tt

SET fl_t_order = tt.t_order

WHERE transaction_bak.id = tt.id;

Any idea

4 REPLIES 4
Highlighted

Re: Hive update a column from another table

Expert Contributor

Hive does not support this directly but you can use SQL Merge statement to achieve this: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML#LanguageManualDML-Merge

Highlighted

Re: Hive update a column from another table

Explorer

Thanks Eugene for swift reply.

We are running on HDP 2.5 and MERGE is available in 2.6. What options do I have?

Thanks

MB

Highlighted

Re: Hive update a column from another table

@M B. Here is an older, pre-merge blog on how to handle incremental changes in Hive. https://hortonworks.com/blog/four-step-strategy-incremental-updates-hive/

Highlighted

Re: Hive update a column from another table

New Contributor

I am also facing the same problem

Don't have an account?
Coming from Hortonworks? Activate your account here