Work with slot 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 Reservations.

Create reservations

Required permissions

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

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

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

    Go to BigQuery

  2. In the navigation panel, go to the Capacity management section, and then click Create reservation.

  3. In the Reservation name field, enter a name for the reservation.

  4. In the Location drop-down list, select the location. If you select a BigQuery Omni location, your edition option is limited to the Enterprise edition.

  5. In the Edition list, select the edition. Autoscaling is only available within an edition. For more information, see Introduction to BigQuery editions.

  6. In the Max reservation size selector list, select the maximum reservation size.

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

  8. To disable idle slot sharing and use only the specified slot capacity, click the Ignore idle slots toggle.

  9. To expand the Advanced settings section, click the expander arrow.

  10. Optional: To set the target job concurrency, click the Override automatic target job concurrency toggle to on and enter the Target Job Concurrency.

  11. The breakdown of slots is displayed in the Cost estimate table. A summary of the reservation is displayed in the Capacity summary table.

  12. Click Save.

The new reservation is visible in the Slot reservations tab.

SQL

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

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

    Go to BigQuery

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

    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

      It must start and end with a lowercase letter or a number and contain only lowercase letters, numbers, and dashes.

    • NUMBER_OF_BASELINE_SLOTS: the number baseline of slots to allocate to the reservation. You cannot set the slot_capacity option and the edition option in the same reservation.
    • EDITION: the edition of the reservation. 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.

  3. 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 \
    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

  • EDITION: the edition of the reservation. 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.

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

  1. Launch Cloud Shell.
  2. 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).

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

  3. Review and modify the sample parameters to apply to your environment.
  4. Save your changes.
  5. 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

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

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

  3. 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}")

Update reservations

You can make the following updates to a reservation:

  • Change the reservation size by adding or removing slots.
  • Configure whether queries in this reservation use idle slots.
  • Change the amount of baseline or autoscaling slots allocated to a reservation.
  • Set the target job concurrency.

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:

Each of the following predefined IAM roles includes this permission:

  • BigQuery Admin
  • BigQuery Resource Admin
  • BigQuery Resource Editor

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

Change the size of a reservation

You can add or remove slots from an existing reservation.

Console

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

    Go to BigQuery

  2. In the navigation panel, go to the Capacity management section.

  3. Click the Slot reservations tab.

  4. Find the reservation you want to update.

  5. Expand the Actions option.

  6. Click Edit.

  7. In the Max reservation size selector dialog, enter the max reservation size.

  8. In the Baseline slots field, enter the number of baseline slots.

  9. To expand the Advanced settings section, click the expander arrow.

  10. Optional: To set the target job concurrency, click the Override automatic target job concurrency toggle to on and enter the Target Job Concurrency.

  11. Click Save.

SQL

To change the size of a reservation, use the ALTER RESERVATION SET OPTIONS data definition language (DDL) statement.

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

    Go to BigQuery

  2. In the query editor, enter the following statement:

    ALTER RESERVATION
      `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME`
    SET OPTIONS (
      slot_capacity = NUMBER_OF_BASELINE_SLOTS,
      autoscale_max_slots = NUMBER_OF_AUTOSCALING_SLOTS);

    Replace the following:

    • ADMIN_PROJECT_ID: the project ID of the administration project that owns the reservation resource
    • LOCATION: the location of the reservation, for example europe-west9.
    • RESERVATION_NAME: the name of the reservation. It must start and end with a lowercase letter or a number and contain only lowercase letters, numbers, and dashes.

    • NUMBER_OF_BASELINE_SLOTS: the number of baseline slots to allocate to the reservation.
    • 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.

  3. Click Run.

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

bq

To update the size of a reservation, use the bq update command with the --reservation flag:

bq update \
    --project_id=ADMIN_PROJECT_ID \
    --location=LOCATION \
    --slots=NUMBER_OF_BASELINE_SLOTS \
    --autoscale_max_slots=NUMBER_OF_AUTOSCALING_SLOTS \
    --reservation RESERVATION_NAME

Replace the following:

  • ADMIN_PROJECT_ID: the project ID
  • LOCATION: the location of the reservation
  • NUMBER_OF_BASELINE_SLOTS: the number of baseline slots to allocate to the reservation
  • RESERVATION_NAME: the name of the reservation
  • 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.

