此文章基于SQL Server 2008 CTP3 (Jun 04, 2007)。
change data capture是在每次对数据库执行insert、update、delete操作的时候,捕获变更数据的方法,这在数据仓库中是常用到的技术手段。Oracle早在9i中就加入了这一特性,在多年后的今天,SQL Server总算是提供了同样的功能。
CDC的原理是每次对源表(Source Tables)执行insert、update、delete时,数据库事务日志会记录DML造成的变更数据,然后捕获处理过程将日志中源表的变更数据写入变更捕获表(Change Tables),最后ETL工具使用CDC查询函数将变更数据抽取到数据仓库。
相比起在源表建立促发器,CDC对源表事务性能影响小,而且可以获取变更元数据。
这里示范一个CDC过程
第一步 启用数据库的SQL Server Agent。
第二步 建立一个数据库Database for CDC。
第三步 然后以服务角色sysadmin的用户登录,执行下面的命令:
USE [Database for CDC];
GO
EXECUTE [sys].[sp_cdc_enable_db_change_data_capture];
GO
执行命令后即启用了数据库的CDC特性。启用CDC特性后系统会自动建立名为CDC的构架和用户,并建立了几个用于CDC的数据表。
第四步 建立源表Source_Table,且将column_1列作为唯一索引IX_Source_Table
第五步 执行命令:
EXEC sys.sp_cdc_enable_table_change_data_capture
@source_schema = 'dbo'
, @source_name = 'Source_Table'
, @role_name = 'change_table_user1'
, @index_name = 'IX_Source_Table'
, @capture_instance = 'ST_Instance'
, @supports_net_changes = 1
, @captured_column_list = 'column_key, column_1, column_2'
, @filegroup_name = 'PRIMARY';
这个命令指定捕获源表Source_Table的变化,参数说明如下:
参数 | 说明 |
source_schema | 源表的构架名 |
source_name | 源表名 |
role_name | 获取变化数据的数据库角色,如果角色不存在,sp_cdc_enable_table_change_data_capture过程执行成功后会创建角色 |
index_name | 唯一索引名,不是必须的,如果源表没有主键可以指定唯一索引以确定哪一行数据是变更数据 |
capture_instance | 捕获实例,一个源表最多可以有两个实例 |
supports_net_changes | 是否捕捉净变化数据,如果是的话,源表必须有主键或者指定唯一标识列 |
captured_column_list | 需要在变更捕获表中保存的列 |
filegroup_name | 变更捕获表使用的文件组 |
为源表Source_Table成功建立捕获实例ST_Instance后,系统自动建立了变更捕获表ST_Instance_CT,变更捕获表的命名规则是实例名+CT。
对源表执行DML命令时,每行受到DML影响的数据都会在变更捕获表中保存数据,不同的DML命令在变更捕获表中保存的数据不同。
命令类型 | 每行数据在变更捕获表的保存情况 |
insert | 保存插入行的数据 |
delete | 保存删除行的数据 |
update | 保存两行数据,一行是更新前的,一行是更新后的 |
可以看到,变更捕获表中除了我们需要保存源表的三个列外,还多出了5个以”__$”开头的列,用于记录元数据。
列名 | 说明 |
__$start_lsn | 日志中序列号,在对源表执行DML时,每个事务有一个日志序列号 |
__$end_lsn | 空,不使用 |
__$seqval | 在一个事务中,DML影响行的序列号 |
__$operation | 识别执行的是何种DML,1=delete,2=insert,3=update(更新前),4=update(更新后) |
__$update_mask | 用2进制标识哪一列发生变更 |
可以先试着执行下面的命令,以检验CDC的效果:
insert dbo.source_table
values ('ck_1','c1_1','c2_1','c3_1');
insert dbo.source_table
values ('ck_2','c1_2','c2_2','c3_2');
insert dbo.source_table
values ('ck_3','c1_3','c2_3','c3_3');
update dbo.source_table
set column_2='c2_2_c'
where column_key='ck_2';
update dbo.source_table
set column_key='ck_3_c'
where column_key='ck_3';
delete dbo.source_table
where column_key='ck_3_c';
select * from cdc.st_instance_ct;
将cdc.st_instance_ct表中数据列出来后如下:
__$start_lsn | __$end_lsn | __$seqval | __$operation | __$update_mask | column_key | column_1 | column_2 | |
1 | 0x0000002D000001A1002C | NULL | 0x0000002D000001A10012 | 2 | 0x07 | ck_1 | c1_1 | c2_1 |
2 | 0x0000002D000001A90004 | NULL | 0x0000002D000001A90002 | 2 | 0x07 | ck_2 | c1_2 | c2_2 |
3 | 0x0000002D000001AB0004 | NULL | 0x0000002D000001AB0002 | 2 | 0x07 | ck_3 | c1_3 | c2_3 |
4 | 0x0000002E0000007E0022 | NULL | 0x0000002E0000007E0020 | 3 | 0x04 | ck_2 | c1_2 | c2_2 |
5 | 0x0000002E0000007E0022 | NULL | 0x0000002E0000007E0020 | 4 | 0x04 | ck_2 | c1_2 | c2_2_c |
6 | 0x0000002E000000830007 | NULL | 0x0000002E000000830002 | 3 | 0x01 | ck_3 | c1_3 | c2_3 |
7 | 0x0000002E000000830007 | NULL | 0x0000002E000000830002 | 4 | 0x01 | ck_3_c | c1_3 | c2_3 |
8 | 0x0000002E0000008A0006 | NULL | 0x0000002E0000008A0004 | 1 | 0x07 | ck_3_c | c1_3 | c2_3 |
其中__$update_mask是受影响的列,对insert和delete来说,影响全部的列,所以值是111(十进制的7);对column_key更新时,值是001(十进制1);对column_2更新时,值是100(十进制4)。