はじめに
皆さん、こんにちは！ システムサポートの寺田です。
本記事では、Snowflakeのオブジェクトの1つであるストリーム（Stream）について
まとめたいと思います。
運用例や、運用上の注意点についても記載しているので、
業務でSnowflakeに携わっている方には是非読んでいただきたいです。
本記事でわかること
・ストリームの概要
・ストリームの運用方法
・ストリームの運用上の注意点
ストリームの概要
ストリームについて
ストリームとは、テーブルやビューに対するDML（データ操作）を追跡し、
その変更履歴を記録するオブジェクトです。
ストリームの作成方法
ストリームの作成方法について記載します。
ストリームは1つのテーブルに対して複数作成することができます。
今回は例として、テーブル（STREAM_TABLE）に対して2つのストリーム
（STANDARD_STREAM、APPEND_ONLY_STREAM）を作成する例を示します。
テーブル作成
ストリームを作成する前に、対象となるテーブルを作成します。
作成後、いくつかサンプルデータも入れておきましょう。
CREATE OR REPLACE TABLE STREAM_TABLE (
id NUMBER,
name VARCHAR
);
INSERT INTO STREAM_TABLE VALUES (1,'寺田');
INSERT INTO STREAM_TABLE VALUES (2,'森本');
INSERT INTO STREAM_TABLE VALUES (3,'槇田');ストリーム作成
さて、いよいよストリームを作成します。今回は2種類のストリームを作成します。
1つ目は標準のストリームです。
CREATE OR REPLACE STREAM STANDARD_STREAM ON TABLE STREAM_TABLE;2つ目はAppend-onlyのストリームです。
CREATE OR REPLACE STREAM APPEND_ONLY_STREAM ON TABLE STREAM_TABLE APPEND_ONLY = TRUE;
2つのストリームの違いについて説明します。
| 種別 | 追跡DML | 目的 |
|---|---|---|
| 標準 | INSERT、DELETE、UPDATE | 全DMLを追跡したい場合 |
| Append-only | INSERT | INSERTのみ追跡したい場合 |
つまり、テーブルのすべての変更を追跡したい場合は標準を、
新規挿入のみを追跡したい場合はAppend-onlyのストリームを選ぶと有効です。
ストリームの参照
ストリームはテーブルと同じように以下のようなクエリで参照可能です。
SELECT * FROM STANDARD_STREAM;
SELECT * FROM APPEND_ONLY_STREAM;注意すべき点は、作成時点ではストリームの中身は空であることです。
ストリームで参照できるデータの範囲を理解するには「オフセット」の概念を知っておく必要があります。オフセットに関しては後述します。
ストリームのデータ
テーブルに以下のDMLを発行してみましょう。
INSERT INTO STREAM_TABLE VALUES (4,'大西');
DELETE FROM STREAM_TABLE WHERE id = 2;
UPDATE STREAM_TABLE SET name = '大久保' WHERE id = 1;ストリームの中身を見ていきましょう。
まずは、標準のストリーム（STANDARD_STREAM）になります。
| ID | NAME | METADATA $ACTION | METADATA $ISUPDATE | METADATA $ROW_ID |
|---|---|---|---|---|
| 1 | 大久保 | INSERT | TRUE | 6b450b1b81a03937e0f753c07e32f0af4aff2da5 |
| 4 | 大西 | INSERT | FALSE | d160749cd074c3ef78279e300e2edf9abca5560f |
| 1 | 寺田 | DELETE | TRUE | 6b450b1b81a03937e0f753c07e32f0af4aff2da5 |
| 2 | 森本 | DELETE | FALSE | 7a8cd30cfc770ad8753a87e2c8480208f79dad19 |
各カラムについて説明します。
| カラム | 説明 |
|---|---|
| ID | テーブルデータ（STREAM_TABLE) |
| NAME | テーブルデータ（STREAM_TABLE) |
| METADATA$ACTION | INSERT or DELETE |
| METADATA$ISUPDATE | TRUE or FALSE |
| METADATA$ROW_ID | 変更データに対する一意のID |
ID、NAMEには追跡対象のテーブル（STREAM_TABLE）のデータが格納されています。
そして、変更データのDML種類を見極めるには
METADATA$ACTION、METADATA$ISUPDATEを見る必要があります。
INSERT、DELETE、UPDATE の際のストリームデータの表示方法を表で整理すると分かりやすいです。
| DML種類 | METADATA$ACTION | METADATA$ISUPDATE |
|---|---|---|
| INSERT | INSERT | FALSE |
| DELETE | DELETE | FALSE |
| UPDATE（更新後データ） | INSERT | TRUE |
| UPDATE（更新前データ） | DELETE | TRUE |
上記を参考に、もう一度 STANDARD_STREAM の結果を確認してみましょう。
次に、Append-onlyのストリーム（APPEND_ONLY_STREAM）のデータを見ていきましょう。
| ID | NAME | METADATA $ACTION | METADATA $ISUPDATE | METADATA $ROW_ID |
|---|---|---|---|---|
| 4 | 大西 | INSERT | FALSE | d160749cd074c3ef78279e300e2edf9abca5560f |
INSERT の変更データのみ保存されています。
ストリームの運用方法
オフセットについて
ストリームのデータ保持範囲を理解するために必要なオフセットについて解説します。
オフセットとは、変更データキャプチャをどこまで読み込んだかを記録する位置情報です。
オフセットが進むと、オフセット以前の変更データキャプチャは表示されなくなります。
オフセットを進める唯一の方法は、ストリームをソースにして
DML（例：INSERT INTO STREAM_TABLE SELECT * FROM STANDARD_STREAM など）
を実行し、そのトランザクションがコミットされることです。
運用例
ストリームを使用した運用例としてストアドプロシージャを作成します。
今回作成するプロシージャは、テーブルにINSERTがあった際に、
1日のインサート件数をメールで通知するものです。
テーブルとストリームは先ほど作成したものを使用します。
CREATE OR REPLACE PROCEDURE send_task_count()
RETURNS VARCHAR
LANGUAGE SQL
AS
DECLARE
num NUMBER;
mail_body VARCHAR;
BEGIN
SELECT COUNT(*) INTO num FROM APPEND_ONLY_STREAM;--ストリーム内の件数を変数に格納
INSERT INTO STREAM_TABLE(id)
SELECT id
FROM APPEND_ONLY_STREAM
WHERE 1 = 0;--オフセットを進めるために、ダミーのDMLを発行
mail_body := '本日は'||:num ||'件の挿入がありました。';
CALL SYSTEM$SEND_EMAIL(
'mail_rule',
'y-terada@sts-inc.co.jp',
'本日のインサート件数',
:mail_body
);
RETURN '完了';
END;作成したプロシージャを実行してみます。
APPEND_ONLY_STREAMに入っている変更データは2件でした。
CALl send_task_count();以下のようなメールが届きました。
ストリーム内のデータ件数が反映されています。
次に、APPEND_ONLY_STREAM がどうなっているかを確認します。
オフセットが進んでおり、ストリームの中身は空になっています。
運用上の注意点
追跡対象のテーブルをRENAMEした時
ストリームは対象のテーブルのテーブルIDを参照しているため、
追跡対象のテーブルをRENAMEした場合も、ストリームを再作成する必要はありません。
ストリームの保持期間
ストリームが参照する変更履歴はソーステーブルの
データ保持期間（Time Travel）に依存します。
保持期間を超えた履歴は参照できなくなるため、
参照までに遅延が発生する運用では保持期間を十分に確保してください。
まとめ
・ストリームは、テーブル／ビューに対するDMLの変更（CDC）
を参照するためのオブジェクトです。
・標準ストリーム（INSERT/DELETE/UPDATEを追跡）とAppend-only（INSERTのみ追跡）
は用途に応じて選びます。
・ストリームのオフセットは読み取りだけでは進みません。
ストリームをソースにしたDMLを実行しコミットすることでオフセットが進み、
オフセット以前の変更は見えなくなります。
・ストリームの変更履歴はTime Travel（保持期間）に依存します。
以上を踏まえ、ストリームを使用する際は
要件に応じたストリーム種別の選定と、オフセット管理、
およびTime Travel（保持期間）の確認に気を付けることが望ましいです。
以上
コメント