hive 历史拉链表的处理

1. 

CREATE TABLE lalian_test(id int,col1 string,col2 string,dt string)--测试表
COMMENT 'this is a test2' 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
STORED AS TEXTFILE
LOCATION
'/user/hive/warehouse/lalian_test';

2. -----模拟数据为txt，以\t分隔 
1aaa100002014-06-17
2bbb200002014-06-17
1aaq100012014-06-18
2bbq200022014-06-18
3ccc300002014-06-18

3. LOAD DATA LOCAL INPATH '/data/home/test/lalian.txt' INTO TABLE default.lalian_test;

4.

----创建中间表--存放数据的增量变动类型
CREATE TABLE mid1_t(realid int,p2col1 string,p2col2 string, data_type string,start_date string) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
STORED AS TEXTFILE;

--存放更新前的数据
CREATE TABLE mid2_t(id int,col1 string,col2 string,start_date string,end_date string) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
STORED AS TEXTFILE;
---存放所有数据，已区分开始时间和结束时间 
CREATE TABLE t_his(id int,col1 string,col2 string,start_date string,end_date string) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
STORED AS TEXTFILE;

 

insert overwrite table mid1_t
SELECT
case when p2.id is not null then p2.id else p1.id end as id,
p2.col1,
p2.col2,
case when p1.id is null and p2.id is not null then 'I'
when p1.id is not null and p2.id is not null and (p1.col1=p2.col1 and p1.col2=p2.col2) then 'R'
when p1.id is not null and p2.id is not null and (p1.col1!=p2.col1 or p1.col2!=p2.col2) then 'U'
when p1.id is not null and p2.id is null then 'D'
end as data_type,
case when p2.id is not null then p2.dt else p1.dt end as start_date 
FROM
(SELECT * FROM lalian_test where dt='2014-06-17')P1
FULL OUTER JOIN
(SELECT * FROM lalian_test where dt='2014-06-18')P2
ON P1.id=P2.id;

a. 初始化 insert overwrite table mid2_t select a.*,'2014-06-18' from lalian_test where dt='2014-06-17';
b. insert overwrite table mid2_t select * from t_his;

--处理闭链
#insert overwrite table t_his select * from mid2_t where end_date<'4712-12-31';

-----插入更新钱的数据 
insert into table t_his
select
    t1.realid,
    t1.p2col1,
    t1.p2col2,
    t1.start_date,
    '2014-06-18' as end_date --该时间可自己控制，区分新旧数据
from mid2_t t1

----插入更新后的数据
insert into table t_his
select
    t1.realid,
    t1.p2col1,
    t1.p2col2,
    '2014-06-19', --该时间自己控制，区分新旧数据
    '4712-12-31' as end_date
from mid1_t t1
where data_type in('U','I');  


-----插入删除的数据
insert into table t_his
select
    t1.realid,
    t1.p2col1,
    t1.p2col2,
    '2014-06-18', --该时间自己控制，区分新旧数据
    '2014-06-19' as end_date
from mid1_t t1
where data_type in('D');