SQL Server CDC配合Kafka Connect監聽資料變化

山治先生發表於2021-04-22
SQLServerKafka

寫在前面

　　好久沒更新Blog了，從CRUD Boy轉型大資料開發，拉寬了不少的知識面，從今年年初開始籌備、組建、招兵買馬，到現在穩定開搞中，期間踏過無數的火坑，也許除了這篇還很寫上三四篇。

　　進入主題，通常企業為了實現資料統計、資料分析、資料探勘、解決資訊孤島等全域性資料的系統化運作管理 ，為BI、經營分析、決策支援系統等深度開發應用奠定基礎，挖掘資料價值 ，企業會開始著手建立資料倉儲，資料中臺。而這些資料來源則來自於企業的各個業務系統的資料或爬取外部的資料，從業務系統資料到資料倉儲的過程就是一個ETL（Extract-Transform-Load）行為，包括了採集、清洗、資料轉換等主要過程，通常異構資料抽取轉換使用Sqoop、DataX等，日誌採集Flume、Logstash、Filebeat等。

　　資料抽取分為全量抽取和增量抽取，全量抽取類似於資料遷移或資料複製，全量抽取很好理解；增量抽取在全量的基礎上做增量，只監聽、捕捉動態變化的資料。如何捕捉資料的變化是增量抽取的關鍵，一是準確性，必須保證準確的捕捉到資料的動態變化，二是效能，不能對業務系統造成太大的壓力。

增量抽取方式

　　通常增量抽取有幾種方式，各有優缺點。

1. 觸發器

 　　在源資料庫上的目標表建立觸發器，監聽增、刪、改操作，捕捉到資料的變更寫入臨時表。

優點：操作簡單、規則清晰，對源表不影響；

缺點：對源資料庫有侵入，對業務系統有一定的影響；

2. 全表比對

　　在ETL過程中，抽取方建立臨時表待全量抽取儲存，然後在進行比對資料。

優點：對源資料庫、源表都無需改動，完全交付ETL過程處理，統一管理；

缺點：ETL效率低、設計複雜，資料量越大，速度越慢，時效性不確定；

3. 全表刪除後再插入

　　在抽取資料之前，先將表中資料清空，然後全量抽取。

優點：ETL 操作簡單，速度快。

缺點：全量抽取一般採取T+1的形式，抽取資料量大的表容易對資料庫造成壓力；

4. 時間戳

　　時間戳的方式即在源表上增加時間戳列，對發生變更的表進行更新，然後根據時間戳進行提取。

優點：操作簡單，ELT邏輯清晰，效能比較好；

缺點：對業務系統有侵入，資料庫表也需要額外增加欄位。對於老的業務系統可能不容易做變更。

5. CDC方式

　　變更資料捕獲Change Data Capture（簡稱CDC），SQLServer為實時更新資料同步提供了CDC機制，類似於Mysql的binlog，將資料更新操作維護到一張CDC表中。開啟CDC的源表在插入INSERT、更新UPDATE和刪除DELETE活動時會插入資料到日誌表中。cdc通過捕獲程式將變更資料捕獲到變更表中，通過cdc提供的查詢函式，可以捕獲這部分資料。詳情可以檢視官方介紹：關於變更資料捕獲 (SQL Server)

優點：提供易於使用的API 來設定CDC 環境，縮短ETL 的時間，無需修改業務系統表結構。

缺點：受資料庫版本的限制，實現過程相對複雜。

CDC增量抽取

先決條件

1. 已搭建好Kafka叢集，Zookeeper叢集；

2. 源資料庫支援CDC，版本採用開發版或企業版。

案例環境：

Ubuntu 20.04

Kafka 2.13-2.7.0

Zookeeper  3.6.2

SQL Server 2012

步驟

 　　除了資料庫開啟CDC支援以外，主要還是要將變更的資料通過Kafka Connect傳輸資料，Debezium是目前官方推薦的聯結器，它支援絕大多數主流資料庫：MySQL、PostgreSQL、SQL Server、Oracle等等，詳情檢視Connectors。

