Kettle | CDC三大类型相关实验总结

前言

CDC的概念

识别出变化的数据并只导入这部分数据被称为变化数据捕获（Change Data Capture）即CDC

---

基本上CDC可以分为两种，一种是侵入性的，另一种是非侵入性的。侵入性是指CDC操作可能会给源系统带来性能的影响。可以简单的认为，只要CDC操作执行了任何一种SQL语句，就是侵入性的CDC.

基于源数据的CDC[非入侵式]

埋坑

基于触发器的CDC

优缺

• 优点：可以实时监测到数据的所有变化
• 缺点：需要服务协议或者数据库管理员的允许，此外各个数据库创建触发器的语法也不同

触发器的种类

可以大致的分为三类：INSERT、UPDATE、DELETE
也就是我们常说的增删改。

---

OK废话不多说，直接进入实战环节！！！

INSERT触发器

• 实验环境：Win 10、jdk8.0+、Kettle9.0、Navicat、MySQL5.x

• 当INSERT的SQL进行执行时，可以触发数据库自有的触发器，并执行某些动作。

• 实验要求：
  1. 转换的输入输出需求

  student_cdc表的INSERT触发器——student_cdc_insert，会在student_cdc表插入一条数据后，将操作数据记录到cdc_opt_log表中。
  cdc_opt_log表中“处理标志”为未处理，“操作”为I的数据，即为插入操作的增量数据，将这些数据保存到表student_cdc_sync表中。其中，cdc_opt_log表的主要作用是记录对哪些数据进行了哪些操作，同时记录该操作是否被处理， 拉取成功后，需要将cdc_opt_log表中对应数据的“处理标志”更新为已处理。

  2. 转换的设计图

  由于我们进行增量导入，所以我们需要从cdc_opt_log中读取处理标志为“未处理”，“操作”为I的数据。然后将学号作为参数传递给下个步骤。最终，执行此转换时，我们得到了符合查询条件的数据，并通过“表输出”步骤输出到student_cdc_sync表，同时将cdc_opt_log表的“处理标志”更新为已处理。

• 实验步骤

1. 设计SQL语句，代码如下：(后续实验SQL与之一致)

