如何更新Hive 0.13中的表格？ [英] How to update table in Hive 0.13?

问题描述

我的Hive版本是0.13。我有两个表， table_1 和 table_2

table_1 包含：

  customer_id |物品|价格| updated_date 
 ------------ + ------- + ------- + ------------- 
 10 |观看| 1000 | 20170626 
 11 |蝙蝠| 400 | 20170625 
  

table_2 包含：

  customer_id |物品|价格| updated_date 
 ------------ + ---------- + ------- + ------------- 
 10 |电脑| 20000 | 20170624 
  

我想要更新 table_2 如果 customer_id 已经存在，那么它应该附加到 table_2 。

由于Hive 0.13不支持更新，我尝试了使用连接，但它失败了。

解决方案

您可以使用 row_number 或完整连接。这是使用 row_number ：

  insert overwrite table_1 
从
（
）中选择customer_id，物品，价格，updated_date 
选择customer_id，物品，价格，updated_date，
 row_number（）（由customer_id order by new_flag desc分区）$从
（
）中选择customer_id，items，price，updated_date，0作为new_flag 
 from table_1 
 union all 
选择customer_id，items，price，updated_date， 1作为new_flag 
 from table_2 
）all_data 
）s其中rn = 1; 
  

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

My Hive version is 0.13. I have two tables, table_1 and table_2

table_1 contains:

customer_id | items | price | updated_date
------------+-------+-------+-------------
10          | watch | 1000  | 20170626
11          | bat   | 400   | 20170625

table_2 contains:

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

I want to update records of table_2 if customer_id already exists in it, if not, it should append to table_2.

As Hive 0.13 does not support update, I tried using join, but it fails.

解决方案

You can use row_number or full join. This is example using 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;

Also see this answer for update using FULL JOIN: https://stackoverflow.com/a/37744071/2700344

这篇关于如何更新Hive 0.13中的表格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！