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

  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 you want to snapshot.

  3. Click the name of the table you want to snapshot.

  4. In the table pane that appears, click Snapshot.

    Click Snapshot

  5. In the Create table snapshot pane that appears, enter the Project, Dataset, and Table information for the new table snapshot.

  6. In the Expiration time field, enter the date and time for 24 hours from now.

  7. Click Save.

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

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

  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 you want to snapshot.

  3. Click the name of the table you want to snapshot.

  4. In the table pane that appears, click Snapshot.

    Click Snapshot

  5. In the Create table snapshot pane that appears, enter the Project, Dataset, and Table information for the new table snapshot.

  6. In the Snapshot time field, enter the date and time for 1 hour ago.

  7. Click Save.

SQL

Use the CREATE SNAPSHOT TABLE DDL statement with a FOR SYSTEM_TIME AS OF clause:

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

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