Manage workload reservations

The BigQuery Reservation API lets you purchase dedicated slots (called commitments), create pools of slots (called reservations), and assign projects, folders, and organizations to those reservations.

Reservations allow you to assign a dedicated number of slots to a workload. For example, you might not want a production workload to compete with test workloads for slots. You could create a reservation named prod and assign your production workloads to this reservation. For more information, see Understand reservations.

Create reservations

Required permissions

To create a reservation, you need the following Identity and Access Management (IAM) permission:

bigquery.reservations.create on the administration project that maintains ownership of the commitments.

Each of the following predefined IAM roles includes this permission:

BigQuery Resource Editor
BigQuery Resource Admin

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

Create a reservation with dedicated slots

Select one of the following options:

Console

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery
In the navigation menu, click Capacity management.
Click Create reservation.
In the Reservation name field, enter a name for the reservation.
In the Location list, select the location. If you select a BigQuery Omni location, your edition option is limited to the Enterprise edition.
In the Edition list, select the edition. BigQuery edition features such as autoscaling are only available within an edition. For more information, see Introduction to BigQuery editions.
In the Max reservation size selector list, select the maximum reservation size.
Optional: In the Baseline slots field, enter the number of baseline slots for the reservation.

The number of available autoscaling slots is determined by subtracting the Baseline slots value from the Max reservation size. For example, if you create a reservation with 100 baseline slots and a max reservation size of 400, your reservation has 300 autoscaling slots. For more information about baseline slots, see Using reservations with baseline and autoscaling slots.
To disable idle slot sharing and use only the specified slot capacity, click the Ignore idle slots toggle.
To expand the Advanced settings section, click the expander arrow.
Optional: To set the target job concurrency, click the Override automatic target job concurrency toggle to on and enter the Target Job Concurrency.
Optional: To set default project scheduling policies for the reservation, configure at least one of the following options:
1. To limit the number of simultaneous queries admitted for each project assigned to the reservation, click the Override project concurrency toggle to the on position, and then enter a value in the Project Concurrency field.
2. To limit the slot consumption of each project assigned to the reservation, click the Override project max slots toggle to the on position, and then enter a value in the Project Max Slots field.
Note: These settings act as a default for all projects assigned to the reservation. You can override these limits for specific projects using assignment rules.
Review the breakdown of slots displayed in the Cost estimate table and the summary of the reservation in the Capacity summary table.
Click Save.

The new reservation is visible in the Slot reservations tab.

SQL

To create a reservation, use the CREATE RESERVATION DDL statement.

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery
In the query editor, enter the following statement:
```
CREATE RESERVATION
  `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME`
OPTIONS (
  slot_capacity = NUMBER_OF_BASELINE_SLOTS,
  edition = EDITION,
  autoscale_max_slots = NUMBER_OF_AUTOSCALING_SLOTS,
  scheduling_policy_max_slots = MAX_SLOTS_PER_PROJECT,
  scheduling_policy_concurrency = MAX_CONCURRENCY_PER_PROJECT);
```
Replace the following:
- ADMIN_PROJECT_ID: the project ID of the administration project that owns the reservation resource
- LOCATION: the location of the reservation. If you select a BigQuery Omni location, your edition option is limited to the Enterprise edition.
- RESERVATION_NAME: the name of the reservation
  
  The name can contain only lowercase alphanumeric characters or dashes, must start with a letter and must not end with a dash, and the maximum length is 64 characters.
- NUMBER_OF_BASELINE_SLOTS: the number of baseline slots to allocate to the reservation. You cannot set the slot_capacity option and the standard edition option in the same reservation.
- EDITION: the edition of the reservation. The supported values are STANDARD, ENTERPRISE, and ENTERPRISE_PLUS. Assigning a reservation to an edition comes with feature and pricing changes. For more information, see Introduction to BigQuery editions.
- NUMBER_OF_AUTOSCALING_SLOTS: the number of autoscaling slots assigned to the reservation. This is equal to the value of the max reservation size minus the number of baseline slots.
- MAX_SLOTS_PER_PROJECT: the default limit on the slot consumption of queries running for each project assigned to the reservation.
- MAX_CONCURRENCY_PER_PROJECT: the default limit on the number of simultaneous queries admitted for each project assigned to the reservation.
Click Run.

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

