Create and manage storage lifecycle policies¶

备注

以下章节介绍如何在表上创建、重新创建和管理存储生命周期策略。

创建存储生命周期策略¶

要创建存储生命周期策略，请使用 CREATE STORAGE LIFECYCLE POLICY 命令。

When you create a storage lifecycle policy, you can choose an archive tier and optionally set an archival period in days. If you set an archival period, Snowflake moves table rows that match the policy expression into a lower-cost storage tier for the specified number of days before expiring the rows. Snowflake also enables change tracking on any tables that you attach the policy to.

例如：

CREATE STORAGE LIFECYCLE POLICY my_slp
  AS (event_ts TIMESTAMP, account_id NUMBER)
  RETURNS BOOLEAN ->
    event_ts < DATEADD(DAY, -60, CURRENT_TIMESTAMP())
    AND EXISTS (
      SELECT 1 FROM closed_accounts
      WHERE id = account_id
    )
  ARCHIVE_TIER = COOL
  ARCHIVE_FOR_DAYS = 90;

Copy

备注

For considerations when you work with tables that have archival storage policies, see 归档存储策略.

最佳实践：对基于时间的表达式使用日期转换¶

为了提高性能并确保一致的策略执行，请在比较时间值时将时间戳转换为策略表达式中的日期。

例如，考虑以下策略表达式：

event_time < DATEADD(DAY, -400, CURRENT_TIMESTAMP())

Copy

此比较包括时间戳的时间部分，这可能会导致不一致的行为。当数据按 event_time 的时间顺序插入时，策略的执行时间会影响从每个文件中删除的行数。

To avoid this inconsistent behavior, convert timestamps to dates in your expression:

TO_DATE(event_time) < TO_DATE(DATEADD(DAY, -400, CURRENT_TIMESTAMP()))

Copy

This method provides consistent policy execution regardless of the time of day.

重新创建存储生命周期策略¶

此功能扩展了用于重新创建指定存储生命周期策略的 GET_DDL 命令。如果要更改策略的归档层，可以执行此操作。

To recreate a storage lifecycle policy named my_slp, return the DDL, as shown in the following example:

SELECT GET_DDL('policy','my_slp');

Copy

输出：

---------------------------------------------------------------------+
                      GET_DDL('POLICY','SLP')                        |
---------------------------------------------------------------------+
create or replace storage lifecycle policy SLP as                    |
  (event_ts timestamp, account_id number)
    returns boolean ->
    event_ts < dateadd(day, -60, current_timestamp())
    and exists (
      select 1 from closed_accounts
      where id = account_id
  )
  ARCHIVE_FOR_DAYS = 365                                             |
;                                                                    |
---------------------------------------------------------------------+

管理表的存储生命周期策略¶

使用以下选项管理存储生命周期策略附件。

将策略附加到表中¶

您可以使用一个存储生命周期策略管理多个表。创建或更改表时附加策略。

To create a table and attach the policy to a new table by using the specified columns, use CREATE TABLE, as shown in the following example.

备注

您必须具有应用该策略所需的权限。有关所需权限的信息，请参阅 Storage lifecycle policy privileges。
一个表只能附加一个存储生命周期策略。
列数必须与策略函数签名中的实参数匹配，并且列数据必须与实参类型兼容。
如果重命名表列，则关联的策略不受影响。Snowflake 使用列 IDs 将策略与表关联。
为了评估和应用存储生命周期策略表达式，Snowflake 会在内部暂时绕过表上的任何治理策略。

CREATE TABLE my_table
  ...
  WITH STORAGE LIFECYCLE POLICY my_slp ON (col1);

Copy

To attach the policy to an existing table by using the specified columns, use ALTER TABLE, as shown in the following example:

ALTER TABLE my_table ADD STORAGE LIFECYCLE POLICY my_slp
  ON (col1);

Copy

将策略作为一次性操作应用¶

If you only need to expire or archive historical data once, as a one-time operation, we recommend the following procedure:

Create, and then attach a storage lifecycle policy to your table.
Wait for the policy to execute, and then archive or expire the data.

Monitor the INFORMATION_SCHEMA.STORAGE_LIFECYCLE_POLICY_HISTORY table function to confirm the process is complete.
To prevent recurring charges, remove the storage lifecycle policy from the table.

Storage lifecycle policies incur cost per execution.

This method ensures that you only pay for a single execution instead of ongoing daily charges for a policy that has already processed all eligible data. For more information about cost, see 存储生命周期策略计费.

从表中移除策略¶

To remove a storage lifecycle policy from a table, use ALTER TABLE, as shown in the following example:

ALTER TABLE my_table DROP STORAGE LIFECYCLE POLICY;

Copy

This command removes all future policy executions for this table.
运行策略执行可能会在从表中删除之前完成。
要删除存储生命周期策略，您必须对策略附加到的表具有 OWNERSHIP 权限。