了解和使用 Time Travel¶

Snowflake Time Travel 允许在定义的时间段内的任何时间点访问历史数据（即，已更改或删除的数据）。

它是执行以下任务的强大工具：

• Restoring objects that might have been accidentally or intentionally deleted. You can restore individual objects, such as tables, or restore all the objects inside a container object by restoring an entire schema or database.
• 复制和备份过去关键点的数据。
• 分析指定时间段内的数据使用/操作。

Time Travel 简介¶

使用 Time Travel，您可以在定义的时间段内执行以下操作：

• 查询过去已更新或删除的数据。
• 创建过去特定时间点或之前的整个表、架构和数据库的克隆。
• 恢复表、架构、数据库和其他一些已删除的对象。

After the defined period of time has elapsed, the data is moved into Snowflake Fail-safe and these actions can no longer be performed.

Time Travel SQL 扩展¶

为了支持 Time Travel，已经实现了以下 SQL 扩展：

• AT | BEFORE clause which can be specified in SELECT statements and CREATE … CLONE commands (immediately after the object name). The clause uses one of the following parameters to pinpoint the exact historical data you want to access:

  • TIMESTAMP
  • OFFSET （与现在时间的时差，以秒为单位）
  • STATEMENT（语句的查询 ID）

• UNDROP <object> command for tables, schemas, databases, accounts, external volumes, and tags.

  

数据保留期

Snowflake Time Travel 的一个关键组成部分是数据保留期。

当表中的数据被修改时（包括删除数据或删除包含数据的对象），Snowflake 会保留更新前的数据状态。数据保留期指定了此历史数据的保留天数，因此可以对数据执行 Time Travel 操作（SELECT、CREATE … CLONE、UNDROP）。

标准保留期为 1 天（24 小时），并自动为所有 Snowflake 账户启用：

• For Snowflake Standard Edition, the retention period can be set to 0 (or unset back to the default of 1 day) at the account and object level (that is, databases, schemas, and tables).
• 对于 Snowflake Enterprise Edition（及更高版本）：
  • For transient databases, schemas, and tables, the retention period can be set to 0 (or unset back to the default of 1 day). The same is also true for temporary tables.
  • 对于永久数据库、架构和表，保留期可以设置为 0 到 90 天之间的任何值。

When the retention period ends for an object, the historical data is moved into Snowflake Fail-safe:

• 历史数据不可再用于查询。
• 无法再克隆过去的对象。
• 无法再恢复过去删除的对象。

要指定 Time Travel 的数据保留期，请执行以下操作：

• The DATA_RETENTION_TIME_IN_DAYS object parameter can be used by users with the ACCOUNTADMIN role to set the default retention period for your account.
• 在创建数据库、架构和单个表时，可以使用相同的参数显式替换默认值。
• 可以随时更改数据库、架构或表的数据保留期。
• The MIN_DATA_RETENTION_TIME_IN_DAYS account parameter can be set by users with the ACCOUNTADMIN role to set a minimum retention period for the account. This parameter does not alter or replace the DATA_RETENTION_TIME_IN_DAYS parameter value. However it may change the effective data retention time. When this parameter is set at the account level, the effective minimum data retention period for an object is determined by MAX(DATA_RETENTION_TIME_IN_DAYS, MIN_DATA_RETENTION_TIME_IN_DAYS).

限制

When using Time Travel, the following object types are not cloned:

• 外部表
• 内部 (Snowflake) 暂存区
• 可以为数据库克隆混合表，但不能为架构克隆混合表。
• User tasks in a database or schema are not cloned when using CREATE SCHEMA … TIMESTAMP. In the following example, tasks in the source schema (S1) are not cloned to the schema with a timestamp (S2) but are cloned to the schema without a timestamp (S3).
  Copy codeExpand code block

  CREATE SCHEMA S1;
  USE SCHEMA S1;
  CREATE TASK T1 AS SELECT 1;
  CREATE SCHEMA S2 CLONE S1 AT(TIMESTAMP => '2025-04-01 12:00:00');
    -- T1 is not cloned into S2
  CREATE SCHEMA S3 CLONE S1;
    -- T1 is cloned into S3

  Show lessSee more

  Scroll to top

