hive 拉链表 新增数据 oracle拉链表原理

拉链表

概念

不是技术,而是解决方案
目的:节约存储空间

记录数据在某一时间区间内的状态
以及数据在某一时点上的变化的数据存储方式

也是应需求而产生的技术解决方案

历史数据的两种存储方式

账户ID	用户	状态	数据日期
001	张三	1	2008-06-27
001	张三	1	2008-06-28
001	张三	1	2008-06-29
001	张三	1	2008-06-30
001	张三	1	2008-07-01
001	张三	1	2008-07-02
001	张三	1	2008-07-03
001	张三	1	...
001	张三	0	2010-02-23
001	张三	0	2010-02-24
001	张三	0	2010-02-25
001	张三	0	2010-02-26
001	张三	0	...

---

账户ID	户名	状态	开始日期	结束日期
001	张三	1	2008-06-27	2010-02-23
001	张三	0	2010-02-23	2999-12-31

流程

1. 建立临时表1
   用于存放转换,处理后的数据
2. 建立临时表2
   用于存放比对出的增量数据
3. 修改目标表
   进行关链更新操作
4. 修改目标表
   进行开链插入操作

代码实现流程

建立源数据

create table test_src
(
ID varchar2(100),
NAME varchar2(100),
BAL number(20,2)
)

往源中插入数据

insert into test_src 
select '1','徐峥',600 from dual
union all
select '2','黄渤',700 from dual

建立目标表

create table test_tag
(
ID varchar2(100),
NAME varchar2(100),
BAL number(20,2),
START_DT date,
END_DT date
)

建立临时表temp1

用于存放原系统数据

--事务临时表
create global temporary table temp1
as select * from test_tag

建立第二个临时表temp2

用于存放对比后的增量或是状态有变化的数据

create global temporary table temp2 --事务临时表
as select * from test_tag

临时表temp1插入数据

此处方便测试,使用前天时间

insert into temp1
select a.*,trunc(sysdate-2,'dd'),to_date('29990101','yyyy/mm/dd')
from test_src a

临时表temp2插入数据

insert into temp2
SELECT  *
FROM  temp1 t1
WHERE  not exists(
SELECT  1
FROM  test_tag g 
WHERE  g.END_DT=to_date('29990101','yyyy/mm/dd')
and t1.id=g.id
and t1.bal=g.bal);

关链

此处方便测试,使用前天时间

update test_tag a set end_dt=trunc(sysdate-2,'dd')
where exists (select 1 from temp2 b where a.id=b.id)
and END_DT = to_date('29990101','yyyy/mm/dd');

开链

insert into test_tag
select * from temp2;
commit;

查看目标表

SQL> select * from test_tag;
ID      NAME     BAL START_DT    END_DT
----- ---------- --- ----------- -----------
2      黄渤     700.00  2020/3/23   2999/1/1
1      徐峥     600.00  2020/3/23   2999/1/1

修改数据再次测试

修改和插入数据

update test_src
set bal=2700
where id=2;
insert into test_src
values(3,'黄晓明',1000);
commit;

SQL> select * from test_src;
ID   NAME           BAL
---  --------  -----------
1    徐峥        600.00
2    黄渤       2700.00
3    黄晓明     1000.00

插入到临时表temp1

此时为测试方便使用昨天时间

insert into temp1
select a.*,trunc(sysdate-1,'dd'),to_date('29990101','yyyy/mm/dd')
from test_src a

插入到临时表temp2

insert into temp2
SELECT  *
FROM  temp1 t1
WHERE  not exists(
SELECT  1
FROM  test_tag g 
WHERE  g.END_DT=to_date('29990101','yyyy/mm/dd')
and t1.id=g.id
and t1.bal=g.bal);

关链

update test_tag a set end_dt=trunc(sysdate-1,'dd')
where exists (select 1 from temp2 b where a.id=b.id)
and END_DT = to_date('29990101','yyyy/mm/dd');

开链

insert into test_tag
select * from temp2;
commit;

查看目标表

SQL> select * from test_tag;
ID   NAME           BAL START_DT    END_DT
---- -------- ---------- --------- -------------
3    黄晓明     1000.00 2020/3/24   2999/1/1
2    黄渤       2700.00 2020/3/24   2999/1/1
2    黄渤        700.00 2020/3/23   2020/3/24
1    徐峥        600.00 2020/3/23   2999/1/1

应用

查看23号大家的余额情况

SQL> select * from test_tag
  2  where start_dt<=to_date('20200323','yyyy/mm/dd')
  3  and
  4  end_dt>to_date('20200323','yyyy/mm/dd')
  5  ;
ID  NAME       BAL START_DT    END_DT
--- ----- -------- ---------- -------------
2   黄渤    700.00 2020/3/23   2020/3/24
1   徐峥    600.00 2020/3/23   2999/1/1

开始时间<=20200323
结束时间>20200323