BigQuery - Delete and Recover Table

Deleting a Table in BigQuery

Deleting a table provides the same two options: UI and SQL syntax. To delete a table in the UI, simply select the table you want to delete and choose "delete table". Since this is a permanent operation, you'll be prompted to type the table name prior to deletion.

Note − You can also DROP a table within the SQL environment.

To delete just the contents of a table and retain the data within, you can use the DELETE command which requires a WHERE clause.

To delete anything without parameters, you can use the following query −

DELETE FROM project.dataset.table
WHERE 1=1

Note − Use this query with extreme caution.

Recovering a Table in BigQuery

Despite the two-step deletion process, it is still very possible to accidentally DROP or delete a table. Consequently, the creators of BigQuery realized that there might be a need to provide users a way to recover tables that were prematurely deleted.

BigQuery Table Snapshots

It's not obvious while you are creating and working with tables in a SQL environment, but in the background, BigQuery is auto-saving your work to an extent.

BigQuery uses "snapshots" to periodically save tables as a way of providing an immediate backup, should the need arise. It's important to note that if a user restores a table from a snapshot they're not actually restoring their original table they're simply restoring to the snapshot or copy of the table.

This tool is not without constraints. BigQuery table snapshots only last 7 days. Therefore, it is only possible to restore a deleted table within 7 days of the initial deletion.

Two Ways to Recover a Table

BigQuery provides two ways to recover a table. Incidentally, neither of them use BigQuery SQL. They require accessing BigQuery through either the gcloud command line or programmatically by accessing the API. Either statement will hinge on selecting the correct timestamp of the snapshot to restore to.

1. Gcloud Command

Here, the command "bq cp" is "bq copy". "No-clobber" is a parameter that will instruct the command to fail if a destination table does not already exist.

bq cp \
 restore \ 
 no-clobber \
snapshot_project_id.snapshot_dataset_id.snapshot_table_id \ 
target_project_id.target_dataset_id.target_table_id

2. Python Function

Ideally, developers will implement precautions to avoid the need for restoration. However, should the worst happen, BigQuery provides this fail-safe.