Getting information about a table snapshot

This document describes how to view the metadata for a BigQuery table snapshot 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 minimum predefined role that you need to view a table snapshot's metadata is the following:

Role Resource
bigquery.metadataViewer 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:

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"
  }
}

Table security

To control access to tables in BigQuery, see Introduction to table access controls.

What's next