ALTER STORAGE LIFECYCLE POLICY¶

Syntax¶

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> ... ]

Parameters¶

name

Specifies the identifier for the policy to alter.

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 Anforderungen an Bezeichner.

RENAME TO new_name

Specifies the new identifier for the policy; must be unique for your schema.

For more information, see Anforderungen an Bezeichner.

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.

Bemerkung

• 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 ...

Specifies one or more properties to set for the policy:

BODY -> expression_on_arg_name

SQL expression that determines the rows to expire.

To transform the data, you can use built-in functions such as Funktionen für bedingte Ausdrücke or user-defined functions (UDFs).

Bemerkung

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

ARCHIVE_TIER = { COOL | COLD }

Specifies a storage tier to convert an expiration policy where ARCHIVE_FOR_DAYS is unset into an archival policy.

• 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.

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'

Adds a comment or overwrites the existing comment for the policy.

Default: No value

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-Kontingent für Objekte.

UNSET ...

Specifies properties to unset for the policy:

• ARCHIVE_FOR_DAYS

• COMMENT

• TAG tag_name [ , tag_name ... ]

Access control requirements¶

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

The USAGE privilege on the parent database and schema are required to perform operations on any object in a schema.

For instructions on creating a custom role with a specified set of privileges, see Erstellen von kundenspezifischen Rollen.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Übersicht zur Zugriffssteuerung.

Usage notes¶

• 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.

• After you set the ARCHIVE_TIER for a policy, you can’t change it. For example, you can’t use this command to change the ARCHIVE_TIER for a policy from COOL to COLD.

• If you unset ARCHIVE_FOR_DAYS for a policy, the storage tier doesn’t change. If you later re-enable archival storage for the policy, you can’t modify the storage tier.

• 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.

Examples¶

The following example updates the storage lifecycle policy to expire closed accounts after 30 days.

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