ALTER SNAPSHOT POLICY¶

Modifies the properties of a snapshot policy. The following changes are supported:

Rename the policy.
Add or update the comment for the policy.
Change the schedule and expiration settings for the policy. The schedule determines how often Snowflake automatically makes a backup and adds the resulting snapshot to the snapshot set that's governed by the policy. The expiration period determines how long each snapshot is retained before Snowflake automatically deletes it from the associated snapshot set.
Unset properties of the policy, so that they revert back to their default values.

另请参阅：: CREATE SNAPSHOT POLICY, DROP SNAPSHOT POLICY, SHOW SNAPSHOT POLICIES

语法¶

ALTER SNAPSHOT POLICY <name> RENAME TO <new_name>

ALTER SNAPSHOT POLICY <name> SET
  [ COMMENT = '<string_literal>' ]
  [ SCHEDULE = '{ <num> MINUTE | USING CRON <expr> <time_zone> }' ]
  [ EXPIRE_AFTER_DAYS = <days_integer> ]

ALTER SNAPSHOT POLICY <name> UNSET { COMMENT | SCHEDULE | EXPIRE_AFTER_DAYS }

Copy

参数¶

name

Specifies the identifier for the snapshot policy.

如果标识符包含空格或特殊字符，则整个字符串必须放在双引号内。放在双引号内的标识符也区分大小写。

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

RENAME TO new_name

Specifies a new identifier for the snapshot policy; must be unique for your account.

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

SET...

Specifies one or more properties to set for the snapshot policy (separated by blank spaces, commas, or new lines):

COMMENT = 'string_literal'

Specifies a comment for the snapshot policy.

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

Specifies the schedule for creating snapshots of an object.

备注

The minimum schedule for snapshots is 60 minutes.

Every policy must include a SCHEDULE clause, an EXPIRE_AFTER_DAYS clause, or both.

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 isn't 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.

备注

If the policy has a retention lock, you can increase the EXPIRE_AFTER_DAYS value, but you can't decrease that value.

Every policy must include a SCHEDULE clause, an EXPIRE_AFTER_DAYS clause, or both.

UNSET...

Unset one of the following properties for the snapshot policy. The property reverts to its default value.

COMMENT
SCHEDULE
EXPIRE_AFTER_DAYS

备注

You can unset the SCHEDULE property, or the EXPIRE_AFTER_DAYS property, but not both. For example, you might keep the EXPIRE_AFTER_DAYS property when you don't intend to create new snapshots, but you want existing snapshots to expire after a certain time.

访问控制要求¶

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

权限	备注
OWNERSHIP	The role used to modify a snapshot policy must have the OWNERSHIP privilege on the snapshot policy.
APPLY SNAPSHOT RETENTION LOCK	The role used to modify a snapshot policy with a retention lock must have this privilege on the account.

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

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

使用说明¶

关于元数据：

注意

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

示例¶

Add a comment to snapshot policy hourly_snapshot_policy:

ALTER SNAPSHOT POLICY hourly_snapshot_policy
  SET COMMENT = 'hourly snapshot expires in 90 days';

Copy

Change schedule for snapshot policy every_two_hours:

ALTER SNAPSHOT POLICY every_two_hours SET SCHEDULE = '120 MINUTE';

Copy

Revert the EXPIRE_AFTER_DAYS property back to its default value:

ALTER SNAPSHOT POLICY sample_snapshot_policy UNSET EXPIRE_AFTER_DAYS;

Copy