关于拉链表的详细操作解说

相信大家会想要仔细学习拉链表的话，对于拉链表能处理的场景，以及拉链表的优点应该都不会陌生了吧，这里就不再解释拉链表如何如何好了。提个前提

拉链表是为了处理缓慢变化维的问题而出现的，绝对不能处理以天为维度以下的数据。所以这种数据的处理环境也都是数据仓库这种离线状态下的集群比如 hive，或许还有其他，但我只hive

会用到数据分层，不懂分层以及分层好处的，看我另一篇博客

为了能够更准确的描述拉链表的操作过程，会结合一些简单的表单数据来描述：

mysql

假装有两用户来注册，并且没有进行任何更改属性的操作，至少有两条数据才足以突出特性

业务数据一般存在mysql这种能处理事物，低数据量查询还快的数据库

id	username	phone	modifytime
1	aa	1111	2020-08-01
2	bb	2222	2020-08-01

dwd

创建分区表，优化查询速率

 CREATE TABLE dwd
(
  id INT, username STRING, phone STRING, modifytime DATE)
partitioned by (dt string)
row format delimited fields terminated by ';'
;

从mysql导入数据（事实上应该先导入ods层，经过了脏数据处理后才到了dwd层）这里专注讲解概念，相对繁琐的导入流程，我都放在另一篇博客 JerryC

id	username	phone	modifytime	dt
1	aa	1111	2020-08-01	2020-08-01
2	bb	2222	2020-08-01	2020-08-01

dws

CREATE TABLE dws
(
    id         INT,
    username   STRING,
    phone      STRING,
    modifytime DATE,
    starttime  DATE,
    endtime    DATE
)
    partitioned by (dt string)
    row format delimited fields terminated by ';'
;

从dwd层导入dws层：

insert into dws partition (dt = '2020-08-01')
select d.id,
       d.username,
       d.phone,
       d.modifytime,
       d.modifytime as starttime,  //起始时间就是数据的出现的时间
       '9999-12-31' as endtime   //自己拟定一个永远也不会到达的最大时间戳
from dwd d
where dt = '2020-08-01';

dw层中的数据要添加两个核心字段，第一个设置字段的添加时间 starttime，第二个设置时间的存活时间 endtime

id	name	phone	modifytime	starttime	endtime	dt
1	aa	1111	2020-08-01	2020-08-01	9999-12-31	2020-08-01
2	bb	2222	2020-08-01	2020-08-01	9999-12-31	2020-08-01

至此第一天数据处理完毕，接下来去看第二天的吧

mysql

第二天，用户名为bb的更改了自己的手机号，数据库中的字段也更新为第二天的时间，同时，新增了一个用户cc，那创建的时间自然为第二天

id	username	phone	modifytime
1	aa	1111	2020-08-01
2	bb	2333	2020-08-02
3	cc	3333	2020-08-02

dwd

下面的数据库表会出现一些打破常理的事情，我来一一解释

为什么数据库的唯一标识，id，重复了？

这是因为在hive数据库表中，并不存在主键这一说，虽然有unique属性可以确定某个字段值唯一，但是我们需要唯一值吗，仔细想想，拉链表是hive中的表，虽然出现了数据重复，但是重复的id代表的却都是一条数据，只不过这个数据的状态发生了变化，从有效变成了无效或者无效变成了有效，而mysql中的数据，状态怎么改id都不会变的，但是mysql中的数据是可以更改的，正好将一个数据的完整变化过程放到了hive里面。

为什么有了modifytime还需要加个dt字段？（3nf）

细心的同学会发现，我们在创建表的时候，字段并没有dt，dt是作为一个hive中的分区字段存在的，是一个文件夹，用来优化查询的

回归正题，这第二天的dwd中的数据，增加了mysql中所有modifytime字段为第二天的数据，并且放到了新的分区来存放

id	username	phone	modifytime	dt
1	aa	1111	2020-08-01	2020-08-01
2	bb	2222	2020-08-01	2020-08-01
2	bb	2333	2020-08-02	2020-08-02
3	cc	3333	2020-08-02	2020-08-02

dws

这一步第二天的dwd到dws数据导入就要用到拉链表的真正核心了，像一个算法一样奇妙

首先我们要确定一个概念，我们要将第二天更新的数据放到dws记录状态，但是不是所有数据都是新增的数据，还有的数据是更新的数据，直接将第二天的数据插入dws中确实没错，但是我们的endtime记录数据失效时间的意义就会没有用了，这里我明确告诉你，必须要在一条查询语句内将拉链数据全部写入到数据库内，否则对于endtime的处理将会无比麻烦，我慢慢引导你如何写出来查询语句：

