Copy table snapshots

This document describes how to make a copy of a table snapshot using the Google Cloud console, the CREATE SNAPSHOT TABLE SQL statement, the bq cp -s --no_clobber command, or the jobs.insert API. It is intended for users who are familiar with BigQuery table snapshots.

Permissions and roles

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

Permissions

To copy a table snapshot, you need the following permissions:

Permission Resource
All of the following:

bigquery.tables.get
bigquery.tables.getData
bigquery.tables.createSnapshot 		The table snapshot that you want to copy.
bigquery.tables.create The dataset that contains the copy of the table snapshot.

Roles

The predefined BigQuery roles that provide the required permissions are the following:

Role Resource
Any of the following:

bigquery.dataViewer
bigquery.dataEditor
bigquery.dataOwner
bigquery.admin 		The table snapshot that you want to copy.
Any of the following:

bigquery.dataEditor
bigquery.dataOwner
bigquery.admin 		The dataset that contains copy of the table snapshot.

Limitations

You can't copy a table snapshot over an existing table or table snapshot.

For more information about table snapshot limitations, see table snapshot limitations.

Copy a table snapshot

The process for making a copy of a table snapshot is similar to the process for creating a snapshot of a table. The difference is that you specify the table snapshot that you want to copy as the source table.

You can make a copy of a table snapshot by using one of the following options:

Console

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

    Go to BigQuery

  2. In the Explorer pane, expand the project and dataset nodes of the table snapshot you want to look at.

  3. Click the name of the table snapshot.

  4. In the snapshot pane that appears, click Copy.

  5. In the Copy table pane that appears, enter the Project, Dataset, and Table information for the destination table snapshot copy.

  6. Click Copy.

SQL

Use the CREATE SNAPSHOT TABLE DDL statement:

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

    Go to BigQuery

  2. In the query editor, enter the following statement:

    CREATE SNAPSHOT TABLE COPY_PROJECT_ID.COPY_DATASET_NAME.COPY_NAME
CLONE SNAPSHOT_PROJECT_ID.SNAPSHOT_DATASET_NAME.SNAPSHOT_NAME;

    Replace the following:

    • COPY_PROJECT_ID: the project ID of the project in which to create the snapshot copy.
    • COPY_DATASET_NAME: the name of the dataset in which to create the snapshot copy.
    • COPY_NAME: the name of the snapshot copy you are creating.
    • SNAPSHOT_PROJECT_ID: the project ID of the project that contains the snapshot you are copying.
    • SNAPSHOT_DATASET_NAME: the name of the dataset that contains the snapshot you are copying.
    • SNAPSHOT_NAME: the name of the snapshot you are copying.

  3. Click Run.

For more information about how to run queries, see Running interactive queries.

bq

Enter the following command in the Cloud Shell:

Go to Cloud Shell 

bq cp \
-s \
--no_clobber \
SNAPSHOT_PROJECT_ID:SNAPSHOT_DATASET_NAME.SNAPSHOT_NAME \
COPY_PROJECT_ID:COPY_DATASET_NAME.COPY_NAME

Replace the following:

  • SNAPSHOT_PROJECT_ID: the project ID of the project that contains the snapshot you are copying.
  • SNAPSHOT_DATASET_NAME: the name of the dataset that contains the snapshot you are copying.
  • SNAPSHOT_NAME: the name of the snapshot you are copying.
  • COPY_PROJECT_ID: the project ID of the project in which to create the snapshot copy.
  • COPY_DATASET_NAME: the name of the dataset in which to create the snapshot copy.
  • COPY_NAME: the name of the snapshot copy you are creating.

The --no_clobber flag is required.

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 
{
  "configuration": {
    "copy": {
      "sourceTables": [
        {
          "projectId": "SNAPSHOT_PROJECT_ID",
          "datasetId": "SNAPSHOT_DATASET_NAME",
          "tableId": "SNAPSHOT_NAME"
        }
      ],
      "destinationTable": {
        "projectId": "COPY_PROJECT_ID",
        "datasetId": "COPY_DATASET_NAME",
        "tableId": "COPY_NAME"
      },
      "operationType": "SNAPSHOT",
      "writeDisposition": "WRITE_EMPTY"
    }
  }
}

Replace the following:

  • SNAPSHOT_PROJECT_ID: the project ID of the project that contains the snapshot you are copying.
  • SNAPSHOT_DATASET_NAME: the name of the dataset that contains the snapshot you are copying.
  • SNAPSHOT_NAME: the name of the snapshot you are copying.
  • COPY_PROJECT_ID: the project ID of the project in which to create the snapshot copy.
  • COPY_DATASET_NAMECOPY_DATASET_NAME: the name of the dataset in which to create the snapshot copy.
  • COPY_NAME: the name of the snapshot copy you are creating.

What's next