数据库 - change data capture(CDC)

2019-06-24 本文已影响4人消失黎明

变化数据捕获方式(CDC)

如果数据量很小，则采取完全源数据抽取；如果源数据量很大，则抽取变化的源数据，这种数据抽取模式叫做变化数据捕获，简称CDC。
CDC大体分为两种：侵入式和非侵入式。侵入式指CDC操作会给源系统带来性能影响，只要CDC操作以任何一种方式对源数据库执行了SQL操作，就认为是侵入式的。
常用的4种CDC方法是：基于时间戳的CDC、基于触发器的CDC、基于快照的CDC 和 基于日志的CDC，其中前三种是侵入式的。

基于时间戳的CDC

抽取过程可以根据某些属性列来判断哪些数据是增量的，最常见的属性列有以下两种：

时间戳：最好有两个列，一个插入时间戳，表示何时创建，一个更新时间戳，表示最后一次更新的时间。
序列：大多数数据库都提供自增功能，如果数据库表列被定义成自增的，就可以很容易地根据该列识别新插入的数据。

这种方法是最简单且常用的，但是有如下缺点：

不能记录删除记录的操作
无法识别多次更新
不具有实时能力

基于触发器的CDC

当执行INSERT、UPDATE、DELETE这些SQL语句时，可以激活数据库里的触发器，并执行一些动作，就是说触发器可以用来捕获变更的数据并把数据保存在中间临时表里。然后这些变更数据再从临时表取出，抽取到数据仓库的过渡区中。大多数场合下，不允许向操作型数据库里添加触发器，且这种方法会降低系统性能，所以用的不多。
可以使用源数据库的复制功能，将源库的数据备用到备用库上，在备库上创建触发器。
有关于这种方法，我们将结合HANA数据库在下面进行详细阐述和举例。

基于快照的CDC

如果没有时间戳，不允许使用触发器，就要使用快照表。可以通过比较源表和快照表来获得数据变化。
基于快照的CDC可以检测到插入、更新和删除的数据，这是相对于基于时间戳的CDC方案的有点。其缺点是需要大量存储空间来保存快照。

基于日志的CDC

最复杂的和没有侵入性的CDC方法是基于日志的方式。数据库会把每个插入、更新、删除操作记录到日志里。

HANA数据库对于CDC的处理

近期在学习HANA的数据同步复制（更新）功能，了解到对于不同的adapter，会具有不同的策略，比如对于hana adapter，主要是通过触发器的方法进行。首先在HANA数据库中创建一个shadow table，然后如果对source table进行了增删改的操作，就会被记录在shadow table中，然后DPAgent就会获取shadow table中的数据变动记录，把这些记录发送给DPServer。具体地讲，就是在shadow table上新建触发器。
当我们想更新virtual table的时候(即：表中的数据不断的添加)，也许我们会用数据库代理，通过写作业，然后让他定时查询shadow table中最新添加的数据，然后更新数据。这样时能实现更新数据的要求，但是数据却不能实时同步更新。所以，在shadow table中创建触发器就成为了必要之举。在这里我举一个例子。

--我们要建触发器的shadow table 
Create table Table_a 
( 
ID int identity(1,1),  --自增ID 
Content nvarchar(50), 
UpdateIDForTrigger int 
)

然后在该表上创建一个触发器

Create TRIGGER [dbo].[Table_a_Ins] 
ON [dbo].[Table_a] 
AFTER INSERT 
AS 
BEGIN 
declare @ID int 
set @ID=(select ID from inserted) 
--更新Table_a表中的UpdateIDForTrigger字段的值，为了能更明显的看出实时执行的效果 
UPDATE Table_a 
SET UpdateIDForTrigger = (@ID+10)--为了能看出不同，就直接将比ID大10的值作为变量赋值 
WHERE ID = @ID; 
END

接下来，我们按照普通一条条的插入结果测试下：

--给信息表添加数据 
insert into Table_a(Content) values('信息一'); 
insert into Table_a(Content) values('信息二');

然后查询shadow table 中的数据情况，

select * from Table_a

然后查询结果，发现如图所示：

ID  |  Content  |  UpdateIDForTrigger
1   |  信息一    |    11
2   |  信息二    |    12

可以看到触发器执行了。在每条数据插入的时候触发器同时执行了Update功能。
然后，我们要批量插入数据，为了方便我们插入，我们这里建立一张临时的基本信息表：

#基本信息表
Create table Table_Info
(
    ID int identity(1,1), 
    Content nvarchar(50)
)

然后插入数据

insert into Table_Info(Content) values('信息三');
insert into Table_Info(Content) values('信息四');
insert into Table_Info(Content) values('信息五');
insert into Table_Info(Content) values('信息六');
insert into Table_Info(Content) values('信息七');
insert into Table_Info(Content) values('信息八');
insert into Table_Info(Content) values('信息九');
insert into Table_Info(Content) values('信息十');

然后我们就可以批量插入数据到动态表中了

insert into Table_a(Content)
select Content from Table_Info

然而，在执行这个sql语句的时候消息框中会出现错误提示：

有经验的朋友会知道，这个错误是由于多个结果用“=”赋值给一个变量导致的。
即：set @变量=(select 多行结果 from Table)
这个时候，我就疑惑了，问题出在哪里了呢？不是触发器在每插一条数据的时候执行一次么？
于是，我将触发器改了下：

Alter TRIGGER [dbo].[Table_a_Ins]
ON [dbo].[Table_a]
AFTER INSERT
AS
    BEGIN
        select ID from inserted;
    END

然后再执行上面的批量插入试试看，看看他inserted表中到底存的是什么值：
果然不出所料，inserted表中的结果并不是一条数据：

知道错误的原因，我们操作起来就简单了，我们可以给inserted表建游标，然后通过游标来对批量插入的每行数据进行编辑。下面是我们修改后的触发器代码：

Alter TRIGGER [dbo].[Table_a_Ins]
ON [dbo].[Table_a]
    AFTER INSERT
    AS
        BEGIN
            declare @ID int
            declare cur_Insert cursor 
            for 
                select ID from inserted
            open cur_Insert
            fetch next from cur_Insert into @ID
            while @@fetch_status=0
                begin
                    UPDATE Table_a
                    SET UpdateIDForTrigger = (@ID+10)--为了能看出不同，就直接将比ID大10的值作为变量赋值
                    WHERE ID = @ID;
                    fetch next from cur_Insert into @ID
                end
            close cur_Insert
            deallocate cur_Insert
        END

然后，我们再按照上面的批量插入数据，然后查询下动态表中的结果：

insert into Table_a(Content)
select Content from Table_Info;

select * from Table_a;

此时运行没有错误提示了，运行结果如下：

总结下：触发器运行是每次执行一次Insert操作或者是Update,Delete等操作的时候才执行的。它的对象不是针对于修改的行数(即：每行修改的时候执行)。
参考：
数据抽取中的CDC方式