启用和停用 Time Travel¶

启用 Time Travel 不需要任何任务。系统会自动启用标准的 1 天保留期。

However, you may want to upgrade to Snowflake Enterprise Edition to enable configuring longer data retention periods of up to 90 days for databases, schemas, and tables. Note that extended data retention requires additional storage which will be reflected in your monthly storage charges. For more information about storage charges, see Storage costs for Time Travel and Fail-safe.

Time Travel cannot be deactivated for an account. A user with the ACCOUNTADMIN role can set DATA_RETENTION_TIME_IN_DAYS to 0 at the account level, which means that all databases (and subsequently all schemas and tables) created in the account have no retention period by default; however, this default can be overridden at any time for any database, schema, or table.

A user with the ACCOUNTADMIN role can also set the MIN_DATA_RETENTION_TIME_IN_DAYS at the account level. This parameter setting enforces a minimum data retention period for databases, schemas, and tables. Setting MIN_DATA_RETENTION_TIME_IN_DAYS does not alter or replace the DATA_RETENTION_TIME_IN_DAYS parameter value. It may, however, change the effective data retention period for objects. When MIN_DATA_RETENTION_TIME_IN_DAYS is set at the account level, the data retention period for an object is determined by MAX(DATA_RETENTION_TIME_IN_DAYS, MIN_DATA_RETENTION_TIME_IN_DAYS).

Time Travel can be deactivated for individual databases, schemas, and tables by specifying DATA_RETENTION_TIME_IN_DAYS with a value of 0 for the object. However, if DATA_RETENTION_TIME_IN_DAYS is set to a value of 0, and MIN_DATA_RETENTION_TIME_IN_DAYS is set at the account level and is greater than 0, the higher value setting takes precedence.

如果 Time Travel 保留期设置为 0，则任何修改或删除的数据都将由后台进程移动到故障安全（对于永久表）或删除（对于瞬态表）。这可能需要很短的时间就会完成。在此期间，表存储指标中的 TIME_TRAVEL_BYTES 可能包含非零值，即使 Time Travel 保留期为 0 天。

指定对象的数据保留期

默认情况下，最长保留期为 1 天（一个 24 小时期限）。使用 Snowflake Enterprise Edition（及更高版本）时，您的账户的默认值可以设置为最多 90 天的任何值：

• When creating a table, schema, or database, the account default can be overridden using the DATA_RETENTION_TIME_IN_DAYS parameter in the command.
• If a retention period is specified for a database or schema, the period is inherited by default for all objects created in the database/schema.

A minimum retention period can be set on the account using the MIN_DATA_RETENTION_TIME_IN_DAYS parameter. If this parameter is set at the account level, the data retention period for an object is determined by MAX(DATA_RETENTION_TIME_IN_DAYS, MIN_DATA_RETENTION_TIME_IN_DAYS).

检查对象的数据保留期

To check the current retention period for a table, schema, or database, you can check the value of the retention_time column in the output of the corresponding SHOW command, such as SHOW TABLES, SHOW SCHEMAS, or SHOW DATABASES.

对于源自表、架构或数据库的对象（例如物化视图），您可以检查父对象的保留期。

For streams, you can check the value of the stale_after column in the output from the SHOW STREAMS command.

要包含有关已删除对象的信息，请在 SHOW 命令中添加 HISTORY 子句。

以下示例显示如何通过筛选 SHOW 命令的输出来检查某些对象的保留期。

以下示例检查特定命名表的保留期：

SHOW TABLES
  ->> SELECT "name", "retention_time"
        FROM $1
        WHERE "name" IN ('MY_TABLE1', 'MY_TABLE2');

以下示例检查是否有关闭 Time Travel 的架构：

SHOW SCHEMAS
  ->> SELECT "name", "retention_time"
        FROM $1
        WHERE "retention_time" = 0;

以下示例检查保留时间大于默认值的数据库。结果包括已经删除的数据库。

