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
In the Google Cloud console, go to the BigQuery page.
In the Explorer pane, expand the project and dataset nodes of the table snapshot you want to look at.
Click the name of the table snapshot.
In the snapshot pane that appears, click Copy.
In the Copy table pane that appears, enter the Project, Dataset, and Table information for the destination table snapshot copy.
Click Copy.
SQL
Use the
CREATE SNAPSHOT TABLE
DDL statement:
In the Google Cloud console, go to the BigQuery page.
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.
Click
Run.
For more information about how to run queries, see Running interactive queries.
bq
Enter the following command in the 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.