如何在hive0.13中更新表?

9wbgstp7  于 2021-06-02  发布在  Hadoop
关注(0)|答案(1)|浏览(360)

我的Hive版本是0.13。我有两张table, table_1 以及
table_2 table_1 包含:

customer_id | items | price | updated_date
------------+-------+-------+-------------
10          | watch | 1000  | 20170626
11          | bat   | 400   | 20170625
``` `table_2` 包含:

customer_id | items | price | updated_date
------------+----------+-------+-------------
10 | computer | 20000 | 20170624

我想更新 `table_2` 如果 `customer_id` 已经存在于其中,如果不存在,则应附加到 `table_2` .
由于hive0.13不支持更新,我尝试使用join,但失败了。
hjqgdpho

hjqgdpho1#

你可以用 row_number 或者 full join . 这是一个使用 row_number :

insert overwrite table_1 
select customer_id, items, price, updated_date
from
(
select customer_id, items, price, updated_date,
       row_number() over(partition by customer_id order by new_flag desc) rn
from 
    (
     select customer_id, items, price, updated_date, 0 as new_flag
       from table_1
     union all
     select customer_id, items, price, updated_date, 1 as new_flag
       from table_2
    ) all_data
)s where rn=1;

另请参阅此答案,以便使用 FULL JOIN : https://stackoverflow.com/a/37744071/2700344

相关问题