SQL Database で CDC がプレビューで使用できるようになったので検証してみました at SE の雑記

Introducing Change Data Capture for Azure SQL Databases (Public Preview) でアナウンスがありましたが、S3 以上の SQL Database ではプレビュー機能として CDC (Change Data Capture : 変更データキャプチャ) が使用できるようになりました。

CDC について結構忘れていたので、この機会にまとめておきたいと思います。

「変更の追跡」と「変更データキャプチャ」の違い

SQL Server にはデータの変更とをトラッキングする機能として「変更の追跡」(Change Tracking) と「変更データキャプチャ」(CDC)という 2 種類の機能を使用することができます。

どちらも「テーブルに対してデータに対しての変更を追跡するための機能」となりますが、実装方法や、取得可能なデータは大きく異なります。(両機能ともに、機能の有効化は、DB に対して実施するのですが、「どのテーブルを対象とするか」を設定する必要があり、変更の追跡を行うかどうかについては、テーブル単位で指定する必要があります)

変更の追跡

変更の追跡は、「どのような操作によりデータの変更が行われたのか」を取得することができますが、「データがどのように変更されたのか (変更前 / 変更後のデータの比較)」というようなことはできません

変更の追跡を有効化するテーブルについては「主キーが必須」となっており、主キーを設定していないテーブルについては、変更の追跡を有効にすることができません。

変更の追跡については、トリガーのような処理で実装が行われており「データの変更が行われた場合は、変更対象となったデータの主キーの情報を追跡用のテーブルに INSERT する」というような形で処理が行われます。

これは、実行プランからも確認することができます。

変更の追跡が有効になっているテーブルについては、「change_tracking_<オブジェクト ID>」という変更の追跡用のテーブルが自動的に作成され、データの変更が行われると、このテーブルに対してデータが挿入されます。

変更されたデータについては「CHANGETABLE」を使用して、取得することができますが、取得できるのは「どのキーのデータが変更されたか」の情報となり、変更後のデータについてはベースとなるテーブルと JOIN することによって取得する必要があります。

変更の追跡のデータを格納するテーブルには、実データの項目は「主キーの値」が格納されるため変更の追跡を有効化するテーブルには「主キーが必須」となります。

また、変更系のクエリを実行した際に変更の追跡のテーブルに対してもデータの投入を行うため「変更系のクエリのスループットに多少影響を与える」というようなことも特徴としてあります。(単純な Insert ですので、1ms あるか無いかぐらいのオーバーヘッドだとは思いますが)

変更データキャプチャ

変更データキャプチャ (CDC) は、変更の追跡と比較すると次のような特徴があります。

主キーが無くても有効にすることができる
バックグラウンドでトランザクションログから、変更データの履歴を作成するため、更新系のクエリには直接のオーバーヘッドはない
「変更前後」のデータを格納するため、「UPDATE によってどのようにデータが変更されたか」「DELETE したデータにはどのような内容が格納されていたのか」を確認することができる

CDC は主キーが無くても設定することができるので、ヒープのテーブルなどにも適用することができます。(CDC のデータを使用して、他にデータ連携をする場合は主キーが無いと厳しケースがありますが)

履歴のデータについても、トランザクションレプリケーションと同様のテクノロジを使用して、バックグラウンドでトランザクションログの内容をキャプチャしますので、更新系のクエリに直接のオーバーヘッドはありません。
ただし、トランザクションログをキャプチャするのはセミリアルタイム程度の間隔になるため、履歴用のデータが生成されるのには多少のタイムラグがあります。

また、変更前後のデータが履歴データとして格納されるため、変更の追跡と比較すると設定後のに履歴で必要となるデータサイズについては、CDC の方が大きくなります。

以下の画像は DELETE した際の履歴データの格納内容となります。
CDC の場合、「どのようにデータが変更されたか」は履歴データで完結しますので元のテーブルの情報と JOIN する必要はありません。
変更されたデータについては履歴テーブルのみを確認することで情報の取得を行うことができます。

変更の追跡と変更データキャプチャの使い分け

どのように使い分けるかについては悩ましいところではありますが、

