差分更新連携を行うために、SQL Serverのデータ変更追跡機能を使ってみました。
SQL Serverからリアルタイムに連携するために、差分更新機能を検討しています。
SQL Serverの場合、変更の追跡方法は次の2つの方法があります。どちらも変更箇所を特定するための機能ではありますが、機能の比較記事はまた別途作れたらと思います。
今回はChange Trackingを使って更新箇所を特定します。
- CDC(Change Data Capture)
- CT(Change Tracking)
実行環境を準備
CTを使うためにデータベースとテーブルを有効化する必要があります。有効にするデータベースとテーブルをあらかじめ作っておきます。
コンテナを起動
SQLServerのコンテナを起動します。
# dokcer-compose.yml version: '3' services: sql-server: image: mcr.microsoft.com/mssql/server:2017-latest tty: true container_name: sqlserver ports: - 1433:1433 environment: - ACCEPT_EULA=Y - SA_PASSWORD=<password> volumes: - .://usr/src/app working_dir: /usr/src/app/ sql-server-tool: image: mcr.microsoft.com/mssql-tools tty: true container_name: sqlserver-tool volumes: - .://usr/src/app working_dir: /usr/src/app/
- image: mcr.microsoft.com/mssql/server:2017-latest: SQL Serverコンテナ
- image: mcr.microsoft.com/mssql-tools: SQL Serverの操作ツール(bcp)など
データベースとテーブル作成
追跡対象のデータベースとテーブルを作ります。CTを行うためにテーブルに主キーは必須です。
コンテナに入る
docker exec -it sqlserver-tool bash
SQLServerに入り、データベースとテーブルを作ります。
sqlcmd -S sqlserver -U sa -P <password> -i /usr/src/app/sql/create_table.sql
# /usr/src/app/sql/create_table.sql CREATE DATABASE TestDB GO USE TestDB GO CREATE TABLE TestTb(id int IDENTITY(1,1) PRIMARY KEY , TIMESTAMP nvarchar(60) NOT NULL, LATITUDE nvarchar(60) NOT NULL, LONGITUDE nvarchar(60) NOT NULL,FREEWAY_ID nvarchar(60) NOT NULL,FREEWAY_DIR nvarchar(60) NOT NULL,LANE nvarchar(60) NOT NULL,SPEED nvarchar(60) NOT NULL) GO alter table TestTb add id int IDENTITY(1,1) GO
SQL AUTO INCREMENT a Field
テーブルの列を自動インクリメント列(自動採番列)に設定する - SQL Server Tips
変更の追跡について
データ変更の追跡 - SQL Server | Microsoft Docs
データを追加
変更箇所のみ取得したいので事前にデータを登録しておきます。BCPコマンドを使い対象のテーブルにデータをロードします。
bcp TestTb in sensor_obs2008_500.csv -S sqlserver -d TestDB -U sa -P <password> -q -c -t ,
Change Trackingを使ってみる
環境が整ったのでChange Trackingを使ってみたいと思います
Change Trackingの有効化
追跡対象のデータベースとテーブルを有効化する必要があります。
データベースの有効化
ALTER DATABASE TestDB SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
テーブルの有効化
ALTER TABLE TestTb ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)
Change Trackingを使って差分を取得する
Change Trackingを使い追加したデータを取得します。
declare @synchronization_version bigint; SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION(); SELECT * FROM TestTb GO
データをロードします。
bcp TestTb in sensor_obs2008_500.csv -S sqlserver -d TestDB -U sa -P <password> -q -c -t ,
変更箇所を取得する。
declare @last_synchronization_version bigint; SET @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION() -1; SELECT CT.id, CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT FROM CHANGETABLE(CHANGES TestTb , @last_synchronization_version) AS CT GO
新しく追加したレコードの主キーが取れることを確認します。
id ----------- 251 252 253 254 255 256 257 258 259 260 261 262 263 264