Kettle + SQLServer 触发器实现 CDC 方案

一 说明

1.1 文档说明

​ 本文档提供一种变化数据的捕获机制，也即是说这种机制能 够保证在数据源数据有变化的时候能够区分出这些变化的新数据和旧数据，增量的抽取这 些变化了的数据，而无需为了保证数据的同步每次都做全量的抽取。

​ 变更数据捕获（Change Data Capture，即CDC）

1.2 方案适用性

​ 方案对所有有触发器机制的数据库都适用，除了创建触发器及中间表的逻辑因 数据库的不同而不同以外，主干抽取逻辑都一致，代码的可复用程度高，数据抽取的性能较高

1.3 实现环境、组件、功能

Kettle 8.2 ：JOB、转换、表输入、获取系统时间、复制记录到结果

SQL Server 2019 ：触发器 IDU、源表、CDC中间表、调度表、

1.4 触发器 CDC 方案说明

CDC 方案提供一种基于触发器的数据增量抽取方法，该方法将业务系统中的变化数据按一定的频率准确地捕获到，同时不能对 业务系统造成太大的压力，影响现有业务。同时，用户可以通过定制计划任务的方式，实现按一定的周期从源系统中抽取当前周期内产生的增量数据，或者可以通过采用系统提供的 实时检测变化数据的方式，实现实时数据同步的能力

1.5 触发器说明

SqlServer 中 DML 触发器有三种：

1. insert触发器：向表中插入数据时被触发；
2. update触发器：修改表中数据时被触发 ；
3. delete触发器：从表中删除数据时被触发

1.6 实现步骤

1. 确定数据来源表作为抽取表

2. 定义暂存抽取表中数据变化的中间表

3. 定义针对抽取表数据发生变化规则的触发器

4. 探测抽取表是否有数据变化，建立的触发器将变化数据捕获到中间表中

5. 启动数据交换任务的执行，增量抽取进程根据中间表记载的变化数据，从 抽取表中提取对应的完整记录，对目标表进行相应的处理

6. 执行完任务，将一个游标存放调度表中最新更新时间，确定每次抽取的起始游标

7. 定时删除中间表已处理过的临时数据

优化建议：

​ 1） 步骤 5 所述的启动数据交换任务的执行可通过手工启动方式、定时调 度方式及实时监控数据变化并启动的方式进行

​ 2） 中间表存储抽取表名称、更新的关键字值和更新操作类型

​ 3） 中间表关键字段需要建立索引

二 架构图

2.1 调度流程

2.2 触发器

三 环境安装

3.1 Kettle 安装

下载 ZIP 包

http://mirror.bit.edu.cn/pentaho/Pentaho%208.2/client-tools/

解压缩

点击 Spoon.bat 启动

安装成功

3.2 SQL Server 2019 安装

参考 https://blog.csdn.net/CHQC388/article/details/104550963/

四 代码实现

第一步 创建源表、CDC 中间表、触发器表、调度表、测试 CDC 功能

1. 创建库

   数据库右键 创建数据库
   
   输入数据库名称
   

2. 创建源表 — 被监听的数据表

   create table test_str(
   id varchar(200) not null
   )
   

3. CDC 中间表 ---- 存储监听到的数据 默认系统时间

   CREATE TABLE cdc_test_str(
   	id varchar(200) NOT NULL,
   	operation varchar(200) NULL,
   	operation_time datetime NOT NULL
   ) ON [PRIMARY]
   GO
   ALTER TABLE [dbo].[cdc_test_str] ADD  CONSTRAINT [DF_cdc_test_str_operation_time]  DEFAULT (getdate()) FOR [operation_time]
   GO 
   

