22 Oct 19 ·  nVector in Bigquery ·        Bookmark ·  Report ·  More actions.. Lock comments ·  Pin thread

How can I undelete from a BigQuery table? Recovering data from deleted table from Web UI

Accidentally deleted one of my BigQuery tables? No Sweat. Here's the steps to recover the data from Bigquery table.

You can only undelete tables that were deleted within the last 7 days

For example if you have deleted from the table:

DELETE FROM `project.dataset.table` WHERE TRUE;

You can now use use the below query, (Make sure to pass the correct system time before the delete happened)

INSERT INTO `project.dataset.table`(COL1, COL2,...COLNAMES)
SELECT * FROM `project.dataset.table`
FOR SYSTEM TIME AS OF '2019-10-21 18:10:00';

Sounds crazy? We are trying to read from the table as of before the delete happened and then inserting into the same itself (which as of now is empty). 

This is a very handy functionality that you may end up using often 😉

The official documentation is here


posted on 22 Oct 19

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds