1.什么是SCD(Type 2 Slowly changing dimension)?SCD用来解决什么问题?
类型二缓慢变化维度(拉链表):维护历史状态,以及最新状态数据的一种表,拉链表根据拉链粒度的不同,实际上相当于快照,只不过做了优化,去除了一部分不变的记录,通过拉链表可以很方便的还原出拉链时间点的客户记录。
在数据仓库的数据模型设计过程中,经常会遇到这样的需求:
- 数据量比较大;
- 表中的部分字段会被update,如用户的地址,产品的描述信息,订单的状态等等;
- 需要查看某一个时间点或者时间段的历史快照信息,比如,查看某一个订单在历史某一个时间点的状态,
比如,查看某一个用户在过去某一段时间内,更新过几次等等; - 变化的比例和频率不是很大,比如,总共有1000万的会员,每天新增和发生变化的有10万左右;
- 如果对这边表每天都保留一份全量,那么每次全量中会保存很多不变的信息,对存储是极大的浪费;
举个简单例子,比如有一张订单表,6月20号有3条记录:
到6月21日,表中有5条记录:
到6月22日,表中有6条记录:
数据仓库中对该表的保留方法:
1. 只保留一份全量,则数据和6月22日的记录一样,如果需要查看6月21日订单001的状态,则无法满足;
2. 每天都保留一份全量,则数据仓库中的该表共有14条记录,但好多记录都是重复保存,没有任务变化,如订单002,004,数据量大了,会造成很大的存储浪费;
如果在数据仓库中设计成历史拉链表保存该表,则会有下面这样一张表:
说明:
1. dw_begin_date表示该条记录的生命周期开始时间,dw_end_date表示该条记录的生命周期结束时间;
2. dw_end_date = '9999-12-31'表示该条记录目前处于有效状态;
3. 如果查询当前所有有效的记录,则select * from order_his where dw_end_date = '9999-12-31'
4. 如果查询2012-06-21的历史快照,则select * from order_his where dw_begin_date <= '2012-06-21' and end_date >= '2012-06-21',这条语句会查询到以下记录:
和源表在6月21日的记录完全一致:
可以看出,这样的历史拉链表,既能满足对历史数据的需求,又能很大程度的节省存储资源;
2.数据开发平台SCD节点定义
数据开发平台SCD节点目前支持【初始化拉链表】和【拉链表增量更新】两类操作。
配置项:
1)选择input,input包含1个或多个分组字段,0或1个日期字段以及一个或多个状态字段
- 选择分组字段:可指定1个或多个分组字段,该字段必须在input表与目标表内均存在。
- 选择日期字段:指定一个日期字段或选择无时间戳
- 选择状态字段:指定需要更新的状态字段
- 添加自定义列(可选):选择需要添加的自定义列及输入值(支持参数)
2)选择目标表,选定一个数据库表,该目标表需要有跟input表一致的分组字段与状态字段,以及状态的起止日期
- 选择分组字段:可指定1个或多个分组字段,该字段必须在input表与目标表内均存在。
- 选择起始日期字段:指定一个日期字段为起始日期
- 选择结束日期字段:指定一个日期字段为结束日期
- 选择状态字段:指定需要更新的状态字段
3)增量更新支持选择更新关注时间为前一天还是当天
2.数据开发平台是如何实现SCD节点的初始化和增量更新
目前SCD节点是以【日】级别的粒度对输入数据进行的初始化和增量更新操作。
(1)初始化逻辑
-
- 先清空目标表
- 获取输入数据,根据输入创建对应的临时表,将输入数据写入到临时表中
-
- 筛选临时表中的数据,将其中日期字段的值大于运行时间的数据清理掉
- 根据分组字段和时间字段将数据进行开关链操作,对于同一天出现的多条数据,根据时间戳排序后合并成一条数据再参与运算,最后将处理后的数据插入到目标表中
(2)增量更新逻辑
-
- 获取输入数据,将输入数据执行一遍初始化逻辑的操作,输出一个初始化完成的SCD表,记【输出数据A】
- 检查目标表中是否有更新关注时间的增量更新数据,如果存在,则说明有对更新关注时间的数据做过增量更新操作,为了避免产生冗余数据和脏数据,需要先将上次操作的数据进行回滚
-
- 从【输出数据A】中去除非更新关注时间的数据,再对比目标表,去除没有发生变化的数据,仅保留更新关注时间下的变化数据,记【输出数据B】
- 将目标表中分组字段存在于【输出数据B】中的数据进行关链,再将【输出数据B】中的数据追加到目标表中,完成整个增量更新的操作
3.数据开发平台SCD节点的功能增强
(1)初始化/增量更新支持无时间戳的源数据
使用运行时间当日/运行时间前一日作为起始时间,由于源数据不包含时间戳字段,所以一组分组字段下不能存在两条及以上的数据,否则程序无法判断两条数据的时间关系,会随机取一条数据作为运行时间当日/运行时间前一日的最终数据,就有可能导致数据的不准确性。
(2)初始化/增量更新支持添加自定义列
自定义列支持用户设置对SCD表的其他业务字段,比如审计字段等,使SCD节点更贴近业务需求。
源表格式:
ID | VALUE | OPERATE_DATE |
1 | A | 2021-12-01 |
1 | B | 2022-01-08 |
2 | C | 2022-02-01 |
3 | D | 2022-02-02 |
最终处理成的拉链表的格式:
ID | VALUE | START_DATE | END_DATE |
1 | A | 2021-12-01 | 2022-01-07 |
1 | B | 2022-01-07 | 9999-12-31 |
2 | C | 2022-02-01 | 9999-12-31 |
3 | D | 2022-02-02 | 9999-12-31 |