Verwenden von Primärschlüsseln zum Optimieren dynamischer Tabellen-Pipelines¶

Snowflake kann Primärschlüssel verwenden, um Änderungen auf Zeilenebene in dynamischen Tabellen zu verfolgen, ohne sich auf Spalten zur Änderungsverfolgung zu verlassen. Dies ermöglicht eine inkrementelle Aktualisierung für Pipelines, die Workloads zum Überschreiben von Einfügungen ausführen, einschließlich dynamischer Tabellen mit vollständiger Aktualisierung, die normalerweise die nachgelagerte inkrementelle Verarbeitung blockieren.

Primärschlüssel sind besonders effektiv, wenn INSERT OVERWRITE für eine Basistabelle ausgeführt wird, in der nur ein kleiner Teil der Daten tatsächlich geändert wird. In diesen Fällen verarbeitet die primärschlüsselbasierte Änderungsverfolgung nur die geänderten Zeilen, anstatt die gesamte Tabelle neu zu berechnen. Ein Primärschlüssel bietet einen stabilen Zeilenbezeichner, der über das Überschreiben hinweg erhalten bleibt.

Informationen zum konzeptionellen Hintergrund finden Sie unter Erläuterungen zu Primärschlüsseln in dynamischen Tabellen.

Leistung für INSERT OVERWRITE-Workloads verbessern¶

Wenn eine Basistabelle regelmäßig mithilfe von INSERT OVERWRITE umgeschrieben wird, werden die Standardspalten zur Änderungsverfolgung zurückgesetzt, und eine dynamische Tabelle, die die Basistabelle nutzt, sieht eine Reihe von Einfügungen und Löschungen für alle Zeilen in der Basistabelle.

Im folgenden Beispiel schreibt ein externer Prozess die dimension_table regelmäßig neu, aber die meisten Zeilen bleiben gleich:

CREATE TABLE dimension_table (
  dim_id INT PRIMARY KEY RELY,
  dim_name VARCHAR,
  category VARCHAR
);

CREATE TABLE fact_table (
  fact_id INT,
  dim_id INT,
  measure FLOAT,
  ts TIMESTAMP
);

CREATE DYNAMIC TABLE enriched_facts
  TARGET_LAG = '30 minutes'
  WAREHOUSE = mywh
  REFRESH_MODE = INCREMENTAL
AS
  SELECT f.fact_id, f.measure, d.dim_name, d.category, f.ts
  FROM fact_table f
  INNER JOIN dimension_table d ON f.dim_id = d.dim_id;

Wenn die Dimensionstabelle mithilfe von INSERT OVERWRITE umgeschrieben wird, verwendet Snowflake den Primärschlüssel, um festzustellen, welche Dimensionszeilen sich tatsächlich geändert haben, und aktualisiert nur die betroffenen Fakten, anstatt die gesamte Verknüpfung neu zu berechnen.

Inkrementelle Aktualisierung nachgelagert von einer dynamischen Tabelle mit vollständiger Aktualisierung aktivieren¶

Normalerweise kann eine dynamische Tabelle mit REFRESH_MODE = INCREMENTAL nicht aus einer dynamischen Tabelle mit REFRESH_MODE = FULL lesen. Wenn die dynamische Tabelle mit vollständiger Aktualisierung einen vom System abgeleiteten eindeutigen Schlüssel hat, können Sie den Aktualisierungsmodus explizit auf INCREMENTAL festlegen.

Beispiel: Verwenden eines Primärschlüssels für eine Basistabelle¶

Erstellen Sie eine Basistabelle mit einem Primärschlüssel, und legen Sie die RELY-Eigenschaft fest, sodass Snowflake sie für die Änderungsverfolgung auf Zeilenebene verwendet:

CREATE TABLE raw_events (
  event_id INT PRIMARY KEY RELY,
  event_type VARCHAR,
  payload VARIANT,
  created_at TIMESTAMP
);

Erstellen Sie eine dynamische Tabelle mit vollständiger Aktualisierung, die aus der Basistabelle liest. Da die Basistabelle über einen zuverlässigen Primärschlüssel verfügt, kann Snowflake einen eindeutigen Schlüssel aus der Basistabelle ableiten und ihn als eindeutige Einschränkung für die dynamische Tabelle registrieren:

CREATE DYNAMIC TABLE transformed_events
  TARGET_LAG = '10 minutes'
  WAREHOUSE = mywh
  REFRESH_MODE = FULL
AS
  SELECT event_id, event_type, payload:user_id::STRING AS user_id, created_at
  FROM raw_events;

Erstellen Sie eine inkrementelle dynamische Tabelle nachgelagert. Dies funktioniert, weil die vorgelagerte Tabelle über einen vom System abgeleiteten, zuverlässigen, eindeutigen Schlüssel verfügt:

CREATE DYNAMIC TABLE event_summary
  TARGET_LAG = '10 minutes'
  WAREHOUSE = mywh
  REFRESH_MODE = INCREMENTAL
AS
  SELECT user_id, COUNT(*) AS event_count, MAX(created_at) AS last_event
  FROM transformed_events
  GROUP BY user_id;

Beispiel: Verwenden eines von der Abfrage abgeleiteten Primärschlüssel¶

Wenn die Abfrage einer dynamischen Tabelle eine GROUP BY-Klausel enthält, leitet Snowflake automatisch einen eindeutigen Schlüssel aus den Gruppierungsspalten ab. Nachgelagerte Tabellen können diesen abgeleiteten Schlüssel für die primärschlüsselbasierte Änderungsverfolgung verwenden und inkrementelle Aktualisierungen ermöglichen.

CREATE DYNAMIC TABLE daily_sales
  TARGET_LAG = '1 hour'
  WAREHOUSE = mywh
  REFRESH_MODE = FULL
AS
  SELECT DATE_TRUNC('day', sale_ts) AS sale_day, product_id, SUM(amount) AS total_sales
  FROM sales
  GROUP BY sale_day, product_id;

Die daily_sales-Tabelle hat einen abgeleiteten eindeutigen Schlüssel für (sale_day, product_id), weil GROUP BY eine Zeile pro Kombination garantiert. Eine nachgelagerte Tabelle kann inkrementell aktualisiert werden:

CREATE DYNAMIC TABLE product_trends
  TARGET_LAG = '1 hour'
  WAREHOUSE = mywh
  REFRESH_MODE = INCREMENTAL
AS
  SELECT product_id, AVG(total_sales) AS avg_daily_sales, COUNT(*) AS days_with_sales
  FROM daily_sales
  GROUP BY product_id;

Vom System abgeleitete eindeutige Schlüssel in einer dynamischen Tabelle überprüfen¶

Um festzustellen, ob eine dynamische Tabelle über einen abgeleiteten eindeutigen Schlüssel verfügt, verwenden Sie den Befehl SHOW UNIQUE KEYS:

SHOW UNIQUE KEYS IN daily_sales;

Wenn die Ausgabe einen eindeutigen Schlüssel enthält, unterstützt die dynamische Tabelle die auf dem Primärschlüssel basierende Änderungsverfolgung. Nachgelagerte dynamische Tabellen können REFRESH_MODE = INCREMENTAL verwenden, um daraus zu lesen, auch wenn der Modus der vollständigen Aktualisierung verwendet wird.

Sie können die Unterstützung auch überprüfen, indem Sie eine nachgelagerte dynamische Tabelle mit REFRESH_MODE = INCREMENTAL erstellen. Wenn die vorgelagerte Tabelle keinen zuverlässigen, eindeutigen Schlüssel hat, schlägt die Erstellung mit einem Fehler fehl.