拉链表

欢迎大家来到IT世界,在知识的湖畔探索吧!

定义

拉链表是一种数据模型，主要是针对数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。拉链表可以避免按每一天存储所有记录造成的海量存储问题，同时也是处理缓慢变化数据（SCD2）的一种常见方式。

用途

1. 数据量比较大 2. 表中的部分字段会被update,如用户的地址，产品的描述信息，订单的状态等等 3. 需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态，比如，查看某一个用户在过去某一段时间内，更新过几次等等 4. 变化的比例和频率不是很大，比如，总共有1000万的会员，每天新增和发生变化的有10万左右 5. 如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费

欢迎大家来到IT世界,在知识的湖畔探索吧!

拉链历史表，既能满足反应数据的历史状态，又可以最大程度的节省存储

案例一(历史表不是分区表)

欢迎大家来到IT世界,在知识的湖畔探索吧!1. 涉及到的表 1.1 业务表: create table info( sid int, sname varchar(12), address varchar(23), create_time varchar(12), update_time varchar(12) ); 1.2 hive 接收业务系统数据表 drop table if exists ods_info_for; create table if not exists ods_info_for( sid int, sname varchar(12), address varchar(23), create_time varchar(12), update_time varchar(12) ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED as textfile ; 1.3 hive 分离出业务系统数据变化表(业务新增/修改的数据) drop table if exists ods_info_add; create table if not exists ods_info_add( sid int, sname varchar(12), address varchar(23), create_time varchar(12), update_time varchar(12), start_time varchar(12), end_time varchar(12) ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED as textfile ; 1.4 hive 存储历史数据变化表 drop table if exists ods_info_his; create table if not exists ods_info_his( sid int, sname varchar(12), address varchar(23), create_time varchar(12), update_time varchar(12), start_time varchar(12), end_time varchar(12) ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED as textfile ; 2. 数据流转 业务数据 : info.txt 1,张三,北京,2020-10-20,2021-05-30 2,李四,上海,2021-03-05,2021-03-05 3,王五,广州,2021-08-25,2021-09-02 4,赵六,深圳,2021-09-23,2021-09-25 2.1 加载数据到 ods 外部表中 load data local inpath '/opt/info.txt' into table demo.ods_info_for; 2.1 第一次全量同步 insert into ods_info_his(sid, sname, address, create_time, update_time, start_time,end_time ) select sid, sname, address, create_time, update_time,current_date() start_time,'9999-12-31' end_time from ods_info_for ; 1 张三 北京 2020-10-20 2021-05-30 2024-04-07 9999-12-31 2 李四 上海 2021-03-05 2021-03-05 2024-04-07 9999-12-31 3 王五 广州 2021-08-25 2021-09-02 2024-04-07 9999-12-31 4 赵六 深圳 2021-09-23 2021-09-25 2024-04-07 9999-12-31 2.2 往后增量同步数据 2.2.1 变化数据 2 李四 哈尔滨 2021-03-05 2024-04-07 5 田七 南京 2021-09-26 2024-04-07 2.2.2 加载数据到 ods 外部表中 load data local inpath '/opt/info_add.txt' overwrite into table demo.ods_info_for; 2.2.3 剥离变化数据(新增/修改)到 ods_info_add 表中 insert overwrite table ods_info_add select *,update_time start_time,'9999-12-31' end_time from ods_info_for where Create_time='2024-04-07' or Update_time='2024-04-07' ; 2 李四 哈尔滨 2021-03-05 2024-04-07 2024-04-07 9999-12-31 5 田七 南京 2021-09-26 2024-04-07 2024-04-07 9999-12-31 2.2.4 获取 ods_info_for 表数据并与 ods_info_add 表数据做左关联,将关联上数据 end_time 字段值改成 右表 update_time 字段值 select h.sid, h.sname, h.address, h.create_time, h.update_time,h.start_time ,if(h.end_time='9999-12-31' and i.sid is not null,i.update_time ,h.end_time) as End_time from ods_info_add i right join ods_info_his h on i.sid = h.sid ; 1 张三 北京 2020-10-20 2021-05-30 2024-04-07 9999-12-31 2 李四 上海 2021-03-05 2021-03-05 2024-04-07 2024-04-07 3 王五 广州 2021-08-25 2021-09-02 2024-04-07 9999-12-31 4 赵六 深圳 2021-09-23 2021-09-25 2024-04-07 9999-12-31 2.2.5 将 2.2.4 步骤获取的数据 与 ods_info_add 表做 UNION all 数据合并 1 张三 北京 2020-10-20 2021-05-30 2024-04-07 9999-12-31 2 李四 上海 2021-03-05 2021-03-05 2024-04-07 2024-04-07 2 李四 哈尔滨 2021-03-05 2024-04-07 2024-04-07 9999-12-31 3 王五 广州 2021-08-25 2021-09-02 2024-04-07 9999-12-31 4 赵六 深圳 2021-09-23 2021-09-25 2024-04-07 9999-12-31 5 田七 南京 2021-09-26 2024-04-07 2024-04-07 9999-12-31 2.2.6 将 2.2.5 合并后的数据复写到 ods_info_his 表中 insert overwrite table ods_info_his select h.sid, h.sname, h.address, h.create_time, h.update_time,h.start_time ,if(h.end_time='9999-12-31' and i.sid is not null,i.update_time ,h.end_time) as end_time from ods_info_add i right join ods_info_his h on i.sid = h.sid union all select * from ods_info_add ; 3. 整理流程 第一次同步数据到历史表 : 将从业务系统获取的数据后面添加 start_time 和 end_time 两个字段,然后将数据同步到 历史表(ods_info_his)中; start_time 值为任务同步的当天时间; end_time 值为 '9999-12-31' 固定值; 往后再次从业务系统获取数据 : 1. 获取变化数据(新增/修改) 添加 start_time 和 end_time 两个字段,然后将数据同步到 记录数据变化表(ods_info_add)中; start_time 值为任务同步的当天时间; end_time 值为 '9999-12-31' 固定值; 2. 通过 ods_info_his 与 ods_info_add 进行左关联,获取 ods_info_his 表数据,将关联上的数据中 end_time 改成 ods_info_add 表中 update_time 字段值; 3. 将 2 获取的数据 与 ods_info_add 表进行 union all 数据合并; 3. 将 3 合并后的数据复写到 ods_info_his 表中;