基于SQLServer的数据变更捕获

最新推荐文章于 2024-05-04 08:13:52 发布

寒冰屋

最新推荐文章于 2024-05-04 08:13:52 发布

阅读量329

点赞数

分类专栏：数据库文章标签： sqlserver 数据库

原文链接：https://mp.weixin.qq.com/s?__biz=MzAwNTMxMzg1MA==&mid=2654097725&idx=8&sn=ec55fcc0c5382a3171c8dd77b6de0671&chksm=80d87568b7affc7e8dda6bcace48f0d13dfda47cca1f647d3265eaf62a26fdf4835d584366ae&mpshare=1&scene=23&srcid=1109HFCADpiEPNQ9J7955snD&sharer_shareinf

版权

数据库专栏收录该内容

228 篇文章 13 订阅

订阅专栏

典型用途

微软的官方文档中定义了变更捕获的典型用途

变更数据捕获使用 SQL Server 代理记录表中发生的插入、更新及删除。此技术针对的数据使用者的一个典型示例是提取、转换和加载 (ETL) 应用程序。ETL 应用程序以增量方式将 SQL Server 源表中的更改数据加载到数据仓库或数据市场。

我这里再补充一点典型用途，需要在异构系统之间需要共享和同步数据的场景，也可以充分利用这项技术。

数据仓库的维度表和事实表的加载一般都采用下面的形式：

暴力的全量加载，即删除目标维度表中的所有数据，然后再全量从源表中加载。1. 这种方式会破坏维度表中的代理键，甚至是不能使用代理键。（例如自增长的ID）。2. 无法保留SCD-Type2即历史类型的更改。
依然使用全量加载，但在实际填充维度表和事实表时候自己来编写使用主键LookUp的比对过程，找出哪些是目标表中不存在的，哪些是需要更新的。种种方式只能轻松识别新增数据，但是对于更新数据依然需要编写复杂的比对逻辑，这些逻辑会大幅度降低数据加载的速度。
在源表设计独立的last_update_date字段，用于记录更新时间。加载过程通过比对这个更新时间来获取变更/新增的数据。

在笔者的职业生涯中，以上三种方式是最常见的，前两种的巨大缺点就是全量加载，如果数据集市中的维度表或实时表中存在几十万甚至上百万数据，就要消耗数小时加载，甚至方案根本不可行。因为数据的全量加载，还会导致对源表的扫描（在默认隔离级别下）加锁，降低源表的UPDATE/DELETE的性能。第三种方案的缺点是依赖表架构，有些情况源系统不在控制范围下，不允许更改表架构，更不可能更改应用程序，同时严重依赖这个字段的准确性，例如这个字段可能被应用程序错误修改，导致更新加载错误的数据。

所以，各大数据库厂商早就意识到这种场景，很早就退出了数据变更捕获这项技术（CDC）, 可惜重视这项技术的人却不是很多，所以写一篇文章来同大家分享。希望大家在做数据同步，数据仓库，各种数据处理的场景下充分利用这项技术。好了下面就开始一步步介绍如何使用SQLServer数据变更捕获。

为数据库启用变更捕获

以我自己新建的Test数据库为例，其中包含一个Account的表。

-- ====
-- 为数据启用变更捕获
-- ====
USE Test
GO
EXEC sys.sp_cdc_enable_db
GO

为表启用捕获

USE Test
GO
    EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'Account', --表名
    @role_name     = NULL, --这里为了演示不采用角色权限控制。实际生产环境往往需要分配一个单独的角色，只有这个角色才能使用和获取变更数据。
    @supports_net_changes = 1 --启用净捕获，即只保留每行数据的最后更改。
GO

查询变更捕获数据

DECLARE @from_lsn binary(10), @to_lsn binary(10)
 
SET @from_lsn = sys.fn_cdc_get_min_lsn ('dbo_Account');  
SET @to_lsn = sys.fn_cdc_get_max_lsn();  

SELECT *  from cdc.fn_cdc_get_net_changes_dbo_Account( @from_lsn, @to_lsn, 'all' );

好了，是不是看的有点懵，我来一个个解释。上图是运行以后的结果，这个结果来源于为表启用CDC捕获时所创建的影子表，影子表是专门用来存储变更信息的。在系统表下可以看到他们。可以看到影子表就是在源表上增加很多用于识别捕获的列而组成的。