-- set names gbk;
-- 导入实验数据集
DROP TABLE IF EXISTS 'student_cdc';
create table 'student_cdc'(
	'学号' int not null auto_increment primary key,
	'姓名' varchar(10) null,
	'性别' varchar(3) null,
	'班级' varchar(6) null,
	'年龄' int null,
	'成绩' int null,
	'身高' int null,
	'手机' varchar(12) null,
	'插入时间' varchar(20) null,
	'更新时间' varchar(20) null
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
insert into student_cdc values(1,'张一','男','1701',16,78,170,'18946554571','2018-08-06' ,'2018-08-06');
insert into student_cdc values(2,'李二','男','1701',17,80,175,'18946554572','2018-08-06' ,'2018-08-06');
insert into student_cdc values(3,'谢逊','男','1702',18,95,169,'18946554573','2018-08-06' ,'2018-08-06');
insert into student_cdc values(4,'赵玲','女','1702',19,86,180,'18956257895','2018-08-06' ,'2018-08-06');
insert into student_cdc values(5,'张明','男','1704',20,85,185,'18946554575','2018-08-07' ,'2018-08-07');
insert into student_cdc values(6,'张三','女','1704',18,92,169,'18946554576','2018-08-06' ,'2018-08-07');

-- 基于触发器的cdc案例
-- set names gbk;
DROP TABLE IF EXISTS 'cdc_opt_log';
create table 'cdc_opt_log'(
	'id' int  not null auto_increment primary key,
	'学号' int,
	'操作' varchar(10),
	'处理标志' varchar(10) default '未处理'
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- mysql默认遇到分号则立即执行此语句，为了写完整段代码，可以用delimiter重新定义";"作为语句结束：即遇到$$才执行整段语句
-- SELECT * FROM information_schema.'TRIGGERS' ;
-- select * from information_schema.'TRIGGERS' where TRIGGER_NAME='student_cdc_insert';
delimiter $$
DROP trigger IF EXISTS 'student_cdc_insert';
create trigger 'student_cdc_insert' after insert on student_cdc for each row
begin 
	declare opt varchar(20)character set utf8;    
	#--declare定义的为begin...end内有效的局部变量，出了范围无法使用
	declare no int;
	declare count int ;
	set opt = 'I';    
	#--设置会话变量，相当于一个全局变量，但客户端只能更改自己的会话变量，而不能更改其他客户端的会话变量
	set no = new.学号;     
	select count(1) into count from cdc_opt_log where 学号=no;   #--满足学号为no的第一列的有多少行值存入count中
	if count>0 then 
		update cdc_opt_log set 操作=opt,处理标志='未处理' where 学号=no;
	else 
		insert into cdc_opt_log(学号,操作) values(no,opt);
	end if;
end;

delimiter $$

DROP trigger IF EXISTS 'student_cdc_update';
create trigger 'student_cdc_update' after update on student_cdc for each row
begin 
                  declare opt varchar(20)character set utf8;
                  declare no varchar(20) character set utf8;
                  declare count int ;
                  set opt = "U";
                  set no = new.学号;
                  select count(1) into count from cdc_opt_log where 学号=no;
                  if count>0 then 
                     update cdc_opt_log set 操作=opt,处理标志='未处理' where 学号=no;
                  else 
                     insert into cdc_opt_log(学号,操作) values(no,opt);
                  end if;
end;

delimiter $$
DROP trigger IF EXISTS 'student_cdc_delete';
create trigger 'student_cdc_delete' after delete on student_cdc for each row
begin 
                 declare opt varchar(20)character set utf8;
                 declare no varchar(20) character set utf8;
                 declare count int ;
                 set opt = "D";
                 set no = old.学号;     
                 select count(1) into count from cdc_opt_log where 学号=no;
                 if count>0 then 
                    update cdc_opt_log set 操作=opt,处理标志='未处理' where 学号=no;
                 else 
                    insert into cdc_opt_log(学号,操作) values(no,opt);
                 end if;
end;

DROP TABLE IF EXISTS 'student_cdc_sync';
create table 'student_cdc_sync'(
	'id' int not null auto_increment primary key,
	'学号' int not null,
	'姓名' varchar(10) null,
	'性别' varchar(3) null,
	'班级' varchar(10) null,
	'年龄' int null,
	'成绩' int null,
	'身高' int null,
	'手机' varchar(13) null,
	'插入时间' varchar(20) null,
	'更新时间' varchar(20) null,
	'导入时间' varchar(20) null
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- insert操作
-- insert into student_cdc('学号', '姓名', '性别', '班级', '年龄', '成绩', '身高', '手机', '插入时间', '更新时间') values(7, '李四', '男', 1701, 17, 82, 170, '18946554571', '2018-08-06', '2018-08-09');

-- 基于快照的cdc案例
-- 创建快照表1
DROP TABLE IF EXISTS 'student_cdc_snap1';
create table 'student_cdc_snap1' as select * from student_cdc;

-- 插入student_cdc_sync
insert into student_cdc_sync ('学号','姓名','性别','班级','年龄','成绩','身高','手机','插入时间','更新时间' ) select 学号, 姓名, 性别, 班级, 年龄, 成绩, 身高, 手机, 插入时间, 更新时间 from student_cdc_snap1;
-- 用到的插入sql：
insert into student_cdc('姓名', '性别', '班级', '年龄', '成绩', '身高', '手机', '插入时间', '更新时间') values('李四', '男', 1701, 17, 82, 170, '18946554571', '2018-08-06', '2018-08-09');

-- 创建快照表2
DROP TABLE IF EXISTS 'student_cdc_snap2';
create table 'student_cdc_snap2' as select * from student_cdc;

2. 打开Navicat，创库建表
   创建名为student_cdc的MySQL数据库，连接数据库步骤带过。
   
   注意字符集选择utf8，不然可能会出现中文乱码情况。

---

• 在student_cdc数据库中运行步骤1的sql文件，点击开始，执行SQL文件

2.1执行sql语句

INSERT INTO student_cdc 
	(学号, 姓名, 性别, 班级, 年龄, 成绩, 身高, 手机, 插入时间, 更新时间)
VALUES
	( 7, '李四', '男', 1701, 17, 82, 170, '18946554571', '2018-08-06', '2018-08-09' )

3. 打开Kettle
   第一次启动需要耐心等待，Kettle依赖jdk8.0以上的java环境。首先连接好MySQL数据库，这里需要注意的是，如果是初次连接数据库，是需要自行导入jdbc驱动的，实验所用MySQL是5.x版本，对应的jdbc驱动jar包，博主使用的是mysql-connector-java-5.1.48-bin.jar，搜下基本都能找得到，需要传送门链接如下：
   https://pan.baidu.com/s/1xwkVdu69myW2pGL2_iuohg提取码:5jn6

jar包放kettle-pdi-ce-9.0.0.0-423\data-integration\lib目录下，已经启动了kettle的话，需要重新启动确保连接顺利进行。

4. 首先新建转换>>主对象树>>DB连接>>右键新建
   
   点击测试弹出上图窗口即连接成功！
   迫不及待要进行数据处理啦~
5. 接着咱们先查看一下总体的转换设计图
   (kettle是可视化编程，操作友好，上手快)

---

本实验有4个步骤，分别是CDC日志输入、学生表输入、学生表的插入、更新CDC日志表。

---

6. 配置“CDC日志表输入”

• 输入>>双击表输入拉入画布
• 双击表输入配置参数
• 修改步骤名称
• 数据库连接选择预先连接好的student_cdc
• SQL栏输入如下语句

select 学号 from cdc_opt_log
where 操作='I' and 处理标志='未处理'

6.1步骤之间的跳连接：按住Shift点击上一步骤，拉至下一步骤，即可实现跳连接

7. 配置“学生表输入”

• 同上步骤
• SQL语句

SELECT
  学号
, 姓名
, 性别
, 班级
, 年龄
, 成绩
, 身高
, 手机
, 插入时间
, 更新时间
,curdate() as 导入时间
,'已处理' as 处理标志
FROM cdc_data.student_cdc
where 学号=?

• 勾选替换变量[?即为变量]和从步骤插入数据
  

8. 配置“插入学生同步表”

• 输出>>插入/更新
• 双击插入/更新配置参数
• 具体操作流程如下图所示
  
  目标表注意是student_cdc下的student_cdc_sync

---

9. 配置“更新CDC日志表”

• 输出>>插入/更新
• 双击插入/更新配置参数
• 具体步骤如下图所示：
  

---

10. 大功告成，赶紧运行转换吧！！！

• 运行转换！！！
  
• 实验结果：查看Navicat中的cdc_opt_log查看变化
• 实验前：
  
• 实验后：
  

• 总结：
  在执行INSERT的SQL语句后，cdc_opt_log表中会增加相应的学号、操作标识符、处理标志，ETL工具Kettle负责将INSERT操作的数据处理，然后添加到student_cdc_sync表中。

基于快照的CDC

基于日志的CDC

---

持续更新中，如果有帮助到你的话，可以点个赞或者收藏一波~
新人博主，多多指教，错漏之处欢迎评论区讨论~