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 left pane, click Explorer:

Highlighted button for the Explorer pane.

If you don't see the left pane, click Expand left pane to open the pane.
In the Explorer pane, expand the project, click Datasets, and then select the dataset that contains the table snapshots that you want to list.
Click Overview > Tables. To find snapshots from the list, check for the SNAPSHOT value in the Type column.

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

bqls \
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';