Zenn
👻

SnowflakeのDynamicTableに入門してみた

2023/09/09に公開
Snowflake
tech

Snowproの上位資格を目指すために、まだ試したことないSnowflakeの機能に入門するシリーズです。
今回はずっと前からやろうと思ってたダイナミックテーブルに触れていきたいと思います。

Dynamic Tableとは

https://docs.snowflake.com/ja/user-guide/dynamic-tables-about

ドキュメントより抜粋
動的テーブル は、指定したクエリの結果を具体化したテーブルです。
ターゲットテーブルを別に作成し、そのテーブルのデータを変換および更新するコードを記述するのではなく、ターゲットテーブルを動的テーブルとして定義し、変換を実行する SQL ステートメントを指定することができます。
自動プロセスでは、定期的な（多くの場合、 増分）リフレッシュによって、具体化された結果が自動的にリフレッシュされます。
動的テーブルリフレッシュの詳細については、 動的テーブルリフレッシュについて をご参照ください。

動的テーブルのコンテンツは特定のクエリによって決定されるため、 DML を使用してコンテンツを変更することはできません。
動的テーブルの行を挿入、更新、削除することはありません。自動リフレッシュプロセスは、クエリ結果を動的テーブルに具体化します。

図でイメージすると・・これまでのリアルタイムデータ更新ではStreamとTaskを使うシチュエーションが主でしたが、Dynamic Tableを使えば、StreamとTaskが要らなくなる場合もあります。

上記説明でピンと来なくても大丈夫。私も最初よく分かってなかったです。
でも実際にDynimic Table構築して動作を見れば理解できると思います。

実践Dynamic Table

データ準備

jaffle_shopのデータを使って動作を見ていきたいと思います。
事前に以下のようにSnowflake上にjaffle_shopのデータが存在していることとします。
今回はRAW_CUSTOMERSデータを使います。

tony#COMPUTE_WH@DEMO_DB.STATING>SELECT * from RAW_CUSTOMERS limit 10;
+----+------------+-----------+
| ID | FIRST_NAME | LAST_NAME |
|----+------------+-----------|
|  1 | Michael    | P.        |
|  2 | Shawn      | M.        |
|  3 | Kathleen   | P.        |
|  4 | Jimmy      | C.        |
|  5 | Katherine  | R.        |
|  6 | Sarah      | R.        |
|  7 | Martin     | M.        |
|  8 | Frank      | R.        |
|  9 | Jennifer   | F.        |
| 10 | Henry      | W.        |
+----+------------+-----------+
10 Row(s) produced. Time Elapsed: 0.722s

ベーステーブルの作成

jaffle_shopのデータはdbt検証とかで利用しているので、環境汚れないようにベーステーブルを作成します。
DYNAMICスキーマ配下にraw_customersと言う同じテーブル名で作成しました。

tony#COMPUTE_WH@DEMO_DB.DYNAMIC>create table raw_customers (
                                id integer,
                                first_name VARCHAR,
                                last_name VARCHAR);
+-------------------------------------------+
| status                                    |
|-------------------------------------------|
| Table RAW_CUSTOMERS successfully created. |
+-------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.187s

Dynamic Tableの作成

lagオプションは動的テーブルのコンテンツがベーステーブルの更新から遅れる最大時間を指定します。

例:

  • 動的テーブルのデータが5分以上遅れないようにするには、 5 minutes を指定します。
  • 動的テーブルのデータが5時間以上遅れないようにするには、 5 hours を指定します。

今回はすぐに反映してほしいので1分にしました。

tony#COMPUTE_WH@DEMO_DB.DYNAMIC>CREATE dynamic table dynamic_customers
                                lag = '1 MINUTE'
                                WAREHOUSE = compute_wh
                                as
                                SELECT id , first_name, last_name from RAW_CUSTOMERS;
+-------------------------------------------------------+
| status                                                |
|-------------------------------------------------------|
| Dynamic table DYNAMIC_CUSTOMERS successfully created. |
+-------------------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.376s

ベーステーブルにデータ投入