SET @from_lsn = sys.fn_cdc_get_min_lsn ('dbo_Account');  
--这一句用于查询被捕获表的最小LSN号，LSN是SQLServer事务日志连续编号，由小到达连续排列。
--sys.fn_cdc_get_min_lsn 用于获取这个LSN，LSN是二进制格式，CDC所有变更捕获只能使用LSN编号进行。

SET @to_lsn = sys.fn_cdc_get_max_lsn();
--这一句是获取日志LSN最大值，这里为什么没有表名？因为最大日志LSN在所有表中都对对齐。例如我在一个事务中对表Account中做了一次更新，同时对另一个捕获表假设Sales表也做了一次更新。那么这两个捕获表的影子表的最大LSN是相同的值，所以没必要在单独传递表名。

SELECT *  from cdc.fn_cdc_get_net_changes_dbo_Account( @from_lsn, @to_lsn, 'all' );  
--这一句是最终调用捕获函数返回结果，参数是两个LSN区间，'all' 代表返回所有的列最终更新，并且在_$update_mask中不标记哪些列被更改了。

好了，再回去看查询出来的结果集的列名含义

列名	描述
_$start_lsn	这行数据更改所属的LSN编号
_$operation	更改的类型编号，参考如下： 1 = 删除 2 = 插入 4 = 更新后的行 3 = 更新前的行 5 = 不关系行是更新还是插入均为5 （最后一个参数如果输入 all with merge 时）
_$update_mask	用于标记哪些列被更改了的掩码，只能利用sys.fn_cdc_is_bit_set 函数来读取，稍后演示如何使用这个函数识别哪些列被更改了。
其余列为源表的列一对一显示	不赘述了。

再来做一个更新和删除操作，然后看看结果。

update Account
set name = 'Gary3'
where ID = 20

update Account
set name3 = 'Gary name 3'
where id = 19

delete from Account
where id = 18

第一行数据对应第二条修改操作。_$operation=4 状态是对的。

第二行数据对应第三条删除操作。_$operation=1 状态是对的

第三行数据对应第一条修改操作。_$operation=2 ，等等这里为什么是2，因为这条数据是我最早手动添加进去，然后修改的。同时使用的是净更改函数读取所以他始终认为这条数据是新增。说简单一点，就是我采用的捕获范围是从最小LSN到最大LSN，因为这条数据最小的更改是第一次新增，所以他始终返回的状态是2，但是他的数据是最新的更改。如果想获取新增后的更改，那么需要改变最小LSN。

下表是影子表中所有的记录，可以清晰的看到ID=20的记录最小LSN是第一行，$operation=2 代表这次事务做的是新增，但是后续ID=20的记录分别做了2次Update，一次是改为Gary，一次是改为Gary3，所以他将最终的Gary3返回了。这就是净更改的意义所在，否则如果返回operation=4的话，这条记录会被认为是更改，那么他新增的状态就丢掉了，这是不对的。我们在做ETL的时候需要知道这条记录时新增的，并且保留他最新的值。

如何知道某一列是否发生了变化？

这也是数据变更捕获最强大的功能之一，如果不使用变更捕获技术，只能用对列使用枯燥乏味而丑陋的 oldValue == newValue的技术来判断值是否更改。我们来做一次更改，讲ID=19的记录的名字修改一下。

update Account
set name3 = 'Gary name 4'
where id = 19

我们可以看到影子表（影子表时系统表中_CT结尾的）中最后一条记录反映了本次更改。__$operation =4 ，表示更新。

这里我们需要记录下这次更该的_start_lsn，因为这次更改的起始lsn变化了，如果还是从最小LSN捕获，那么这条数据只反应初始的状态（这点非常重要）所以我们改用新的start_lsn捕获，这里为了演示方便，我直接硬编码拷贝过来了。

DECLARE @from_lsn binary(10), @to_lsn binary(10),@name int
 
SET @from_lsn = 0x0000002C000003D60003
SET @to_lsn = sys.fn_cdc_get_max_lsn();  

SET @name = sys.fn_cdc_get_column_ordinal  
    ( 'dbo_Account','Name');

SELECT *,
sys.fn_cdc_is_bit_set(@name, __$update_mask) as 'Is_Name_Changed'

from cdc.fn_cdc_get_net_changes_dbo_Account( @from_lsn, @to_lsn, 'all with mask' );

可以看到最后一列is_Name_Changed = 1 的表示Name列被修改了。有了这个标记，我们就可以很容易将 operation=4 $$ is_name_changed = 1作为条件判断列是否发生了更新，在ETL过程中可以利用这个判断将这个字段作为历史添加到维度表中。

只捕获需要的列

