hive大数据除重问题研究

方法一(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 limao_store s left outer join limao_incre i on(s.p_key=i.p_key) where i.p_key=null
 union all
 select p_key,sort_word from limao_incre);
 


分析： 不能识别 incre中的重复数据 长表关联 ， 表宽度加倍


方法三（left outer join + insert into）

insert overwrite table store 
 select s.* from store s left outer join incre i on(s.p_key=i.p_key) where i.p_key=null 
 insert into table jm_g_l_cust_secu_acct 
 select * from jm_g_l_cust_secu_acct_tmp;

 分析： insert into 最好不用。 使用insert into 在hdfs中的表现为，在表（分区）文件夹下，建立新的文件
存放insert into数据， 造成文件碎片，降低以后该表查询效率。
 

==================================================================================


use nets_life;
create table limao_store 
(
 p_key string,
 sort_word string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;

create table limao_incre 
(
 p_key string,
 sort_word string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;


建表语句

use nets_life;
create table limao_store 
(
 p_key string,
 sort_word string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;

create table limao_incre 
(
 p_key string,
 sort_word string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;

=====================================================

================================================================================================