How can I undelete a BigQuery table?

Question

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

Answer 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 | +----+