存量表: store
增量表: incre
字段:
1. p_key 除重主键
2. w_sort 排序依据
3. info 其他信息
方法一(union all + row_number()over ):
insert overwrite table limao_store
select p_key,sort_word
from ( select tmp1.*, row_number() over(distribute by sort_word sort by p_key desc) rownum
from ( select *
from limao_store
union all
select *
from limao_incre
) tmp1
) hh
where hh.rownum = 1;
分析, 长表排序
方法二(left outer join + union all):
注意: hive 不支持 顶层 union all ,而且union all 结果必须有别名
insert overwrite table limao_store
select t.p_key,t.sort_word from (
select s.p_key,s.sort_word from l