Getting table snapshot metadata using INFORMATION_SCHEMA

BigQuery maintains a table named INFORMATION_SCHEMA.TABLE_SNAPSHOTS, which contains information (metadata) about your table snapshots. This document describes how to query the INFORMATION_SCHEMA.TABLE_SNAPSHOTS table to get metadata for the table snapshots in a specified dataset or region. 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 view the INFORMATION_SCHEMA.TABLE_SNAPSHOTS table, and the predefined IAM roles that grant those permissions.

Permissions

To view the INFORMATION_SCHEMA.TABLE_SNAPSHOTS table, you need the following permission:

Permission Resource
bigquery.tables.list The dataset

Roles

The minimum predefined role that you need to view the INFORMATION_SCHEMA.TABLE_SNAPSHOTS table is the following:

Role Resource
bigquery.metadataViewer The dataset

Syntax

Queries against the INFORMATION_SCHEMA.TABLE_SNAPSHOTS table must have a dataset or region qualifier.

-- Returns metadata for the table snapshots in the specified dataset.
SELECT * FROM myDataset.INFORMATION_SCHEMA.TABLE_SNAPSHOTS;

-- Returns metadata for the table snapshots in the specified region.
SELECT * FROM `region-us`.INFORMATION_SCHEMA.TABLE_SNAPSHOTS;

The TABLE_SNAPSHOTS table

When you query the INFORMATION_SCHEMA.TABLE_SNAPSHOTS table, the results contain one row for each table snapshot in the specified dataset or region.

The INFORMATION_SCHEMA.TABLE_SNAPSHOTS table has the following schema. The standard table that the table snapshot was taken from is called the base table.

Column name Data type Value
table_catalog STRING The name of the project that contains the table snapshot
table_schema STRING The name of the dataset that contains the table snapshot
table_name STRING The name of the table snapshot
base_table_catalog STRING The name of the project that contains the base table
base_table_schema STRING The name of the dataset that contains the base table
base_table_name STRING The name of the base table
snapshot_time TIMESTAMP The time that the table snapshot was created

Example

The following query retrieves metadata for the table snapshots in the mydataset dataset. In this example, it displays the table snapshot myproject.mydataset.mytablesnapshot, which was taken from the base table myproject.mydataset.mytable on May 14, 2021, at 12 PM UTC.

Run the query as follows:

Console

  1. In the Cloud Console, open the BigQuery page.

    Go to BigQuery

  2. Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires Standard SQL syntax, which is the default syntax in the Cloud Console.

    SELECT *
    FROM
    myproject.mydataset.INFORMATION_SCHEMA.TABLE_SNAPSHOTS
    
  3. Click Run.

bq

Use the bq query command and specify standard SQL syntax by using the --use_legacy_sql=false flag, as follows:

bq query --nouse_legacy_sql \
'SELECT *
 FROM
 myproject.mydataset.INFORMATION_SCHEMA.TABLE_SNAPSHOTS'

The results look similar to the following:

  +----------------+---------------+-----------------+--------------------+-------------------+-----------------+-----------------------------+
  | table_catalog  | table_schema  | table_name      | base_table_catalog | base_table_schema | base_table_name | snapshot_time               |
  +----------------+---------------+-----------------+----------------------------------------------------------------------------------------+
  | myproject      | mydataset     | mytablesnapshot | myProject          | mydataset         | mytable         | 2021-05-14 12:00:00.000 UTC |
  +----------------+---------------+-----------------+--------------------+-------------------+-----------------+-----------------------------+