List table snapshots

This document describes how to get a list of the table snapshots in a BigQuery dataset in the Google Cloud console, by querying the INFORMATION_SCHEMA.TABLE_SNAPSHOTS table, by using the bq ls command, or by calling the tables.list API. It also describes how to list all of the table snapshots of a specified base table by querying the INFORMATION_SCHEMA.TABLE_SNAPSHOTS table. This document 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) permissions that you need to list the table snapshots in a dataset, and the predefined IAM roles that grant those permissions. The permissions and roles for listing table snapshots are the same as the permissions and roles required for listing other types of tables.

Permissions

To list the table snapshots in a dataset, you need the following permission:

Permission	Resource
`bigquery.tables.list`	The dataset that contains the table snapshots.

Roles

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

Role	Resource
Any of the following: `bigquery.dataUser` `bigquery.dataViewer` `bigquery.dataEditor` `bigquery.dataOwner` `bigquery.admin`	The dataset that contains the table snapshots.

List the table snapshots in a dataset

Getting a list of table snapshots in a dataset is similar to listing other types of tables. The table snapshots have the type SNAPSHOT.

You can list table snapshots by using one of the following options:

Console

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery
In the Explorer pane, expand the project and dataset nodes of the dataset whose table snapshots you want to list.
You can identify the table snapshots in the dataset by their icon.

SQL

Query the INFORMATION_SCHEMA.TABLE_SNAPSHOTS view:

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery
In the query editor, enter the following statement:
```
SELECT
  *
FROM
  PROJECT_ID.DATASET_NAME.INFORMATION_SCHEMA.TABLE_SNAPSHOTS;
```
Replace the following:
- PROJECT_ID: the project ID of the project that contains the snapshots you want to list.
- DATASET_NAME: the name of the dataset that contains the snapshots you want to list.
Click Run.

For more information about how to run queries, see Run an interactive query.

The result looks similar to the following:

+---------------+----------------+------------------+--------------------+-------------------+-----------------+-----------------------------+
| table_catalog | table_schema   | table_name       | base_table_catalog | base_table_schema | base_table_name | snapshot_time               |
+---------------+----------------+------------------+--------------------+-------------------+-----------------+-----------------------------+
| myproject     | mydataset      | mysnapshot       | basetableproject   | basetabledataset           | basetable           | 2021-04-16 14:05:27.519 UTC |
+---------------+----------------+------------------+--------------------+-------------------+-----------------+-----------------------------+

bq

Enter the following command in the Cloud Shell:

Go to Cloud Shell

bq ls \
PROJECT_ID:DATASET_NAME

Replace the following:

PROJECT_ID: the project ID of the project that contains the snapshots you want to list.
DATASET_NAME: the name of the dataset that contains the snapshots you want to list.

The output looks similar to the following:

+-------------------------+--------+---------------------+-------------------+
|         tableId         |  Type  |       Labels        | Time Partitioning |
+-------------------------+--------+---------------------+-------------------+
| mysnapshot              |SNAPSHOT|                     |                   |
+-------------------------+--------+---------------------+-------------------+

API

Call the tables.list method with the following parameters:

Parameter	Value
`projectId`	The project ID of the project that contains the snapshots you want to list.
`datasetId`	The name of the dataset that contains the snapshots you want to list.

List the table snapshots of a specified base table

You can list the table snapshots of a specified base table by querying the INFORMATION_SCHEMA.TABLE_SNAPSHOTS view:

SELECT
  *
FROM
  PROJECT_ID.DATASET_NAME.INFORMATION_SCHEMA.TABLE_SNAPSHOTS
WHERE
  base_table_name = 'books';