对动态表使用不可变性约束

不可变性约束 让您可以更精细地控制动态表的更新方式和时间。约束让表的部分数据保持静态，而不是整个表始终反映最新查询结果。

通过将动态表的特定部分标记为不可变，您可以执行以下操作：

  • 防止将更新或删除传播到现有数据。

  • 限制满足条件的行的插入、更新和删除。

  • 在允许其他部分增量更新的同时，限制未来修改。

  • 使用不可变性约束填充动态表。有关更多信息，请参阅 使用回填创建动态表。

表的其余部分（即不符合不可变性条件的行）仍可修改，并可在刷新期间更新。

要强制执行不可变性约束，请在运行 CREATE DYNAMIC TABLE 或 ALTER DYNAMIC TABLE 命令时指定 IMMUTABLE WHERE 参数。在 IMMUTABLE WHERE 条件中引用的列必须是动态表中的列，而不能是基表中的列。例如，以下 SQL 语句中的 IMMUTABLE WHERE 表达式只能使用 id1，而不能使用 id：

CREATE DYNAMIC TABLE my_dynamic_table (id1 INT)
  TARGET_LAG = '20 minutes'
  WAREHOUSE = mywh
  IMMUTABLE WHERE ( <expr> )
  AS
    SELECT id AS id1 FROM staging_table;
Copy

要查看某行是否可修改，请使用 METADATA$IS_IMMUTABLE 列。例如 SELECT * , METADATA$IS_IMMUTABLE FROM my_dynamic_table。

要查看动态表上的不可变性约束，请运行 SHOW DYNAMIC TABLES 命令。immutable_where 列显示表上设置的 IMMUTABLE WHERE 约束，如果未设置，则显示为 NULL。

有关计算成本的信息，请参阅 计算不变性约束的成本。

示例：使用 IMMUTABLE WHERE 防止在维度表更改时重新计算旧数据

在以下示例中，更新维度表中的一行只会导致与其关联的最近一天的事实数据被重新处理，而不是处理所有历史事实数据：

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

示例：无限保留的动态表与有限保留的基表

以下示例创建了一个仅保留最近添加数据的暂存表，以及一个存储所有解析和过滤数据的动态表：

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

-- Delete old staging data using Task
CREATE TASK delete_old_staging_data
  WAREHOUSE = mywh
  SCHEDULE = '24 hours'
  AS
    DELETE FROM staging_data WHERE ts < CURRENT_TIMESTAMP() - INTERVAL '30 days';
Copy

示例：当上游动态表在完全刷新模式下运行时，可使用 IMMUTABLE WHERE 启用增量下游刷新

某些查询结构，例如 Python 用户定义的表函数 (UDTFs) 需要动态表才能在完全刷新模式下运行。通常情况下，此限制会阻止增量处理。要使下游表保持增量，可使用 IMMUTABLE WHERE 子句定义不可变区域，如以下示例所示。这样，即使上游表需要进行完全刷新，下游表仍能受益于性能优化。

CREATE FUNCTION my_udtf(x varchar)
  RETURNS TABLE (output VARCHAR)
  LANGUAGE PYTHON
  AS $$ ... $$;

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

设置不可变性约束的限制和注意事项

  • 在初始刷新期间，IMMUTABLE WHERE 谓词会被忽略，但在后续所有刷新中生效。在完全刷新模式下，谓词仅对不符合条件的行重新计算。增量刷新模式下的流和动态表可以从这些完全刷新表中读取数据。

  • IMMUTABLE WHERE 约束在克隆和复制过程中会复制，且没有任何限制。

  • 常规动态表的所有限制 仍然适用。此外，还适用以下限制：

    • 动态表上只能有一个 IMMUTABLE WHERE 谓词。使用 ALTER DYNAMIC TABLE ... SET IMMUTABLE WHERE 命令设置新的谓词时，会替换任何现有谓词。

    • IMMUTABLE WHERE 约束不能包含以下内容：

      • 子查询。

      • 非确定性函数，但时间戳函数除外，如 CURRENT_TIMESTAMP() 或 CURRENT_DATE()。如果使用时间戳函数，必须确保不可变区域随时间不会收缩。例如，TIMESTAMP_COL < CURRENT_TIMESTAMP() is allowed but TIMESTAMP_COL > CURRENT_TIMESTAMP() 不允许。

      • 用户定义函数或外部函数。

      • 元数据列，例如以 METADATA$ 开头的列。

      • 聚合函数、窗口函数或非确定性函数的结果列，或通过窗口函数操作符传递的列。在以下示例动态表中，只有 col3 可以在 IMMUTABLE WHERE 谓词中使用：

        CREATE DYNAMIC TABLE aggregates TARGET_LAG = '1 minute' WAREHOUSE = mywh
  AS SELECT col3, SUM(col2) AS col2 FROM input_table
  GROUP BY col3;

CREATE DYNAMIC TABLE window_fns TARGET_LAG = '1 minute' WAREHOUSE = mywh
  AS SELECT col3, SUM(col4) OVER (PARTITION BY col3 ORDER BY col4) AS col2, col5 FROM input_table;
        Copy

    • 一个动态表上只能设置一个 IMMUTABLE WHERE 条件。要替换现有谓词，可以使用 ALTER DYNAMIC TABLE 命令，如下例所示：

      -- Set or replace an existing predicate:
ALTER DYNAMIC TABLE my_dynamic_table SET IMMUTABLE WHERE ( <expr> );
      Copy
      -- Remove an existing predicate:
ALTER DYNAMIC TABLE my_dynamic_table UNSET IMMUTABLE WHERE;
      Copy
语言: 中文