作者:Mochou
来源: 恒生LIGHT云社区
大数据云时代,数据上云ETL已成了最基础,最根本,也是最必须的一个步骤。目前数据传输迁移的工具非常多,比如DataX,DTS,Kettle等等。为了保证云上存储空间的有效利用和数据的整体唯一性,就没必要每天都上一份全量,故几乎所有数据上云的策略都是全量加增量的模式:即第一次上一份初始化全量,后续每天只上增量,这样前一天的全量加上今天的增量就是今天的全量。
T+1(全) = T (全) + T+1(增)
既然是要每天上增量,那么如何获取增量数据便成了一个问题所在,笔者依据书籍资料加上公司的实践经验简单提一下四种获取增量数据的方法
以某关系型数据库表T1举例,其建表DDL如下
CREATE TABLE T1 (
ID INT,
P_DATE TIMESTAMP,
P_NAME VARCHAR ( 10 )
)
基于日志文件读取增量数据
这种方式可以通过读取数据库的归档日志,比如MySQL的binlog等得到增量数据,然后在目标库或者文档服务器里进行操作
该场景的优势明显,目前适用于绝大多数的实时数据传输场景,常见的日志解析工具有OGG以及阿里开源工具canal等,这类工具可把日志数据解析为可识别的json数据并通过Kafka,MQ等实时消费到目标库
优点:可以做到数据无误差的实时传输,有回滚机制,有容灾备份的能力
缺点:开归档会对源端数据库的磁盘造成压力,增加储存成本,此外大多数数据库的日志都是不对外开放的,只针对数据库本身的工具开放读取,例如ORACLE的OGG
基于全量对比得出增量数据
增量数据即DML操作,无非I(INSERT),D(DELETE),U(UPDATE)三种,故可以拿昨天的全量数据与今天的全量数据进行对比得出昨天的增量,具体方法是通过关联表的主键,唯一键用left join,right join,inner join 等对比出增量数据,参考脚本如下
-- 对比出两个时间区间所有的不等数据,得出增量(部分需去重)
SELECT A1.* FROM
(SELECT * FROM T1 WHERE P_DATE <= '2021-07-07')A1 LEFT JOIN
(SELECT * FROM T1 WHERE P_DATE <= '2021-07-06')B1 ON A1.ID = B1.ID
AND A1.P_DATE = B1.P_DATE AND A1.P_NAME = B1.P_NAME WHERE B1.ID IS NULL
UNION ALL
SELECT B1.* FROM
(SELECT * FROM T1 WHERE P_DATE <= '2021-07-07')A1 RIGHT JOIN
(SELECT * FROM T1 WHERE P_DATE <= '2021-07-06')B1 ON A1.ID = B1.ID
AND A1.P_DATE = B1.P_DATE AND A1.P_NAME = B1.P_NAME WHERE A1.ID IS NULL
优点:因为是云上merge对比,所以对源库无影响
缺点:这个操作仅仅适合表有主键,唯一键或者数据量较小的表,不然海量数据中每条数据的每一列都进行逐一比对,很显然这种频繁的I/O操作以及复杂的比对运算会造成很大的性能开销。
基于时间字段切分增量数据
此方法依据表的某一时间字段,在etl工具里进行条件设定即可快速抽取增量数据
比如我们要抽取2021-07-06 13:00:00 到 2021-07-06 16:00:00,则可限定
SELECT * FROM T1
WHERE
P_DATE BETWEEN '2021-07-06 13:00:00'
AND '2021-07-06 16:00:00'
优点:数据处理逻辑清楚,速度较快,成本低廉,流程简单
缺点:此方法要求表的时间字段必须是随表变动而变动的不为空数据,此外由于是直接读取表数据,该方法无法获取删除类型的数据。
基于建触发器生成增量数据
触发器的概念我们都知道,故我们可以建立IDU三种操作类型的触发器,并由触发器将变更的数据写到库里的临时表里,然后用ETL工具直接抽取这张临时表即可进行增量上云
CREATE
OR REPLACE TRIGGER tri_t1 BEFORE INSERT
OR UPDATE
OR DELETE ON t1 BEGIN
IF
USER <> 'ADMIN' THEN
raise_application_error (- 20001, 'You don''t have access to modify this table.' );
END IF;
END;
优点:是数据库本身的触发器机制,契合度高,可靠性高,不会存在有增量数据未被捕获到的现象
缺点:对于源端有较大的影响,需要建立触发器机制,增加运维人员,还要建立临时表,储存临时表,增加储存成本和运维成本
课后总结
这四种增量获取的方法应用都比较宽泛,各有各的优势和缺点
一般来讲
- 对于大型数据库,数据变更频率快,表数量多,对数据传输要求有备份,安全,零差数据的采用基于数据库日志的方法
- 对于小型数据库,且未开归档,但数据变更频率快的采用基于全量对比的方法
- 对于含有标准时间戳字段,且应用环境适合,表数量较少的采用基于时间字段的方法
- 至于触发器,由于需要源端运维成本较大,且对源端存储有压力(既然都是对存储有压力为何不用OGG),故很少有客户选择这一种