离线数仓中的拉链表
拉链表
什么是拉链表？
拉链表，记录每条信息的生命周期，一旦一条记录的生命周期结束，就重新开始一条新的记录，并把当前日期放入生效的开始日期。（就是在原来表的基础上，加上创建时间/开始时间/操作时间/结束时间，能保证看出记录的变化过程就行了）
如果当前消息至今有效，在生效结束日期中填入一个极大值（如：9999-12-31）
为什么要做拉链表
拉链表适合于：数据会发生变化，但是变化频率不高的维度，常作为增量同步表
比如：用户信息会发生变化，但是每天变化的比例不高。如果数据量有一定规模，按照每日全量的方式保存效率很低。比如：一亿用户*365天，每天一份用户信息（做每日全量效率低）
每日全量表：
| 用户ID | 姓名 | 手机号码 | dt |
|---|---|---|---|
| 1 | 张三 | 136****9999 | 2023-01-25 |
| 1 | 张三 | 136****9999 | 2023-01-26 |
| 1 | 张三 | 136****9999 | 2023-01-27 |
| ... | ... | ... | ... |
| 1 | 张三 | 136****9999 | 2023-01-30 |
| 1 | 张三 | 157****1111 | 2023-01-31 |
拉链表：
| 用户ID | 姓名 | 手机号码 | 开始日期 | 结束日期 |
|---|---|---|---|---|
| 1 | 张三 | 136****9999 | 2023-01-25 | 2023-01-30 |
| 1 | 张三 | 157****1111 | 2023-01-31 | 9999-12-31 |
通过上面，可以看出，拉链表大大降低的存储内容，也使数据变化过程变得更加明了
如何使用拉链表
以用户维度表为例
在数仓项目当中，拉链表常常采用增量同步策略，增量同步策略主要有两种方式，一种使用过binlog，另一种是通过查询（当然，首日直接全量同步就行了）
通过binlog的方式实现增量同步
采用MaxWell同步工具的话，因为其是支持断点续传的，会把1天中的中间状态也记录到，所以还要考虑过滤数据的因数
第一步：过滤数据的中间状态，得出一天中最终的状态
- 获取ods层最新数据，然后通过
row_number()函数按id分组、时间降序，取行号为1的数据即可过滤出一天中最终的状态，最后再查询一遍，赋予开始时间（当前时间）和结束时间（最大时间）-- 新表
第二步：昨日用户维度表数据（分区为9999-12-31）与新表数据进行关联
- 查找该拉链表的最新分区数据（往往找最大时间分区的数据就行了）通过
full outer join与第一步得出的表按id相等条件进行关联，得到了昨天与今天的全部数据。
第三步：判断
- 开启动态分区，通过
if或者nvl函数，判断新表id是否为空，如果为空就为旧表id对应的数据，否则返回自己id对应的数据。-- 得出新增数据 union all再查询一次，筛选出新表id和旧表id不为null的数据，修改结束时间为当前时间减一。 -- 得出历史数据
案例：
with
tmp as
(
select
old.id old_id,
...
old.create_time old_create_time,
old.operate_time old_operate_time,
old.start_date old_start_date,
old.end_date old_end_date,
new.id new_id,
...
new.create_time new_create_time,
new.operate_time new_operate_time,
new.start_date new_start_date,
new.end_date new_end_date
from
(
select
id,
...
create_time,
operate_time,
start_date,
end_date
from dim_user_zip
where dt='9999-12-31'
)old
full outer join
(
select
id,
...
create_time,
operate_time,
'2023-01-25' start_date,
'9999-12-31' end_date
from
(
select
data.id, -- ods层的数据存储采用结构体，根据自己情况改变
...
data.create_time,
data.operate_time,
row_number() over (partition by data.id order by ts desc) rn
from ods_user_info_inc
where dt='2023-01-25'
)t1
where rn=1
)new
on old.id=new.id
)
insert overwrite table dim_user_zip partition(dt)
select
if(new_id is not null,new_id,old_id),
...
if(new_id is not null,new_create_time,old_create_time),
if(new_id is not null,new_operate_time,old_operate_time),
if(new_id is not null,new_start_date,old_start_date),
if(new_id is not null,new_end_date,old_end_date),
if(new_id is not null,new_end_date,old_end_date) dt
from tmp
union all
select
old_id,
...
old_create_time,
old_operate_time,
old_start_date,
cast(date_add('2023-01-25',-1) as string) old_end_date,
cast(date_add('2023-01-25',-1) as string) dt
from tmp
where old_id is not null
and new_id is not null;
例2：
-- 假设T-1的日期为2024-07-25
insert overwrite table dim_user_zip partition (dt)
select
`id`,
`name`,
`phone_num`,
`email`,
`user_level`,
`birthday`,
`gender`,
`create_time`,
`operate_time`,
`start_date`,
if (rn == 1, '9999-12-31', date_sub('2024-07-25',1)) as end_date,
if (rn == 1, '9999-12-31', date_sub('2024-07-25',1)) -- 用于自动分区
from (
select
`id`,
`name`,
`phone_num`,
`email`,
`user_level`,
`birthday`,
`gender`,
`create_time`,
`operate_time`,
`start_date`,
`end_date`,
row_number() over(partition by id order by start_date desc) as rn
from (
select
`id`,
`name`,
`phone_num`,
`email`,
`user_level`,
`birthday`,
`gender`,
`create_time`,
`operate_time`,
`start_date`,
`end_date`
from dim_user_zip
where dt = '2024-07-25'
union
select
`id`,
`name`,
`phone_num`,
`email`,
`user_level`,
`birthday`,
`gender`,
`create_time`,
`operate_time`,
'2024-07-25' as `start_date`,
'9999-12-31' as `end_date`
from ods_user_info_inc
where dt= '2024-07-25'
and type in ('insert','update') -- type为ods层maxwell对业务系统监听到操作的类型
) t
)t1
通过查询的方式实现增量同步
这与binlog类似，只是少了过滤数据的步骤