ベーステーブルにデータがまだ入っていないので、jaffle_shopのRAW_CUSTOMERSテーブルからベーステーブルへINSERTします。

tony#COMPUTE_WH@DEMO_DB.DYNAMIC>INSERT INTO RAW_CUSTOMERS (id ,FIRST_NAME,LAST_NAME) SELECT id,FIRST_NAME,LAST_NAME from DEMO_DB.STATING.RAW_CUSTOMERS;
+-------------------------+
| number of rows inserted |
|-------------------------|
|                     100 |
+-------------------------+
100 Row(s) produced. Time Elapsed: 0.434s
あれこれしている間に1分が経過しdynamic_customersテーブルにデータが取り込まれている （長いので折りたたみ）
tony#COMPUTE_WH@DEMO_DB.DYNAMIC>SELECT * from dynamic_customers order by id;
+-----+------------+-----------+
|  ID | FIRST_NAME | LAST_NAME |
|-----+------------+-----------|
|   1 | Michael    | P.        |
|   2 | Shawn      | M.        |
|   3 | Kathleen   | P.        |
|   4 | Jimmy      | C.        |
|   5 | Katherine  | R.        |
|   6 | Sarah      | R.        |
|   7 | Martin     | M.        |
|   8 | Frank      | R.        |
|   9 | Jennifer   | F.        |
|  10 | Henry      | W.        |
|  11 | Fred       | S.        |
|  12 | Amy        | D.        |
|  13 | Kathleen   | M.        |
|  14 | Steve      | F.        |
|  15 | Teresa     | H.        |
|  16 | Amanda     | H.        |
|  17 | Kimberly   | R.        |
|  18 | Johnny     | K.        |
|  19 | Virginia   | F.        |
|  20 | Anna       | A.        |
|  21 | Willie     | H.        |
|  22 | Sean       | H.        |
|  23 | Mildred    | A.        |
|  24 | David      | G.        |
|  25 | Victor     | H.        |
|  26 | Aaron      | R.        |
|  27 | Benjamin   | B.        |
|  28 | Lisa       | W.        |
|  29 | Benjamin   | K.        |
|  30 | Christina  | W.        |
|  31 | Jane       | G.        |
|  32 | Thomas     | O.        |
|  33 | Katherine  | M.        |
|  34 | Jennifer   | S.        |
|  35 | Sara       | T.        |
|  36 | Harold     | O.        |
|  37 | Shirley    | J.        |
|  38 | Dennis     | J.        |
|  39 | Louise     | W.        |
|  40 | Maria      | A.        |
|  41 | Gloria     | C.        |
|  42 | Diana      | S.        |
|  43 | Kelly      | N.        |
|  44 | Jane       | R.        |
|  45 | Scott      | B.        |
|  46 | Norma      | C.        |
|  47 | Marie      | P.        |
|  48 | Lillian    | C.        |
|  49 | Judy       | N.        |
|  50 | Billy      | L.        |
|  51 | Howard     | R.        |
|  52 | Laura      | F.        |
|  53 | Anne       | B.        |
|  54 | Rose       | M.        |
|  55 | Nicholas   | R.        |
|  56 | Joshua     | K.        |
|  57 | Paul       | W.        |
|  58 | Kathryn    | K.        |
|  59 | Adam       | A.        |
|  60 | Norma      | W.        |
|  61 | Timothy    | R.        |
|  62 | Elizabeth  | P.        |
|  63 | Edward     | G.        |
|  64 | David      | C.        |
|  65 | Brenda     | W.        |
|  66 | Adam       | W.        |
|  67 | Michael    | H.        |
|  68 | Jesse      | E.        |
|  69 | Janet      | P.        |
|  70 | Helen      | F.        |
|  71 | Gerald     | C.        |
|  72 | Kathryn    | O.        |
|  73 | Alan       | B.        |
|  74 | Harry      | A.        |
|  75 | Andrea     | H.        |
|  76 | Barbara    | W.        |
|  77 | Anne       | W.        |
|  78 | Harry      | H.        |
|  79 | Jack       | R.        |
|  80 | Phillip    | H.        |
|  81 | Shirley    | H.        |
|  82 | Arthur     | D.        |
|  83 | Virginia   | R.        |
|  84 | Christina  | R.        |
|  85 | Theresa    | M.        |
|  86 | Jason      | C.        |
|  87 | Phillip    | B.        |
|  88 | Adam       | T.        |
|  89 | Margaret   | J.        |
|  90 | Paul       | P.        |
|  91 | Todd       | W.        |
|  92 | Willie     | O.        |
|  93 | Frances    | R.        |
|  94 | Gregory    | H.        |
|  95 | Lisa       | P.        |
|  96 | Jacqueline | A.        |
|  97 | Shirley    | D.        |
|  98 | Nicole     | M.        |
|  99 | Mary       | G.        |
| 100 | Jean       | M.        |
+-----+------------+-----------+
100 Row(s) produced. Time Elapsed: 0.338s

