CREATE SNAPSHOT POLICY¶

语法¶

CREATE [OR REPLACE] SNAPSHOT POLICY [IF NOT EXISTS] <name>
   [ WITH RETENTION LOCK ]
   [ SCHEDULE = '{ <num> MINUTE | USING CRON <expr> <time_zone> }' ]
   [ EXPIRE_AFTER_DAYS = <days_integer> ]
   [ COMMENT = <string> ];

必填参数¶

name

Identifier for the snapshot policy; must be unique for your account.

此外，标识符必须以字母字符开头，且不能包含空格或特殊字符，除非整个标识符字符串放在双引号内（例如，"My object"）。放在双引号内的标识符也区分大小写。

有关更多信息，请参阅 标识符要求。

可选参数¶

OR REPLACE

If a snapshot policy with this name already exists, delete it and create a new one. This clause is mutually exclusive with IF NOT EXISTS.

IF NOT EXISTS

Creates the snapshot policy only if there isn't a snapshot policy with the same name. If a snapshot policy already exists, the command returns a success message even though it has no effect. This clause is mutually exclusive with OR REPLACE.

WITH RETENTION LOCK

Specifies the mandatory retention period for snapshots. Snapshots with retention locks can't be deleted, even by a privileged user. For more information, see the restrictions for a snapshot with a retention lock.

备注

Only a user with the APPLY SNAPSHOT RETENTION LOCK privilege can create a snapshot policy with retention lock.

重要

Applying a snapshot policy with a retention lock to a snapshot set is irreversible. Due to the strong guarantees that are needed for regulatory compliance, after you put a retention lock on a snapshot set, you can't revoke the lock. Snowflake support also can't revoke such a retention lock. Plan carefully before you set a retention lock on a snapshot set with a long expiration period, to avoid unexpected storage charges for undeletable snapshot sets, and the schemas and databases that contain them.

If a Snowflake organization is deleted, the organization is no longer a Snowflake customer. In this case, Snowflake deletes all snapshots, including those with retention locks. Deleting a Snowflake organization requires the involvement of Snowflake support. It isn't something that an administrator can do by accident.

SCHEDULE = '{ num MINUTE | USING CRON expr time_zone }'

Specifies the schedule for creating snapshots of an object.

备注

The minimum schedule for snapshots must be 60 minutes.

Each snapshot policy must have one or both of the schedule and expiration period properties. For more information, see Snapshot policy.

• USING CRON expr time_zone

  Specifies a cron expression and time zone for the point in time a snapshot of an object is created. Supports a subset of standard cron utility syntax.

  有关时区列表，请参阅 ` tz 数据库时区列表 <https://en.wikipedia.org/wiki/List_of_tz_database_time_zones (link removed)>`_ （在维基百科中）。

  cron 表达式由以下字段组成：

  # __________ minute (0-59)
  # | ________ hour (0-23)
  # | | ______ day of month (1-31, or L)
  # | | | ____ month (1-12, JAN-DEC)
  # | | | | __ day of week (0-6, SUN-SAT, or L)
  # | | | | |
  # | | | | |
    * * * * *
  

  支持以下特殊字符：

  *

  通配符。指定字段的任何出现。

  L

  Stands for "last". When used in the day-of-week field, it lets you specify constructs such as "the last Friday" ("5L") of a given month. In the day-of-month field, it specifies the last day of the month.

  /n

  Indicates the nth instance of a given unit of time. Each quanta of time is computed independently. For example, if 4/3 is specified in the month field, then the snapshot is scheduled for April, July and October (that is, every 3 months, starting with the 4th month of the year). The same schedule is maintained in subsequent years. That is, the snapshot is not scheduled to run in January (3 months after the October run).

  备注

  • The cron expression currently evaluates against the specified time zone only. Altering the TIMEZONE parameter value for the account (or setting the value at the user or session level) does not change the time zone for the snapshot.

  • The cron expression defines all valid run times for the snapshot. Snowflake attempts to create a snapshot based on this schedule; however, any valid run time is skipped if a previous run has not completed before the next valid run time starts.

  • When both a specific day of month and day of week are included in the cron expression, then the snapshot is scheduled on days satisfying either the day of the month or the day of the week. For example, SCHEDULE = 'USING CRON 0 0 10-20 * TUE,THU UTC' schedules a snapshot at 0AM (midnight) on any 10th to 20th day of the month and also on any Tuesday or Thursday outside of those dates.

