動的テーブルを作成する¶

例:テーブルの一部からのバックフィル¶

次の例では、my_backfill_table から my_dynamic_table の不変リージョンを、さらに動的テーブルの定義から可変リージョンをバックフィルします。

このシナリオでは、動的テーブルが増分リフレッシュモードで作成された場合、再初期化によって可変のすべての行が削除され、可変のリージョンのみが再入力されます。動的テーブルがフルリフレッシュモードで作成された場合、フルリフレッシュは同じ効果でトリガーされます。

CREATE DYNAMIC TABLE my_dynamic_table (day TIMESTAMP, totalSales NUMBER)
  IMMUTABLE WHERE (day < '2025-01-01')
  BACKFILL FROM my_backfill_table
  TARGET_LAG = '20 minutes'
  WAREHOUSE = 'mywh'
  AS SELECT DATE_TRUNC('day', ts) AS day, sum(price)
    FROM my_base_table
    GROUP BY day;

例:バックフィルを使用して、動的テーブルのデータを回復または変更する¶

動的テーブルのデータや定義を直接編集することはできません。データを回復または修正するには、次の回避策の手順を完了します。

1. 動的テーブルを通常のテーブルにクローンします。

2. 必要に応じて、クローンされたテーブルを変更します。

3. 編集されたテーブルから新しい動的テーブルにバックフィルします。

次の例では my_dynamic_table が sales ベーステーブルから日次販売データを集計します。

CREATE OR REPLACE TABLE sales(item_id INT, ts TIMESTAMP, sales_price FLOAT);

INSERT INTO sales VALUES (1, '2025-05-01 01:00:00', 10.0), (1, '2025-05-01 02:00:00', 15.0), (1, '2025-05-01 03:00:00', 11.0);
INSERT INTO sales VALUES (1, '2025-05-02 00:00:00', 11.0), (1, '2025-05-02 05:00:00', 13.0);


CREATE DYNAMIC TABLE my_dynamic_table
  TARGET_LAG = 'DOWNSTREAM'
  WAREHOUSE = mywh
  INITIALIZE = on_create
  IMMUTABLE WHERE (day <= '2025-05-01')
  AS
    SELECT item_id, date_trunc('DAY', ts) day, count(sales_price) AS sales_count FROM sales
    GROUP BY item_id, day;

SELECT item_id, to_char(day, 'YYYY-MM-DD') AS day, sales_count FROM my_dynamic_table;

+---------+------------+-------------+
| ITEM_ID | DAY        | SALES_COUNT |
|---------+------------+-------------|
| 1       | 2025-05-01 | 3           |
| 1       | 2025-05-02 | 2           |
+---------+------------+-------------+

オプションで、古いデータをアーカイブしてストレージコストを節約できます。

DELETE FROM sales WHERE ts < '2025-05-02';

ALTER DYNAMIC TABLE my_dynamic_table REFRESH;

SELECT item_id, to_char(day, 'YYYY-MM-DD') AS day, sales_count FROM my_dynamic_table;

その後、2025-05-01 で販売エラーを見つけます。条件: sales_count は2である必要があります。これを修正するには

1. my_dynamic_table を通常のテーブルにクローンします。

   CREATE OR REPLACE TABLE my_dt_clone_table CLONE my_dynamic_table;
   

   Copy

2. クローンされたテーブルを更新します。

   UPDATE my_dt_clone_table SET
     sales_count = 2
     WHERE day = '2025-05-01';
   
   SELECT item_id, to_char(day, 'YYYY-MM-DD') AS day, sales_count FROM my_dt_clone_table;
   

   Copy

   +---------+------------+-------------+
   | ITEM_ID | DAY        | SALES_COUNT |
   |---------+------------+-------------|
   | 1       | 2025-05-01 | 2           |
   | 1       | 2025-05-02 | 2           |
   +---------+------------+-------------+
   

3. 編集されたクローンをバックフィルのソースとして使用し、動的テーブルを再作成します。

   CREATE OR REPLACE DYNAMIC TABLE my_dynamic_table
     BACKFILL FROM my_dt_clone_table
     IMMUTABLE WHERE (day <= '2025-05-01')
     TARGET_LAG = 'DOWNSTREAM'
     WAREHOUSE = mywh
     INITIALIZE = on_create
     AS
       SELECT item_id, date_trunc('DAY', ts) day, count(sales_price) AS sales_count FROM sales
       GROUP BY item_id, day;
   

   Copy

   このアプローチにより、ベーステーブルを変更することなく、動的テーブルのデータを回復または修正できます。

   SELECT item_id, to_char(day, 'YYYY-MM-DD') AS day, sales_count FROM my_dynamic_table;
   

   Copy

   +---------+------------+-------------+
   | ITEM_ID | DAY        | SALES_COUNT |
   |---------+------------+-------------|
   | 1       | 2025-05-01 | 2           |
   | 1       | 2025-05-02 | 2           |
   +---------+------------+-------------+
   

例:バックフィルを使用して動的テーブルのスキーマを変更する¶

動的テーブルのスキーマを直接変更することはできません。スキーマを更新する場合（たとえば、列を追加するなど）は、次のステップに従います。

1. 動的テーブルを通常のテーブルにクローンします。次の例では、sales`（:ref:`上記 <label-data-surgery-example>）から作成した my_dynamic_table を使用します。

   CREATE OR REPLACE TABLE my_dt_clone_table CLONE my_dynamic_table;
   

   Copy

2. クローンされたテーブルのスキーマを変更します。

   ALTER TABLE my_dt_clone_table ADD COLUMN sales_avg FLOAT;
   
   SELECT item_id, to_char(day, 'YYYY-MM-DD') as DAY, SALES_COUNT, SALES_AVG FROM my_dt_clone_table;
   

   Copy

3. オプションで、新しい列にデータを入力します。

4. 編集されたクローンをバックフィルのソースとして使用し、動的テーブルを再作成します。

   CREATE OR REPLACE DYNAMIC TABLE my_dynamic_table
     BACKFILL FROM my_dt_clone_table
     IMMUTABLE WHERE (day <= '2025-05-01')
     TARGET_LAG = 'DOWNSTREAM'
     WAREHOUSE = mywh
     INITIALIZE = on_create
     AS
       SELECT item_id, date_trunc('DAY', ts) day, count(sales_price) AS sales_count, avg(sales_price) as sales_avg FROM sales
       GROUP BY item_id, day;
   

   Copy

   このアプローチにより、ベーステーブルを変更することなく、動的テーブルのデータを回復または修正できます。

   SELECT item_id, to_char(day, 'YYYY-MM-DD') as DAY, SALES_COUNT, SALES_AVG, metadata$is_immutable as IMMUTABLE from my_dynamic_table ORDER BY ITEM_ID, DAY;
   

   Copy

   +---------+------------+-------------+-----------+-----------+
   | ITEM_ID | DAY        | SALES_COUNT | SALES_AVG | IMMUTABLE |
   |---------+------------+-------------|-----------|-----------|
   | 1       | 2025-05-01 | 3           | NULL      | TRUE      |
   | 1       | 2025-05-02 | 2           | 12        | FALSE     |
   +---------+-------------+------------+-----------+-----------+