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
$ date -juf "%Y-%m-%d %H:%M:%S" "2018-01-08 14:45:00" +%s
1515422700Step 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-devWaiting on bqjob_r4d8174e2e41ae73_0000014a5af2a028_1 ... (0s)Current status: DONETables '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
$ 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-devWaiting on bqjob_r3c0bb9302fb81d59_0000014a5af2dc7b_1 ... (0s)Current status: DONETables '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.temprm: remove table 'helixdata2:dataset1.temp'? (y/N) y