Add labels to resources (original) (raw)

This document describes how to add labels to BigQuery resources, including the following resources:

datasets
tables and views
jobs
job sessions
reservations

For more information about labels in BigQuery, seeIntroduction to labels.

Before you begin

Grant users the necessary Identity and Access Management (IAM) roles to perform each task in this document. Each task's 'Required IAM roles' section lists the permissions needed to perform that task.

Add labels to datasets

You can add a label to a BigQuery dataset when you create it by using the bq command-line tool's bq mk command or by calling thedatasets.insert API method. You cannot add a label to a dataset when you create it using the Google Cloud console. However, you can add a dataset label after creation. For more information about creating a dataset, seeCreating a dataset.

When you add a label to a dataset, the label does not propagate to resources within the dataset. Tables or views do not inherit dataset labels. Also, when you add a label to a dataset, it is included in your storage billing data, but not in your job-related billing data.

For more information about label format, seeRequirements for labels.

Required IAM roles

To get the permission that you need to add a label to an existing dataset, ask your administrator to grant you theBigQuery Data Owner (roles/bigquery.dataOwner) IAM role. For more information about granting roles, see Manage access to projects, folders, and organizations.

This predefined role contains thebigquery.datasets.update permission, which is required to add a label to an existing dataset.

You might also be able to get this permission with custom roles or other predefined roles.

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

Add a label to a dataset

To add a label to a dataset after it is created:

Console

In the Google Cloud console, select the dataset.
On the dataset details page, in the Labels section, clickEdit.
In the Edit labels dialog:
- Click Add label
- Enter the key and value in the appropriate fields. To apply additional labels, click Add label. Each key can be used only once per dataset, but you can use the same key in different datasets within the same project.
- To update a label, modify the existing keys or values.
- To save your changes, click Update.

SQL

Use theALTER SCHEMA SET OPTIONS DDL statementto set the labels on an existing dataset. This action overwrites any existing labels on the dataset. The following example sets a label on themydataset dataset:

In the Google Cloud console, go to the BigQuery page.
Go to BigQuery
In the query editor, enter the following statement:
ALTER SCHEMA mydataset
SET OPTIONS (
labels = [('sensitivity', 'high')]);
Click Run.

For more information about how to run queries, see Run an interactive query.

bq

To add a label to an existing dataset, use the bq update command with the set_label flag. To add multiple labels, repeat the flag.

If the dataset is in a project other than your default project, specify the project ID in the following format:PROJECT_ID:DATASET.

bq update --set_label KEY:VALUE PROJECT_ID:DATASET

Replace the following:

KEY:VALUE: The key-value pair for a label you want to add. The key must be unique. Keys and values can contain only lowercase letters, numeric characters, underscores, and dashes. All characters must use UTF-8 encoding, and international characters are allowed.
PROJECT_ID: your project ID.
DATASET: the dataset you're labeling.

Examples:

To add a label to track departments, use the bq update command and specify department as the label key. For example, to add adepartment:shipping label to mydataset in your default project, use:

    bq update --set_label department:shipping mydataset

To add multiple labels to a dataset, repeat the set_label flag and specify a unique key for each label. For example, to add a department:shippinglabel and cost_center:logistics label to mydataset in your default project, use:

    bq update \
    --set_label department:shipping \
    --set_label cost_center:logistics \
    mydataset

API

To add a label to an existing dataset, call thedatasets.patchmethod and populate the labels property for thedataset resource.

Because the datasets.update method replaces the entire dataset resource, use the datasets.patch method instead.

Go

Before trying this sample, follow the Go setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Go API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Java

This sample uses the Google HTTP Client Library for Javato send a request to the BigQuery API.

Before trying this sample, follow the Java setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Java API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Node.js

Before trying this sample, follow the Node.js setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Node.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Python

Before trying this sample, follow the Python setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Python API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Add labels to tables and views

This document describes how to add a label to an existing table or view. For more information about adding a label when you create a table or view, seeCreating a table orCreating a view.

Because views are treated like table resources, you use the tables.patchmethod to modify both views and tables.

Table and view labels are not included in billing data.

Required IAM roles

To get the permissions that you need to add a label to an existing table or view, ask your administrator to grant you theBigQuery Data Editor (roles/bigquery.dataEditor) IAM role. For more information about granting roles, see Manage access to projects, folders, and organizations.

This predefined role contains the permissions required to add a label to an existing table or view. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

The following permissions are required to add a label to an existing table or view:

bigquery.tables.update
bigquery.tables.get

You might also be able to get these permissions with custom roles or other predefined roles.

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

Add a label to a table or view

To add a label to an existing table or view:

Console

In the Google Cloud console, select the table, or view.
Click the Details tab.
In the Labels section, clickEdit.
In the Edit labels dialog:
- Click Add label
- Enter the key and value in the appropriate fields. To apply additional labels, click Add label. Each key can be used only once per dataset, but you can use the same key in different datasets within the same project.
- Modify existing keys or values to update a label.
- Click Update to save your changes.

SQL

