Restore table snapshots

This document describes how to create a writeable table from a table snapshot by using the Google Cloud console, a CREATE TABLE CLONE query, a bq cp command, or the jobs.insert API. It is intended for users who are familiar with table snapshots.

Permissions and roles

This section describes the Identity and Access Management (IAM) permissions that you need to create a writeable table from a table snapshot, and the predefined IAM roles that grant those permissions.

Permissions

To create a writeable table from a table snapshot, you need the following permissions:

Permission	Resource
All of the following: `bigquery.tables.get` `bigquery.tables.getData` `bigquery.tables.restoreSnapshot`	The table snapshot that you want to copy into a writeable table.
`bigquery.tables.create`	The dataset that contains the destination table.

Roles

The predefined BigQuery roles that provide the required permissions are as follows:

Role	Resource
Any of the following: `bigquery.dataEditor` `bigquery.dataOwner` `bigquery.admin`	The table snapshot that you want to copy into a writeable table.
Any of the following: `bigquery.dataEditor` `bigquery.dataOwner` `bigquery.admin`	The dataset that contains the destination table.

Restore a table snapshot

To create a writeable table from a snapshot, specify the table snapshot that you want to copy and the destination table. The destination table can be a new table, or you can overwrite an existing table with the table snapshot.

Restore to a new table

You can restore a table snapshot into a new table by using one of the following options:

Console

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery
In the Explorer pane, expand the project and dataset nodes of the table snapshot you want to restore from.
Click the name of the table snapshot.
In the table snapshot pane that appears, click Restore.
In the Restore snapshot pane that appears, enter the Project, Dataset, and Table information for the new table.
Click Save.

SQL

Use the CREATE TABLE CLONE DDL statement:

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery
In the query editor, enter the following statement:
```
CREATE TABLE TABLE_PROJECT_ID.TABLE_DATASET_NAME.NEW_TABLE_NAME
CLONE SNAPSHOT_PROJECT_ID.SNAPSHOT_DATASET_NAME.SNAPSHOT_NAME;
```
Replace the following:
- TABLE_PROJECT_ID: the project ID of the project in which to create the new table.
- TABLE_DATASET_NAME: the name of the dataset in which to create the new table.
- NEW_TABLE_NAME: the name of the new table.
- SNAPSHOT_PROJECT_ID: the project ID of the project that contains the snapshot you are restoring from.
- SNAPSHOT_DATASET_NAME: the name of the dataset that contains the snapshot you are restoring from.
- SNAPSHOT_NAME: the name of the snapshot you are restoring from.
Click Run.

For more information about how to run queries, see Run an interactive query.

bq

Enter the following command in the Cloud Shell:

Go to Cloud Shell

bq cp \
--restore \
--no_clobber \
SNAPSHOT_PROJECT_ID:SNAPSHOT_DATASET_NAME.SNAPSHOT_NAME \
TABLE_PROJECT_ID:TABLE_DATASET_NAME.NEW_TABLE_NAME

Replace the following:

SNAPSHOT_PROJECT_ID: the project ID of the project that contains the snapshot you are restoring from.
SNAPSHOT_DATASET_NAME: the name of the dataset that contains the snapshot you are restoring from.
SNAPSHOT_NAME: the name of the snapshot you are restoring from.
TABLE_PROJECT_ID: the project ID of the project in which to create the new table.
TABLE_DATASET_NAME: the name of the dataset in which to create the new table.
NEW_TABLE_NAME: the name of the new table.

The --no_clobber flag instructs the command to fail if the destination table already exists.

API

Call the jobs.insert method with the following parameters:

Parameter Value

projectId The project ID of the project to bill for this operation.

Request body

Parameter	Value
`projectId`	The project ID of the project to bill for this operation.
Request body	{ "configuration": { "copy": { "sourceTables": [ { "projectId": "`SNAPSHOT_PROJECT_ID`", "datasetId": "`SNAPSHOT_DATASET_NAME`", "tableId": "`SNAPSHOT_NAME`" } ], "destinationTable": { "projectId": "`TABLE_PROJECT_ID`", "datasetId": "`TABLE_DATASET_NAME`", "tableId": "`NEW_TABLE_NAME`" }, "operationType": "RESTORE", "writeDisposition": "WRITE_EMPTY" } } }

{
  "configuration": {
    "copy": {
      "sourceTables": [
        {
          "projectId": "SNAPSHOT_PROJECT_ID",
          "datasetId": "SNAPSHOT_DATASET_NAME",
          "tableId": "SNAPSHOT_NAME"
        }
      ],
      "destinationTable": {
        "projectId": "TABLE_PROJECT_ID",
        "datasetId": "TABLE_DATASET_NAME",
        "tableId": "NEW_TABLE_NAME"
      },
      "operationType": "RESTORE",
      "writeDisposition": "WRITE_EMPTY"
    }
  }
}

