Work with slot reservations

The BigQuery Reservation API enables you to purchase dedicated slots (called commitments), create pools of slots (called reservations), and assign projects and organizations to those reservations. For more information about these concepts, see Introduction to Reservations. For information about the commitment plans BigQuery provides, see Capacity commitments.

Reservations allow you to assign a dedicated number of slots from a commitment 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 Admin
  • BigQuery Resource Admin
  • BigQuery Resource Editor

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

Create a reservation with dedicated slots

Console

  1. In the Cloud Console, go to the BigQuery page.

    Go to BigQuery

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

  3. Click Create Reservation.

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

  5. In the Location drop-down list, select the location.

  6. Under Allocate slots, enter the number of slots for the reservation.

  7. Click Save.

The new reservation is visible in the Reservations tab.

Reservation overview.

SQL

Use the CREATE RESERVATION statement to create a reservation.

  CREATE RESERVATION `ADMIN_PROJECT_ID.region-LOCATION
  .RESERVATION_NAME`
     AS JSON """{
     "slot_capacity": NUMBER_OF_SLOTS
     }"""

Where:

  • ADMIN_PROJECT_ID is the project ID.
  • LOCATION is the location of the project.
  • NUMBER_OF_SLOTS is the number of slots to allocate to the reservation. There must be enough unallocated slots in the commitment.
  • RESERVATION_NAME is the name of the reservation. It must start and end with a lower case letter or a number and contain only lower case letters, numbers and dashes.

    For more information about the CREATE RESERVATION statement, see CREATE RESERVATION statement in the data control language (DCL) reference.

For more information about how to run queries, see Running interactive queries.

bq

Use the bq mk command with the --reservation flag to create a reservation.

bq mk \
  --project_id=ADMIN_PROJECT_ID \
  --location=LOCATION \
  --reservation \
  --slots=NUMBER_OF_SLOTS \
  --ignore_idle_slots=false \
  RESERVATION_NAME

Where:

  • ADMIN_PROJECT_ID is the project ID.
  • LOCATION is the location of the project.
  • NUMBER_OF_SLOTS is the number of slots to allocate to the reservation. There must be enough unallocated slots in the commitment.
  • RESERVATION_NAME is the name of the reservation.

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

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

# ...

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

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 capacity commitment:

  • Change the reservation size by adding or removing slots.
  • Configure whether queries in this reservation use idle slots.

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

  7. In the Edit slots dialog, enter the number of slots.

  8. Click Save.

bq

Use the bq update command with the --reservation flag to update the size of a reservation.

bq update \
  --project_id=ADMIN_PROJECT_ID \
  --location=LOCATION \
  --slots=NUMBER_OF_SLOTS \
  --reservation RESERVATION_NAME

Where:

  • ADMIN_PROJECT_ID is the project ID.
  • LOCATION is the location of the project.
  • NUMBER_OF_SLOTS is the number of slots to allocate to the reservation.
  • RESERVATION_NAME is the name of the reservation.

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

# ...

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

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.

bq

Use the bq update command with the --reservation flag to update a reservation. 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

Where:

  • ADMIN_PROJECT_ID is the project ID.
  • LOCATION is the location of the project.
  • RESERVATION_NAME is the name of the reservation.

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

Use the DROP RESERVATION statement to delete a reservation.

  DROP RESERVATION `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME`

Where:

  • ADMIN_PROJECT_ID is the project ID.
  • LOCATION is the location of the project.
  • RESERVATION_NAME is the ID of the reservation.

    For more information about the DROP RESERVATION statement, see DROP RESERVATION statement in the data control reference (DCL) reference.

For more information about how to run queries, see Running interactive queries.

bq

Use the bq rm command with the --reservation flag to delete a reservation.

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

Where:

  • ADMIN_PROJECT_ID is the project ID.
  • LOCATION is the location of the project.
  • RESERVATION_NAME is 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"

# ...

from google.cloud.bigquery_reservation_v1.services import reservation_service

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

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