How to Undelete a bigquery table

    You can undelete big query table using table snapshot decorators and copy command. Deleted table will be available in big query temporary location for next 48 hours. So it is possible to retrieve your data that were deleted within the last 2 days :)

But you can’t undelete the table in following situations.

• You have recreated new table with same name
• Tables which were deleted time is more then 2 days

Step 1: Find Unix timestamp when the table was alive:

    First find the unix timestamp when the table was alive. For example if you were deleted the table at today 10 AM, then you can use any timestamp before 10 AM to retrieve the table.

Unix/Linux: 

$ date -d '06/12/2012 07:21:22' +"%s"
1339485682

Mac OS :

$ date -juf "%Y-%m-%d %H:%M:%S" "2018-01-08 14:45:00" +%s
1515422700

Above commands will return seconds value but we need milliseconds value, so multiply it with 1000 to convert to milliseconds 

Step 2: Copy table snapshot to temporary table

    Once you got the appropriate timestamp, then you can use bq copy command to copy deleted table snapshot using table decorator. It will copy the data where available at the point of timestamp we passed in table decorators to temporary table. 

$ bq cp dataset1.table1@ 1515422700000 dataset1.temp --project project-dev




Waiting on bqjob_r4d8174e2e41ae73_0000014a5af2a028_1 ... (0s)

    Current status: DONE   

Tables 'project-dev:dataset1.table1@1515422700000' successfully copied to    

    'project-dev:dataset1.temp'

Step 3 : Verify temporary table data:

$ bq query "select * from dataset1.table1"  --project project-dev




Waiting on bqjob_r5967bea49ed9e97f_0000014a5af34dec_1 ... (0s)

    Current status: DONE

Step 4: Rename the temporary table to actual table name:

$ bq cp dataset1.temp dataset1.table1 --project project-dev




Waiting on bqjob_r3c0bb9302fb81d59_0000014a5af2dc7b_1 ... (0s)

    Current status: DONE   

Tables 'project-dev:dataset1.temp' successfully copied to

    'project-dev:dataset1.table1’

Step 5:  Delete temporary table once you copied all data to destination table:

$ bq rm dataset1.temp



rm: remove table 'helixdata2:dataset1.temp'? (y/N) y