I need to revert a table back to the state it was in before a CREATE OR REPLACE statement was run (i.e. the table is still populated).
I can see the QueryID in the history but can't for the life of me remember how I revert the change
Yes you can do this.
You need to rename the current table and undrop the old one. It's like a fist-in-last-out stack where the current table sits at the top and you need to pop it off (rename it) to get to the previous one:
I've got some comments in the SQL for you to follow though:
-- 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|
-- +---------+---------+