SHOW DATABASES HISTORY
  ->> SELECT "name", "retention_time", "dropped_on"
        FROM $1
        WHERE "retention_time" > 1;

更改对象的数据保留期

如果更改表的数据保留期，则新的保留期会影响所有处于活动状态的数据，以及当前在 Time Travel 中的任何数据。影响取决于您是增加还是减少期限：

Increasing Retention:

使当前在 Time Travel 中的数据保留更长的时间段。

例如，如果您有一个保留期为 10 天的表，并将保留期增加到 20 天，则本应在 10 天后删除的数据现在将再保留 10 天，然后再进入故障安全。

请注意，这不适用于超过 10 天且已移至故障安全的任何数据。

Decreasing Retention:

减少数据在 Time Travel 中的保留时间：

• 对于在保留期缩短后修改的活动数据，将适用新的较短期限。
• 对于当前在 Time Travel 中的数据：
  • 如果数据仍在新的较短期限内，则它仍保留在 Time Travel 中。
  • 如果数据超出新期限，则会进入故障安全。

例如，如果您有一个保留期为 10 天的表，并且将该保留期缩短为 1 天，则第 2 天到第 10 天的数据将移至故障安全，仅第 1 天的数据可通过 Time Travel 访问。

但是，将数据从 Time Travel 移动到故障安全的过程是由后台进程执行的，因此更改不会立即可见。Snowflake 保证数据将被移动，但没有指定该进程何时完成；在后台进程完成之前，数据仍可通过 Time Travel 访问。

To change the retention period for an object, use the appropriate ALTER <object> command. For example, to change the retention period for a table:

CREATE TABLE mytable(col1 NUMBER, col2 DATE) DATA_RETENTION_TIME_IN_DAYS=90;

ALTER TABLE mytable SET DATA_RETENTION_TIME_IN_DAYS=30;

删除的容器和对象保留继承

查询历史数据

When any DML operations are performed on a table, Snowflake retains previous versions of the table data for a defined period of time. This enables querying earlier versions of the data using the AT | BEFORE clause.

此子句支持在保留期内在表历史记录中的指定点处或紧接之前查询数据。指定点可以是基于时间的（例如，时间戳或与当前时间的时间偏移量），也可以是完整语句（例如 SELECT 或 INSERT）的 ID。

例如：

• The following query selects historical data from a table as of the date and time represented by the specified timestamp:

  Copy codeExpand code block

  SELECT * FROM my_table AT(TIMESTAMP => 'Wed, 26 Jun 2024 09:20:00 -0700'::timestamp_tz);

  Show lessSee more

  Scroll to top

• 以下查询从截至 5 分钟前的表中选择历史数据：

  Copy codeExpand code block

  SELECT * FROM my_table AT(OFFSET => -60*5);

  Show lessSee more

  Scroll to top

