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
play_circle
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';
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.
What's next