あらためてベーステーブルにデータを追加してみる

tony#COMPUTE_WH@DEMO_DB.DYNAMIC>INSERT INTO RAW_CUSTOMERS (id ,FIRST_NAME,LAST_NAME) VALUES (101,'Tony','S');
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       1 |
+-------------------------+
1 Row(s) produced. Time Elapsed: 0.420s

1分待つ・・・・

ちゃんと更新分のデータ入ってきています！

tony#COMPUTE_WH@DEMO_DB.DYNAMIC>SELECT * from dynamic_customers where id = 101;
+-----+------------+-----------+
|  ID | FIRST_NAME | LAST_NAME |
|-----+------------+-----------|
| 101 | Tony       | S         |
+-----+------------+-----------+
1 Row(s) produced. Time Elapsed: 0.235s

色々な使い分け

マテビューを使うシチュ

  • さまざまなレベルの集計 (クエリ リライト) が必要な BI ツールでビジュアライゼーションを構築する場合
  • 外部テーブルのパフォーマンスを向上させたいと考えています。
  • 単一のテーブルで単純な集計が必要な場合。
  • データは常にできるだけ早く更新する必要があります。

動的テーブルを使うシチュ

  • SQL ベースの変換パイプラインを構築している場合
  • 変換には、結合、集計、ウィンドウ関数などを含む複雑な SQL が必要な場合
  • 単一テーブル上で変換と集計のパイプラインを構築している場合
  • テーブルをいつ更新するかをより詳細に制御する必要がある場合

ストリームとタスクを使うシチュ

  • Python、Java、または Scala で記述された UDF/UDTF、ストアド プロシージャ、外部関数、および Snowpark 変換を組み込む必要がある場合
  • スケジュールと依存関係の管理に関して柔軟性が必要な場合
  • 増分処理を完全に制御する必要がある場合

課金のお話

忘れちゃいけないのが、課金のお話ですよね。
ダイナミックテーブルはどこで課金が発生するのか理解しておかないと、トンデモ請求が来ちゃいますよね。
以下3つの方法でコストが発生します。

  • ストレージ
    動的テーブルには、他のテーブルと同様にストレージが必要です。ストレージのコストは、テラバイト (TB) あたりの定額料金に基づいています。
  • クラウドサービスコンピューティング
    動的テーブルはクラウド サービス コンピューティングを使用して、基礎となるベース オブジェクトが変更されたときに更新をトリガーします。
    クラウド サービスのコンピューティング コストには、1 日のクラウド サービスのコストがアカウントの 1 日のウェアハウス コストの 10% を超える場合にのみ Snowflake が請求するという制約が適用されます。
  • 仮想ウェアハウスのコンピューティング
    動的テーブルでは、ベース オブジェクトが最初に設定されるとき、およびその後継続的に更新されるときに、ベース オブジェクトに対してクエリを実行するために仮想ウェアハウスが必要です。

ダイナミックテーブルの停止

使わない時はダイナミックテーブルを停止させておきましょう。

tony#COMPUTE_WH@DEMO_DB.DYNAMIC>alter dynamic table dynamic_customers suspend;
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 1.156s

※参考文献
https://snowflakewiki.medium.com/snowflake-dynamic-tables-the-complete-guide-e7b0d5909a81

Discussion