データの変更は、変更対象となったデータの主キーから、ベーステーブルの「現在のデータ」を取得できれば問題ない
履歴テーブルのサイズを抑えたい
テーブルのスキーマ変更を柔軟に実施したい

ということであれば、変更の追跡の方が向いているかと思います。

変更の追跡は「主キーの値」のみが履歴テーブルに格納されますので、主キー以外のテーブル定義に変更があったとしても、履歴テーブルには影響を与えません。

CDC については、「CDC を有効にしたタイミングのテーブル構造を元に履歴テーブルを作成する」という動作になりますので、新しく列を追加した場合は、追加した列の情報は履歴テーブルには反映されません。

これについてはソーステーブルに対する変更の処理でも触れられています。

CDC のキャプチャインスタンス (履歴テーブル) は 2 つまで作成することができますので、テーブルレイアウトが変更される場合には、変更後のテーブルレイアウトが作成されたタイミングで、新しいキャプチャインスタンスを作成し、変更後のテーブルレイアウトの履歴テーブルを作成する必要があります。

CDC は変更の追跡より、リッチな履歴テーブルを保持していますが、テーブルのレイアウト変更を伴う場合の運用については、変更の追跡より複雑になりますので、「ベースとなるテーブルに対してどのようなオペレーションが発生するか」は意識しておいた方がよいかと思います。

SQL Database の変更データキャプチャ

使ってみた感じ、SQL Database の変更データキャプチャは、SQL Server とほぼ同じように見えます。
変更データキャプチャ関連のテーブル等については、以下に記載されていますが、これらは SQL Database でも利用することができます。

SQL Database では、msdb にアクセスができないため、一部のテーブルはユーザーデータベースに格納されている
キャプチャのジョブがユーザーから見える SQL Server エージェントジョブではなく、バックグラウンドで動作しているようである
sp_cdc_change_job 実行後の stop / start は自動的に実施されている

というような違いはありそうですが、基本的な操作は SQL Server と同様に実施することができました。

CDC が有効になっているかや、データベース単位の有効化については次にクエリで実行でき、

-- CDC 有効化状態の確認
SELECT name, is_cdc_enabled FROM sys.databases
GO

-- CDC の有効化
EXEC sys.sp_cdc_enable_db
GO 

-- CDC のオブジェクトの確認
SELECT s.name,s.schema_id,o.name,o.type_desc
FROM sys.schemas AS s
    INNER JOIN sys.all_objects AS o
        ON o.schema_id = s.schema_id
WHERE s.name = 'cdc'
ORDER BY o.type_desc ASC, o.name ASC
GO

-- CDC テーブルの情報
SELECT * FROM cdc.change_tables
SELECT * FROM cdc.lsn_time_mapping
SELECT * FROM cdc.cdc_jobs
GO

テーブル単位の有効化 / 無効化については次のクエリで実行できます。

-- CDC の無効化 (対象テーブルの設定)
EXEC sys.sp_cdc_disable_table  
@source_schema = N'dbo',  
@source_name   = N'orders',  
@capture_instance = N'dbo_orders'  
GO

-- CDC の有効化 (対象テーブルの設定)
EXEC sys.sp_cdc_enable_table  
@source_schema = N'dbo',  
@source_name   = N'orders',  
@role_name     = NULL ,
@supports_net_changes = 1

ジョブ (キャプチャ / クリーンアップ) の設定は次のようなクエリで変更が可能です。

-- CDC のジョブの設定
SELECT * FROM cdc.cdc_jobs

-- ジョブの変更 (SQL DB の場合、sp_cdc_stop_job / sp_cdc_start_job の明示的な再起動は不要)
EXEC sys.sp_cdc_change_job @job_type = 'cleanup', @retention='30'

変更テーブルのアクセスについても、SQL Server と同様に利用することができます。

DECLARE  @from_lsn binary(10), @to_lsn binary(10);

SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_orders');
SET  @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal',GETDATE())

SELECT *
FROM cdc.fn_cdc_get_net_changes_dbo_orders(@from_lsn, @to_lsn, 'all') 
ORDER BY 1