先确定我们查询的时间：

1	select * from dws left join (select * from dwd d3 where d3.dt='2020-08-02' ) dwd on dwd.id=dws.id //用所有的dws中的数据，左连接刚刚更新的dwd中的数据

上面这个sql的数据模型就是， dws（所有历史拉链数据）：dwd（昨天出现更新的所有数据），dws中的所有字段都会存在（dws中数据代表历史数据），dwd中的数据字段并不一定会存在（dwd代表更新的数据，并不是所有历史数据都一定会有更新状态的）

上面数据有两种，一种是没有历史更新的aa，所以对应的右边更新数据就是都null没有数据，另一种是更新过的数据bb，对应右边就会有数据。那我们只需要根据这个刚刚生成的宽表判断右边字段是否为空，就可以区别出需要更新的数据和不需要更新的数据，得到以下sql

历史数据状态更新：

select d2.id,
       d2.username,
       d2.phone,
       d2.modifytime,
       d2.starttime as starttime,
       if(d2.endtime = '9999-12-31' and dwd.id is not null, to_date(dwd.modifytime), to_date(d2.endtime))
                    as endtime
from dws d2
         left join (select * from dwd d3 where d3.dt = '2020-08-02') dwd on d2.id = dwd.id;

上面的sql，唯一需要更新的只有dws的endtime，其余字段返回他的原来的值就行了，右边为空的数据也有可能是状态已经结束的数据。查询结果：

最新数据查询结果：

select
id,
       username,
       phone,
       modifytime,
       modifytime as starttime,
       '9999-12-31' as endtime
from dwd where dt='2020-08-02';

将两个sql拼接：

select d2.id,
       d2.username,
       d2.phone,
       d2.modifytime,
       d2.starttime as starttime,
       if(d2.endtime = '9999-12-31' and dwd.id is not null, to_date(dwd.modifytime), to_date(d2.endtime))
                    as endtime
from dws d2
         left join (select * from dwd d3 where d3.dt = '2020-08-02') dwd on d2.id = dwd.id
union all
select
id,
       username,
       phone,
       modifytime,
       modifytime as starttime,
       '9999-12-31' as endtime
from dwd where dt='2020-08-02';

最终插入表中：

要先插入临时表中，然后再将数据导入最终表

drop table if exists dws_ls;
create table dws_ls as
select d2.id,
       d2.username,
       d2.phone,
       d2.modifytime,
       d2.starttime as starttime,
       if(d2.endtime = '9999-12-31' and dwd.id is not null, to_date(dwd.modifytime), to_date(d2.endtime))
                    as endtime
from dws d2
         left join (select * from dwd d3 where d3.dt = '2020-08-02') dwd on d2.id = dwd.id
union all
select id,
       username,
       phone,
       modifytime,
       modifytime   as starttime,
       '9999-12-31' as endtime
from dwd
where dt = '2020-08-02';

再看这个临时表中的数据：

很好，已经完全符合我们的预期了，只需要插入到历史的拉链表中就行了

通过至少两天的数据，才可以完成一次拉链表操作哦

数据库表中哪些数据日期变更到了最新，那些就会被更新到dwd中。

dwd中多出几行数据，dws中就会多出几行数据，

总结sql：

综合第二天导入的sql，删除临时表:arrow_right:通过查询创建临时表:arrow_right:删除历史拉链表:arrow_right:创建分区历史拉链表:arrow_right:查询插入添加分区的方式

drop table if exists dws_ls;
create table dws_ls as
select d2.id,
       d2.username,
       d2.phone,
       d2.modifytime,
       d2.starttime as starttime,
       if(d2.endtime = '9999-12-31' and dwd.id is not null, to_date(dwd.modifytime), to_date(d2.endtime))
                    as endtime
from dws d2
         left join (select * from dwd d3 where d3.dt = '2020-08-02') dwd on d2.id = dwd.id
union all
select id,
       username,
       phone,
       modifytime,
       modifytime   as starttime,
       '9999-12-31' as endtime
from dwd
where dt = '2020-08-02';


drop table if exists dws;
CREATE TABLE dws
(
    id         INT,
    username   STRING,
    phone      STRING,
    modifytime DATE,
    starttime  DATE,
    endtime    DATE
)
    partitioned by (dt string)
    row format delimited fields terminated by ';'
;
insert into  dws partition (dt='2020-08-02')
select * from dws_ls;