Listing datasets
This document describes how to list and get information about datasets in BigQuery.
Before you begin
Grant Identity and Access Management (IAM) roles that give users the necessary permissions to perform each task in this document.
Required role
To get the permission that you need to list datasets or get information on datasets,
ask your administrator to grant you the
BigQuery Metadata Viewer (roles/bigquery.metadataViewer
) IAM role on your project.
For more information about granting roles, see
Manage access.
This predefined role contains the
bigquery.datasets.get
permission, which is
required to list datasets or get information on datasets.
You might
also be able to get this
permission
with custom roles or
other predefined roles.
When you apply the roles/bigquery.metadataViewer
role at the project or
organization level, you can list all the datasets in the project.
List datasets
Select one of the following options:
Console
In the navigation menu, click SQL workspace.
In the Explorer panel, expand a project name to see the datasets in that project, or use the search box to search by dataset name.
SQL
Query the INFORMATION_SCHEMA.SCHEMATA
view:
In the Google Cloud console, go to the BigQuery page.
In the query editor, enter the following statement:
SELECT schema_name FROM PROJECT_ID.INFORMATION_SCHEMA.SCHEMATA;
Replace PROJECT_ID with the ID of the project that you want to list available datasets for.
Click
Run.
For more information about how to run queries, see Running interactive queries.
bq
Issue the bq ls
command to list datasets by dataset ID. The --format
flag can be used to control the output. If you are listing dataset in a
project other than your default project, add the --project_id
flag to the
command.
To list all datasets in a project, including anonymous datasets,
use the --all
flag or the -a
shortcut.
To list all datasets in a project, excluding anonymous datasets, use the
--datasets
flag or the -d
shortcut. This flag is optional. By default,
anonymous datasets are not listed.
Additional flags include:
--filter
: List datasets that match the filter expression. Use a space-separated list of label keys and values in the formlabels.key:value
. For more information on filtering datasets using labels, see Adding and using labels.--max_results
or-n
: An integer indicating the maximum number of results. The default value is50
.
bq ls --filter labels.key:value \ --max_results integer \ --format=prettyjson \ --project_id project_id
Replace the following:
- key:value: a label key and value
- integer: an integer representing the number of datasets to list
- project_id: the name of your project
Examples:
Enter the following command to list datasets in your default project. --
format
is set to pretty to return a basic formatted table.
bq ls --format=pretty
Enter the following command to list datasets in myotherproject
. --format
is set to prettyjson
to return detailed results in JSON format.
bq ls --format=prettyjson --project_id myotherproject
Enter the following command to list all datasets including anonymous datasets in your default project. In the output, anonymous datasets begin with an underscore.
bq ls -a
Enter the following command to return more than the default output of 50 datasets from your default project.
bq ls --max_results 60
Enter the following command to list datasets in your default project with
the label org:dev
.
bq ls --filter labels.org:dev
API
To list datasets using the API, call the datasets.list
API method.
C#
Before trying this sample, follow the C# setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery C# API reference documentation.
Go
Before trying this sample, follow the Go setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Go API reference documentation.
Java
Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.
Node.js
Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Node.js API reference documentation.
PHP
Before trying this sample, follow the PHP setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery PHP API reference documentation.
Python
Before trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Python API reference documentation.
Ruby
Before trying this sample, follow the Ruby setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Ruby API reference documentation.
Get information about datasets
Select one of the following options:
Console
In the Explorer panel, expand your project and select a dataset.
Expand the
Actions option and click Open. The description and details appear in the details panel. The tables for a dataset are listed with the dataset name in the Explorer panel.
By default, anonymous datasets
are hidden from the Google Cloud console. To show information about
anonymous datasets, use the bq
command-line tool or the API.
SQL
Query the INFORMATION_SCHEMA.SCHEMATA
view:
In the Google Cloud console, go to the BigQuery page.
In the query editor, enter the following statement:
SELECT * EXCEPT (schema_owner) FROM `PROJECT_ID`.INFORMATION_SCHEMA.SCHEMATA;
Replace PROJECT_ID with the ID of the project that you want to list available datasets for.
Click
Run.
For more information about how to run queries, see Running interactive queries.
You can also query the INFORMATION_SCHEMA.SCHEMATA_OPTIONS
view.
SELECT * FROM [PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.SCHEMATA;
Replace the following:
-
Optional:
PROJECT_ID
: the ID of your Cloud project. If not specified, the default project is used. -
REGION
: any dataset location name. For example,region-us
.
bq
Issue the bq show
command. The --format
flag can be used to control the
output. If you are getting information about a dataset in a project other
than your default project, add the project ID to the dataset name in the
following format: project_id:dataset
.
To show information about an anonymous dataset,
use the bq ls --all
command to list all datasets and then use the name of the anonymous dataset
in the bq show
command.
bq show --format=prettyjson project_id:dataset
Replace the following:
- project_id is the name of your project.
- dataset is the name of the dataset.
Examples:
Enter the following command to display information about mydataset
in your
default project.
bq show --format=prettyjson mydataset
Enter the following command to display information about mydataset
in
myotherproject
.
bq show --format=prettyjson myotherproject:mydataset
Enter the following command to display information about an anonymous dataset,
_1234abcd56efgh78ijkl1234
in your default project.
bq show --format=prettyjson _1234abcd56efgh78ijkl1234
API
Call the datasets.get
API method and provide any relevant parameters.
Go
Before trying this sample, follow the Go setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Go API reference documentation.
Java
Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.
Node.js
Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Node.js API reference documentation.
Python
Before trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Python API reference documentation.
Next steps
- For more information on creating datasets, see Creating datasets.
- For more information on assigning access controls to datasets, see Controlling access to datasets.
- For more information on changing dataset properties, see Updating dataset properties.
- For more information on creating and managing labels, see Creating and managing labels.
- To see an overview of
INFORMATION_SCHEMA
, go to Introduction to BigQueryINFORMATION_SCHEMA
.