ALTER STORAGE LIFECYCLE POLICY¶

Modifies the properties of an existing storage lifecycle policy.

Attention

Changes to a storage lifecycle policy can have significant impact on all associated tables. Use the QUERY_HISTORY view in the ACCOUNT_USAGE schema to audit policy changes regularly. For more information, see QUERY_HISTORY view.

See also:: CREATE STORAGE LIFECYCLE POLICY , DESCRIBE STORAGE LIFECYCLE POLICY , DROP STORAGE LIFECYCLE POLICY , SHOW STORAGE LIFECYCLE POLICIES

语法

ALTER STORAGE LIFECYCLE POLICY [ IF EXISTS ] <name> RENAME TO <new_name>

ALTER STORAGE LIFECYCLE POLICY [ IF EXISTS ] <name> SET

  BODY -> <expression_on_arg_name>
  | ARCHIVE_TIER = { COOL | COLD }
  | ARCHIVE_FOR_DAYS = <number_of_days>
  | COMMENT = '<string_literal>'
  | TAG <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ]

ALTER STORAGE LIFECYCLE POLICY [ IF EXISTS ] <name> UNSET
  ARCHIVE_FOR_DAYS
  | COMMENT
  | TAG <tag_name> [ , <tag_name> ... ]

参数

name

指定要更改的策略的标识符。

If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

For more information, see Identifier requirements.

RENAME TO new_name

指定策略的新标识符；对于架构来说必须唯一。

For more information, see Identifier requirements.

You can move the object to a different database and/or schema while optionally renaming the object. To do so, specify a qualified new_name value that includes the new database and/or schema name in the form db_name.schema_name.object_name or schema_name.object_name, respectively.

Note

The destination database and/or schema must already exist. In addition, an object with the same name cannot already exist in the new location; otherwise, the statement returns an error.
Moving an object to a managed access schema is prohibited unless the object owner (that is, the role that has the OWNERSHIP privilege on the object) also owns the target schema.

重命名对象时，引用该对象的其他对象必须使用新名称进行更新。

SET ...

指定要为策略设置的一个或多个属性：

BODY -> expression_on_arg_name

确定要过期的行的 SQL 表达式。

To transform the data, you can use built-in functions such as Conditional expression functions or user-defined functions (UDFs).

Note

Currently, only SQL and JavaScript UDFs are supported in the body of a storage lifecycle policy.

ARCHIVE_TIER = { COOL | COLD }

指定要转换过期策略的存储层，其中 ARCHIVE_FOR_DAYS 未设置到归档策略中。

COOL requires that you set an archival period (ARCHIVE_FOR_DAYS) of 90 days or longer.
COLD requires that you set an archival period (ARCHIVE_FOR_DAYS) of 180 days or longer.

For supported cloud providers, see Storage lifecycle policies.

ARCHIVE_FOR_DAYS = number_of_days

Specifies the number of days to keep rows that match the policy expression in archive storage. If set, Snowflake moves the data into archive storage according to the value you select for ARCHIVE_TIER. If unset, Snowflake expires the rows from the table without archiving the data.

Values:

ARCHIVE_TIER = COOL: 90 - 2147483647
ARCHIVE_TIER = COLD: 180 - 2147483647

Default: Unset

COMMENT = 'string_literal'

为策略添加注释或覆盖现有注释。

默认：无值

TAG tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ]

Specifies the tag name and the tag string value.

The tag value is always a string, and the maximum number of characters for the tag value is 256.

For information about specifying tags in a statement, see Tag quotas.

UNSET ...

指定要为策略取消设置的属性，这会将属性重置为其默认值：

ARCHIVE_FOR_DAYS
COMMENT
TAG tag_name [ , tag_name ... ]

To unset multiple properties or parameters with a single ALTER statement, separate each property or parameter with a comma.

When unsetting a property or parameter, specify only the property or parameter name (unless the syntax above indicates that you should specify the value). Specifying the value returns an error.

访问控制要求

A role used to execute this operation must have the following privileges at a minimum:

Privilege	Object	Notes
OWNERSHIP	Storage lifecycle policy	OWNERSHIP is a special privilege on an object that is automatically granted to the role that created the object, but can also be transferred using the [GRANT OWNERSHIP](/sql-reference/sql/grant-ownership) command to a different role by the owning role (or any role with the MANAGE GRANTS privilege).

Operating on an object in a schema requires at least one privilege on the parent database and at least one privilege on the parent schema.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

使用说明

If you want to update an existing policy and need to see the current definition of the policy, call the GET_DDL function or run the DESCRIBE STORAGE LIFECYCLE POLICY command.
You can’t change the policy signature with this command. To change the signature, use the DROP STORAGE LIFECYCLE POLICY command and then create a new policy.
为策略设置 ARCHIVE_TIER 后，您无法对其进行更改。例如，您不能使用此命令将策略的 ARCHIVE_TIER 从 COOL 更改为 COLD。
如果为策略取消设置 ARCHIVE_FOR_DAYS，存储层不会更改。如果稍后为策略重新启用归档存储，则无法修改存储层。
Including one or more subqueries in the policy body might cause errors. When possible, limit the number of subqueries, limit the number of JOIN operations, and simplify WHERE clause conditions.
关于元数据：

Attention

Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata fields in Snowflake.

示例

以下示例将存储生命周期策略更新为在 30 天后使已关闭的账户过期。

ALTER STORAGE LIFECYCLE POLICY expire_storage_for_closed_accounts
  SET BODY ->
    event_ts < DATEADD(DAY, -30, CURRENT_TIMESTAMP())
    AND EXISTS (
      SELECT 1 FROM closed_accounts
      WHERE id = account_id
    );