主キーを使用して動的テーブルパイプラインを最適化する¶

Snowflakeは主キーを使用して、変更追跡列に依存することなく、動的テーブルの行レベルの変更を追跡できます。これにより、通常は下流の増分処理をブロックするフルリフレッシュ動的テーブルを含む、挿入上書きワークロードを実行するパイプラインの増分リフレッシュが可能になります。

主キーは、データのごく一部のみが実際に変更されるベーステーブルで INSERTOVERWRITE が実行される場合に特に効果的です。このような場合、主キーベースの変更追跡は、テーブル全体を再計算するのではなく、変更された行のみを処理します。主キーは、上書きされても維持される安定した行識別子を提供します。

概念的な背景については、動的テーブルの主キーを理解するをご参照ください。

INSERTOVERWRITE ワークロードのパフォーマンスを改善する¶

INSERTOVERWRITE を通してベーステーブルが定期的に書き換えられる場合、標準の変更追跡列がリセットされ、ベーステーブルを消費する動的テーブルには、ベーステーブルのすべての行に対する挿入と削除のセットが表示されます。

次の例では、外部プロセスが定期的に dimension_table を書き換えますが、ほとんどの行は同じままです。

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;

ディメンションテーブルが INSERTOVERWRITE によって書き換えられた場合、Snowflakeは主キーを使用して、どのディメンション行が実際に変更されたかを識別し、結合全体を再計算するのではなく影響を受けるファクトのみを更新します。

フルリフレッシュ動的テーブルの下流で増分リフレッシュを有効にする¶

通常、 REFRESH_MODE = INCREMENTAL を持つ動的テーブルでは REFRESH_MODE = FULL を持つ動的テーブルからの読み取りができません。フルリフレッシュ動的テーブルにシステム固有の一意のキーがある場合は、明示的にリフレッシュモードを INCREMENTAL に設定できます。

例:ベーステーブルの主キーを使用する¶

主キーを持つベーステーブルを作成し、 RELY プロパティを設定すると、Snowflakeはそれを行レベルの変更追跡に使用します。

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

ベーステーブルから読み込むフルリフレッシュ動的テーブルを作成します。ベーステーブルには信頼できる主キーがあるため、Snowflakeはベーステーブルから一意のキーを抽出し、動的テーブルの一意の制約として登録できます。

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;

増分動的テーブルを下流に作成します。これは、上流のテーブルにシステム派生の信頼できる一意のキーがあるため機能します。

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;

例:クエリ派生の主キーを使用する¶

動的テーブルのクエリに GROUP BY 句が含まれている場合、Snowflakeはグループ化列から一意のキーを自動的に取得します。下流テーブルは、この派生キーを主キーベースの変更追跡に使用し、増分リフレッシュを有効にすることができます。

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;

GROUP BY は組み合わせごとに1行を保証するため、 daily_sales テーブルには (sale_day, product_id) に派生した一意のキーがあります。下流のテーブルは増分リフレッシュできます。

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;

動的テーブルでシステムが派生した一意のキーをチェックする¶

動的テーブルに派生した一意のキーがあるかどうかを確認するには、 SHOW UNIQUE KEYS コマンドを使用します。

SHOW UNIQUE KEYS IN daily_sales;

出力に一意のキーが含まれている場合、動的テーブルは主キーベースの変更追跡をサポートします。下流の動的テーブルはフルリフレッシュモードを使用している場合でも、 REFRESH_MODE = INCREMENTAL を使用してそこから読み込むことができます。

また、 REFRESH_MODE = INCREMENTAL で下流の動的テーブルを作成することで、サポートを確認することもできます。上流テーブルに信頼できる一意のキーがない場合、作成はエラーで失敗します。