case-kの備忘録

日々の備忘録です。データ分析とか基盤系に興味あります。

Change Trackingを使って、SQL Serverのデータ変更箇所を取得する

差分更新連携を行うために、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/
データベースとテーブル作成

追跡対象のデータベースとテーブルを作ります。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)

変更の追跡の有効化と無効化 - SQL Server | Microsoft Docs

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