• 以下查询从表中选择历史数据，但不包括指定语句所做的任何更改：

  Copy codeExpand code block

  SELECT * FROM my_table BEFORE(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726');

  Show lessSee more

  Scroll to top

克隆历史对象

In addition to queries, the AT | BEFORE clause can be used with the CLONE keyword in the CREATE command for a table, schema, or database to create a logical duplicate of the object at a specified point in the object’s history. If you don’t specify a point in time, the clone defaults to the state of the object as of now (the CURRENT_TIMESTAMP value).

例如：

• The following CREATE TABLE statement creates a clone of a table as of the date and time represented by the specified timestamp:

  Copy codeExpand code block

  CREATE TABLE restored_table CLONE my_table
    AT(TIMESTAMP => 'Wed, 26 Jun 2024 01:01:00 +0300'::timestamp_tz);

  Show lessSee more

  Scroll to top

• The following CREATE SCHEMA statement creates a clone of a schema and all its objects as they existed 1 hour before the current time:

  Copy codeExpand code block

  CREATE SCHEMA restored_schema CLONE my_schema AT(OFFSET => -3600);

  Show lessSee more

  Scroll to top

• The following CREATE DATABASE statement creates a clone of a database and all its objects as they existed prior to the completion of the specified statement:

  Copy codeExpand code block

  CREATE DATABASE restored_db CLONE my_db
    BEFORE(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726');

  Show lessSee more

  Scroll to top

• The following CREATE DATABASE statement creates a clone of a database and all its objects as they existed four days ago, skipping any tables that have a data retention period of less than four days:
  Copy codeExpand code block

  CREATE DATABASE restored_db CLONE my_db
    AT(TIMESTAMP => DATEADD(days, -4, current_timestamp)::timestamp_tz)
    IGNORE TABLES WITH INSUFFICIENT DATA RETENTION;

  Show lessSee more

  Scroll to top

删除和恢复对象

以下部分解释了 DROP、SHOW 和 UNDROP 命令的 Time Travel 注意事项。

弃用对象

When a table, schema, or database is dropped, it is not immediately overwritten or removed from the system. Instead, it is retained for the data retention period for the object, during which time the object can be restored. Once dropped objects are moved to Fail-safe, you cannot restore them.

若要删除笔记本、表、架构或数据库，请使用以下命令：

• DROP NOTEBOOK
• DROP TABLE
• DROP SCHEMA
• DROP DATABASE

列出删除的对象

可以使用指定了 HISTORY 关键字的以下命令列出删除的对象：

• SHOW TABLES
• SHOW SCHEMAS
• SHOW DATABASES
• SHOW ACCOUNTS

例如：

Copy codeExpand code block

SHOW TABLES HISTORY LIKE 'load%' IN mytestdb.myschema;

SHOW SCHEMAS HISTORY IN mytestdb;

SHOW DATABASES HISTORY;

Show lessSee more

Scroll to top

输出包括所有被删除的对象和一个附加 DROPPED_ON 列，该列显示删除对象的日期和时间。如果一个对象被多次删除，则该对象的每个版本都将作为单独的行包含在输出中。

恢复对象

A dropped object that has not been purged from the system (that is, the object is displayed in the SHOW <object_type> HISTORY output) can be restored using the following commands:

• UNDROP NOTEBOOK
• UNDROP TABLE
• UNDROP SCHEMA
• UNDROP DATABASE
• UNDROP ICEBERG TABLE
• UNDROP DYNAMIC TABLE
• UNDROP EXTERNAL VOLUME
• UNDROP TAG
• UNDROP ACCOUNT

调用 UNDROP 会将对象恢复到发出 DROP 命令之前的最新状态。

例如：

Copy codeExpand code block

UNDROP TABLE mytable;

UNDROP SCHEMA myschema;

UNDROP DATABASE mydatabase;

UNDROP NOTEBOOK mynotebook;

Show lessSee more

Scroll to top

访问控制要求和名称解析

与删除对象类似，用户必须具有对象的 OWNERSHIP 权限才能恢复该对象。此外，用户必须对删除的对象将恢复到的数据库或架构的对象类型具有 CREATE 权限。

仅当前架构或当前数据库支持恢复表和架构，即使指定了完全限定的对象名称也是如此。

示例：多次删除和恢复表

In the following example, the mytestdb.public schema contains two tables: loaddata1 and proddata1. The loaddata1 table is dropped and recreated twice, creating three versions of the table:

• 当前版本
• 第二个（最新的）删除版本
• 第一个删除版本

然后，该示例演示了如何恢复表的两个删除版本：

1. First, the current table with the same name is renamed to loaddata3. This enables restoring the most recent version of the dropped table, based on the timestamp.
2. 然后，将恢复表的最新删除版本。
3. The restored table is renamed to loaddata2 to enable restoring the first version of the dropped table.
4. 最后，将恢复删除的表的第一个版本。

Copy codeExpand code block

SHOW TABLES HISTORY;

+---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------+
| created_on                      | name      | database_name | schema_name | kind  | comment | cluster_by | rows | bytes | owner  | retention_time | dropped_on                      |
|---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------|
| Tue, 17 Mar 2016 17:41:55 -0700 | LOADDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 48   | 16248 | PUBLIC | 1              | [NULL]                          |
| Tue, 17 Mar 2016 17:51:30 -0700 | PRODDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 12   | 4096  | PUBLIC | 1              | [NULL]                          |
+---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------+

DROP TABLE loaddata1;

SHOW TABLES HISTORY;

+---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------+
| created_on                      | name      | database_name | schema_name | kind  | comment | cluster_by | rows | bytes | owner  | retention_time | dropped_on                      |
|---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------|
| Tue, 17 Mar 2016 17:51:30 -0700 | PRODDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 12   | 4096  | PUBLIC | 1              | [NULL]                          |
| Tue, 17 Mar 2016 17:41:55 -0700 | LOADDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 48   | 16248 | PUBLIC | 1              | Fri, 13 May 2016 19:04:46 -0700 |
+---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------+

CREATE TABLE loaddata1 (c1 number);
INSERT INTO loaddata1 VALUES (1111), (2222), (3333), (4444);

DROP TABLE loaddata1;

CREATE TABLE loaddata1 (c1 varchar);

SHOW TABLES HISTORY;

+---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------+
| created_on                      | name      | database_name | schema_name | kind  | comment | cluster_by | rows | bytes | owner  | retention_time | dropped_on                      |
|---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------|
| Fri, 13 May 2016 19:06:01 -0700 | LOADDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 0    | 0     | PUBLIC | 1              | [NULL]                          |
| Tue, 17 Mar 2016 17:51:30 -0700 | PRODDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 12   | 4096  | PUBLIC | 1              | [NULL]                          |
| Fri, 13 May 2016 19:05:32 -0700 | LOADDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 4    | 4096  | PUBLIC | 1              | Fri, 13 May 2016 19:05:51 -0700 |
| Tue, 17 Mar 2016 17:41:55 -0700 | LOADDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 48   | 16248 | PUBLIC | 1              | Fri, 13 May 2016 19:04:46 -0700 |
+---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------+

ALTER TABLE loaddata1 RENAME TO loaddata3;

UNDROP TABLE loaddata1;

SHOW TABLES HISTORY;

+---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------+
| created_on                      | name      | database_name | schema_name | kind  | comment | cluster_by | rows | bytes | owner  | retention_time | dropped_on                      |
|---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------|
| Fri, 13 May 2016 19:05:32 -0700 | LOADDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 4    | 4096  | PUBLIC | 1              | [NULL]                          |
| Fri, 13 May 2016 19:06:01 -0700 | LOADDATA3 | MYTESTDB      | PUBLIC      | TABLE |         |            | 0    | 0     | PUBLIC | 1              | [NULL]                          |
| Tue, 17 Mar 2016 17:51:30 -0700 | PRODDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 12   | 4096  | PUBLIC | 1              | [NULL]                          |
| Tue, 17 Mar 2016 17:41:55 -0700 | LOADDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 48   | 16248 | PUBLIC | 1              | Fri, 13 May 2016 19:04:46 -0700 |
+---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------+

ALTER TABLE loaddata1 RENAME TO loaddata2;

UNDROP TABLE loaddata1;

+---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------+
| created_on                      | name      | database_name | schema_name | kind  | comment | cluster_by | rows | bytes | owner  | retention_time | dropped_on                      |
|---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------|
| Tue, 17 Mar 2016 17:41:55 -0700 | LOADDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 48   | 16248 | PUBLIC | 1              | [NULL]                          |
| Fri, 13 May 2016 19:05:32 -0700 | LOADDATA2 | MYTESTDB      | PUBLIC      | TABLE |         |            | 4    | 4096  | PUBLIC | 1              | [NULL]                          |
| Fri, 13 May 2016 19:06:01 -0700 | LOADDATA3 | MYTESTDB      | PUBLIC      | TABLE |         |            | 0    | 0     | PUBLIC | 1              | [NULL]                          |
| Tue, 17 Mar 2016 17:51:30 -0700 | PRODDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 12   | 4096  | PUBLIC | 1              | [NULL]                          |
+---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------+

Show lessSee more

Scroll to top