How can I undelete a BigQuery table?

I've accidentally deleted one of my BigQuery tables. Is it possible to get it back? The API doesn't seem to support undelete.

Solution 1:

Undelete in BigQuery is possible via table copy and snapshot decorators. That is, you can copy a snapshot of the table from before the table was deleted.

BigQuery used to have restrictions on undeletes, but over time, those have been removed.

Here is an example using bq, but you can do the same thing with the BigQuery Web UI.

First let's create a dummy bigquery dataset and table that we're going to delete:

$ bq mk -d dataset1
Dataset 'helixdata2:dataset1' successfully created.
$ bq query --destination_table=dataset1.table1 "SELECT 17 as a"
Waiting on bqjob_ra0dedbee5cb4228_0000014a5af133d6_1 ... (0s) 
Current status: DONE   
+----+
| a  |
+----+
| 17 |
+----+

Now, grab the current unix timestamp from a time when the table was alive.

$ date +%s
1418864998

Note that this time is in seconds, we'll need miliseconds.

Remove the table 'accidentally'

$ bq rm dataset1.table1
rm: remove table 'helixdata2:dataset1.table1'? (y/N) y

Now we can undelete the table by copying a snapshot:

$ bq cp dataset1.table1@1418864998000 dataset1.temp
Waiting on bqjob_r4d8174e2e41ae73_0000014a5af2a028_1 ... (0s) 
    Current status: DONE    
Tables 'helixdata2:dataset1.table1@1418864998000' successfully copied to     
    'helixdata2:dataset1.temp'

(note we multiplied the time by 1000 since we want milliseconds) This copied an old snapshot of the table to dataset1.temp. Let's copy it back to the old location and then remove the temp table.

$ bq cp dataset1.temp dataset1.table1
Waiting on bqjob_r3c0bb9302fb81d59_0000014a5af2dc7b_1 ... (0s) 
    Current status: DONE    
Tables 'helixdata2:dataset1.temp' successfully copied to 
    'helixdata2:dataset1.table1'
$ bq rm dataset1.temp
rm: remove table 'helixdata2:dataset1.temp'? (y/N) y

Now let's verify that the table has been restored:

$ bq query "select * from dataset1.table1"
Waiting on bqjob_r5967bea49ed9e97f_0000014a5af34dec_1 ... (0s) 
    Current status: DONE   
+----+
| a  |
+----+
| 17 |
+----+

Solution 2:

Revovering how was your table 1hour ago (@-3600000 time in miliseconds):

bq cp my_project:old_dataset.old_table_name@-3600000 my_project:new_dataset.new_table_name

Solution 3:

As expressed in other answers, "undelete" will not work after a CREATE OR REPLACE table.

If you need to undelete a table that has been replaced, please upvote the following issue:

• https://issuetracker.google.com/issues/131816256

However, this is how you can get to an earlier version of a table with only #standardSQL:

SELECT *
FROM `project.dataset.table` 
FOR SYSTEM TIME AS OF '2019-06-05 18:10:00'

• https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#syntax