我是Hive的新手，我有一个主表t1和临时表t2。临时表每天都会通过传入数据上载。传入的数据可以是更新的记录，也可以是新的记录。

table t2(temp):               table t1(main)
id   name                      id      name
1   vinni                      1       vikki
3   anna                       2       amita

id  name
1   vinni
2   amita
3   anna

最佳答案

您可以通过合并两个表来完成此操作，然后为已存在的ID选择更新的记录，例如

with union_table as (
  select *, 0 as new_flag from t1
  union all
  select *, 1 as new_flag from t2
),
stage_table as (
  select *, max(new_flag) over (partition by id) as max_flag
    from union_table
),
stage_table2 as (
  select id, name, new_flag
    from stage_table
   where new_flag = max_flag
)
select id, name
  from stage_table2

https://stackoverflow.com/questions/55137509/