KettleTransformLoggingCDC文章.docVIP

Wednesday, January 26, 2011 Kettle Transformation Logging and Change Data Capture (New) Kettle Transformation Logging and Change Data Capture Updated Version including the great feedback from Matt Casters and Jos (both authors of the fantastic Kettle Solutions book) CDC Setting up Logging A safe approach to CDC Sample File Tutorial Details Software: PDI/Kettle 4.1 (download?here), MySQL Server (download?here) Knowledge: Intermediate (To follow this tutorial you should have good knowledge of the software and hence not every single step will be described) Tutorial files can be downloaded here CDC In this session we will look at the extensive Pentaho Data Integration/Kettle 4.1 logging features. Now how come we also talk about Change Date Capture (CDC)? This is because Kettle incorporates logging of the start and end date for CDC in the logging architecture.? If you are not familiar with CDC, here a brief summary: CDC allows you to capture all the data from the source system that has changed since the last time you ran the ETL process. Wikipedia gives a more detailed overview in case you are interested. In the easiest case you have some sort of timestamp or datetime column that you can use for this purpose. The following example will be based on a simple sales table that has a date column which we can use for CDC: Note that this is dataset has actually quite old data, something that we have to keep in mind. What happens if new data gets inserted later on? More on this after the basics. Open your favourite SQL Client (and start your MySQL server if it is not running yet) and issue following SQL statements: USE test ; DROP TABLE IF EXISTS `sales` ; CREATE TABLE `sales` ( `date` DATETIME, `product_type` VARCHAR(45), `sales` INT(255) ) ; INSERT INTO `sales` VALUES (2010-01-20 00:00:00,Shoes,234), (2010-01-20 00:00:00,Cheese,456), (2010-01-21 00:00:00,Shoes,256), (2010-01-21 00:00:00,Cheese,156), (2010-01-22 00:00:00,Shoes,535), (2010-01-23 00:00:00,Cheese,433) ; SELE