1. 資料庫步驟

開啟資料庫CDC支援

　　在源資料庫執行以下命令：

EXEC sys.sp_cdc_enable_db GO

　　附上關閉語句：

exec sys.sp_cdc_disable_db

查詢是否啟用

select * from sys.databases where is_cdc_enabled = 1

建立測試資料表：（已有表則跳過此步驟）

create  table T_LioCDC
(
    ID int identity(1,1) primary key ,
    Name nvarchar(16),
    Sex bit,
    CreateTime datetime,
    UpdateTime datetime
);

對源表開啟CDC支援：

exec sp_cdc_enable_table 
@source_schema='dbo', 
@source_name='T_LioCDC', 
@role_name=null,
@supports_net_changes = 1;

確認是否有許可權訪問CDC Table：

EXEC sys.sp_cdc_help_change_data_capture

 確認SQL Server Agent已開啟：

EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',N'SQLSERVERAGENT'

 　　以上則完成對資料庫的CDC操作。

2. Kafka步驟

　　Kafka Connect的工作模式分為兩種，分別是standalone模式和distributed模式。standalone用於單機測試，本文用distributed模式，用於生產環境。（Kafka必須先執行啟動，再進行以下步驟進行配置。）

下載Sql Server Connector

　　下載聯結器後，建立一個資料夾來存放，解壓到該目錄下即可，例子路徑： /usr/soft/kafka/kafka_2.13_2.7.0/plugins （記住這個路徑，配置中要用到）

 下載地址：debezium-connector-sqlserver-1.5.0.Final-plugin.tar.gz

 編輯connect-distributed.properties配置

　　修改Kafka connect配置檔案，$KAFKA_HOME/config/connect-distributed.properties，變更內容如下：

//kafka叢集ip+port
bootstrap.servers=172.192.10.210:9092,172.192.10.211:9092,172.192.10.212:9092

key.converter.schemas.enable=false
value.converter.schemas.enable=false

offset.storage.topic=connect-offsets
offset.storage.replication.factor=1
offset.storage.partitions=3
offset.storage.cleanup.policy=compact

config.storage.topic=connect-configs
config.storage.replication.factor=1

status.storage.topic=connect-status
status.storage.replication.factor=1
status.storage.partitions=3
//剛剛下載聯結器解壓的路徑
plugin.path=/usr/soft/kafka/kafka_2.13_2.7.0/plugins

看到配置中有三個Topic，分別是

config.storage.topic：用以儲存connector和task的配置資訊，需要注意的是這個主題的分割槽數只能是1，而且是有多副本的。
offset.storage.topic：用以儲存offset資訊。
status.storage.topic：用以儲存connetor的狀態資訊。

這些Topic可以不用建立，啟動後會預設建立。

啟動Kafka叢集

　　儲存配置之後，將connect-distributed.properties分發到叢集中，然後啟動：

bin/connect-distributed.sh config/connect-distributed.properties

檢查是否啟動

　　connector支援REST API的方式進行管理，所以用Post man或者Fiddler可以呼叫相關介面進行管理。檢查是否啟動：

SQL Server CDC配合Kafka Connect監聽資料變化

 不用奇怪，上面配置叢集的IP是172段，這裡的192.168.1.177仍是我的叢集中的一個伺服器，因為伺服器都使用了雙網路卡。因為還沒有聯結器相關配置，所以介面返回是一個空陣列，接下來將新增一個聯結器。

編寫sqlserver-cdc-source.json