bq

To create a reservation, use the bq mk command with the --reservation flag:

bq mk \
    --project_id=ADMIN_PROJECT_ID \
    --location=LOCATION \
    --reservation \
    --slots=NUMBER_OF_BASELINE_SLOTS \
    --ignore_idle_slots=false \
    --edition=EDITION \
    --autoscale_max_slots=NUMBER_OF_AUTOSCALING_SLOTS \
    --max_slots=MAXIMUM_NUMBER_OF_SLOTS
    --scaling_mode=SCALING_MODE
    RESERVATION_NAME

Replace the following:

ADMIN_PROJECT_ID: the project ID
LOCATION: the location of the reservation. If you select a BigQuery Omni location, your edition option is limited to the Enterprise edition.
NUMBER_OF_BASELINE_SLOTS: the number of baseline slots to allocate to the reservation
RESERVATION_NAME: the name of the reservation. The name can contain only lowercase alphanumeric characters or dashes, must start with a letter and must not end with a dash, and the maximum length is 64 characters.
EDITION: the edition of the reservation. The supported values are STANDARD, ENTERPRISE, and ENTERPRISE_PLUS. Assigning a reservation to an edition comes with feature and pricing changes. For more information, see Introduction to BigQuery editions.
NUMBER_OF_AUTOSCALING_SLOTS: the number of autoscaling slots assigned to the reservation. This is equal to the value of the max reservation size minus the number of baseline slots. This can't be configured with either the --max_slots or --scaling_mode flags.
MAXIMUM_NUMBER_OF_SLOTS: the maximum number of slots the reservation can consume. This value must be configured with the --scaling_mode flag (Preview).
SCALING_MODE: the scaling mode of the reservation. The options are ALL_SLOTS, IDLE_SLOTS_ONLY, or AUTOSCALE_ONLY. This value must be configured with the --scaling_mode flag (Preview).

For information about the --ignore_idle_slots flag, see Idle slots. The default value is false.

Terraform

Use the google_bigquery_reservation resource.

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

The following example creates a reservation named my-reservation:

resource "google_bigquery_reservation" "default" {
  name              = "my-reservation"
  location          = "us-central1"
  slot_capacity     = 100
  edition           = "ENTERPRISE"
  ignore_idle_slots = false # Use idle slots from other reservations
  concurrency       = 0     # Automatically adjust query concurrency based on available resources
  autoscale {
    max_slots = 200 # Allow the reservation to scale up to 300 slots (slot_capacity + max_slots) if needed
  }
}

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell

Launch Cloud Shell.
Set the default Google Cloud project where you want to apply your Terraform configurations.

You only need to run this command once per project, and you can run it in any directory.
```
export GOOGLE_CLOUD_PROJECT=PROJECT_ID
```
Environment variables are overridden if you set explicit values in the Terraform configuration file.

Prepare the directory

Each Terraform configuration file must have its own directory (also called a root module).

In Cloud Shell, create a directory and a new file within that directory. The filename must have the .tf extension—for example main.tf. In this tutorial, the file is referred to as main.tf.
```
mkdir DIRECTORY && cd DIRECTORY && touch main.tf
```
If you are following a tutorial, you can copy the sample code in each section or step.

Copy the sample code into the newly created main.tf.

Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.
Review and modify the sample parameters to apply to your environment.
Save your changes.
Initialize Terraform. You only need to do this once per directory.
```
terraform init
```
Optionally, to use the latest Google provider version, include the -upgrade option:
```
terraform init -upgrade
```

Apply the changes

Review the configuration and verify that the resources that Terraform is going to create or update match your expectations:
```
terraform plan
```
Make corrections to the configuration as necessary.
Apply the Terraform configuration by running the following command and entering yes at the prompt:
```
terraform apply
```
Wait until Terraform displays the "Apply complete!" message.
Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.

