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 datasetsbigquery.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
In the navigation pane, expand the Resources section.
In the search bar, enter your
key
,value
, orkey: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
In the navigation pane, scroll to the Filter by ID or label box. This box appears just above your list of projects.
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'sdatasets.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 implicitAND
operators. - The only field currently eligible for filtering is
labels.key
wherekey
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 aslabels.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 .
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 .
What's next
- Learn how to add labels to BigQuery resources.
- Learn how to view labels on BigQuery resources.
- Learn how to update labels on BigQuery resources.
- Learn how to delete labels on BigQuery resources.
- Read about Using labels in the Resource Manager documentation.