在保留期内的任何一天，你都可以在一张“滴滴和再造”桌子上旅行。

Question

如果存留期为30天，但该表每天被删除并重新创建，是否有可能看到10天前的表？

如果表被截断，而不是重新创建，是否有可能回到第30天？

取消删除表之前，可能会还原该表的最新版本。它能在保留期内恢复任何版本吗？

Answer 1

这是个有趣的问题。如果表被多次删除，我们可以执行UNDROP。在这里可以找到一个很好的解释- https://community.snowflake.com/s/article/Solution-Unable-to-access-Deleted-time-travel-data-even-within-retention-period。

BwE#example-dropping-and-restoring-a-table-multiple-times

我也测试过了，如下所示-

参考下面的历史

SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>select query_id,query_text,start_time from table(information_schema.query_history()) where query_text like '%test_undrop_1%';
+--------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+-------------------------------+
| QUERY_ID                             | QUERY_TEXT                                                                                                                    | START_TIME                    |
|--------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+-------------------------------|
| 01a31a99-0000-81fe-0001-fa120003d75e | select query_id,query_text,start_time from table(information_schema.query_history()) where query_text like '%test_undrop_1%'; | 2022-03-22 14:13:58.953 -0700 |
| 01a31a99-0000-81c6-0001-fa120003f7ee | drop table test_undrop_1;                                                                                                     | 2022-03-22 14:13:55.098 -0700 |
| 01a31a99-0000-81fe-0001-fa120003d73e | create or replace table test_undrop_1(id number, name varchar2(10));                                                          | 2022-03-22 14:13:53.425 -0700 |
| 01a31a99-0000-81fe-0001-fa120003d72a | drop table test_undrop_1;                                                                                                     | 2022-03-22 14:13:46.968 -0700 |
| 01a31a99-0000-81c6-0001-fa120003f79e | create or replace table test_undrop_1(id1 number, name varchar2(10));                                                         | 2022-03-22 14:13:44.002 -0700 |
| 01a31a99-0000-81fe-0001-fa120003d70e | drop table test_undrop_1;                                                                                                     | 2022-03-22 14:13:36.078 -0700 |
| 01a31a99-0000-81c6-0001-fa120003f77e | select query_id,query_text,start_time from table(information_schema.query_history()) where query_text like '%test_undrop_1%'; | 2022-03-22 14:13:14.711 -0700 |
| 01a31a99-0000-81fe-0001-fa120003d70a | select count(*) from test_undrop_1;                                                                                           | 2022-03-22 14:13:04.640 -0700 |
| 01a31a98-0000-81fe-0001-fa120003d706 | select * from test_undrop_1;                                                                                                  | 2022-03-22 14:12:52.230 -0700 |
| 01a31a98-0000-81c6-0001-fa120003f75e | create or replace table test_undrop_1(id1 number, name1 varchar2(10));                                                        | 2022-03-22 14:12:43.734 -0700 |
+--------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+-------------------------------+

SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>select * from test_undrop_1;
+----+------+
| ID | NAME |
|----+------|
+----+------+
0 Row(s) produced. Time Elapsed: 0.760s
SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>alter table TEST_UNDROP_1 rename to test_undrop_1_1;
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.142s

UNDROP 1

SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>undrop table test_undrop_1;
+--------------------------------------------+
| status                                     |
|--------------------------------------------|
| Table TEST_UNDROP_1 successfully restored. |
+--------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.155s
SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>select * from test_undrop_1;
+----+------+
| ID | NAME |
|----+------|
+----+------+
0 Row(s) produced. Time Elapsed: 0.223s
SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>alter table TEST_UNDROP_1 rename to test_undrop_1_2;
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.191s

UNDROP 2

SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>undrop table test_undrop_1;
+--------------------------------------------+
| status                                     |
|--------------------------------------------|
| Table TEST_UNDROP_1 successfully restored. |
+--------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.155s
SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>select * from test_undrop_1;
+-----+------+
| ID1 | NAME |
|-----+------|
+-----+------+
0 Row(s) produced. Time Elapsed: 0.140s
SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>alter table TEST_UNDROP_1 rename to test_undrop_1_3;
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.396s

DROP-3(耶！有我的桌子版)

SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>undrop table test_undrop_1;
+--------------------------------------------+
| status                                     |
|--------------------------------------------|
| Table TEST_UNDROP_1 successfully restored. |
+--------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.149s
SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>select * from test_undrop_1;
+-----+-------+
| ID1 | NAME1 |
|-----+-------|
+-----+-------+
0 Row(s) produced. Time Elapsed: 0.178s

Answer 2

是的，您可以在表的时间旅行期间的任何一点上查询它。

Undrop将恢复该表，就像它被删除时的情况一样。

若要在以前的时间点还原表，需要使用CREATE…克隆…

Answer 3

1. 如果存留期为30天，但该表每天被删除并重新创建，是否有可能看到10天前的表？

开发计划署命令仅还原表的最新版本。但是@Pankaj的测试表明，您仍然可以恢复任何尚未清除的丢失的表。

若要检查历史记录中表的所有版本(删除)，可以使用以下命令

SHOW TABLES HISTORY LIKE '<table_name>';

然后进行一系列的开发和重新命名，直到恢复要恢复的表的版本为止。

然而，这不是一个好做法。如果你每天重新创建表格，减少它的保留期。如果你想恢复一个月的历史，做一个截断而不是下降。

1. 如果表被截断，而不是重新创建，是否有可能回到第30天？

是。

create table tbl_clone clone tbl at (timestamp => dateadd('day',-30,current_timestamp()));

1. 取消删除表之前，可能会还原该表的最新版本。它能在保留期内恢复任何版本吗？

是的，正如@Pankaj已经提到和指出的那样。