例如如下 @captured_column_list 参数可以配置需要捕获的列，而不用默认捕获所有列，这对于性能提高有很大的帮助。

USE Test
GO
    EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'Account', --表名
    @role_name     = NULL, --这里为了演示不采用角色权限控制。实际生产环境往往需要分配一个单独的角色，只有这个角色才能使用和获取变更数据。
    @supports_net_changes = 1 --启用净捕获，即只保留每行数据的最后更改。
    @captured_column_list = N'DepartmentID, Name, GroupName',
GO

滑动时间加载

前面讲到的，变更捕获最重要的参数就是_start_lsn, _end_lsn. 简单来说这两个值就是确定读取变更数据的区间，所返回数据是否时是增删改状态，完全由这个区间决定。例如一行数据在更早的区间可能是新增，但是晚一点的区间就是更新或者删除。所以确定好区间非常重要。

好了，那么在真是的ETL加载中，很大程度我们要记录下来每次加载的区间的start_lsn,end_lsn。下次加载我们需要使用上次的end_lsn来计算一个开始lsn.

例如：上次加载完毕后的最大lsn = 0x0000002C000006370004 在这之后源表又进行了很多次增删改查，

紧接着的一个是lsn = 0x0000002C000006370005，那么我们下次加载就需要用这个作为起点，那么如何根据上次的lsn得到下一个紧挨着的lsn呢？

这需要用到 sys.fn_cdc_increment_lsn 函数, 参数是上一次加载完毕后的最大lsn。那么我们改造一下代码。

DECLARE @from_lsn binary(10), @to_lsn binary(10),@name int
 
SET @from_lsn = sys.fn_cdc_increment_lsn(0x0000002C000003D60003);
SET @to_lsn = sys.fn_cdc_get_max_lsn();  

SET @name = sys.fn_cdc_get_column_ordinal  
    ( 'dbo_Account','Name');

SELECT *,
sys.fn_cdc_is_bit_set(@name, __$update_mask) as 'Is_Name_Changed'

from cdc.fn_cdc_get_net_changes_dbo_Account( @from_lsn, @to_lsn, 'all with mask' );

进一步改进，我们需要用一张表来记录每次加载完毕后的lsn，然后查询这个lsn动态传递个代码。假设我已经创建了这个历史表,并且这个历史表始终只有一条记录，就是最后一次加载的lsn. 然我我们把查出来的结果传递给sys.fn_cdc_increment_lsn函数，他会返回这个以后最近的lsn，我们用这个做为起点lsn进行加载，然后加载完毕又把最大的@to_lsn存到表Capture_Last_lsn

DECLARE @last_load_lsn binary(10)，@from_lsn binary(10), @to_lsn binary(10),@name int
select  @last_load_lsn = last_load_lsn from Capture_Last_lsn

SET @from_lsn = sys.fn_cdc_increment_lsn(@last_load_lsn);
SET @to_lsn = sys.fn_cdc_get_max_lsn();  

SET @name = sys.fn_cdc_get_column_ordinal  
    ( 'dbo_Account','Name');

SELECT *,
sys.fn_cdc_is_bit_set(@name, __$update_mask) as 'Is_Name_Changed'

from cdc.fn_cdc_get_net_changes_dbo_Account( @from_lsn, @to_lsn, 'all with mask' );

好了，这样我们运行加载的时候总可以从上次加载的起点以后开始读取变更的数据。

总结

数据变更捕获技术是一项非常关键的核心技术，要想设计出可靠的数据捕获传输方案，这可能是唯一的可靠方案，并且非常高效。那么们总结下有点和影响：

优点：

数据库级别提供的可靠性，不用依赖其他第三方技术。
每次只读取捕获的更改，不需要查询源表全部数据。
启用和禁用捕获，对源表透明（唯一需要原表有主键）
捕获是SQLServer内核中利用了复制中的技术捕获事务日志而生成的，所以是异步捕获，对源表影响很小。
对源表的表架构进行更改不破坏捕获，被删除的列会返回null，新增的列也不会出现在捕获表中。除非重新禁用启用捕获。

问题：

捕获采用SQLServer代理执行job进行捕获和清理。
数据捕获保留的时间默认为3天，当然可以更改为更长日期。也就是说如果你3天不进行捕获查询，那么SQLServer会自动清除3天前影子表中捕获的修改内容。所以你要谨慎考虑你的ETL数据加载频率，例如1天运行一次捕获查询进行数据同步。因为捕获有效期设置的越长，那么影子表中的数据越多，这对于频繁更新的大表是一个极大挑战。

引入地址