TABLE_SNAPSHOTS view

TheINFORMATION_SCHEMA.TABLE_SNAPSHOTS view contains metadata about yourtable snapshots. For more information, seeIntroduction to tablesnapshots.

Required permissions

To query theINFORMATION_SCHEMA.TABLE_SNAPSHOTS view, you need thebigquery.tables.list Identity and Access Management (IAM) permission for the dataset.Theroles/bigquery.metadataViewer predefined role includes the requiredpermission.

For more information about BigQuery permissions, seeAccess control with IAM.

Schema

When you query theINFORMATION_SCHEMA.TABLE_SNAPSHOTS table, the resultscontain one row for each table snapshot in the specified dataset or region.

TheINFORMATION_SCHEMA.TABLE_SNAPSHOTS table has the following schema.The standard table that thetable snapshot was taken from is called thebase table.

For stability, we recommend that you explicitly list columns in your information schema queries instead ofusing a wildcard (SELECT *). Explicitly listing columns prevents queries frombreaking if the underlying schema changes.

Scope and syntax

Queries against this view must include a dataset or a region qualifier. Forqueries with a dataset qualifier, you must have permissions for the dataset.For queries with a region qualifier, you must have permissions for the project.For moreinformation seeSyntax.The following table explains the region and resource scopes for this view:

• Optional:PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.
• REGION: anydataset region name. For example,`region-us`.
• DATASET_ID: the ID of your dataset. For more information, seeDataset qualifier.

  Note: You must usea region qualifier to queryINFORMATION_SCHEMA views. The location of the query execution must match the region of theINFORMATION_SCHEMA view.

Example

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

Example

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

SELECT*FROM`myproject`.mydataset.INFORMATION_SCHEMA.TABLE_SNAPSHOTS;

The result is 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 |+----------------+---------------+-----------------+--------------------+-------------------+-----------------+-----------------------------+