變化數(shù)據(jù)捕獲方式(CDC)
如果數(shù)據(jù)量很小,則采取完全源數(shù)據(jù)抽人┕隆;如果源數(shù)據(jù)量很大甲捏,則抽取變化的源數(shù)據(jù)演熟,這種數(shù)據(jù)抽取模式叫做變化數(shù)據(jù)捕獲,簡(jiǎn)稱CDC司顿。
CDC大體分為兩種:侵入式和非侵入式芒粹。侵入式指CDC操作會(huì)給源系統(tǒng)帶來(lái)性能影響,只要CDC操作以任何一種方式對(duì)源數(shù)據(jù)庫(kù)執(zhí)行了SQL操作大溜,就認(rèn)為是侵入式的化漆。
常用的4種CDC方法是:基于時(shí)間戳的CDC、基于觸發(fā)器的CDC钦奋、基于快照的CDC 和 基于日志的CDC获三,其中前三種是侵入式的。
基于時(shí)間戳的CDC
抽取過(guò)程可以根據(jù)某些屬性列來(lái)判斷哪些數(shù)據(jù)是增量的锨苏,最常見(jiàn)的屬性列有以下兩種:
- 時(shí)間戳:最好有兩個(gè)列,一個(gè)插入時(shí)間戳棺聊,表示何時(shí)創(chuàng)建伞租,一個(gè)更新時(shí)間戳,表示最后一次更新的時(shí)間限佩。
- 序列:大多數(shù)數(shù)據(jù)庫(kù)都提供自增功能葵诈,如果數(shù)據(jù)庫(kù)表列被定義成自增的,就可以很容易地根據(jù)該列識(shí)別新插入的數(shù)據(jù)祟同。
這種方法是最簡(jiǎn)單且常用的作喘,但是有如下缺點(diǎn):
- 不能記錄刪除記錄的操作
- 無(wú)法識(shí)別多次更新
- 不具有實(shí)時(shí)能力
基于觸發(fā)器的CDC
當(dāng)執(zhí)行INSERT、UPDATE晕城、DELETE這些SQL語(yǔ)句時(shí)泞坦,可以激活數(shù)據(jù)庫(kù)里的觸發(fā)器,并執(zhí)行一些動(dòng)作砖顷,就是說(shuō)觸發(fā)器可以用來(lái)捕獲變更的數(shù)據(jù)并把數(shù)據(jù)保存在中間臨時(shí)表里贰锁。然后這些變更數(shù)據(jù)再?gòu)呐R時(shí)表取出赃梧,抽取到數(shù)據(jù)倉(cāng)庫(kù)的過(guò)渡區(qū)中。大多數(shù)場(chǎng)合下豌熄,不允許向操作型數(shù)據(jù)庫(kù)里添加觸發(fā)器授嘀,且這種方法會(huì)降低系統(tǒng)性能,所以用的不多锣险。
可以使用源數(shù)據(jù)庫(kù)的復(fù)制功能蹄皱,將源庫(kù)的數(shù)據(jù)備用到備用庫(kù)上,在備庫(kù)上創(chuàng)建觸發(fā)器芯肤。
有關(guān)于這種方法巷折,我們將結(jié)合HANA數(shù)據(jù)庫(kù)在下面進(jìn)行詳細(xì)闡述和舉例。
基于快照的CDC
如果沒(méi)有時(shí)間戳纷妆,不允許使用觸發(fā)器盔几,就要使用快照表⊙诖保可以通過(guò)比較源表和快照表來(lái)獲得數(shù)據(jù)變化逊拍。
基于快照的CDC可以檢測(cè)到插入、更新和刪除的數(shù)據(jù)际邻,這是相對(duì)于基于時(shí)間戳的CDC方案的有點(diǎn)芯丧。其缺點(diǎn)是需要大量存儲(chǔ)空間來(lái)保存快照。
基于日志的CDC
最復(fù)雜的和沒(méi)有侵入性的CDC方法是基于日志的方式世曾。數(shù)據(jù)庫(kù)會(huì)把每個(gè)插入缨恒、更新、刪除操作記錄到日志里轮听。
HANA數(shù)據(jù)庫(kù)對(duì)于CDC的處理
近期在學(xué)習(xí)HANA的數(shù)據(jù)同步復(fù)制(更新)功能骗露,了解到對(duì)于不同的adapter,會(huì)具有不同的策略血巍,比如對(duì)于hana adapter萧锉,主要是通過(guò)觸發(fā)器的方法進(jìn)行。首先在HANA數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)shadow table述寡,然后如果對(duì)source table進(jìn)行了增刪改的操作柿隙,就會(huì)被記錄在shadow table中,然后DPAgent就會(huì)獲取shadow table中的數(shù)據(jù)變動(dòng)記錄鲫凶,把這些記錄發(fā)送給DPServer禀崖。具體地講,就是在shadow table上新建觸發(fā)器螟炫。
當(dāng)我們想更新virtual table的時(shí)候(即:表中的數(shù)據(jù)不斷的添加)波附,也許我們會(huì)用數(shù)據(jù)庫(kù)代理,通過(guò)寫(xiě)作業(yè),然后讓他定時(shí)查詢shadow table中最新添加的數(shù)據(jù)叶雹,然后更新數(shù)據(jù)财饥。這樣時(shí)能實(shí)現(xiàn)更新數(shù)據(jù)的要求,但是數(shù)據(jù)卻不能實(shí)時(shí)同步更新折晦。 所以钥星,在shadow table中創(chuàng)建觸發(fā)器就成為了必要之舉。在這里我舉一個(gè)例子满着。
--我們要建觸發(fā)器的shadow table
Create table Table_a
(
ID int identity(1,1), --自增ID
Content nvarchar(50),
UpdateIDForTrigger int
)
然后在該表上創(chuàng)建一個(gè)觸發(fā)器
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字段的值谦炒,為了能更明顯的看出實(shí)時(shí)執(zhí)行的效果
UPDATE Table_a
SET UpdateIDForTrigger = (@ID+10)--為了能看出不同,就直接將比ID大10的值作為變量賦值
WHERE ID = @ID;
END
接下來(lái)风喇,我們按照普通一條條的插入結(jié)果測(cè)試下:
--給信息表添加數(shù)據(jù)
insert into Table_a(Content) values('信息一');
insert into Table_a(Content) values('信息二');
然后查詢shadow table 中的數(shù)據(jù)情況宁改,
select * from Table_a
然后查詢結(jié)果,發(fā)現(xiàn)如圖所示:
ID | Content | UpdateIDForTrigger
1 | 信息一 | 11
2 | 信息二 | 12
可以看到觸發(fā)器執(zhí)行了魂莫。在每條數(shù)據(jù)插入的時(shí)候觸發(fā)器同時(shí)執(zhí)行了Update功能还蹲。
然后,我們要批量插入數(shù)據(jù)耙考,為了方便我們插入谜喊,我們這里建立一張臨時(shí)的基本信息表:
#基本信息表
Create table Table_Info
(
ID int identity(1,1),
Content nvarchar(50)
)
然后插入數(shù)據(jù)
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('信息十');
然后我們就可以批量插入數(shù)據(jù)到動(dòng)態(tài)表中了
insert into Table_a(Content)
select Content from Table_Info
然而,在執(zhí)行這個(gè)sql語(yǔ)句的時(shí)候消息框中會(huì)出現(xiàn)錯(cuò)誤提示:
有經(jīng)驗(yàn)的朋友會(huì)知道倦始,這個(gè)錯(cuò)誤是由于多個(gè)結(jié)果用“=”賦值給一個(gè)變量導(dǎo)致的斗遏。
即:set @變量=(select 多行結(jié)果 from Table)
這個(gè)時(shí)候,我就疑惑了鞋邑,問(wèn)題出在哪里了呢诵次?不是觸發(fā)器在每插一條數(shù)據(jù)的時(shí)候執(zhí)行一次么?
于是枚碗,我將觸發(fā)器改了下:
Alter TRIGGER [dbo].[Table_a_Ins]
ON [dbo].[Table_a]
AFTER INSERT
AS
BEGIN
select ID from inserted;
END
然后再執(zhí)行上面的批量插入試試看逾一,看看他inserted表中到底存的是什么值:
果然不出所料,inserted表中的結(jié)果并不是一條數(shù)據(jù):
知道錯(cuò)誤的原因肮雨,我們操作起來(lái)就簡(jiǎn)單了嬉荆,我們可以給inserted表建游標(biāo),然后通過(guò)游標(biāo)來(lái)對(duì)批量插入的每行數(shù)據(jù)進(jìn)行編輯酷含。下面是我們修改后的觸發(fā)器代碼:
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
然后汪茧,我們?cè)侔凑丈厦娴呐坎迦霐?shù)據(jù)椅亚,然后查詢下動(dòng)態(tài)表中的結(jié)果:
insert into Table_a(Content)
select Content from Table_Info;
select * from Table_a;
此時(shí)運(yùn)行沒(méi)有錯(cuò)誤提示了,運(yùn)行結(jié)果如下:
總結(jié)下:觸發(fā)器運(yùn)行是每次執(zhí)行一次Insert操作或者是Update,Delete等操作的時(shí)候才執(zhí)行的舱污。它的對(duì)象不是針對(duì)于修改的行數(shù)(即:每行修改的時(shí)候執(zhí)行)呀舔。
參考:
數(shù)據(jù)抽取中的CDC方式