変更テーブルに対して LSN を指定することで、指定した LSN を「含む」変更データの取得を行うことができます。(変更の追跡の「CHANGETABLE」は、指定した last_sync_version 「より大きい」変更を取得し、CDC と変更の追跡は境界値の取り扱いが異なる点については注意する必要があります)

CDC が有効なテーブルについては上記のようなクエリで LSN を指定することで、指定した LSN の範囲の変更データを取得することができます。

主キーがある場合は、有効化する際に「@support_net_changes = 1」を指定することができ、この指定を行った場合には、「cdc.fn_cdc_get_all_changes」ではなく、「cdc.fn_cdc_get_net_changes」を使用することができます。

変更データを生成するためのキャプチャプロセスはトランザクション単位に変更データを生成しますので、同一レコードに対して複数の変更を行った場合は、各変更毎に変更テーブル上にレコードが作成されます。

指定した LSN の範囲で複数の変更が行われた場合「最終的な状態のレコード」があれば、どのように変更されたかを把握することが可能です。

このような「最終的なレコードの状態」を取得する場合には「cdc.fn_cdc_get_net_changes」を使用します。(CDC の変更テーブルと MERGE ステートメントを組み合わせる場合は、「cdc.fn_cdc_get_net_changes」を使用しないと、複数の変更を正しく MERGE 先のターゲットに伝搬させることができません)

変更データキャプチャの活用方法

CDC の活用方法ですが、Azure Data Factory を使用したハイブリット ETL や変更データキャプチャ (CDC) を使用して Azure SQL Managed Instance から Azure Storage へのデータの増分読み込みを行うで解説が行われています。

CDC で取得した変更データを他のデータストレージに連携するというような活用を行うことができます。

docs のドキュメントでは Data Factory を使用していますが、今回は基本的な動作を確認するために同一 DB 上のテーブルに対して変更データを流してみたいと思います。

CDC により連携を行うのは、TPC-C の orders テーブルを使用してみます。

CDC の有効化

最初に orders テーブルで CDC を有効化します。

今回、orders テーブルは主キーのあるテーブルですので「@support_net_changes = 1」で設定を行っています。

-- CDC の有効化 (対象テーブルの設定)
EXEC sys.sp_cdc_enable_table  
@source_schema = N'dbo',  
@source_name   = N'orders',  
@role_name     = NULL ,
@supports_net_changes = 1

連携先のテーブルの作成

CDC の有効化が終わったら、初期データを投入した連携先のテーブルを作成します。

DROP TABLE IF EXISTS orders2;

SELECT * INTO orders2 FROM orders;
ALTER TABLE orders2 ADD CONSTRAINT PK_orders2 PRIMARY KEY CLUSTERED (o_id ASC,o_d_id ASC,o_w_id ASC)

LSN 管理用テーブルの作成

定期的にデータを連携する場合「今、どこまで連携を行っているか」の Water Mark の情報が必要となりますので管理用のテーブルを作成しておきます。

DROP TABLE IF EXISTS water_mark;
CREATE TABLE water_mark (source_object_name varchar(255) PRIMARY KEY, from_lsn binary(10))

処理対象となる LSN の取得

どの範囲の LSN のデータを変更テーブルから取得するかの判断を行います。

water_mark テーブルに対象テーブルのレコードが存在していない場合は、LSN の最小値 (CDC を有効にしたタイミングの LSN) を取得し、既にレコードが登録されている場合は、登録されている LSN を FROM として取得します。

TO については、クエリを実行したタイミングで生成されているトランザクションの最大値を取得し、この範囲の変更データを処理対象とします。

DECLARE  @from_lsn binary(10), @to_lsn binary(10);
IF EXISTS(SELECT 1 FROM water_mark WHERE source_object_name = 'orders')
BEGIN
    SET @from_lsn = (SELECT from_lsn FROM water_mark WHERE source_object_name = 'orders')
END
ELSE
BEGIN
    SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_orders');
END
SET  @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal',GETDATE())

変更対象の取得と反映

どの範囲までデータを取得するかが決まったら、実際にデータの取得を行います。

