kettle基于快照的CDC

一、转换的输入输出需求

➢第一步将student_cdc表中的数据复制到student_cdc_sanp1表中，使 student_cdc_sanp1作为student_cdc表的第一个快照，同时将数据输出到 student_cdc_sync表中。

➢第二步对student_cdc中的数据进行插入、更新、删除操作。

➢第三步将student_cdc表中的数据复制到student_cdc_sanp2表中，使 student_cdc_sanp2作为student_cdc表的第二个快照。

➢通过比较student_cdc_sanp1和student_cdc_sanp2表中的数据找出增量数据， 并将增量更新到student_cdc_sync表中

效果图如下：

 表输入

1.连接数据库

点击新建

SELECT 学号, 姓名, 性别, 班级, 年龄, 成绩, 身高, 手机, 
插入时间, 更新时间, CURDATE() AS 导入时间 FROM student_cdc_snap1

表输入 2

SELECT 学号, 姓名, 性别, 班级, 年龄, 成绩, 身高, 手机, 
插入时间, 更新时间, CURDATE() AS 导入时间 FROM student_cdc_snap2

合并记录

数据同步

 运行

运行成功

数据库预期表

数据库脚本

 
CREATE DATABASE `world` 
 
USE `world`;
 
/*Table structure for table `student_cdc` */
 
DROP TABLE IF EXISTS `student_cdc`;
 
CREATE TABLE `student_cdc` (
  `学号` int(11) NOT NULL,
  `姓名` varchar(45) DEFAULT NULL,
  `性别` varchar(45) DEFAULT NULL,
  `班级` varchar(45) DEFAULT NULL,
  `年龄` varchar(45) DEFAULT NULL,
  `成绩` varchar(45) DEFAULT NULL,
  `身高` varchar(45) DEFAULT NULL,
  `手机` varchar(45) DEFAULT NULL,
  `插入时间` date DEFAULT NULL,
  `更新时间` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
/*Data for the table `student_cdc` */
 
insert  into `student_cdc`(`学号`,`姓名`,`性别`,`班级`,`年龄`,`成绩`,`身高`,`手机`,`插入时间`,`更新时间`) values (2,'李二','男','1701','17','80','175','18946554572','2018-08-06','2018-08-06'),(3,'谢逊','男','1702','18','95','169','18946554573','2018-08-06','2018-08-06'),(4,'赵玲','女','1702','19','86','180','18946554575','2018-08-06','2018-08-06'),(5,'张明','男','1704','20','85','185','18946554575','2018-08-07','2018-08-07'),(6,'张三','女','1704','18','82','169','18946554576','2018-08-06','2018-08-07'),(0,'李四','男','1701','17','82','170','18946554571','2023-10-09','2023-10-09');
 
/*Table structure for table `student_cdc_snap1` */
 
DROP TABLE IF EXISTS `student_cdc_snap1`;
 
CREATE TABLE `student_cdc_snap1` (
  `学号` int(11) NOT NULL,
  `姓名` varchar(45) DEFAULT NULL,
  `性别` varchar(45) DEFAULT NULL,
  `班级` varchar(45) DEFAULT NULL,
  `年龄` varchar(45) DEFAULT NULL,
  `成绩` varchar(45) DEFAULT NULL,
  `身高` varchar(45) DEFAULT NULL,
  `手机` varchar(45) DEFAULT NULL,
  `插入时间` date DEFAULT NULL,
  `更新时间` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
/*Data for the table `student_cdc_snap1` */
 
insert  into `student_cdc_snap1`(`学号`,`姓名`,`性别`,`班级`,`年龄`,`成绩`,`身高`,`手机`,`插入时间`,`更新时间`) values (1,'张一','男','1701','16','78','170','18946554571','2018-08-06','2018-08-06'),(2,'李二','男','1701','17','80','175','18946554572','2018-08-06','2018-08-06'),(3,'谢逊','男','1702','18','95','169','18946554573','2018-08-06','2018-08-06'),(4,'赵玲','女','1702','19','86','180','18946554575','2018-08-06','2018-08-06'),(5,'张明','男','1704','20','85','185','18946554575','2018-08-07','2018-08-07'),(6,'张三','女','1704','18','92','169','18946554576','2018-08-06','2018-08-07');
 
/*Table structure for table `student_cdc_snap2` */
 
DROP TABLE IF EXISTS `student_cdc_snap2`;
 
CREATE TABLE `student_cdc_snap2` (
  `学号` int(11) NOT NULL,
  `姓名` varchar(45) DEFAULT NULL,
  `性别` varchar(45) DEFAULT NULL,
  `班级` varchar(45) DEFAULT NULL,
  `年龄` varchar(45) DEFAULT NULL,
  `成绩` varchar(45) DEFAULT NULL,
  `身高` varchar(45) DEFAULT NULL,
  `手机` varchar(45) DEFAULT NULL,
  `插入时间` date DEFAULT NULL,
  `更新时间` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
/*Data for the table `student_cdc_snap2` */
 
insert  into `student_cdc_snap2`(`学号`,`姓名`,`性别`,`班级`,`年龄`,`成绩`,`身高`,`手机`,`插入时间`,`更新时间`) values (1,'张一','男','1701','16','78','170','18946554571','2018-08-06','2018-08-06'),(2,'李二','男','1701','17','80','175','18946554572','2018-08-06','2018-08-06'),(3,'谢逊','男','1702','18','95','169','18946554573','2018-08-06','2018-08-06'),(4,'赵玲','女','1702','19','86','180','18946554575','2018-08-06','2018-08-06'),(5,'张明','男','1704','20','85','185','18946554575','2018-08-07','2018-08-07'),(6,'张三','女','1704','18','82','169','18946554576','2018-08-06','2018-08-07'),(0,'李四','男','1701','17','82','170','18946554571','2023-10-09','2023-10-09');
 
/*Table structure for table `student_cdc_sync` */
 
DROP TABLE IF EXISTS `student_cdc_sync`;
 
CREATE TABLE `student_cdc_sync` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `学号` int(11) NOT NULL,
  `姓名` varchar(45) DEFAULT NULL,
  `性别` varchar(45) DEFAULT NULL,
  `班级` varchar(45) DEFAULT NULL,
  `年龄` varchar(45) DEFAULT NULL,
  `成绩` varchar(45) DEFAULT NULL,
  `身高` varchar(45) DEFAULT NULL,
  `手机` varchar(45) DEFAULT NULL,
  `插入时间` date DEFAULT NULL,
  `更新时间` date DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
 
/*Data for the table `student_cdc_sync` */
 
insert  into `student_cdc_sync`(`ID`,`学号`,`姓名`,`性别`,`班级`,`年龄`,`成绩`,`身高`,`手机`,`插入时间`,`更新时间`) values (1,1,'张一','男','1701','16','78','170','18946554571','2018-08-06','2018-08-06'),(2,2,'李二','男','1701','17','80','175','18946554572','2018-08-06','2018-08-06'),(3,3,'谢逊','男','1702','18','95','169','18946554573','2018-08-06','2018-08-06'),(4,4,'赵玲','女','1702','19','86','180','18946554575','2018-08-06','2018-08-06'),(5,5,'张明','男','1704','20','85','185','18946554575','2018-08-07','2018-08-07'),(6,6,'张三','女','1704','18','92','169','18946554576','2018-08-06','2018-08-07');
 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;