Replace the following:

SNAPSHOT_PROJECT_ID: the project ID of the project that contains the snapshot you are restoring from.
SNAPSHOT_DATASET_NAME: the name of the dataset that contains the snapshot you are restoring from.
SNAPSHOT_NAME: the name of the snapshot you are restoring from.
TABLE_PROJECT_ID: the project ID of the project in which to create the new table.
TABLE_DATASET_NAME: the name of the dataset in which to create the new table.
NEW_TABLE_NAME: the name of the new table.

If an expiration is not specified, then the destination table expires after the default table expiration time for the dataset that contains the destination table.

Overwrite an existing table

You can overwrite an existing table with a table snapshot by using one of the following options:

Console

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery
In the Explorer pane, expand the project and dataset nodes of the table snapshot you want to restore from.
Click the name of the table snapshot.
In the table snapshot pane that appears, click Restore.
In the Restore snapshot pane that appears, enter the Project, Dataset, and Table information for the existing table.
Select Overwrite table if it exists.
Click Save.

SQL

Use the CREATE TABLE CLONE DDL statement:

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery
In the query editor, enter the following statement:
```
CREATE OR REPLACE TABLE TABLE_PROJECT_ID.TABLE_DATASET_NAME.TABLE_NAME
CLONE SNAPSHOT_PROJECT_ID.SNAPSHOT_DATASET_NAME.SNAPSHOT_NAME;
```
Replace the following:
- TABLE_PROJECT_ID: the project ID of the project in which to create the new table.
- TABLE_DATASET_NAME: the name of the dataset that contains the table you are overwriting.
- TABLE_NAME: the name of the table you are overwriting.
- SNAPSHOT_PROJECT_ID: the project ID of the project that contains the snapshot you are restoring from.
- SNAPSHOT_DATASET_NAME: the name of the dataset that contains the snapshot you are restoring from.
- SNAPSHOT_NAME: the name of the snapshot you are restoring from.
Click Run.

For more information about how to run queries, see Run an interactive query.

bq

Enter the following command in the Cloud Shell:

Go to Cloud Shell

bq cp \
--restore \
--force \
SNAPSHOT_PROJECT_ID:SNAPSHOT_DATASET_NAME.SNAPSHOT_NAME \
TABLE_PROJECT_ID:TABLE_DATASET_NAME.TABLE_NAME

Replace the following:

SNAPSHOT_PROJECT_ID: the project ID of the project that contains the snapshot you are restoring from.
SNAPSHOT_DATASET_NAME: the name of the dataset that contains the snapshot you are restoring from.
SNAPSHOT_NAME: the name of the snapshot you are restoring from.
TABLE_PROJECT_ID: the project ID of the project in which to create the new table.
TABLE_DATASET_NAME: the name of the dataset that contains the table you are overwriting.
TABLE_NAME: the name of the table you are overwriting.

API

Call the jobs.insert method with the following parameters:

Parameter Value

projectId The project ID of the project to bill for this operation.

Request body

Parameter	Value
`projectId`	The project ID of the project to bill for this operation.
Request body	{ "configuration": { "copy": { "sourceTables": [ { "projectId": "`SNAPSHOT_PROJECT_ID`", "datasetId": "`SNAPSHOT_DATASET_NAME`", "tableId": "`SNAPSHOT_NAME`" } ], "destinationTable": { "projectId": "`TABLE_PROJECT_ID`", "datasetId": "`TABLE_DATASET_NAME`", "tableId": "`TABLE_NAME`" }, "operationType": "RESTORE", "writeDisposition": "WRITE_TRUNCATE" } } }

{
  "configuration": {
    "copy": {
      "sourceTables": [
        {
          "projectId": "SNAPSHOT_PROJECT_ID",
          "datasetId": "SNAPSHOT_DATASET_NAME",
          "tableId": "SNAPSHOT_NAME"
        }
      ],
      "destinationTable": {
        "projectId": "TABLE_PROJECT_ID",
        "datasetId": "TABLE_DATASET_NAME",
        "tableId": "TABLE_NAME"
      },
      "operationType": "RESTORE",
      "writeDisposition": "WRITE_TRUNCATE"
    }
  }
}

Replace the following:

SNAPSHOT_PROJECT_ID: the project ID of the project that contains the snapshot you are restoring from.
SNAPSHOT_DATASET_NAME: the name of the dataset that contains the snapshot you are restoring from.
SNAPSHOT_NAME: the name of the snapshot you are restoring from.
TABLE_PROJECT_ID: the project ID of the project in which to create the new table.
TABLE_DATASET_NAME: the name of the dataset that contains the table you are overwriting.
TABLE_NAME: the name of the table you are overwriting.

If an expiration is not specified, then the destination table expires after the default table expiration time for the dataset that contains the destination table.

What's next

List the table snapshots of a specified base table.