snowflake-cloud-data-platform - Snowflake - 在我替换它之前想要恢复到以前版本的表

Question

我需要将表恢复到运行 CREATE OR REPLACE 语句之前的状态(即表仍然填充)。

我可以在历史记录中看到 QueryID，但我终生不记得我是如何还原更改的

Answer 1

解决方案已由 Francesco & Nick 提供，但这里以示例的形式提供了更多细节...基本上，您需要重命名当前表并取消删除 旧表。这就像一个先入后出的堆栈，当前表位于顶部，您需要将其弹出(重命名)才能到达前一个表:

我在 SQL 中有一些注释供您遵循:

-- Create the initial table
create table dont_drop_me(col1 varchar, col2 varchar)
;

-- Insert 4 rows of some sample data
insert overwrite into dont_drop_me values
('col1_row1', 'col2_row1'),
('col1_row2', 'col2_row2'),
('col1_row3', 'col2_row3'),
('col1_row4', 'col2_row4')
;

-- Replace the table (by accident?). New table has an extra column to prove changes.
create or replace table dont_drop_me(col1 varchar, col2 varchar, col3 varchar);

-- Now the new table contains no data but has 1 extra column
select * from dont_drop_me;
-- +----+----+----+
-- |COL1|COL2|COL3|
-- +----+----+----+

-- View what tables are on the history. The top row is the current table, the second
-- row is the first table that we replaced
show tables history like 'dont_drop_me';
-- +------------------------------+------------+-------------+-----------+-----+-------+----------+----+-----+--------+--------------+------------------------------+--------------------+---------------+-------------------+----------------------------+-------------------------+-----------+
-- |created_on                    |name        |database_name|schema_name|kind |comment|cluster_by|rows|bytes|owner   |retention_time|dropped_on                    |automatic_clustering|change_tracking|search_optimization|search_optimization_progress|search_optimization_bytes|is_external|
-- +------------------------------+------------+-------------+-----------+-----+-------+----------+----+-----+--------+--------------+------------------------------+--------------------+---------------+-------------------+----------------------------+-------------------------+-----------+
-- |2021-02-26 04:56:23.948 -08:00|DONT_DROP_ME|SIMON_DB     |PUBLIC     |TABLE|       |          |0   |0    |SYSADMIN|1             |NULL                          |OFF                 |OFF            |OFF                |NULL                        |NULL                     |N          |
-- |2021-02-26 04:56:19.610 -08:00|DONT_DROP_ME|SIMON_DB     |PUBLIC     |TABLE|       |          |4   |1024 |SYSADMIN|1             |2021-02-26 04:56:24.073 -08:00|OFF                 |OFF            |OFF                |NULL                        |NULL                     |N          |
-- +------------------------------+------------+-------------+-----------+-----+-------+----------+----+-----+--------+--------------+------------------------------+--------------------+---------------+-------------------+----------------------------+-------------------------+-----------+

-- We need to rename existing object to move it off the top of the stack so that we can recover the first one
alter table dont_drop_me rename to renamed_dont_drop_me;

-- Now view what tables are in the history again. You can see that the first table created has moved to the top of the stack
show tables history like 'dont_drop_me';
-- +------------------------------+------------+-------------+-----------+-----+-------+----------+----+-----+--------+--------------+------------------------------+--------------------+---------------+-------------------+----------------------------+-------------------------+-----------+
-- |created_on                    |name        |database_name|schema_name|kind |comment|cluster_by|rows|bytes|owner   |retention_time|dropped_on                    |automatic_clustering|change_tracking|search_optimization|search_optimization_progress|search_optimization_bytes|is_external|
-- +------------------------------+------------+-------------+-----------+-----+-------+----------+----+-----+--------+--------------+------------------------------+--------------------+---------------+-------------------+----------------------------+-------------------------+-----------+
-- |2021-02-26 04:56:19.610 -08:00|DONT_DROP_ME|SIMON_DB     |PUBLIC     |TABLE|       |          |4   |1024 |SYSADMIN|1             |2021-02-26 04:56:24.073 -08:00|OFF                 |OFF            |OFF                |NULL                        |NULL                     |N          |
-- +------------------------------+------------+-------------+-----------+-----+-------+----------+----+-----+--------+--------------+------------------------------+--------------------+---------------+-------------------+----------------------------+-------------------------+-----------+

-- Now undrop the table and prove that it is the old one (the one with 4 rows and 2 columns)
undrop table dont_drop_me;
select * from dont_drop_me;
-- +---------+---------+
-- |COL1     |COL2     |
-- +---------+---------+
-- |col1_row1|col2_row1|
-- |col1_row2|col2_row2|
-- |col1_row3|col2_row3|
-- |col1_row4|col2_row4|
-- +---------+---------+