Use theALTER TABLE SET OPTIONS DDL statementto set the labels on an existing table, or theALTER VIEW SET OPTIONS DDL statementto set the labels on an existing view. This action overwrites any existing labels on the table or view. The following example sets two labels on the mytable table:

In the Google Cloud console, go to the BigQuery page.
Go to BigQuery
In the query editor, enter the following statement:
ALTER TABLE mydataset.mytable
SET OPTIONS (
labels = [('department', 'shipping'), ('cost_center', 'logistics')]);
Click Run.

For more information about how to run queries, see Run an interactive query.

bq

To add a label to an existing table or view, use the bq update command with the set_label flag. To add multiple labels, repeat the flag.

If the table or view is in a project other than your default project, specify the project ID in the following format:PROJECT_ID:DATASET.

bq update
--set_label KEY:VALUE
PROJECT_ID:DATASET.TABLE_OR_VIEW

Replace the following:

KEY:VALUE: The key-value pair for a label you want to add. The key must be unique. Keys and values can contain only lowercase letters, numeric characters, underscores, and dashes. All characters must use UTF-8 encoding, and international characters are allowed.
PROJECT_ID: your project ID.
DATASET: the dataset that contains the table or view you're labeling.
TABLE_OR_VIEW: the name of the table or view you're labeling.

Examples:

To add a table label that tracks departments, use the bq update command and specify department as the label key. For example, to add adepartment:shipping label to mytable in your default project, use:

    bq update --set_label department:shipping mydataset.mytable

To add a view label that tracks departments, use the bq update command and specify department as the label key. For example, to add adepartment:shipping label to myview in your default project, use:

    bq update --set_label department:shipping mydataset.myview

To add multiple labels to a table or view, repeat the set_label flag and specify a unique key for each label. For example, to add adepartment:shipping label and cost_center:logistics label to mytablein your default project, use:

    bq update \
    --set_label department:shipping \
    --set_label cost_center:logistics \
    mydataset.mytable

API

To add a label to an existing table or view, call thetables.patchmethod and populate the labels property for thetable resource.

Because views are treated like table resources, you use the tables.patchmethod to modify both views and tables.

Because the tables.update method replaces the entire dataset resource, use the tables.patch method instead.

Go

Before trying this sample, follow the Go setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Go API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Java

This sample uses the Google HTTP Client Library for Javato send a request to the BigQuery API.

Before trying this sample, follow the Java setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Java API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Node.js

Before trying this sample, follow the Node.js setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Node.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Python

Before trying this sample, follow the Python setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Python API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Add labels to jobs

Labels can be added to query jobs through the command line by using the bq command-line tool's --label flag. The bq tool supports adding labels only to query jobs.

You can also add a label to a job when it's submitted through the API by specifying the labels property in the job configuration when you call thejobs.insert method. The API can be used to add labels to any job type.

You cannot add labels to or update labels on pending, running, or completed jobs.

When you add a label to a job, the label is included in your billing data.

Required IAM roles

To get the permission that you need to add a label to a job, ask your administrator to grant you theBigQuery User (roles/bigquery.user) IAM role. For more information about granting roles, see Manage access to projects, folders, and organizations.

This predefined role contains thebigquery.jobs.create permission, which is required to add a label to a job.

You might also be able to get this permission with custom roles or other predefined roles.

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

Add a label to a job

To add a label to a job:

bq

To add a label to a query job, use the bq query command with the --label flag. To add multiple labels, repeat the flag. The flag indicates that your query uses GoogleSQL syntax.

bq query --label KEY:VALUE 'QUERY'

Replace the following:

KEY:VALUE: The key-value pair for a label you want to add to the query job. The key must be unique. Keys and values can contain only lowercase letters, numeric characters, underscores, and dashes. All characters must use UTF-8 encoding, and international characters are allowed. To add multiple labels to a query job, repeat the--label flag and specify a unique key for each label.
QUERY: a valid GoogleSQL query.

Examples:

To add a label to a query job, use:

    bq query \
    --label department:shipping \
     \
    'SELECT
       column1, column2
     FROM
       `mydataset.mytable`'

To add multiple labels to a query job, repeat the --label flag and specify a unique key for each label. For example, to add adepartment:shipping label and cost_center:logistics label to a query job, use:

    bq query \
    --label department:shipping \
    --label cost_center:logistics \
     \
    'SELECT
       column1, column2
     FROM
       `mydataset.mytable`'

API

To add a label to a job, call thejobs.insertmethod and populate the labels property for thejob configuration. You can use the API to add labels to any job type.

Python

Before trying this sample, follow the Python setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Python API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Associate jobs in a session with a label

If you run queries in asession, you can assign a label to all future query jobs in that session using BigQuery multi-statement queries.

SQL

Set the @@query_labelsystem variable in the session by running the following query:

SET @@query_label = "KEY:VALUE";

KEY:VALUE: The key-value pair for the label to assign to all future queries in the session. You can also add multiple key-value pairs, separated by a comma; for example, SET @@query_label = "key1:value1,key2:value2". The key must be unique. Keys and values can contain only lowercase letters, numeric characters, underscores, and dashes. All characters must use UTF-8 encoding, and international characters are allowed.

Example:

SET @@query_label = "cost_center:logistics";

