❄️

CHANGES句を利用して効率的に差分データを確認する

2022/12/18に公開

本記事は、Snowflake Advent Calendar 2022 Calendar 2 Day 18の記事になります。
Part1のDay 18は @yuquitta13 さんの「Terraform無しでSnowflakeを始めちゃった人へのTerraform導入ガイド」です。

はじめに

様々なデータソースからのデータを取り込むにあたり、取り込んだデータの件数の妥当性やデータ自体の正しさ・異常値の有無についてチェックする必要があるかと思います。
その際に全レコードに対するチェックを毎回毎回行うのは効率が悪いので（処理時間が長くなる。。。コストが増える。。。）、追加されたデータだけに対してチェックを行う必要がある（行いたい）ユースケースは数多くあるのではないでしょうか？
テーブル自体にロードした日付を定義しているテーブルは対象となるデータの抽出が可能となりますが、ロード日が定義されていないテーブルもあるかと思います。

本記事ではそのようなテーブルに対して有効となるかもしれない？！CHANGES 句を利用した方法をご紹介したいと思います。

CHANGES 句とは

CHANGES 句とは何かドキュメントでは以下のように説明が記載されています。

CHANGES 句を使用すると、明示的なトランザクションオフセットでテーブルストリームを作成しなくても、指定された時間間隔でテーブルまたはビューの変更追跡メタデータをクエリできます。複数のクエリにより、異なるトランザクションの開始と終了の間で変更追跡メタデータを取得できます。

テーブルストリームを利用しなくても、指定した時間内でのデータの変更を捉えることが可能になります。
テーブルストリームには消費機能があるため、あるテーブルのデータ変更を捉えて、他のテーブルを更新するようなユースケースにおいては有効かと思いますが、一定期間の差分データを対象とするチェック処理においてはオブジェクトの追加が不要となるCHANGES 句を利用する事も検討する価値があるかと思います。

テーブルストリームを使用したデータパイプラインについては @uta さんがSnowflake Advent Calendar 2022 Calendar 1 Day 16の記事で紹介してくださっているので、合わせて参照して頂ければと思います。

CHANGES 句の使用における前提事項

CHANGES 句を利用するためには以下の設定が必要になります。テーブルだけではなく、ビューでも使用可能です。

現在、テーブルの変更追跡メタデータが記録される前に、次の少なくとも1つがtrueである必要があります。

テーブルで変更追跡が有効になります（ ALTER TABLE ... CHANGE_TRACKING = TRUE を使用）。

テーブルのストリームが作成されます（ CREATE STREAM を使用）。

どちらのオプションも、変更追跡メタデータを保存する非表示の列をテーブルに追加します。列は少量のストレージを消費します。

ビューの変更データをクエリするには、ソースビューとその基になるテーブルで変更追跡を有効にする必要があります。手順については、ビューと基になるテーブルの変更追跡の有効化をご参照ください。

要求されたデータがTime Travel保持期間（デフォルトは1日）を超えている場合、ステートメントは失敗します。

ディレクトリテーブルまたは外部テーブルの変更（変更追跡メタデータを使用して解決）をクエリする場合、 CHANGES 句はサポートされません。

構文

構文は以下のような形になります。 INFORMATION が DEFAULT では挿入、更新、削除の情報が抽出されますが、 APPEND_ONLY にすると挿入された行のみ抽出されます。

SELECT ...
FROM ...
CHANGES ( INFORMATION => { DEFAULT | APPEND_ONLY } )
AT ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> | STREAM => '<name>' } ) | BEFORE ( STATEMENT => <id> )
[ END( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) ]
[ ... ]

CHANGES 句を試してみる

ここから実際にテーブルに対して　change_trackingを有効化し、CHANGES句を利用した差分データを参照してみたいと思います。　
10億件のデータが存在するテーブルに対して、0.1億件のレコードを追加し、1つのカラムに対して、特定の文字列を含んでいるレコード数を取得してみたいと思います。

準備.1 テーブル作成・データ挿入 10億レコード

検証のターゲットとなるテーブルにchange_trackingを有効化し、10億レコードを挿入

-- Create Table enable change tracking on the table
create or replace TABLE T_CHANGES_TEST   (
	 ID VARCHAR
	,col1 VARCHAR
	,col2 VARCHAR
	,col3 VARCHAR)  
    change_tracking = true;

-- Insert data
 insert into T_CHANGES_TEST
 as select
	 abs(random())%2000000
	,randstr(10, random())
	,randstr(10, random())
	,randstr(10, random())
	from table(generator(rowcount => 100000000));

-- Count table record
select count(1) from T_CHANGES_TEST;

/**
COUNT(1)
1,000,000,000
**/

準備.2 データ追加 0.1億レコード

差分データとして検証するために、0.1億レコードを追加で挿入

set ts1 = (select current_timestamp());

insert into T_CHANGES_TEST
select
    abs(random())%2000000,
    randstr(10, random()),
    randstr(10, random()),
    randstr(10, random())
    from table(generator(rowcount => 10000000));

-- Count table record
select count(1) from T_CHANGES_TEST;

/**
COUNT(1)
1,010,000,000
**/

検証.1 カラム(col1)に対して'abc'を含む値の検索をテーブルに対して実施

まずは'abc'を含む値の検索を行いたいと思います。

-- Count table record
select count(1) from T_CHANGES_TEST
where col1 like '%abc%';

/**
COUNT(1)
33,767
**/

Snowsightのクエリプロファイルを利用してクエリの状況を確認してみます。
テーブルの全てのマイクロパーティションへのアクセスしていることがわかるかと思います。初期構築時等のチェックでは全レコードに対する確認が必要となるケースもあるかと思いますが、日々の運用の中で毎回全レコードに対するチェックは避けたいところ。

Snowsight クエリプロファイルより

検証.2 カラム(col1)に対して'abc'を含む値の検索をCHNAGES 句を使用して実施

次にCHNAGES 句を使用して準備.2で実施した時刻以降に発生した差分データに対する検索を行ってみたいと思います。

-- Count table record
select count(1) from T_CHANGES_TEST
	CHANGES ( INFORMATION =>  DEFAULT )
	at(timestamp => $ts1)
where col1 like '%abc%';
	
/**
COUNT(1)
303
**/

検証.1と同じようにSnowsightのクエリプロファイルを利用してクエリの状況を確認してみます。
検証.1とは違い準備.2で実施した追加データのみに対するチェックとなり、アクセス対象のマイクロパーティションの数が大幅に減りました。 これなら日々の運用でも差分データに対するチェックを効率的に実施出来そうです。

Snowsight クエリプロファイルより

検証.3 テーブルサイズへの影響確認

前提事項に以下の記載をしました。ストレージの追加消費があるとの記載がありますね。

現在、テーブルの変更追跡メタデータが記録される前に、次の少なくとも1つがtrueである必要があります。

テーブルで変更追跡が有効になります（ ALTER TABLE ... CHANGE_TRACKING = TRUE を使用）。

テーブルのストリームが作成されます（ CREATE STREAM を使用）。
- どちらのオプションも、変更追跡メタデータを保存する非表示の列をテーブルに追加します。列は少量のストレージを消費します。

検証.3ではchange_trackingを有効化することによるストレージの増加量はどのくらいになるか確認してみたいと思います。change_trackingを有効化したテーブルとそうでないテーブルを用意し、検証１・２で使用したテーブルからデータをロードしてテーブル間のサイズを比較してみたいと思います。

--Create table enable change tracking
create or replace TABLE T_changes_test_enable_changes   (
	 ID VARCHAR(32) NOT NULL
	,col1 VARCHAR 
	,col2 VARCHAR
	,col3 VARCHAR
	) change_tracking = true;
    
insert into T_changes_test_enable_changes
select * from T_changes_test;
	
--Create table disenable change tracking
create or replace TABLE T_changes_test_disenable_changes   (
	 ID VARCHAR(32) NOT NULL
	,col1 VARCHAR 
	,col2 VARCHAR
	,col3 VARCHAR);

insert into T_changes_test_disenable_changes
select * from T_changes_test;



--count table size
select TABLE_NAME,ROW_COUNT,BYTES
    from information_schema.tables;

/**
TABLE_NAME	                        ROW_COUNT	BYTES
T_CHANGES_TEST_ENABLE_CHANGES	        1,010,000,000	25,729,753,600
T_CHANGES_TEST_DISENABLE_CHANGES	1,010,000,000	25,728,743,936

**/

結果は以下となりました。追加されるサイズは少量であるので、コストへのインパクトは軽微と言って良いのではないかと思います。

\frac{25,729 (MB)}{25,728 (MB)} = 0.000039 %

まとめ

CHANGES 句をデータの差分検出機能として利用することで効率的にデータの参照が可能となりそうです。例えばCHANGES 句を利用して、本日、ロードを行ったデータに対してのみ特定の文字列や想定外の値（定義していない値やNULL、Zero 等)がテーブルに入っていないか等のチェックが効率的に可能になります。
テーブルに日付項目がない場合やストリームオブジェクトが参照できない場合はCHANGES 句を利用してみて頂けると良いかなと思っております。

最後までお読みいただきありがとうございました。