Python

Install the google-cloud-bigquery-reservation package before using this code sample. Construct a ReservationServiceClient. Describe the updated properties with a Reservation and the FieldMask.paths property. Update the reservation with the update_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 new number of slots in the
# reservation.
slot_capacity = 50

# 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,
)
from google.protobuf import field_mask_pb2

reservation_client = reservation_service.ReservationServiceClient(
    transport=transport
)

reservation_name = reservation_client.reservation_path(
    project_id, location, reservation_id
)
reservation = reservation_types.Reservation(
    name=reservation_name,
    slot_capacity=slot_capacity,
)
field_mask = field_mask_pb2.FieldMask(paths=["slot_capacity"])
reservation = reservation_client.update_reservation(
    reservation=reservation, update_mask=field_mask
)

print(f"Updated reservation: {reservation.name}")
print(f"\tslot_capacity: {reservation.slot_capacity}")

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.

bq update \
    --project_id=ADMIN_PROJECT_ID \
    --location=LOCATION \
    --ignore_idle_slots=true \
    --reservation RESERVATION_NAME

Replace the following:

  • ADMIN_PROJECT_ID: the project ID
  • LOCATION: the location of the reservation
  • RESERVATION_NAME: the name of the reservation

List the idle slot configuration

To list the idle slots setting for a reservation, do the following:

SQL

Query the ignore_idle_slots column of the INFORMATION_SCHEMA.RESERVATIONS_BY_PROJECT view.

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

    Go to BigQuery

  2. In the query editor, enter the following statement:

    SELECT
      reservation_name,
      ignore_idle_slots
    FROM
      `ADMIN_PROJECT_ID.region-LOCATION`.INFORMATION_SCHEMA.RESERVATIONS_BY_PROJECT;

    Replace the following:

  3. Click Run.

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

bq

Use the bq ls command with the --reservation flag:

bq ls --reservation \
    --project_id=ADMIN_PROJECT_ID \
    --location=LOCATION

Replace the following:

The ignoreIdleSlots field contains the configuration setting.

Delete reservations

When you delete a reservation, any jobs that are currently executing with slots from that reservation will fail. To prevent errors, allow running jobs to complete before deleting the reservation.

Required permissions

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

Each of the following predefined IAM roles includes this permission:

  • BigQuery Admin
  • BigQuery Resource Admin
  • BigQuery Resource Editor

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

Delete a reservation

Console

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

    Go to BigQuery

  2. In the navigation panel, go to the Capacity Management section.

  3. Click the Reservations tab.

  4. Find the reservation you want to delete.

  5. Expand the Actions option.

  6. Click Delete.

  7. In the Delete reservation dialog, click Delete.

SQL

To delete a reservation, use the DROP RESERVATION DDL statement.

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

    Go to BigQuery

  2. In the query editor, enter the following statement:

    DROP RESERVATION
      `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME`;

    Replace the following:

    • ADMIN_PROJECT_ID: the project ID of the administration project that owns the reservation resource
    • LOCATION: the location of the reservation
    • RESERVATION_NAME: the ID of the reservation

  3. Click Run.

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

bq

To delete a reservation, use the bq rm command with the --reservation flag:

bq rm \
    --project_id=ADMIN_PROJECT_ID \
    --location=LOCATION \
    --reservation RESERVATION_NAME

Replace the following:

  • ADMIN_PROJECT_ID: the project ID of the administration project that owns the reservation resource
  • LOCATION: the location of the reservation
  • RESERVATION_NAME: the name of the reservation

Python

Install the google-cloud-bigquery-reservation package before using this code sample. Construct a ReservationServiceClient. Delete the reservation with the delete_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): Choose a transport to use. Either 'grpc' or 'rest'
transport = "grpc"

# ...

from google.cloud.bigquery_reservation_v1.services import reservation_service

reservation_client = reservation_service.ReservationServiceClient(
    transport=transport
)
reservation_name = reservation_client.reservation_path(
    project_id, location, reservation_id
)
reservation_client.delete_reservation(name=reservation_name)

print(f"Deleted reservation: {reservation_name}")