{
    "name": "sqlserver-cdc-source",
    "config": {
        "connector.class" : "io.debezium.connector.sqlserver.SqlServerConnector",
        "database.server.name" : "JnServer",
        "database.hostname" : "172.192.20.2", --目標資料庫的ip
        "database.port" : "1433",  --目標資料庫的埠
        "database.user" : "sa",   --目標資料庫的賬號
        "database.password" : "123456",  --密碼
        "database.dbname" : "Dis",  --目標資料庫的資料庫名稱
        "table.whitelist": "dbo.T_LioCDC", --監聽表名
         "schemas.enable" : "false",  
         "mode":"incrementing",  --增量模式
         "incrementing.column.name": "ID", --增量列名
        "database.history.kafka.bootstrap.servers" : "172.192.10.210:9092,172.192.10.211:9092,172.192.10.212", --kafka叢集
        "database.history.kafka.topic": "TopicTLioCDC",  --kafka topic內部使用，不是由消費者使用
        "value.converter.schemas.enable":"false",
        "value.converter":"org.apache.kafka.connect.json.JsonConverter"
    }
}
//源文地址： https://www.cnblogs.com/EminemJK/p/14688907.html

還有其他額外的配置，可以參考官方文件。然後執行

 繼續執行檢查，就發現聯結器已經成功配置了：

SQL Server CDC配合Kafka Connect監聽資料變化

其他API

GET /connectors – 返回所有正在執行的connector名。
POST /connectors – 新建一個connector; 請求體必須是json格式並且需要包含name欄位和config欄位，name是connector的名字，config是json格式，必須包含你的connector的配置資訊。
GET /connectors/{name} – 獲取指定connetor的資訊。
GET /connectors/{name}/config – 獲取指定connector的配置資訊。
PUT /connectors/{name}/config – 更新指定connector的配置資訊。
GET /connectors/{name}/status – 獲取指定connector的狀態，包括它是否在執行、停止、或者失敗，如果發生錯誤，還會列出錯誤的具體資訊。
GET /connectors/{name}/tasks – 獲取指定connector正在執行的task。
GET /connectors/{name}/tasks/{taskid}/status – 獲取指定connector的task的狀態資訊。
PUT /connectors/{name}/pause – 暫停connector和它的task，停止資料處理知道它被恢復。
PUT /connectors/{name}/resume – 恢復一個被暫停的connector。
POST /connectors/{name}/restart – 重啟一個connector，尤其是在一個connector執行失敗的情況下比較常用
POST /connectors/{name}/tasks/{taskId}/restart – 重啟一個task，一般是因為它執行失敗才這樣做。
DELETE /connectors/{name} – 刪除一個connector，停止它的所有task並刪除配置。

//源文地址： https://www.cnblogs.com/EminemJK/p/14688907.html

檢視Topic

/usr/soft/kafka/kafka_2.13_2.7.0# bin/kafka-topics.sh --list --zookeeper localhost:2000

Topic JnServer.dbo.T_LioCDC 則是供我們消費的主題，啟動一個消費者進行監聽測試：

bin/kafka-console-consumer.sh --bootstrap-server 172.192.10.210:9092  --consumer-property group.id=group1 --consumer-property client.id=consumer-1  --topic JnServer.dbo.T_LioCDC

然後再源表進行一些列增刪改操作，

--測試程式碼
insert into T_LioCDC(name, sex, createtime,UpdateTime)  values ('A',1,getdate(),getdate())
insert into T_LioCDC(name, sex, createtime,UpdateTime)  values ('B',0,getdate(),getdate())
insert into T_LioCDC(name, sex, createtime,UpdateTime)  values ('C',1,getdate(),getdate())
insert into T_LioCDC(name, sex, createtime,UpdateTime)  values ('D',0,getdate(),getdate())
insert into T_LioCDC(name, sex, createtime,UpdateTime)  values ('E',1,getdate(),getdate())
insert into T_LioCDC(name, sex, createtime,UpdateTime)  values ('F',1,getdate(),getdate())
insert into T_LioCDC(name, sex, createtime,UpdateTime)  values ('G',0,getdate(),getdate())

update T_LioCDC
set Name='Lio.Huang',UpdateTime=getdate()
where ID=7

 已經成功捕捉到資料的變更，對比幾個操作Json，依次是insert、update、delete：

 最後

 　　下班！

 

相關文章