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

nVector

posted on 22 Oct 19