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 --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.

For example, make a copy of the library_backup.books table snapshot in the library_snapshots_backup dataset as follows:

Console

  1. In the Cloud Console, go to the BigQuery page.

    Go to BigQuery

  2. In the Explorer pane, expand the project and dataset nodes of the library_backup.books table snapshot.

  3. Click the table snapshot you want to make a copy of, in this case, books.

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

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

  6. Click Copy.

SQL

Run the following query:

CREATE SNAPSHOT TABLE
  myproject.library_snapshot_backup.books
  CLONE myproject.library_backup.books

bq

Enter the following command in the Cloud Shell:

Go to Cloud Shell

bq cp --no_clobber --expiration=86400 library_backup.books \
  library_snapshot_backup.books

The --no_clobber flag is required.

API

Call the jobs.insert method with the following parameters:

Parameter Value
projectId myProject
Request body
{
  "configuration": {
    "copy": {
      "sourceTables": [
        {
          "projectId": "myProject",
          "datasetId": "library_backup",
          "tableId": "books"
        }
      ],
      "destinationTable": {
        "projectId": "myProject",
        "datasetId": "library_snapshot_backup",
        "tableId": "books"
      },
      "writeDisposition": "WRITE_EMPTY"
    }
  }
}

What's next