-- ①
IF @from_lsn <> @to_lsn
BEGIN
-- ②
    -- 取得した LSN 自身は前回処理時に対象となっているため、インクリメント
    SET @from_lsn = sys.fn_cdc_increment_lsn(@from_lsn) 

-- ③
    MERGE orders2 AS target
    USING(
        SELECT __$operation, o_id,o_d_id,o_w_id,o_c_id,o_carrier_id,o_ol_cnt,o_all_local,o_entry_d
        FROM cdc.fn_cdc_get_net_changes_dbo_orders(@from_lsn, @to_lsn, 'all') 
    ) AS source
    ON 
        source.o_id = target.o_id
        AND source.o_d_id = target.o_d_id
        AND source.o_w_id = target.o_w_id
    WHEN MATCHED AND source.__$operation = '1' THEN 
        DELETE
    WHEN MATCHED AND source.__$operation = '4' THEN
        UPDATE SET 
            target.o_c_id = source.o_c_id
            ,target.o_carrier_id = source.o_carrier_id
            ,target.o_ol_cnt = source.o_ol_cnt
            ,target.o_all_local = source.o_all_local
            ,target.o_entry_d = source.o_entry_d
    WHEN NOT MATCHED THEN
        INSERT VALUES(source.o_id, source.o_d_id, source.o_w_id, source.o_c_id, source.o_carrier_id, source.o_ol_cnt, source.o_all_local, source.o_entry_d)
    ;
-- ④
    -- Water Mark の更新
    MERGE water_mark AS target
    USING(
        SELECT 'orders' AS source_object_name, @to_lsn AS to_lsn
    ) AS source
    ON source.source_object_name = target.source_object_name
    WHEN MATCHED THEN
        UPDATE SET from_lsn = source.to_lsn
    WHEN NOT MATCHED THEN
        INSERT VALUES('orders', @to_lsn)
    ;
END

①データの変更が行われていない場合、FROM の LSN と TO の LSN が同一になっています。(前回の連携からデータが更新されていないケース)

そのため、FROM と TO が同一であった場合は、処理は行わないようにしています。

②FROM と TO が同一でない場合は、前回の処理から何らかの変更が発生していますので、変更内容を orders2 に反映させるようにします。

この際、water_mark から取得した LSN ですが、取得した LSN のトランザクションについては、前回の処理時に処理対象となっています。(CDC の変更テーブルの取得時に指定した LSN も変更対象として含まれるため)

そのため、取得する変更データは「前回処理した LSN の次の LSN」とするため、開始点となる LSN については、インクリメントしてからデータの取得を行っています。

③これで変更データの取得範囲が決まりましたので、「cdc.fn_cdc_get_net_changes」から変更データの取得を行っています。

「cdc.fn_cdc_get_net_changes」を使用していますので、指定した LSN 内で複数の変更があった場合、「最終的な形の変更データ」が取得され、主キー毎に 1 レコードにサマライズされた変更レコードとなっています。

変更対象のレコードが取得できたら、その変更対象を MERGE ステートメントを使用して、orders2 に反映を行っています。

④反映が完了したら今回使用した TO の LSN を warter_mark テーブルに格納し、次回処理時の起点とします。

これで一連の流れが完了です。

上記のような処理を定期的に実行することで、変更内容を反映できるかと。

HammerDB でTPC-C のワークロードを実行しながら、同期を行ってみましたが、次のクエリの結果としては差分はなさそうでした。

select * from orders2
EXCEPT
select * from orders
ORDER BY 1


SELECT COUNT(*) FROM orders
SELECT COUNT(*) FROM orders2

CDC についてはアナウンス内に次の記載があります。

Stay up to date with the change data capture blog series

We are happy to start a bi-weekly blog series for customers who’d like to learn more about enabling CDC in their Azure SQL Databases! This series will explore different features/services that can be integrated with CDC to enhance change data functionality.

Some of the upcoming CDC blog series:

Deep Dive into Change Data Capture in Azure SQL Databases

Using Azure Data Factory to send Change Data Capture Data to Other Destinations

今後、情報が公開されるようですので期待ですね。

月	火	水	木	金	土	日
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30

SE の雑記