Verwenden von Unveränderlichkeitseinschränkungen¶

Beispiel: Verhindern von Neuberechnungen, wenn sich eine Dimensionstabelle ändert¶

Wenn Sie eine Zeile in einer Dimensionstabelle aktualisieren, verarbeiten Sie nur die Fakten aus dem veränderbaren Zeitraum erneut:

CREATE DYNAMIC TABLE joined_data
  TARGET_LAG = '1 minute'
  WAREHOUSE = mywh
  IMMUTABLE WHERE (timestamp_col < CURRENT_TIMESTAMP() - INTERVAL '1 day')
AS
  SELECT F.primary_key primary_key, F.timestamp_col timestamp_col, D.value dim_value
  FROM fact_table F
  LEFT OUTER JOIN dimension_table D USING (primary_key);

Beispiel: Behalten Sie Daten länger als die Quelltabelle bei¶

Erstellen Sie eine dynamische Tabelle, die geparste Daten länger aufbewahrt als die Staging-Tabelle, und löschen Sie alte Staging-Daten mit einer Aufgabe:

CREATE TABLE staging_data (raw TEXT, ts TIMESTAMP);

CREATE DYNAMIC TABLE parsed_data
  TARGET_LAG = '1 minute'
  WAREHOUSE = mywh
  IMMUTABLE WHERE (ts < CURRENT_TIMESTAMP() - INTERVAL '7 days')
AS
  SELECT
    parse_json(raw):event_id::string event_id,
    parse_json(raw):name::string name,
    parse_json(raw):region::string region,
    ts
  FROM staging_data
  WHERE region = 'US';

CREATE TASK delete_old_staging_data
  WAREHOUSE = mywh
  SCHEDULE = '24 hours'
AS
  DELETE FROM staging_data WHERE ts < CURRENT_TIMESTAMP() - INTERVAL '30 days';

Beispiel: Erlauben Sie nachgelagerten Tabellen, eine inkrementelle Aktualisierung aus einer Tabelle mit vollständiger Aktualisierung zu verwenden¶

Einige Abfragekonstrukte (wie benutzerdefinierte Python-Tabellenfunktionen) erfordern den Modus der vollständigen Aktualisierung. Aufgrund von Unveränderlichkeitseinschränkungen können nachgelagerte Tabellen weiterhin die inkrementelle Aktualisierung verwenden:

CREATE DYNAMIC TABLE udtf_dt
  TARGET_LAG = '1 hour'
  WAREHOUSE = mywh
  REFRESH_MODE = FULL
  IMMUTABLE WHERE (ts < current_timestamp() - interval '1 day')
AS
  SELECT ts, data, output, join_key
  FROM input_table, TABLE(my_udtf(data));

CREATE DYNAMIC TABLE incremental_join_dt
  TARGET_LAG = '1 hour'
  WAREHOUSE = mywh
  REFRESH_MODE = INCREMENTAL
  IMMUTABLE WHERE (ts < current_timestamp() - interval '1 day')
AS
  SELECT * FROM udtf_dt JOIN dim_table USING (join_key);

Beispiel: Backfilling aus einem Teil der Tabelle¶

Im folgenden Beispiel wird der unveränderlichen Bereich von my_dynamic_table aus my_backfill_table und der veränderliche Bereich aus der Definition der dynamischen Tabelle durch Backfilling aufgefüllt.

Wenn Sie diese dynamische Tabelle neu initialisieren:

• Modus der inkrementellen Aktualisierung: Snowflake löscht alle veränderbaren Zeilen und füllt nur die veränderbare Region neu auf.

• Modus der vollständigen Aktualisierung: Snowflake führt eine vollständige Aktualisierung mit demselben Effekt aus.

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;

Beispiel: Verwenden von Backfilling zum Wiederherstellen oder Ändern von Daten in einer dynamischen Tabelle¶

Sie können die Daten oder die Definition einer dynamischen Tabelle nicht direkt bearbeiten. Um Daten wiederherzustellen oder zu korrigieren, führen Sie die folgenden Problemumgehungsschritte aus:

1. Klonen Sie die dynamische Tabelle in eine reguläre Tabelle.

2. Ändern Sie die geklonte Tabelle wie erforderlich.

3. Nehmen Sie ein Backfilling aus der bearbeiteten Tabelle in eine neue dynamische Tabelle vor.

Im folgenden Beispiel aggregiert my_dynamic_table tägliche Verkaufsdaten aus der Basistabelle 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           |
+---------+------------+-------------+

Optional können Sie die alten Daten archivieren, um Speicherkosten zu sparen:

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;

Später finden Sie einen Verkaufsfehler am 2025-05-01, wobei sales_count „2“ sein sollte. So korrigieren Sie dies:

1. Klonen Sie my_dynamic_table in einer regulären Tabelle:

   CREATE OR REPLACE TABLE my_dt_clone_table CLONE my_dynamic_table;
   

   Copy

2. Aktualisieren Sie die geklonte Tabelle:

   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. Erstellen Sie die dynamische Tabelle neu, indem Sie den bearbeiteten Klon als Backfilling-Quelle verwenden.

   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

   Mit dieser Methode können Sie Daten in einer dynamischen Tabelle wiederherstellen oder korrigieren, ohne die Basistabelle ändern zu müssen:

   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           |
   +---------+------------+-------------+
   

Beispiel: Ändern des Schemas einer dynamischen Tabelle mithilfe von Backfilling¶

Sie können das Schema einer dynamischen Tabelle nicht direkt ändern. Um das Schema zu aktualisieren, z. B. eine Spalte hinzuzufügen, gehen Sie wie folgt vor:

1. Klonen Sie die dynamische Tabelle in eine reguläre Tabelle. Im folgenden Beispiel wird my_dynamic_table verwendet, die aus sales erstellt wurde (zuvor).

   CREATE OR REPLACE TABLE my_dt_clone_table CLONE my_dynamic_table;
   

   Copy

2. Ändern Sie das Schema der geklonten Tabelle:

   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. Optional können Sie der neuen Spalte Daten hinzufügen.

4. Erstellen Sie die dynamische Tabelle neu, indem Sie den bearbeiteten Klon als Backfilling-Quelle verwenden.

   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

5. Überprüfen Sie, ob die neue Spalte in der dynamischen Tabelle angezeigt wird:

   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     |
   +---------+-------------+------------+-----------+-----------+