Python

Install the google-cloud-bigquery-reservation package before using this code sample. Construct a ReservationServiceClient. Describe the reservation you'd like to create with a Reservation. Create the reservation with the create_reservation method.

# TODO(developer): Set project_id to the project ID containing the
# reservation.
project_id = "your-project-id"

# TODO(developer): Set location to the location of the reservation.
# See: https://cloud.google.com/bigquery/docs/locations for a list of
# available locations.
location = "US"

# TODO(developer): Set reservation_id to a unique ID of the reservation.
reservation_id = "sample-reservation"

# TODO(developer): Set slot_capicity to the number of slots in the
# reservation.
slot_capacity = 100

# TODO(developer): Choose a transport to use. Either 'grpc' or 'rest'
transport = "grpc"

# ...

from google.cloud.bigquery_reservation_v1.services import reservation_service
from google.cloud.bigquery_reservation_v1.types import (
    reservation as reservation_types,
)

reservation_client = reservation_service.ReservationServiceClient(
    transport=transport
)

parent = reservation_client.common_location_path(project_id, location)

reservation = reservation_types.Reservation(slot_capacity=slot_capacity)
reservation = reservation_client.create_reservation(
    parent=parent,
    reservation=reservation,
    reservation_id=reservation_id,
)

print(f"Created reservation: {reservation.name}")

Create a predictable reservation

Before you create a reservation with a maximum number of slots, you must first enable reservation-based fairness.

Enable reservation-based fairness

To enable reservation-based fairness, set the enable_reservation_based_fairness flag to true.

To update the reservation-based fairness on a project, you need the bigquery.config.update permission on the project that maintains ownership of the reservations. The predefined BigQuery Admin role includes this permission.

For more information about updating the default configuration of a project, see Manage configuration settings.

ALTER PROJECT `PROJECT_NAME` SET OPTIONS (
    `region-LOCATION.enable_reservation_based_fairness`= true);

Replace the following:

PROJECT_NAME: the project ID of the administration project
LOCATION: the location of the reservation

Create a predictable reservation

To create a predictable reservation with a maximum number of slots, select one of the following options:

SQL

Update reservations

To change the edition of a reservation, first delete the reservation, then create a reservation with the updated edition.

Required permissions

To update a reservation, you need the following Identity and Access Management (IAM) permission:

Enable BigQuery fluid scaling

To enable per-second billing with no minimum duration for a reservation, enable it at the administration-project level by listing the reservation in the preflight_fluid_autoscaling_reservations option. Changes to the BigQuery fluid scaling configuration can take a few minutes to propagate.

For reservations using managed disaster recovery, you must enable BigQuery fluid scaling in both the primary and secondary regions to ensure per-second billing after a failover.

To update the list of reservations with BigQuery fluid scaling, modify the list in the preflight_fluid_autoscaling_reservations option. To disable the feature for all reservations in a region, set the preflight_fluid_autoscaling_reservations option to NULL.

Change the size of a reservation

SQL

Configure whether queries use idle slots

The --ignore_idle_slots flag controls whether queries running in a reservation can use idle slots from other reservations. For more information, see Idle slots. You can update this configuration on an existing reservation.

To update a reservation, use the bq update command with the --reservation flag. The following example sets --ignore_idle_slots to true, meaning the reservation will only use slots allocated to the reservation.

List the idle slot configuration

Delete reservations

If you delete a reservation, any running jobs that use slots from that reservation fail. To prevent errors, allow running jobs to complete before you delete the reservation.

Required permissions

To delete a reservation, you need the following Identity and Access Management (IAM) permission:

Delete a reservation

Control access to reservations

You can control which users have access to specific reservations. For a user to override a reservation on their query, they must have the reservations.use permission on that reservation.

Required permissions

To get the permission that you need to specify a particular reservation for your job, ask your administrator to grant you the Resource Editor (roles/bigquery.resourceEditor) IAM role on the reservation resource. For more information about granting roles, see Manage access to projects, folders, and organizations.

