Get information about table snapshots

This document describes how to view the metadata for a BigQuery table snapshot in the Google Cloud Console, by querying the TABLE_SNAPSHOTS view of the INFORMATION_SCHEMA table, by using the bq show command, or by calling the tables.get API. It is intended for users who are familiar with BigQuery tables and table snapshots.

Permissions and roles

This section describes the Identity and Access Management (IAM) permission that you need to view the metadata for a table snapshot, and the predefined IAM roles that grant those permissions.

Permissions

To view a table snapshot's metadata, you need the following permission:

Permission Resource
bigquery.tables.get The table snapshot

Roles

The predefined BigQuery roles that provide the required permission are as follows:

Role Resource
Any of the following:

bigquery.metadataViewer
bigquery.dataViewer
bigquery.dataEditor
bigquery.dataOwner
bigquery.admin
The table snapshot

Get a table snapshot's metadata

The metadata for a table snapshot is similar to the metadata for a standard table, with the following differences:

  • An additional baseTableReference field identifies the base table that the snapshot was taken from.
  • The type field has the value SNAPSHOT.

You can view the metadata for the library_backup.books table snapshot, for example, 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.

  3. In the books pane that appears, you can do the following:

    • Click the Schema tab to view the table snapshot's schema and access policies.

    • Click the Details table to view the table snapshot's size, expiration, base table, snapshot time, and other information.

SQL

Run the following query:

SELECT *
  FROM
  myproject.library_backup.INFORMATION_SCHEMA.TABLE_SNAPSHOTS

For more information, see Table snapshot metadata.

bq

Enter the following command in the Cloud Shell:

Go to Cloud Shell

bq show --format=prettyjson library_backup.books

The output is similar to the following:

{
  "creationTime": "1593194331936",
   ...
  "snapshotDefinition": {
    "baseTableReference": {
      "datasetId": "library",
      "projectId": "myProject",
      "tableId": "books"
    },
    "snapshotTime": "2020-06-26T17:58:50.815Z"
  },
  "tableReference": {
    "datasetId": "library_backup",
    "projectId": "myProject",
    "tableId": "books"
  },
  "type": "SNAPSHOT"
}

API

Call the tables.get method with the following parameters:

Parameter Value
projectId myProject
datasetId library_backup
tableId books

The response body is similar to the following:

{
  "kind": "bigquery#table",
  "etag": "...",
  "id": "myProject:library_backup.books",
  "selfLink": "https://content-bigquery.googleapis.com/bigquery/v2/projects/myProject/datasets/library_backup/tables/books",
  "tableReference": {
    "projectId": "myProject",
    "datasetId": "library_backup",
    "tableId": "books"
  },
  "description": "books",
  "schema": {
    "fields": [
      ...
    ]
  },
  "numBytes": "637931",
  "numLongTermBytes": "0",
  "numRows": "33266",
  "creationTime": "1593194331936",
  "lastModifiedTime": "1593194331936",
  "type": "SNAPSHOT",
  "location": "US",
  "snapshotDefinition": {
    "baseTableReference": {
      "projectId": "myProject",
      "datasetId": "library",
      "tableId": "books"
    },
    "snapshotTime": "2020-06-26T17:58:50.815Z"
  }
}

What's next