Create table snapshots
This document describes how to create a snapshot of a table by using the
Google Cloud console, the CREATE SNAPSHOT TABLE
SQL statement, the
bq cp --snapshot
command,
or the jobs.insert
API. This
document 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 create a table snapshot, and the predefined IAM roles that grant those permissions.
Permissions
To create a table snapshot, you need the following permissions:
Permission | Resource | Notes |
---|---|---|
All of the following:bigquery.tables.get bigquery.tables.getData bigquery.tables.createSnapshot bigquery.datasets.get bigquery.jobs.create
|
The table that you want to snapshot. | Because snapshot expiration deletes the snapshot at a later time, to
create a snapshot with an expiration time you must have the
bigquery.tables.deleteSnapshot permission. |
bigquery.tables.create bigquery.tables.updateData
|
The dataset that contains the table snapshot. |
Roles
The predefined BigQuery roles that provide the required permissions are as follows:
Role | Resource | Notes |
---|---|---|
At least one of the following:bigquery.dataViewer bigquery.dataEditor bigquery.dataOwner And at least one of the following: bigquery.jobUser bigquery.studioUser bigquery.user bigquery.studioAdmin bigquery.admin
|
The table that you want to snapshot. | Only bigquery.dataOwner , bigquery.admin , and
bigquery.studioAdmin can be used for creating a snapshot with an
expiration time. |
At least one of the following:bigquery.dataEditor bigquery.dataOwner bigquery.studioAdmin bigquery.admin
|
The dataset that contains the new table snapshot. |
Limitations
For information about table snapshot limitations, see table snapshot limitations.
In addition, table snapshot creation is subject to the following limitations, which apply to all table copy jobs:
- When you create a table snapshot, its name must adhere to the same naming rules as when you create a table.
- Table snapshot creation is subject to BigQuery limits on copy jobs.
- The table snapshot dataset must be in the same region, and under the same organization, as the dataset that contains the table you are taking a snapshot of. For example, you cannot create a table snapshot in a US-based dataset of a table located in an EU-based dataset. You would need to make a copy of the table instead.
- The time that BigQuery takes to create table snapshots might vary significantly across different runs because the underlying storage is managed dynamically.
- When creating a table snapshot using the BigQuery CLI, the snapshot has the default encryption key of the destination dataset. When creating a table snapshot using SQL, the snapshot has the same encryption key as the source table.
Create a table snapshot
Best practice is to create a table snapshot in a different dataset from the base table. This practice allows the base table to be restored from its table snapshot even if the base table's dataset is accidentally deleted.
When you create a table snapshot, you specify the table you want to snapshot and a unique name for the table snapshot. You can optionally specify the time of the snapshot and the table snapshot's expiration.
Create a table snapshot with an expiration
You can create a snapshot of a table that expires after 24 hours 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 you want to snapshot.
Click the name of the table you want to snapshot.
In the table pane that appears, click Snapshot.
In the Create table snapshot pane that appears, enter the Project, Dataset, and Table information for the new table snapshot.
In the Expiration time field, enter the date and time for 24 hours from now.
Click Save.
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 SNAPSHOT_PROJECT_ID.SNAPSHOT_DATASET_NAME.SNAPSHOT_NAME CLONE TABLE_PROJECT_ID.TABLE_DATASET_NAME.TABLE_NAME OPTIONS ( expiration_timestamp = TIMESTAMP 'TIMESTAMP_VALUE');
Replace the following:
SNAPSHOT_PROJECT_ID
: the project ID of the project in which to create the snapshot.SNAPSHOT_DATASET_NAME
: the name of the dataset in which to create the snapshot.SNAPSHOT_NAME
: the name of the snapshot you are creating.TABLE_PROJECT_ID
: the project ID of the project that contains the table you are creating the snapshot from.TABLE_DATASET_NAME
: the name of the dataset that contains the table you are creating the snapshot from.TABLE_NAME
: the name of the table you are creating the snapshot from.TIMESTAMP_VALUE
: A timestamp value representing the date and time 24 hours from now.
Click
Run.
For more information about how to run queries, see Run an interactive query.
bq
Enter the following command in the Cloud Shell:
bq cp \ --snapshot \ --no_clobber \ --expiration=86400 \ TABLE_PROJECT_ID:TABLE_DATASET_NAME.TABLE_NAME \ SNAPSHOT_PROJECT_ID:SNAPSHOT_DATASET_NAME.SNAPSHOT_NAME
Replace the following:
TABLE_PROJECT_ID
: the project ID of the project that contains the table you are creating the snapshot from.TABLE_DATASET_NAME
: the name of the dataset that contains the table you are creating the snapshot from.TABLE_NAME
: the name of the table you are creating the snapshot from.SNAPSHOT_PROJECT_ID
: the project ID of the project in which to create the snapshot.SNAPSHOT_DATASET_NAME
: the name of the dataset in which to create the snapshot.SNAPSHOT_NAME
: the name of the snapshot 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": "TABLE_PROJECT_ID", "datasetId": "TABLE_DATASET_NAME", "tableId": "TABLE_NAME" } ], "destinationTable": { "projectId": "SNAPSHOT_PROJECT_ID", "datasetId": "SNAPSHOT_DATASET_NAME", "tableId": "SNAPSHOT_NAME" }, "operationType": "SNAPSHOT", "writeDisposition": "WRITE_EMPTY", "destinationExpirationTime":"TIMESTAMP_VALUE" } } } |
Replace the following:
TABLE_PROJECT_ID
: the project ID of the project that contains the table you are creating the snapshot from.TABLE_DATASET_NAME
: the name of the dataset that contains the table you are creating the snapshot from.TABLE_NAME
: the name of the table you are creating the snapshot from.SNAPSHOT_PROJECT_ID
: the project ID of the project in which to create the snapshot.SNAPSHOT_DATASET_NAME
: the name of the dataset in which to create the snapshot.SNAPSHOT_NAME
: the name of the snapshot you are creating.TIMESTAMP_VALUE
: A timestamp value representing the date and time 24 hours from now.
As with tables, if an expiration is not specified, then the table snapshot expires after the default table expiration time or the dataset that contains the table snapshot.
Create a table snapshot using time travel
You can create a table snapshot of a table as it was one hour ago 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 you want to snapshot.
Click the name of the table you want to snapshot.
In the table pane that appears, click Snapshot.
In the Create table snapshot pane that appears, enter the Project, Dataset, and Table information for the new table snapshot.
In the Snapshot time field, enter the date and time for 1 hour ago.
Click Save.
SQL
Use the
CREATE SNAPSHOT TABLE
DDL statement
with a FOR SYSTEM_TIME AS OF
clause:
In the Google Cloud console, go to the BigQuery page.
In the query editor, enter the following statement:
CREATE SNAPSHOT TABLE SNAPSHOT_PROJECT_ID.SNAPSHOT_DATASET_NAME.SNAPSHOT_NAME CLONE TABLE_PROJECT_ID.TABLE_DATASET_NAME.TABLE_NAME FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);
Replace the following:
SNAPSHOT_PROJECT_ID
: the project ID of the project in which to create the snapshot.SNAPSHOT_DATASET_NAME
: the name of the dataset in which to create the snapshot.SNAPSHOT_NAME
: the name of the snapshot you are creating.TABLE_PROJECT_ID
: the project ID of the project that contains the table you are creating the snapshot from.TABLE_DATASET_NAME
: the name of the dataset that contains the table you are creating the snapshot from.TABLE_NAME
: the name of the table you are creating the snapshot 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:
bq cp \ --no_clobber \ --snapshot \ TABLE_PROJECT_ID:TABLE_DATASET_NAME.TABLE_NAME@-3600000 \ SNAPSHOT_PROJECT_ID:SNAPSHOT_DATASET_NAME.SNAPSHOT_NAME
Replace the following:
TABLE_PROJECT_ID
: the project ID of the project that contains the table you are creating the snapshot from.TABLE_DATASET_NAME
: the name of the dataset that contains the table you are creating the snapshot from.TABLE_NAME
: the name of the table you are creating the snapshot from.SNAPSHOT_PROJECT_ID
: the project ID of the project in which to create the snapshot.SNAPSHOT_DATASET_NAME
: the name of the dataset in which to create the snapshot.SNAPSHOT_NAME
: the name of the snapshot 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": "TABLE_PROJECT_ID", "datasetId": "TABLE_DATASET_NAME", "tableId": "TABLE_NAME@-360000" } ], "destinationTable": { "projectId": "SNAPSHOT_PROJECT_ID", "datasetId": "SNAPSHOT_DATASET_NAME", "tableId": "SNAPSHOT_NAME" }, "operationType": "SNAPSHOT", "writeDisposition": "WRITE_EMPTY" } } } |
Replace the following:
TABLE_PROJECT_ID
: the project ID of the project that contains the table you are creating the snapshot from.TABLE_DATASET_NAME
: the name of the dataset that contains the table you are creating the snapshot from.TABLE_NAME
: the name of the table you are creating the snapshot from.SNAPSHOT_PROJECT_ID
: the project ID of the project in which to create the snapshot.SNAPSHOT_DATASET_NAME
: the name of the dataset in which to create the snapshot.SNAPSHOT_NAME
: the name of the snapshot you are creating.
For more information about specifying a past version of a table, see Accessing historical data using time travel.
Table access control
To control access to tables in BigQuery, see Introduction to table access controls.
When you create a table snapshot, table-level access to the table snapshot is set as follows:
- If the table snapshot overwrites an existing table, then the table-level access for the existing table is maintained. Tags aren't copied from the base table.
- If the table snapshot is a new resource, then the table-level access for the table snapshot is determined by the access policies of the dataset in which the table snapshot is created. Additionally, tags are copied from the base table to the table snapshot.
What's next
- Update a table snapshot's description, expiration date, or access policy.
- Restore a table snapshot.
- Create monthly snapshots of a table by using a service account that runs a scheduled query.