Verwenden von Unveränderlichkeitseinschränkungen¶

Um Snowflake mitzuteilen, dass sich bestimmte Zeilen in einer dynamischen Tabelle nicht ändern werden, verwenden Sie die IMMUTABLE WHERE-Klausel in einer CREATE DYNAMIC TABLE- oder ALTER DYNAMIC TABLE-Anweisung.

Die Unveränderlichkeit beschleunigt Aktualisierungen, indem Zeilen übersprungen werden, die sich nicht ändern. Backfilling mit Unveränderlichkeit bietet sowohl sofortige als auch kontinuierliche Leistungsvorteile:

Erstmalige Erstellung: Backfilling kopiert historische Daten sofort ohne Computekosten. Dadurch sind Tabellen mit historischen Daten aus mehreren Jahren sofort verfügbar, anstatt teure Erstaktualisierungen zu erfordern.
Laufende Aktualisierungen: Unveränderlichkeitseinschränkungen schützen bei zukünftigen Aktualisierungen die aufgefüllten Daten vor einer erneuten Verarbeitung. Nur die veränderbare Region wird aktualisiert, sodass die Aktualisierungszeiten schnell bleiben, auch wenn die Tabelle wächst.

Informationen zum konzeptionellen Hintergrund finden Sie unter Erläuterungen zu Unveränderlichkeitseinschränkungen.

Grundlegende Beispiele¶

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);

Beispiele für Backfilling¶

Die folgenden Beispiele zeigen, wie Sie neue dynamische Tabellen aus Tabellen mit durch Backfilling aufgefüllten Daten erstellen können.

Die Backfilling-Tabelle muss übereinstimmende Spalten mit kompatiblen Datentypen in derselben Reihenfolge enthalten wie Ihre dynamische Tabelle. Snowflake kopiert keine Tabelleneigenschaften oder Berechtigungen aus der Backfilling-Tabelle.

Bei Angabe der Time Travel-Parameter AT | BEFORE kopiert Snowflake die Daten aus der Backfilling-Tabelle zum angegebenen Zeitpunkt.

Die folgenden Beschränkungen gelten, wenn Sie mit Unveränderlichkeitseinschränkungen und rückwirkend aufgefüllten Daten (Backfilling) arbeiten:

Derzeit können nur reguläre und dynamische Tabellen für das Backfilling verwendet werden.
Sie können in der neuen dynamischen Tabelle keine Richtlinien oder Tags angeben, da diese aus der Backfilling-Tabelle kopiert werden.
Die Gruppierungsschlüssel in der neuen dynamischen Tabelle und der Backfilling-Tabelle müssen identisch sein.

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:

Klonen Sie die dynamische Tabelle in eine reguläre Tabelle.
Ändern Sie die geklonte Tabelle wie erforderlich.
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:

Klonen Sie my_dynamic_table in einer regulären Tabelle:
```
CREATE OR REPLACE TABLE my_dt_clone_table CLONE my_dynamic_table;
```

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;

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

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;

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;

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

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;
```

Ä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;

Optional können Sie der neuen Spalte Daten hinzufügen.

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;

Ü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;

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

Prüfen des Status der Unveränderlichkeit¶

Um zu prüfen, ob eine Zeile in einer dynamischen Tabelle veränderbar ist, fragen Sie die Spalte METADATA$IS_IMMUTABLE ab:

SELECT *, METADATA$IS_IMMUTABLE FROM my_dynamic_table;

Um die Unveränderlichkeitseinschränkung für eine dynamische Tabelle anzuzeigen, führen Sie SHOW DYNAMIC TABLES aus, und überprüfen Sie die Spalte immutable_where.