Listing datasets

This document describes how to list datasets in BigQuery.

You can list datasets in the following ways:

  • Using the Cloud Console.
  • Using the INFORMATION_SCHEMA SQL query.
  • Using the bq ls command in the bq command-line tool.
  • Calling the datasets.list API method.
  • Using the client libraries.

Required permissions

When you list datasets, only datasets for which you have bigquery.datasets.get permissions are returned. The following predefined IAM roles include bigquery.datasets.get permissions:

  • bigquery.user
  • bigquery.metadataViewer
  • bigquery.dataViewer
  • bigquery.dataOwner
  • bigquery.dataEditor
  • bigquery.admin

For more information on IAM roles and permissions in BigQuery, see Access control.

Listing datasets in a project

To list the datasets in a project:

Console

  1. In the navigation menu, click SQL workspace.

  2. 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

Use a project qualifier in the INFORMATION_SCHEMA query to list all the datasets in a project.

The following example lists all the datasets available in the specified project:

SELECT
 schema_name
FROM
'PROJECT_ID'.INFORMATION_SCHEMA.SCHEMATA

Replace PROJECT_ID with the ID of the project for which you want to list the available datasets.

For more information about INFORMATION_SCHEMA, see Introduction to BigQuery INFORMATION_SCHEMA.

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 form labels.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 is 50.
bq ls --filter labels.key:value \
--max_results integer \
--format=prettyjson \
--project_id project_id

Replace the following:

  • key:value is a label key and value.
  • integer is an integer representing the number of datasets to list.
  • project_id is 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. 


using Google.Cloud.BigQuery.V2;
using System;
using System.Collections.Generic;
using System.Linq;

public class BigQueryListDatasets
{
    public void ListDatasets(
        string projectId = "your-project-id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        // Retrieve list of datasets in project
        List<BigQueryDataset> datasets = client.ListDatasets().ToList();
        // Display the results
        if (datasets.Count > 0)
        {
            Console.WriteLine($"Datasets in project {projectId}:");
            foreach (var dataset in datasets)
            {
                Console.WriteLine($"\t{dataset.Reference.DatasetId}");
            }
        }
        else
        {
            Console.WriteLine($"{projectId} does not contain any datasets.");
        }
    }
}

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. 

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
	"google.golang.org/api/iterator"
)

// listDatasets demonstrates iterating through the collection of datasets in a project.
func listDatasets(projectID string, w io.Writer) error {
	// projectID := "my-project-id"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	it := client.Datasets(ctx)
	for {
		dataset, err := it.Next()
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		fmt.Fprintln(w, dataset.DatasetID)
	}
	return nil
}

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. 

import com.google.api.gax.paging.Page;
import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQuery.DatasetListOption;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Dataset;

public class ListDatasets {

  public static void runListDatasets() {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    listDatasets(projectId);
  }

  public static void listDatasets(String projectId) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      Page<Dataset> datasets = bigquery.listDatasets(projectId, DatasetListOption.pageSize(100));
      if (datasets == null) {
        System.out.println("Dataset does not contain any models");
        return;
      }
      datasets
          .iterateAll()
          .forEach(
              dataset -> System.out.printf("Success! Dataset ID: %s ", dataset.getDatasetId()));
    } catch (BigQueryException e) {
      System.out.println("Project does not contain any datasets \n" + e.toString());
    }
  }
}

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. 

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function listDatasets() {
  // Lists all datasets in current GCP project.

  // Lists all datasets in the specified project
  const [datasets] = await bigquery.getDatasets();
  console.log('Datasets:');
  datasets.forEach(dataset => console.log(dataset.id));
}

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. 

use Google\Cloud\BigQuery\BigQueryClient;

/** Uncomment and populate these variables in your code */
// $projectId  = 'The Google project ID';

$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$datasets = $bigQuery->datasets();
foreach ($datasets as $dataset) {
    print($dataset->id() . PHP_EOL);
}

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. 


from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

datasets = list(client.list_datasets())  # Make an API request.
project = client.project

if datasets:
    print("Datasets in project {}:".format(project))
    for dataset in datasets:
        print("\t{}".format(dataset.dataset_id))
else:
    print("{} project does not contain any datasets.".format(project))

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. 

require "google/cloud/bigquery"

def list_datasets project_id = "your-project-id"
  bigquery = Google::Cloud::Bigquery.new project: project_id

  puts "Datasets in project #{project_id}:"
  bigquery.datasets.each do |dataset|
    puts "\t#{dataset.dataset_id}"
  end
end

Next steps