45

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

Jordan Tigani
  • 26,089
  • 4
  • 60
  • 63

4 Answers4

68

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 |
+----+
Jordan Tigani
  • 26,089
  • 4
  • 60
  • 63
  • Just a little addition , if you get this error "Error In Query Operation: Cannot start a job without a project id" . Add your project id. For bq shell :- [ bq shell --project_id=myprojectid ] – Yogesh Yadav Aug 27 '15 at 16:37
  • @ Jordan Tigani Can I get the snapshot of a dataset ? I want to know which tables are present at a particular time stamp because I am not sure which tables I deleted accidentally. – Yogesh Yadav Aug 27 '15 at 16:44
  • 3
    Important note: you *cannot* undelete tables if they've been recreated. This means this snapshot recovery cannot be used to recover from mistakes in combination with the "create tables on demand while streaming" template tables feature. We learned this the hard way: we accidentally deleted a production table, which was immediately re-created so we could not recover from the snapshot. – Evan Jones Aug 08 '16 at 14:04
  • We ran this from Terminal Command Line and get "sqlite3.OperationalError: attempt to write a readonly database"??? What are we missing? – Praxiteles Mar 13 '18 at 22:21
  • It appears that the answer of where to run these commands is in the Cloud Shell for your project on console.cloud.google.com. They seem to be running successfully for us there – Praxiteles Mar 13 '18 at 22:47
  • @Jordan you should be aware that this method doesn't work when you run `CREATE OR REPLACE table` sql. That replaces the table and the snapshots are not preserved so the un-delete doesn't work. If you know a way for that, it would be good to add, it just happened to us. – Pentium10 Apr 24 '19 at 06:23
  • @Pentium10 https://issuetracker.google.com/issues/131816256 to vote the feature up – Felipe Hoffa Jun 06 '19 at 01:27
  • Simpler to write old_table_name@-3600000 for 1 hour ago. And don't forget to include the dataset for the new table - even if its the same. – intotecho Jul 16 '19 at 11:51
  • The current GCP document on Time Travel does not mention a limitation of the table being recreated. See https://cloud.google.com/bigquery/docs/time-travel – AstroTom Jan 08 '23 at 15:39
14

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
ammportal
  • 991
  • 1
  • 11
  • 27
Israel Gómez
  • 176
  • 1
  • 2
  • 1
    A useful addition to the syntax of selected answer. Worked for me, when I multiplied by 10 to get 10 hours. – intotecho Jul 16 '19 at 11:37
13

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:

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'
Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325
0

You can use the BigQuery CLI, specifying the old tables name, the desired date in EPOCH time and the new table name:

<project>:<dataset>.<table>@<DESIRED_EPOCH_TIME> <project>:<dataset>.<table>

For example:

bq cp my_project.old_dataset.old_table_name@1551880188000 my_project.new_dataset.new_table_name

Will copy the content that was in , in the March 9th 2019 (EPOCH:1551880188000), to the new table

shayms8
  • 671
  • 6
  • 13
  • 28