4. 触发器创建 (IDU)

   Insert 触发器

   USE [TestDB]
   GO
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   
   ALTER TRIGGER [dbo].[CDC_Insert_test_str]   -- 触发器名称
      ON  [dbo].[test_str]  --  表名
      AFTER INSERT          -- 触发时间点
   AS 
   BEGIN
   	-- SET NOCOUNT ON added to prevent extra result sets from
   	-- interfering with SELECT statements.
   	SET NOCOUNT ON;
   
       -- Insert statements for trigger here
       INSERT INTO cdc_test_str(
           id,
   		operation
       )
       SELECT
           i.id,
   		'I'
       FROM
   	inserted i
   
   END
   

   Delete 触发器

   USE [TestDB]
   GO
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   ALTER TRIGGER [dbo].[CDC_Delete_test_str]   -- 触发器名称
      ON  [dbo].[test_str]  --  表名
      AFTER DELETE          -- 触发时间点
   AS 
   BEGIN
   	-- SET NOCOUNT ON added to prevent extra result sets from
   	-- interfering with SELECT statements.
   	SET NOCOUNT ON;
   
       -- Insert statements for trigger here
       INSERT INTO cdc_test_str(
           id,
   		operation
       )
       SELECT
           i.id,
   		'D'
       FROM
   	deleted i
   
   END
   

   Update 触发器

   USE [TestDB]
   GO
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   ALTER TRIGGER [dbo].[CDC_Update_test_str]   -- 触发器名称
      ON  [dbo].[test_str]  --  表名
      AFTER UPDATE          -- 触发时间点
   AS 
   BEGIN
   	-- SET NOCOUNT ON added to prevent extra result sets from
   	-- interfering with SELECT statements.
   	SET NOCOUNT ON;
   
       -- Insert statements for trigger here
       INSERT INTO cdc_test_str(
           id,
   		operation
       )
       SELECT
           i.id,
   		'U'
       FROM
   	inserted i
   
   END
   

5. 调度表

   -- 为防止不同库含同表名 所以建议每个库单独一个调度表
   -- table_name 库中的表名 唯一 该表上次同步时间   
   -- start_time 调度表上次同步时间 （同步失败不保存开始时间、即 错过调度 ， 假设原抽取 0 - 5 分钟数据 ，但异常数据未进入。 则下次 0 - 10 分钟数据 ）
   CREATE TABLE testDB_schedule(
   	table_name varchar(200) NOT NULL,
   	start_time varchar(200) NULL
   ) ON [PRIMARY]
   GO
   

6. 相关测试 sql

   -- 源表
   select * from test_str
   -- 触发器 临时表
   select * from cdc_test_str
   -- 插入测试
   insert into test_str values('tigger_ddddd');
   -- 修改测试
   update test_str set id = 'changed_ifff' where id = 'tigger_ddddd'
   -- 删除测试
   delete from test_str where id = 'changed_ifff'
   -- 清空临时表
   delete from CDC_test_str where 1= 1
   

经过测试应该发现增删改 test_str 已实时同步插入至 触发器临时表 cdc_test_str

第二步 Kettle Job 实现调度

1. 打开 kettle

   创建 Job [文件] --> [新建] --> [作业] -->

2. 拉取 开始按钮

3. 拉取转换
   

4. 双击转换设置
   

5.再次拉取转换

6. 拉取成功。 并用 shift 依次连接 start --> 转换 --> 转换 --> 成功

【CTRL】 + 【S 】保存 本文文件名 CDC_SQLServer

第三步 设置《生成当前时间作为结束时间》

​ 为防止不同数据库实现不同，所以采用获取 系统时间作为触发器的结尾时间

1. 文件 新建 转换
   

2. 选择核心对象-- 获取系统信息 拖拽至面板，双击输入内容
   

3. 为查看时间是否成功获得，增加一步 《拉取复制记录到结果》 然后连接
   

4. 启动测试

5. 查看测试日志 至此《生成当前时间作为结束时间》 结束
   

【CTRL】 + 【S 】保存 本文文件名为 sysdate

第四步 设置《获取结束时间，执行 sql 查询区间数据》

1. 拉取表输入两次

2. 设置第一张表查询调度表，得出 需要执行同步的表名和上次同步时间

3. 第一次使用 没有数据库连接 需要新建

如果没有账号密码 需要参考

SQLServer 2019 安装 https://blog.csdn.net/CHQC388/article/details/104550963/

SQLServer 账号密码 https://blog.csdn.net/CHQC388/article/details/104565370

如果出现 缺少 jar 包可以从我的资源处下载：
https://download.csdn.net/download/cs261244787/13124532

4. 预览数据 

5. 设置第二张表提取上一步记录并拼装 SQL 查询 CDC 数据

【CTRL】 + 【S 】保存 本文文件名为 CDC

第五步 配置 job 任务

1.  点击至 job 页面

2. 配置两个转换 

第一个选择保存的 sysdate.ktr
第二个选择保存的 CDC.ktr

3. 启动并查看日志

至此已完成 SQL Server 动态变更捕获、 Kettle 拉取数据。 后续可推送 kafka / hbase / hive 等