This predefined role contains the reservations.use permission, which is required to specify a particular reservation for your job.

Control access to a reservation

To manage access to a specific reservation resource, use the

bq
set-iam-policy

command.

To manage access to multiple reservation resources, use the Google Cloud console to grant the BigQuery Resource Editor role on the project, folder, or organization. When you grant the role, use an IAM condition to allow access to the reservation resources when the specified conditions are met.

Prioritize idle slots with reservation groups

You can control which reservations get priority access to idle slots by creating a reservation group. Reservations within a reservation group will share idle slots with each other before they are available to other reservations in the project.

Required permissions

To get the permissions that you need to update a particular reservation to set the reservation group, ask your administrator to grant you the Reservation Editor (roles/bigquery.reservationEditor) IAM role on the reservation resource. For more information about granting roles, see Manage access to projects, folders, and organizations.

Create a reservation group

Console

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery
In the navigation menu, click Workload management.
Select the checkbox next to the reservation that you want to add to a group.
Click Create reservation group button in the table header.
In the Create reservation group pane, enter your group name in Group name field.
Optional: In the Reservations field, select additional reservations to be added to the group. Click OK.
Click Create.

The new reservation group is visible in the Slot reservations tab.

bq

To create a reservation, use the bq mk command with the --reservation flag:

bq mk \
    --project_id=ADMIN_PROJECT_ID \
    --location=LOCATION \
    --reservation_group \
    RESERVATION_GROUP_NAME

Replace the following:

ADMIN_PROJECT_ID: the project ID
LOCATION: the location of the reservation.
RESERVATION_GROUP_NAME: the name of the reservation group. The name can contain only lowercase alphanumeric characters or dashes, must start with a letter and must not end with a dash, and the maximum length is 64 characters.

Terraform

Use the google_bigquery_reservation_group resource.

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

The following example creates a reservation group named my-reservation-group:

resource "google_bigquery_reservation_group" "default" {
  name     = "my-reservation-group"
  location = "us-central1"
}

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell

Launch Cloud Shell.
Set the default Google Cloud project where you want to apply your Terraform configurations.

You only need to run this command once per project, and you can run it in any directory.
```
export GOOGLE_CLOUD_PROJECT=PROJECT_ID
```
Environment variables are overridden if you set explicit values in the Terraform configuration file.

Prepare the directory

Each Terraform configuration file must have its own directory (also called a root module).

In Cloud Shell, create a directory and a new file within that directory. The filename must have the .tf extension—for example main.tf. In this tutorial, the file is referred to as main.tf.
```
mkdir DIRECTORY && cd DIRECTORY && touch main.tf
```
If you are following a tutorial, you can copy the sample code in each section or step.

Copy the sample code into the newly created main.tf.

Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.
Review and modify the sample parameters to apply to your environment.
Save your changes.
Initialize Terraform. You only need to do this once per directory.
```
terraform init
```
Optionally, to use the latest Google provider version, include the -upgrade option:
```
terraform init -upgrade
```

Apply the changes

Review the configuration and verify that the resources that Terraform is going to create or update match your expectations:
```
terraform plan
```
Make corrections to the configuration as necessary.
Apply the Terraform configuration by running the following command and entering yes at the prompt:
```
terraform apply
```
Wait until Terraform displays the "Apply complete!" message.
Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.

Add a reservation to a reservation group

To add a reservation to a reservation group, update the reservation_group property of the reservation:

List the reservations that have a reservation group

To list the reservation group information for your reservations, do the following:

Remove a reservation from a reservation group

To remove a reservation from a reservation group, update the reservation_group property of the reservation to be the empty string:

Remove an empty reservation group

A reservation group can only be deleted if it does not contain any member reservations. Reservation groups are not automatically deleted when the last member reservation is deleted. You must manually delete the reservation group after removing all member reservations.

Troubleshoot

You might encounter the following errors when creating or updating a reservation: