Snowflakeのストリームはどの操作によりレコードが消えるか確かめてみた #SnowflakeDB

※本エントリは、Snowflakeをより使いこなそう！ Advent Calendar 2021の1日目の記事となります。

さがらです。

Snowflakeのストリームに対して、どういった操作を行うとストリーム内のデータが削除されるのか、確かめてみました。

ストリームとは

ストリームはテーブルやビュー等と同じように扱えるオブジェクトの1種です。このストリームをテーブルに対して設定すると、そのテーブルの変更履歴をストリームに追跡・記録してくれます。「変更データキャプチャ(Change Data Capture)」という手法・仕組みを実現できるものとなっています。

Snowpipeやタスクと組み合わせることで、「新規または変更のあったレコードだけに処理を行って、その処理結果を分析用のテーブルにいれたい」「有効・無効フラグを付けてこれまでのレコード履歴を全て保持するテーブルを作りたい」等のようなことをしたい時に役立つ機能です。

弊社でも検証を行っておりますので、ぜひこちらも読んで頂けると良いと思います。

ストリームの特徴とこのブログで試すこと

ストリームは対象テーブルの変更を追うことができるのですが、ストリームを用いたDML(データ操作言語)を発行すると、ストリーム上のレコードが消えるという仕様があります。

ただ、DMLといっても、SELECT、INSERT、など様々なコマンドがあります。

そこで、ストリームに対してどういったDMLを実行するとストリーム上のレコードが消えるのか、確かめてみました。

その１：SELECT

まずは、定番のSELECT文から試してみます。

下記のクエリを実行して、テーブルとストリームを作ってみます。

CREATE DATABASE stream_test;

CREATE TABLE sample_tbl AS
    SELECT
        1 as no;

CREATE STREAM sample_stream ON TABLE sample_tbl;

INSERT INTO sample_tbl
    SELECT
        2 as no;

この状態で下記のようにSELECT文を2回ストリームに対して実行して、1回目のSELECT文でストリームの内容が消えていないか確かめてみます。

-- 1回目のSELECT
SELECT * FROM sample_stream;

-- 2回目のSELECT
SELECT * FROM sample_stream;

結果：ストリームのデータは消えませんでした！

まあ、ストリームの内容を確かめたくて実行したSELECT文でストリームの内容が消えてしまったら、やっていられないですよね…笑

その２：ストリームに対してSELECT *した結果のINSERT

次は、ストリームに対するSELECT文を用いたINSERT文を実行してみます。

まずは前準備として下記のクエリを実行します。その１との違いは、ストリームに保持されている履歴データを保持するテーブルsample_tbl_historyも作成します。

CREATE OR REPLACE DATABASE stream_test;

CREATE OR REPLACE TABLE sample_tbl AS
    SELECT
        1 as no;

CREATE OR REPLACE STREAM sample_stream ON TABLE sample_tbl;

INSERT INTO sample_tbl
    SELECT
        2 as no;

CREATE OR REPLACE TABLE sample_tbl_history (
  no number,
  dml_type varchar(10)
);

この時点で、ストリームには1レコードだけ登録されています。

この状態で、ストリームに対するSELECT文を含むINSERT文を実行し、ストリームにデータが残っているかを確認してみます。

INSERT INTO sample_tbl_history
    SELECT
        no,
        METADATA$ACTION
    FROM
        sample_stream;

SELECT * FROM sample_stream;

結果：ストリームのデータは全て消えました！

INSERTと併せて使うと、消えてしまうようです。

その３：ストリームに対してフィルタをかけた結果のINSERT

次は同じINSERTでも、ストリームに対してWHERE句でフィルタをかけた結果をINSERTすることを考えてみます。

前準備として下記のクエリを実行します。その２との違いは、2レコードINSERTしていることです。

CREATE OR REPLACE DATABASE stream_test;

CREATE OR REPLACE TABLE sample_tbl AS
    SELECT
        1 as no;

CREATE OR REPLACE STREAM sample_stream ON TABLE sample_tbl;

INSERT INTO sample_tbl
    SELECT
        3 as no;

INSERT INTO sample_tbl
    SELECT
        4 as no;

この時点で、ストリームには2レコード登録されています。

この状態で、WHERE句を含むストリームに対するSELECT文を含むINSERT文を実行し、ストリームにデータが残っているかを確認してみます。

INSERT INTO sample_tbl_history
    SELECT
        no,
        METADATA$ACTION
    FROM
        sample_stream
    WHERE
        no = 3;

結果：ストリームのデータは全て消えました！

なんと、WHERE句で絞られて出力されるレコードだけではなく、全てのレコードがストリームから消えてしまいました。

その４：ストリームを副問合せとして用いたDELETE

次は、ストリームを副問合せとして用いたDELETE文を実行してみます。

前準備として下記のクエリを実行します。sample_tbl_2という、「3」だけ登録したDELETE文実行用のテーブルも用意していることがポイントです。

CREATE OR REPLACE DATABASE stream_test;

CREATE OR REPLACE TABLE sample_tbl AS
    SELECT
        1 as no;

CREATE OR REPLACE STREAM sample_stream ON TABLE sample_tbl;

INSERT INTO sample_tbl
    SELECT
        3 as no;

INSERT INTO sample_tbl
    SELECT
        4 as no;

CREATE OR REPLACE TABLE sample_tbl_2 AS
    SELECT
        3 as no;

この時点で、ストリームには2レコード登録されています。

この状態で、下記のようにストリームを副問合せとして用いたDELETE文を実行してみます。

DELETE FROM sample_tbl_2 tbl2
WHERE EXISTS (SELECT 'aaa' FROM sample_stream str WHERE tbl2.no = str.no);

結果：ストリームのデータは全て消えました！

その３と同様に、ストリームにおいて関連のあるレコードは一部だけであっても、全てのレコードがストリームから消えてしまいました。

まとめ

ということで、とても簡単な検証でしたがまとめとなります。

• ストリームをSELECTするだけでは、ストリームのデータは消えない。
• ストリームを用いたINSERT、DELETEを用いると、対象のストリームの一部のレコードだけを参照していても、全てのストリームのレコードが消える

ストリームを用いたINSERTやDELETEを行う際は、WHERE句などにより絞り込まれたレコードだけが消えるわけではなく、ストリーム上の全てのレコード消えてしまうので注意しましょう！

次回

Snowflakeをより使いこなそう！ Advent Calendar 2021、次回の2日目では、「Snowflakeのタイムトラベル関連でよく使いそうなコマンドを試してみた」というタイトルで執筆します。お楽しみに！