CREATE STORAGE LIFECYCLE POLICY¶

Creates a new storage lifecycle policy in the current or specified schema, or replaces an existing policy. The policy runs an expression on arguments that you specify to determine which rows to expire in the table that the policy is attached to. The arguments in a policy refer to columns in your tables.

After you create a policy, use the ALTER TABLE command to add the policy to a table.

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

Syntax¶

CREATE [ OR REPLACE ] STORAGE LIFECYCLE POLICY [ IF NOT EXISTS ] <name>
  AS ( <arg_name> <arg_type> [ , ... ] )
  RETURNS BOOLEAN -> <body>
  ARCHIVE_TIER = { COOL | COLD }
  [ ARCHIVE_FOR_DAYS = <number_of_days> ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]

Copy

Required parameters¶

name

String that specifies the identifier for the storage lifecycle policy. This must be unique for the schema.

In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (for example, "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more information, see 식별자 요구 사항.

AS ( arg_name arg_type [ , ... ] )

The signature for the policy. You must include at least one argument in the signature.

A signature specifies a set of attributes that must be considered to determine whether the row is ready for expiration. The attribute values come from the database object (table).

RETURNS BOOLEAN -> body

A storage lifecycle policy must evaluate to true or false. A user that queries a table protected by a storage lifecycle policy sees rows in the output based on how the body is written.

body

SQL expression that Snowflake uses to determine which rows to expire.

To transform the data, you can use built-in functions such as 조건식 함수 or user-defined functions (UDFs).

참고

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

ARCHIVE_TIER = { COOL | COLD }

Specifies the type of storage tier to use for archiving rows. After you set the ARCHIVE_TIER for a policy, you can’t modify it.

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

Optional parameters¶

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'

Specifies a comment for the storage lifecycle 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 오브젝트에 대한 태그 할당량.

Access control requirements¶

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

Privilege	Object	Notes
CREATE STORAGE LIFECYCLE POLICY	Schema	None

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 사용자 지정 역할 만들기.

For general information about roles and privilege grants for performing SQL actions on securable objects, see 액세스 제어의 개요.

Usage notes¶

CREATE OR REPLACE <object> statements are atomic. That is, when an object is replaced, the old object is deleted and the new object is created in a single transaction.
- If you specify OR REPLACE and the policy is attached to any objects, the command fails.
- You can’t use OR REPLACE and IF NOT EXISTS together for this command.
- If you want to replace an existing storage lifecycle policy and need to see the current definition of the policy, call the GET_DDL function or run the DESCRIBE STORAGE LIFECYCLE POLICY command.
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.
Snowflake ignores row access policies and masking policies in favor of storage lifecycle policies. Masked and protected rows are evaluated for expiration.
You cannot change the policy signature if the policy is attached to a table. If you need to change the signature, use the DROP STORAGE LIFECYCLE POLICY command and create a new policy.

주의

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 Snowflake의 메타데이터 필드.

Examples¶

The following lifecycle policy moves data from rows that correspond to closed accounts and are more than 60 days old into archive storage (COOL tier).

CREATE STORAGE LIFECYCLE POLICY example_policy
  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 = 180;

Copy