• num MINUTE

  Specifies an interval (in minutes) of wait time between snapshots. Accepts positive integers only.

  还支持 num M 语法。

  To avoid ambiguity, a base interval time is set in the following circumstances:

  • When the object is created (using CREATE SNAPSHOT SET ... WITH SNAPSHOT POLICY).

  • When a different interval is set (using ALTER SNAPSHOT SET ... APPLY SNAPSHOT POLICY or ALTER SNAPSHOT POLICY ... SET SCHEDULE).

  The base interval time starts the interval counter from the current clock time. For example, if an INTERVAL value of 10 is set and the scheduled snapshot is enabled at 9:03 AM, then the next snapshot is created at 9:13 AM, 9:23 AM, and so on. Note that we make a best effort to ensure absolute precision, but only guarantee that a snapshot does not execute before the set interval occurs (that is, in the current example, the snapshot could first run at 9:14 AM, but will definitely not run at 9:12 AM).

EXPIRE_AFTER_DAYS = days_integer

Specifies the number of days until the snapshot expires. Snowflake automatically deletes expired snapshots. If this parameter is not specified, snapshots remain in the snapshot set until they are manually deleted from the set.

• Minimum value: 1.

• Maximum value: 3653 (roughly 10 years) if you don't specify the SCHEDULE clause. If you specify the SCHEDULE clause, the maximum depends on how often snapshots are taken. For more information, see Limitations of snapshots during the public preview period.

备注

Each snapshot policy must have one or both of the schedule and expiration period properties. For more information, see Snapshot policy.

COMMENT = 'string_literal'

Specifies a comment for the snapshot policy.

默认：无值

访问控制要求¶

用于执行此操作的 角色 必须至少具有以下 权限：

有关创建具有指定权限集的自定义角色的说明，请参阅 创建自定义角色。

有关对 安全对象 执行 SQL 操作的相应角色和权限授予的一般信息，请参阅 访问控制概述。

使用说明¶

• Time Travel and Failsafe retention do not apply to snapshots. A snapshot can't be recovered after it expires.

• 关于元数据：

  注意

  客户应确保在使用 Snowflake 服务时，不会将个人数据（用户对象除外）、敏感数据、出口管制数据或其他受监管数据作为元数据输入。有关更多信息，请参阅 Snowflake 中的元数据字段。

示例¶

Create a snapshot policy that creates a snapshot every hour and expires after 90 days:

CREATE SNAPSHOT POLICY hourly_snapshot_policy
  SCHEDULE = '60 MINUTE'
  EXPIRE_AFTER_DAYS = 90
  COMMENT = 'Hourly snapshots that expire after 90 days';

Create a snapshot policy with a retention lock that creates a snapshot every 24 hours and expires after 90 days. The snapshots created using this snapshot policy can't be modified or deleted before the expiration period ends:

CREATE SNAPSHOT POLICY daily_snapshot_policy_with_lock
  WITH RETENTION LOCK
  SCHEDULE = '1440 MINUTE'
  EXPIRE_AFTER_DAYS = 90
  COMMENT = 'regulatory backups expire after 90 days with retention lock';

Create a snapshot policy using a cron expression for the schedule. The following statement creates a policy that creates snapshots every Tuesday and Friday of the week at 11PM:

CREATE SNAPSHOT POLICY twice_weekly_snapshot_policy
  SCHEDULE = 'USING CRON 0 23 * * 2,5 UTC'
  EXPIRE_AFTER_DAYS = 7
  COMMENT = 'Twice-weekly snapshots that expire after 7 days';