CREATE SNAPSHOT SET¶

Creates a snapshot set for a table, a schema, or a database. Once the snapshot set exists, you can add a new backup (snapshot) to the snapshot set at any time by running an ALTER SNAPSHOT SET command. Snowflake also adds snapshots to the snapshot set automatically, if you defined a schedule in a snapshot policy and associated that snapshot policy with the snapshot set.

Each snapshot set represents a set of backups for a specific table, or the objects in a specific schema, or the objects in a specific database. That way, you can make your backups very granular or very comprehensive. And the backups for each table, schema, or database can have their own independent schedules.

For the kinds of objects that are included in schema snapshots and database snapshots, see Snapshot objects.

另请参阅：: ALTER SNAPSHOT SET, DROP SNAPSHOT SET, SHOW SNAPSHOT SETS, CREATE SNAPSHOT POLICY

语法¶

CREATE [OR REPLACE] SNAPSHOT SET [IF NOT EXISTS] <name>
   FOR [DYNAMIC] TABLE <table_name>
   [ WITH SNAPSHOT POLICY <policy_name> ]
   [ COMMENT = <string> ]

Copy

CREATE [OR REPLACE] SNAPSHOT SET [IF NOT EXISTS] <name>
  FOR SCHEMA <schema_name>
   [ WITH SNAPSHOT POLICY <policy_name> ]
   [ COMMENT = <string> ]

Copy

CREATE [OR REPLACE] SNAPSHOT SET [IF NOT EXISTS] <name>
  FOR DATABASE <database_name>
   [ WITH SNAPSHOT POLICY <policy_name> ]
   [ COMMENT = <string> ]

Copy

必填参数¶

name

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

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

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

FOR [DYNAMIC] TABLE table_name

Specifies the name of the table or dynamic table. In that case, the snapshot set represents backups of a single table.

FOR SCHEMA schema_name

Specifies the name of the schema. In that case, the snapshot set represents backups of all the tables and other objects in a specific schema.

FOR DATABASE database_name

Specifies the name of the database. In that case, the snapshot set represents backups of all the tables, schemas, and other objects in a specific database.

可选参数¶

OR REPLACE

If a snapshot set with this name already exists, delete it and create a new one. If the snapshot set can't be deleted because of snapshot policy rules for retention locks, legal holds, and expiry times, the command fails. This clause is mutually exclusive with IF NOT EXISTS.

IF NOT EXISTS

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

WITH SNAPSHOT POLICY policy_name

Specifies the name of the snapshot policy for the set. The snapshot policy defines properties of the snapshot set such as the schedule for backups, the retention period for each snapshot, and whether to prevent snapshots from being removed before the end of the retention period.

If you omit this parameter from the CREATE SNAPSHOT SET command, you can apply a policy later with the ALTER SNAPSHOT SET command.

重要

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.

COMMENT = 'string_literal'

Specifies a comment for the snapshot set.

默认：无值

访问控制要求¶

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

权限	备注
CREATE SNAPSHOT SET	The role used to create a snapshot set must have this privilege granted on the schema in which the snapshot set is created. To actually create the snapshot set also requires the appropriate privilege on the object that's the subject of the snapshot set: SELECT for a table snapshot, or USAGE for a schema snapshot or database snapshot.
SELECT	The role used to create a snapshot set for a table must have the SELECT privilege on that table.
USAGE	The role used to create a snapshot set for a schema or database must have the USAGE privilege on that schema or database.
APPLY SNAPSHOT RETENTION LOCK	The role used to apply a snapshot policy with retention lock on a snapshot set must have this privilege on the account.

These privileges are required on the currently active primary role, not a secondary role.

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

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

使用说明¶

关于元数据：

注意

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

重要

If the snapshot policy has a retention lock applied to it, and there are any unexpired snapshots in the snapshot set, then you can't delete the snapshot set. In that case, you must wait for all the snapshots in the set to expire. This restriction applies even to privileged roles such as ACCOUNTADMIN, and to Snowflake support. For that reason, be careful when specifying retention lock and a long expiration period in a snapshot policy.

示例¶

Create a snapshot set named t1_snapshots for table t1:

CREATE SNAPSHOT SET t1_snapshots
  FOR TABLE t1;

Copy

Create a snapshot set t1_snapshots for table t1 with a snapshot policy:

CREATE SNAPSHOT SET t1_snapshots
  FOR TABLE t1
  WITH SNAPSHOT POLICY hour_snapshot_policy;

Copy

Create a snapshot set s1_snapshots for schema s1 with a snapshot policy:

CREATE SNAPSHOT SET s1_snapshots
  FOR SCHEMA s1
  WITH SNAPSHOT POLICY hour_snapshot_policy;

Copy

Create a snapshot set d1_snapshots for database d1 with a snapshot policy:

CREATE SNAPSHOT SET d1_snapshots
  FOR DATABASE d1
  WITH SNAPSHOT POLICY hour_snapshot_policy;

Copy