业务系统数据定期归档

1.1 应用场景

针对上述问题，本文提供解决方案。

1.2 实现思路

本方案实现业务数据库的读写分离。下面序号为实现步骤的先后顺序：

1.3 任务展示

FineDataLink 中的数据处理过程，详情参见：https://demo.finedatalink.com/ 

1）管道任务：

2）定时任务：「数据归档1-」。

2.1 场景模拟

业务数据库 demo1 中存放着「客户订单」表、「库存」表，这两张表每天都会接收大量新增数据、部分更新数据，为保证业务数据库的性能，希望将这两张表信息备份到归档库 demotest2 中。同时，想避免数据分析工具对业务数据库的读取压力（数据分析工具对近三年数据进行分析）。

具体表信息如下表所示：

2.2 方案说明

注：中间库的存在原因请参见本文 1.2 节内容。

2.3 业务数据库实时同步到中间库

2.3.1 准备工作

数据管道详细介绍请参见：配置数据管道任务

1）数据管道支持的数据源详情参见：数据管道支持的数据源

2）前提条件请参见：使用数据管道的前提条件

3）配置传输队列，其实就是配置暂存来源库数据，方便目标库写入数据的「数据管道」，目前是通过 Kafka 实现的。

详情参见：配置传输队列

2.3.2 选择数据源

首先选择需要数据同步的来源数据。

点击「数据管道>新建任务」，进入任务设置界面，选择来源数据库以及需要进行数据同步的数据表，默认读取方式为 Binlog，默认先对所有存量数据同步，然后持续同步新增变化。

将已经存在的「客户订单」表、「库存」表选到「要同步的表」中。

如下图所示：

2.3.3 选择数据去向

由于数据分析工具从「中间库」中读取数据，为保证「中间库」的性能，「中间库」与「业务数据库」一样，只保留最近三年的数据，所以执行物理删除；并且在目标表追加时间戳字段，记录数据新增或更新的时间，便于筛选每天变化的数据同步到归档库中。

点击「下一步」进入「选择去向」界面，选定目标数据库。勾选「目标端执行物理删除」，开启「同步时标记时间戳」、「同步源表结构变化」按钮。如下图所示：

2.3.4 设置表字段映射

1）点击「下一步」进入「字段映射」设置界面。

2）首先设置来源表「客户订单」的同步，去向设置为自动建表，命名目标表为「客户订单副本」，映射方式选择「同名映射」，目标表中设置「订单ID」字段为主键。如下图所示：

之后设置来源表「库存」的同步，去向设置为自动建表，命名目标表为「库存副本」中，映射方式选择「同名映射」，目标表中设置「ID」字段为主键。如下图所示：

在此界面下划，可看到管道同步数据时，系统将在目标表中新建字段_fdl_update_timestamp（毫秒级时间戳的形式记录数据在数据库中实际新增和更新的时间），同步时将所有数据传入时间戳。如下图所示：

2.3.5 设置管道控制

1）点击「下一步」进行数据管道的任务设置。

2）数据同步允许一定的容错，比如字段类型、长度不匹配、主键冲突等等问题，可以设置产生的脏数据上限，达到上限则自动终止管道任务，同时设置当任务异常时的通知。如下图所示：

2.3.6 效果查看

点击「保存并启动」按钮，执行管道任务。任务执行成功后，业务数据库表信息已经同步到中间库中。如下图所示：

2.4 中间库数据同步到归档库

1）新建定时任务，新增「数据同步」节点，重命名，筛选出中间库 「库存副本」表前一天的数据 。如下图所示：

SELECT * FROM demotest.库存副本
WHERE FROM_UNIXTIME(_fdl_update_timestamp/1000,'%Y-%m-%d') = date_sub(curdate(),interval 1 day)

2）点击「数据去向与映射」将筛选出的数据同步到归档库 demotest2 的 stock 表中，取消  _fdl_update_timestamp 字段映射，字段  _fdl_update_timestamp 不同步到目标表中。如下图所示：

3）点击「写入方式」，写入方式选择「直接将数据写入目标表」，设置主键映射为  ID 字段，当主键冲突时，覆盖目标表数据，实现数据的更新。如下图所示：

2.5 删除业务库三年前数据

增加一个「SQL脚本」节点，与「数据同步」节点相连，删除业务数据库中「库存」表三年前的数据。如下图所示：

DELETE FROM demo1.库存 WHERE 日期 < DATE_SUB(NOW(), INTERVAL 3 YEAR)

后续步骤：

「客户订单」表与 「库存」表执行的步骤相同。

1）筛选出中间库 「客户订单副本」 表前一天的数据。如下图所示：

SELECT * FROM demotest.客户订单副本
WHERE FROM_UNIXTIME(_fdl_update_timestamp/1000,'%Y-%m-%d') = date_sub(curdate(),interval 1 day)

2）点击「数据去向与映射」，设置数据同步到归档库 demotest2 的 order_information 表中。 取消  _fdl_update_timestamp 字段映射，字段  _fdl_update_timestamp 不同步到目标表中。如下图所示：

3）点击「写入方式」，写入方式选择「直接将数据写入目标表」，设置主键映射为  ID 字段，当主键冲突时，覆盖目标表数据，实现数据的更新。如下图所示：

4）增加一个「SQL脚本」节点，与前一个「数据同步」节点相连，删除业务数据库中「客户订单 」表三年前的数据。如下图所示：

DELETE FROM demo1.客户订单 WHERE 订购日期 < DATE_SUB(NOW(), INTERVAL 3 YEAR)

2.6 设置定时调度

1）保存后发布定时任务到生产模式，点击「调度计划>定时调度>添加单个调度」，设置任务每天 0 点执行。如下图所示：

2）点击右上角「运行」按钮。

定时任务执行后：

1）中间库的前一天数据同步到归档库中。如下图所示：

2）业务数据库中的「客户订单」表、「库存」表已删除三年前数据。如下图所示：

「库存」表删除前后最小日期对比：

「客户订单信息」表删除前后最小日期对比：

3）业务数据库删除数据后，中间库 demotest 中，「库存副本」和「客户订单副本」表中，也已删除三年前数据，效果与业务数据库相同，此处不再赘述。