API

To add a label to a query job in a session when yourun a query using an API call, call the jobs.insertmethod and populate the query_label property for theconnectionProperties job configuration.

After you associate a query label with a session and run queries in that session, you can collect audit logs for those queries. For more information, see theAudit log reference for BigQuery.

Add a label to a reservation

When you add a label to a reservation, it is included in your billing data. You can use labels to filter the Analysis Slots Attribution SKU in your Cloud Billing data.

The Analysis Slots Attribution SKU only records slot usage. It doesn't record costs for BigQuery Reservation API SKUs. Reservation labels aren't supported as filters for BigQuery Reservation API SKUs.

For more information about using labels in your billing data, see UseFilters to refine data.

Required IAM roles

To get the permission that you need to add a label to a reservation, ask your administrator to grant you theBigQuery Resource Editor (roles/bigquery.resourceEditor) IAM role on the administration project. For more information about granting roles, see Manage access to projects, folders, and organizations.

This predefined role contains thebigquery.reservations.update permission, which is required to add a label to a reservation.

You might also be able to get this permission with custom roles or other predefined roles.

Add a label to a reservation

To add a label to a reservation:

Console

In the Google Cloud console, go to the BigQuery page.
Go to BigQuery
In the navigation menu, click Capacity management.
Click the Slot reservations tab.
Find the reservation you want to update.
Expand theActions option.
Click Edit.
To expand the Advanced settings section, click theexpander arrow.
Click Add Label.
Enter the key-value pair in the appropriate fields. To apply additional labels, click Add label.
Click Save.

SQL

To add a label to a reservation, use the ALTER RESERVATION SET OPTIONSDDL statement. This action overwrites any existing labels on the reservation. The following example sets a label on the myreservation reservation:

In the Google Cloud console, go to the BigQuery page.
Go to BigQuery
In the query editor, enter the following statement:
ALTER RESERVATION myreservation
SET OPTIONS (
labels = [('sensitivity', 'high')]);
Click Run.

For more information about how to run queries, see Run an interactive query.

bq

To add a label to a reservation, use the bq update command with theset_label flag and the --reservation flag. To add multiple labels, repeat the set_label flag.

bq update --set_label KEY:VALUE --location LOCATION --reservation RESERVATION_NAME

Replace the following:

KEY:VALUE: The key-value pair for a label you want to add to the reservation. The key must be unique. Keys and values can contain only lowercase letters, numeric characters, underscores, and dashes. All characters must use UTF-8 encoding, and international characters are allowed. To add multiple labels to a reservation, repeat the--set_label flag and specify a unique key for each label.
LOCATION: the location of the reservation. The location flag can't be last in the command, otherwise the FATAL Flags positioning error is returned.
RESERVATION_NAME: the name of the reservation.

Add a label without a value

A label that has a key with an empty value is sometimes called a tag. Do not confuse this with a tag resource. For more information, see labels and tags. You can create a new label without a value, or remove a value from an existing label key.

Labels without values are useful when you label a resource but you don't need the key-value format. For example, if a table contains test data used by multiple groups, such as support or development, you can add a test_data label to the table to identify it.

To add a label without a value:

Console

In the Google Cloud console, select the appropriate resource (a dataset, table, or view).
For datasets, the dataset details page opens automatically. For tables and views, click Details to open the details page.
On the details page, in the Labels section, clickEdit.
In the Edit labels dialog:
- Click Add label.
- Enter a new key in the appropriate field and leave the value field blank. To apply additional labels, click Add label and repeat.
- To save your changes, click Update.

SQL

To add a label without a value, use theALTER TABLE SET OPTIONS DDL statement:

In the Google Cloud console, go to the BigQuery page.
Go to BigQuery
In the query editor, enter the following statement:
ALTER TABLE mydataset.mytable
SET OPTIONS (
labels=[("key1", ""), ("key2", "")]);
Click Run.

For more information about how to run queries, see Run an interactive query.

bq

To add a label without a value to an existing resource, use thebq update command with theset_label flag. Specify the key, followed by a colon, but leave the value unspecified.

bq update --set_label KEY: RESOURCE_ID

Replace the following:

KEY:: the label key that you want to use.
RESOURCE_ID: a valid dataset, table, or view name. If the resource is in a project other than your default project, specify the project ID in the following format:PROJECT_ID:DATASET.

Examples:

Use the following command to create a test_data label formydataset.mytable. mydataset is in your default project.

bq update --set_label test_data: mydataset

API

Call the datasets.patchmethod or the tables.patchmethod and add labels with the value set to the empty string ("") in thedataset resourceor the table resource. You can remove values from existing labels by replacing their values with the empty string.

Because views are treated like table resources, you use the tables.patchmethod to modify both views and tables. Also, because the tables.updatemethod replaces the entire dataset resource, use the tables.patch method instead.

What's next

Learn how to view labels on BigQuery resources.
Learn how to identify and analyze agent-generated queries using labels.
Learn how to update labels on BigQuery resources.
Learn how to filter resources using labels.
Learn how to delete labels on BigQuery resources.
Read about Using labels in the Resource Manager documentation.