在Kettle里使用快照实现变化数据捕获（CDC）

1. 建立测试表，插入数据。

use test;    
    
create table t_color (    
    id int unsigned not null auto_increment primary key,    
    color varchar(10)  
)  engine=MyISAM;    
    
insert into t_color (color) values('Black'),('Green'),('Red'),('Blue');    
select * from t_color; 

2. 建立快照表。

use test;   
  
create table t_color_stg engine=MyISAM as select * from t_color ;  
select * from t_color_stg;  

3. 建立目标表。

use test;   
  
create table dim_color engine=MyISAM as select * from t_color ;  
select * from dim_color; 

4. 建立基于快照的CDC转换

说明：

• 创建两个“表输入”步骤，一个是t_color的表输入，另一个是t_color_stg的表输入。在表输入里选中所有字段，并按照关键字段排序。然后添加一个“合并记录”步骤，把两个表输入步骤都连接到“合并记录”步骤，选择哪个步骤是旧数据来源，哪个步骤是新数据来源，选择标志字段包含unchanged、changed、new、deleted数据，另外设置关键字段和需要比较的字段。
• 为了过滤没有发生变化的数据，在后面再增加一个“过滤记录”步骤，过滤条件是“flagfield=identical”，把所有没有变换的数据都发送到“空操作”步骤，把新增、删除、修改的数据发送到“数据同步”步骤，该步骤可以根据标志字段自动进行增加、删除、修改等操作。

5. 测试
-- 执行转换
-- 查看dim_color表
mysql> select * from dim_color;
+----+--------+
| id | color  |
+----+--------+
|  1 | Black  |
|  2 | Green  |
|  3 | Red    |
|  4 | Blue   |
+----+--------+
4 rows in set (0.00 sec)

-- 修改数据

delete from t_color where id=3;
update t_color set color='Grey' where id=1;
insert into t_color (color) values('Yellow');

-- 执行转换
-- 查看dim_color表
mysql> select * from dim_color;
+----+--------+
| id | color  |
+----+--------+
|  1 | Grey   |
|  2 | Green  |
|  5 | Yellow |
|  4 | Blue   |
+----+--------+
4 rows in set (0.00 sec)

6. 总结

• 快照表就是一次性抽取源系统中的全部数据，把这些数据加载到数据仓库的缓冲区中。下一次需要同步时，再从源系统中抽取全部数据，并把全部数据也放到数据仓库的缓冲区中，作为这个的第二个版本，然后再比较这两个版本的数据，找到变化。
• 基于快照的CDC可以检测到插入、更新和删除的数据，这是相对于基于时间戳的CDC方案的有点，但它的缺点是要大量的存储空间来保存这些快照。另外，在表比较大时，也会有比较严重的性能问题。因为会有这种性能问题，所以也可以使用SQL来做比较，数据库引擎的性能往往比ETL引擎的性能更好。

比较的SQL语句如下：

select 'U' as flag, t2.id as id, t2.color as color  
  from t_color_stg t1 inner join t_color t2 ON t1.id = t2.id  
 where t1.color != t2.color   
union all 
select 'D' as flag, t1.id as id, t1.color as color  
  from t_color_stg t1 left join t_color t2 ON t1.id = t2.id  
 where t2.id is null   
union all 
select 'I' as flag, t2.id as id, t2.color as color  
  from t_color as t2 left join t_color_stg as t1 ON t2.id = t1.id  
 where t1.id is null;  

结果如下：

+------+----+--------+
| flag | id | color  |
+------+----+--------+
| U    |  1 | Grey   |
| D    |  3 | Red    |
| I    |  5 | Yellow |
+------+----+--------+
3 rows in set (0.00 sec)