?
快捷搜索:  as  test  1111  test aNd 8=8  test++aNd+8=8  as++aNd+8=8  as aNd 8=8

亦博游戲平臺:在SQL Server 2008中實現CDCSQL Server 2008

?

此文章基于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[DatabaseforCDC];

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的效果:

insertdbo.source_table

values('ck_1','c1_1','c2_1','c3_1');

insertdbo.source_table

values('ck_2','c1_2','c2_2','c3_2');

insertdbo.source_table

values('ck_3','c1_3','c2_3','c3_3');

updatedbo.source_table

setcolumn_2='c2_2_c'

wherecolumn_key='ck_2';

updatedbo.source_table

setcolumn_key='ck_3_c'

wherecolumn_key='ck_3';

deletedbo.source_table

wherecolumn_key='ck_3_c';

select*fromcdc.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)。

變化數據查詢

變化捕獲表是那些CDC實例名+CT的表,表中記錄了源表的數據變化歷史。但平日要按照必然的要求查詢這些變化。

在數據庫的CDC構架中,除了變化捕獲表外,還可看到有5個在數據庫啟用CDC時建立的表:

表名

感化

captured_columns

記錄所有CDC實例要保存的列

change_tables

記錄所有的CDC實例

ddl_history

記錄所有源表由DDL孕育發生的變化

index_columns

記錄CDC實例應用的獨一索引

lsn_time_mapping

記錄日志序列號的光陰,每個DML事務都有一個日志序列號

除了關聯查詢這些CDC表外,SQL Server 2008里還有多個CDC函數和儲存歷程,用于查詢變化數據。

1. 日志序列號與事務光陰的變換

在change tables中沒有記錄事務發生的光陰,只記錄了事務的日志序列號(lsn),而日志序號號對應的光陰記錄在lsn_time_mapping表中。sys.fn_cdc_map_lsn_to_time和sys.fn_cdc_map_time_to_lsn是兩個用于轉換日志序列號與事務光陰的函數;sys.fn_cdc_map_time_to_lsn用于獲取某一光陰段內的所有日志序列號。

2. 最小和最大年夜日志序列號

sys.fn_cdc_get_min_lsn和 sys.fn_cdc_get_max_lsn函數得到今朝存在的最大年夜和最小日志序列號。

3. 查詢變化數據

cdc.fn_cdc_get_all_changes_函數用于查詢實例中滿意要求的所有變化記錄。cdc.fn_cdc_get_net_changes_函數用于查詢實例中滿意要求的凈變化記錄,所謂的凈變化記錄既是著末一次DML操作后源表的記錄,比如在對一行數據進行了多次update后,應用cdc.fn_cdc_get_all_changes_亦博游戲平臺會返回所有更新前和更新后的數據記錄,而凈變化只返回著末一次更新后的記錄。

4. 獲取變化列

在對源表進行update操作后,無意偶爾必要知道更新的是哪一列。在變化捕獲表中__$update_mask字段保存變化列的2進制編碼。sys.fn_cdc_is_bit_set用于返回列序的二進制值,比如要知道第3 列是否變化,應用sys.fn_cdc_is_bit_set( 3, __$update_mask ),若返回1,則注解第3列變化,返回0,則注解沒有變化。別的要知道實例中的列是第幾列,可應用sys.fn_cdc_get_column_ordinal函數。

5. 獲取源表DDL變化歷史

sys.sp_cdc_get_ddl_history函數用于查詢對源表應用數據定義語句的歷史,平日在用DDL改變源表時,也要應用同樣的DLL改變變化捕獲表。比如刪除源表中某一列,或者將某一列的值類型由int改成long,那么變化捕獲表也要隨著變更。

變化數據的清理

變化捕獲表中數據要周期性的加載到數據倉庫中,被加載后的數據就要清理掉落,否則用于cdc的數據會越來越多。應用sys.sp_cdc_cleanup_change_table存儲歷程清除變化數據。此外,在啟用數據庫CDC時,系統自動在SQL Server Agent中加入逐日清除變化數據的功課。

停用CDC

sys.sp_cdc_disable_table_change_data_capture存儲歷程用于停用CDC實例。sys.sp_cdc_disable_db_change_data_capture存儲歷程用于停用數據庫CDC功能。

小結

CDC是在SQL Server2008 CTP3中才加入特點,在前兩個CTP中還沒有呈現過,可以看到還有些不盡人意,沒有Oracle那么完善,盼望在正式版中能進一步改進。

免責聲明:以上內容源自網絡,版權歸原作者所有,如有侵犯您的原創版權請告知,我們將盡快刪除相關內容。

您可能還會對下面的文章感興趣:

快三平台开户