kettle snapshot-based CDC

1. Input and output requirements for conversion

?The first step is to copy the data in the student_cdc table to the student_cdc_sanp1 table, making student_cdc_sanp1 the first snapshot of the student_cdc table, and at the same time output the data to the student_cdc_sync table.

?The second step is to insert, update, and delete the data in student_cdc.

?The third step is to copy the data in the student_cdc table to the student_cdc_sanp2 table, so that student_cdc_sanp2 serves as the second snapshot of the student_cdc table.

?Find the incremental data by comparing the data in the student_cdc_sanp1 and student_cdc_sanp2 tables, and update the incremental data to the student_cdc_sync table

The renderings are as follows:

Table input

1. Connect to the database

Click New

SELECT student ID, name, gender, class, age, grades, height, mobile phone,
Insert time, update time, CURDATE() AS import time FROM student_cdc_snap1

Table input 2

SELECT student ID, name, gender, class, age, grades, height, mobile phone,
Insert time, update time, CURDATE() AS import time FROM student_cdc_snap2

Merge records

Data synchronization

Run

Run successfully

Database expected table

database script

CREATE DATABASE `world`

USE `world`;

/*Table structure for table `student_cdc` */

DROP TABLE IF EXISTS `student_cdc`;

CREATE TABLE `student_cdc` (
  `Student ID` int(11) NOT NULL,
  `Name` varchar(45) DEFAULT NULL,
  `Gender` varchar(45) DEFAULT NULL,
  `Class` varchar(45) DEFAULT NULL,
  `Age` varchar(45) DEFAULT NULL,
  `Grade` varchar(45) DEFAULT NULL,
  `Height` varchar(45) DEFAULT NULL,
  `Mobile` varchar(45) DEFAULT NULL,
  `insert time` date DEFAULT NULL,
  `Update time` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `student_cdc` */

insert into `student_cdc`(`student number`,`name`,`gender`,`class`,`age`,`score`,`height`,`mobile phone`,`insert time`,`update time`) values (2,'Li Er','Male','1701','17','80','175','18946554572','2018- 08-06','2018-08-06'),(3,'Xie Xun','Male','1702','18','95', '169','18946554573','2018-08-06','2018-08-06'),(4,'Zhao Ling','Female',\ '1702','19','86','180','18946554575','2018-08-06','2018-08-06'), (5,'Zhang Ming','Male','1704','20','85','185','18946554575','2018- 08-07','2018-08-07'),(6,'Zhang San','Female','1704','18','82' ,'169','18946554576','2018-08-06','2018-08-07'),(0,'李思','Male', '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` (
  `Student ID` int(11) NOT NULL,
  `Name` varchar(45) DEFAULT NULL,
  `Gender` varchar(45) DEFAULT NULL,
  `Class` varchar(45) DEFAULT NULL,
  `Age` varchar(45) DEFAULT NULL,
  `Grade` varchar(45) DEFAULT NULL,
  `Height` varchar(45) DEFAULT NULL,
  `Mobile` varchar(45) DEFAULT NULL,
  `insert time` date DEFAULT NULL,
  `Update time` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `student_cdc_snap1` */

insert into `student_cdc_snap1`(`student number`,`name`,`gender`,`class`,`age`,`score`,`height`,`mobile phone`,`insert time`,`update time`) values (1,'Zhang Yi','Male','1701','16','78','170','18946554571','2018- 08-06','2018-08-06'),(2,'Li Er','Male','1701','17','80' ,'175','18946554572','2018-08-06','2018-08-06'),(3,'Xie Xun','Male',\ '1702','18','95','169','18946554573','2018-08-06','2018-08-06'), (4,'Zhao Ling','Female','1702','19','86','180','18946554575','2018- 08-06','2018-08-06'),(5,'Zhang Ming','Male','1704','20','85' ,'185','18946554575','2018-08-07','2018-08-07'),(6,'Zhang San','Female', '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` (
  `Student ID` int(11) NOT NULL,
  `Name` varchar(45) DEFAULT NULL,
  `Gender` varchar(45) DEFAULT NULL,
  `Class` varchar(45) DEFAULT NULL,
  `Age` varchar(45) DEFAULT NULL,
  `Grade` varchar(45) DEFAULT NULL,
  `Height` varchar(45) DEFAULT NULL,
  `Mobile` varchar(45) DEFAULT NULL,
  `insert time` date DEFAULT NULL,
  `Update time` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `student_cdc_snap2` */

insert into `student_cdc_snap2`(`student number`,`name`,`gender`,`class`,`age`,`score`,`height`,`mobile phone`,`insert time`,`update time`) values (1,'Zhang Yi','Male','1701','16','78','170','18946554571','2018- 08-06','2018-08-06'),(2,'Li Er','Male','1701','17','80' ,'175','18946554572','2018-08-06','2018-08-06'),(3,'Xie Xun','Male',\ '1702','18','95','169','18946554573','2018-08-06','2018-08-06'), (4,'Zhao Ling','Female','1702','19','86','180','18946554575','2018- 08-06','2018-08-06'),(5,'Zhang Ming','Male','1704','20','85' ,'185','18946554575','2018-08-07','2018-08-07'),(6,'Zhang San','Female', '1704','18','82','169','18946554576','2018-08-06','2018-08-07') ,(0,'李思','Male','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,
  `Student ID` int(11) NOT NULL,
  `Name` varchar(45) DEFAULT NULL,
  `Gender` varchar(45) DEFAULT NULL,
  `Class` varchar(45) DEFAULT NULL,
  `Age` varchar(45) DEFAULT NULL,
  `Grade` varchar(45) DEFAULT NULL,
  `Height` varchar(45) DEFAULT NULL,
  `Mobile` varchar(45) DEFAULT NULL,
  `insert time` date DEFAULT NULL,
  `Update time` 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`,`student number`,`name`,`gender`,`class`,`age`,`score`,`height`,`mobile phone`,`insert time`,`update Time`) values (1,1,'Zhang Yi','Male','1701','16','78','170','18946554571\ ','2018-08-06','2018-08-06'),(2,2,'李二','Male','1701','17 ','80','175','18946554572','2018-08-06','2018-08-06'),(3,3,'Xie Xun ','Male','1702','18','95','169','18946554573','2018-08-06',\ '2018-08-06'),(4,4,'Zhao Ling','Female','1702','19','86','180\ ','18946554575','2018-08-06','2018-08-06'),(5,5,'Zhang Ming','Male','1704 ','20','85','185','18946554575','2018-08-07','2018-08-07'),(6 ,6,'Zhang San','Female','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 */;