1:etl介绍
ETL 定义
etl 是数据抽取(extract),转换(transform),清洗(cleaning),装在(load)这个动作是在数据迁移的时候发生的,数据从源数据库中传输到数据仓库中的过程时候的操作
2: 步骤
数据抽取
- 确定所有数据源
确定数据是从哪里来的!明确业务系统中的数据源是个什么类型的数据库!这个数据库是干什么的! - 定义接口
确定数据的编码格式(UTF-8)
数据的分隔符 (尽量选的分隔符不是传输数据的)
接口类型(规定文件是怎么格式的,是以日志文件还是直接从数据库中抽取) - 数据抽取方法
是主动抽取还是系统提供文件。
增量抽取还是全量抽取
数据抽取的标示
是每天抽取呢?还是每个月抽取一次呢?
数据清洗与转换
- 数据清洗
这个阶段主要就是做数据的过滤 因为数据源的数据是多种多样的,各种表,肯定有的数据是不正确,或者不需要的。比如有的表中的字段是标示位。如下数据是可以过滤的:
a. 不完整的数据
b. 错误的数据(有的字段的意思是错误的)
c. 重复的数据 - 数据转化
a. 代码标准值(统一字段的意思,比如性别,在有的数据库中是用1和0,有的数据库是m和w表示的,这都没有一个统一的标准值,这个阶段我们要统一)
b. 数据粒度的转换
c. 根据业务规则来转化
数据加载
将清洗和转换之后的数据,加载到数据仓库中(根据数据仓库的不同选择合适的)
3:拉链表
拉链表就是记录一个事物的变化轨迹,从开始一直在现在
插一句,存储历史的方式有两种
1: 快照存储 (就保存当时的状态就好了。)
2:拉链存储(一般是这个好)
一般拉链都有
VT_NEW (用来存放源系统中传过来的数据,将表的格式变为和数据仓库中表的结构一样)
VT_INC(用VT_NEW 和数据仓库的源表来对比,找出增量数据);
甭管什么表,肯定有两个字段,一个是开始时间,一个是结束时间
常规拉链算法
这个算法是用于没有删除操作的表
从源数据库中拿到数据(全量或者增量都可以)将这部分数据放到VT_NEW表中,最后将对比的增量放到VT_INC中(这里对比直接用left join 通过主键 VT_NEW是主动表,数据仓库中的表为被动表),然后将数据仓库中的那个表的结束时间修改为现在的日子,结束时间改为一个9999-12-31,就好了。
追加算法
这个是最简单的,直接追加在最后就好了
Upsert算法
这个算法是update,insert组合到一块的。
针对的是不删除的表,
还是和之前的一样的。VT_NEW 和 数据仓库中的target做left join 之后,找出增量的数据放到VT_INC中,之后用VT_INC和目标表做merge into 操作 (这个merge into 就是两张表,要是匹配条件满足就是跟新,不匹配就删除)
全删全加算法
这个也简单,就是把历史数据truncate掉,重新加载
全量带删除算法
适合有删除的表
要求是全量的数据
在之前的常规算法之上,新加一个字段 表示删除,要是对比之后VT_INC中没有,那就在target表中的删除字段中标记
性能
处理复杂度 从高到低
历史拉链 upsert 追加 全删全加
性能,低高到高
历史拉链 upsert 追加 全删全加
图片化的对比
历史快照 和 拉链表对比
历史快照
拉链表
很明显的差别,拉链表小,占用的空间小 但这个不是绝对的,还要和自己的业务去相关联