Filtering resources using labels

This page explains how to filter your BigQuery resources using labels.

Overview

To filter resources using labels:

  • Use the search bar in the Cloud Console or classic web UI.
  • Create a filter specification for use in the CLI, API, or client libraries.

Limitations

Currently:

  • The API, CLI, and client libraries support filtering only for datasets.
  • You cannot filter jobs by label in any of the BigQuery tools.

Required permissions

To filter resources using labels, you must be able to retrieve resource metadata. At a miniumum, this requires:

  • bigquery.datasets.get to filter datasets
  • bigquery.tables.get to filter tables and views

The following predefined Cloud IAM roles include bigquery.datasets.get permissions:

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

The following predefined Cloud IAM roles include bigquery.tables.get permissions:

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

In addition, if a user has bigquery.datasets.create permissions, when that user creates a dataset, they are granted bigquery.dataOwner access to it. bigquery.dataOwner access gives the user the ability to filter the resources they create.

For more information on Cloud IAM roles and permissions in BigQuery, see Predefined roles and permissions.

Filtering resources in the UI

To generate a filtered list of resources by using the Cloud Console or classic web UI:

Console

  1. In the navigation pane, expand the Resources section.

  2. In the search bar, enter your key, value, or key:value pair. Your results include any partial matches.

    For example, to show only datasets with the label department:shipping, you could enter the:

    • key value: department
    • value: shipping
    • key:value pair: department:shipping

Classic UI

  1. In the navigation pane, scroll to the Filter by ID or label box. This box appears just above your list of projects.

  2. In the search bar, enter your key, value, or key-value pair. The key-value pair should be in key:value format. Your results include any partial matches.

    For example, to show only datasets with the label department:shipping, you could enter the:

    • key value: department
    • value: shipping
    • key:value pair: department:shipping

Filtering datasets in the CLI or API

Currently, the API, CLI, and client libraries support filtering only for datasets.

To filter datasets by using the CLI, API, or client libraries, create a filter specification and use the specification:

  • As the parameter for the --filter flag in the CLI
  • As the value for the filter property in the API's datasets.list method

Limitations on filter specifications

Filter specifications have the following limitations:

  • Only the AND logical operator is supported. Space-separated comparisons are treated as having implicit AND operators.
  • The only field currently eligible for filtering is labels.key where key is the name of a label.
  • The filter can include up to ten expressions.
  • Filtering is case-sensitive.
  • Currently, the CLI, API, and client libraries support filtering only for datasets.

Filter specification examples

A filter specification uses the following syntax:

"field[:value][ field[:value]..."

Where:

  • field is expressed as labels.key where key is a label key.
  • value is an optional label value.

The following examples show how to generate filter expressions.

To list resources that have a department:shipping label, use the following filter specification:

labels.department:shipping

To list resources using multiple labels, separate the key:value pairs with a space. The space is treated as a logical AND operator. For example, to list datasets with the department:shipping label and the location:usa label, use the following filter specification:

labels.department:shipping labels.location:usa

You can filter on the presence of a key alone, rather than matching against a key:value pair. The following filter specification lists all datasets labeled department regardless of the value.

labels.department

An equivalent filter specification uses an asterisk to represent all possible values associated with the department key.

labels.department:*

You can also use tags in a filter specification. For example, to list resources with the department:shipping label and test_data tag, use the following filter specification:

labels.department:shipping labels.test_data

Filtering datasets in the CLI and API

To filter datasets by using the CLI, API, or client libraries:

CLI

Issue the bq ls command with the --filter flag. If you are listing datasets in a project other than your default project, specify the --project_id flag.

bq ls \
--filter "filter_specification" \
--project_id project_id

Where:

  • filter_specification is a valid filter specification.
  • project_id is your project ID.

Examples:

Enter the following command to list datasets in your default project that have a department:shipping label:

bq ls --filter "labels.department:shipping"

Enter the following command to list datasets in your default project that have a department:shipping label and a test_data tag.

bq ls --filter "labels.department:shipping labels.test_data"

Enter the following command to list datasets in myotherproject that have a department:shipping label:

bq ls --filter "labels.department:shipping" --project_id myotherproject

The output for each of these commands returns a list of datasets like the following. 

+-----------+
| datasetId |
+-----------+
| mydataset |
| mydataset2|
+-----------+

API

Call the datasets.list API method and provide the filter specification using the filter property.

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"
)

// listDatasetsByLabel demonstrates walking the collection of datasets in a project, and
// filtering that list to a subset that has specific label metadata.
func listDatasetsByLabel(w io.Writer, projectID string) error {
	// projectID := "my-project-id"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}

	it := client.Datasets(ctx)
	it.Filter = "labels.color:green"
	for {
		dataset, err := it.Next()
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "dataset: %s\n", dataset.DatasetID)
	}
	return nil
}

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 listDatasetsByLabel() {
  // Lists all datasets in current GCP project, filtering by label color:green.

  const options = {
    filter: 'labels.color:green',
  };
  // Lists all datasets in the specified project
  const [datasets] = await bigquery.getDatasets(options);

  console.log('Datasets:');
  datasets.forEach(dataset => console.log(dataset.id));
}

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()

label_filter = "labels.color:green"
datasets = list(client.list_datasets(filter=label_filter))  # Make an API request.

if datasets:
    print("Datasets filtered by {}:".format(label_filter))
    for dataset in datasets:
        print("\t{}.{}".format(dataset.project, dataset.dataset_id))
else:
    print("No datasets found with this filter.")

What's next