Support Questions
Find answers, ask questions, and share your expertise

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

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

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

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

New Contributor

I am also facing the same problem

; ;