Working with Reservations

This page shows how to perform common tasks with BigQuery Reservations.

The Reservations 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 roles and permissions required to manage reservations, see Reservations details and limitations.

This topic provides guidance on the following reservation tasks:

Working with commitments

Commitments are a regional resource.

Purchase slots

Commitments are a regional resource. Commitments purchased in one region cannot be used in any other regions, and commitments cannot be moved between regions.

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

  4. Under Configuration:

    1. Select the commitment plan.
    2. Select the location.
    3. Enter the number of slots to purchase.
    4. Click Next.
  5. Review the cost estimate for your purchase.

  6. Under Confirm and submit:

    1. Type CONFIRM to confirm the purchase.
    2. Click Purchase to purchase the slots.
  7. To view the commitment, click View slot commitments. After the capacity is provisioned, the requested capacity commitment has a green status.

    Slot commitments.

The first time you purchase capacity, a default reservation is created.

SQL

Use the CREATE CAPACITY statement to purchase slots.

  CREATE CAPACITY `ADMIN_PROJECT_ID.region-LOCATION.COMMITMENT_ID`
     AS JSON """{
       "slot_count": NUMBER_OF_SLOTS,
       "plan": "PLAN_TYPE"
     }"""

Where:

  • ADMIN_PROJECT_ID is the project ID of the administration project that will maintain ownership this commitment.
  • LOCATION is the location of the project.
  • COMMITMENT_ID is the ID of the commitment. It must be unique to the project and location. It must start and end with a lower case letter or a number and contain only lower case letters, numbers and dashes.
  • PLAN_TYPE is the plan type. One of: FLEX, MONTHLY, ANNUAL.
  • NUMBER_OF_SLOTS is the number of slots to purchase.

For more information about the CREATE CAPACITY statement, see CREATE CAPACITY 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 --capacity_commitment flag to purchase slots.

bq mk \
  --project_id=ADMIN_PROJECT_ID \
  --location=LOCATION \
  --capacity_commitment \
  --plan=PLAN_TYPE \
  --slots=NUMBER_OF_SLOTS

Where:

  • ADMIN_PROJECT_ID is the project ID of the administration project that will maintain ownership this commitment.
  • LOCATION is the location of the project.
  • PLAN_TYPE is the plan type. One of: FLEX, MONTHLY, ANNUAL.
  • NUMBER_OF_SLOTS is the number of slots to purchase.

View purchased commitments

To view your capacity commitments by project:

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 Slot commitments tab. Your capacity commitments are listed in the table under Commitments.

    Slot commitments.

SQL

Use the INFORMATION_SCHEMA.CAPACITY_COMMITMENTS_BY_PROJECT view to view the commitments for an administration project.

SELECT capacity_commitment_id
   FROM `region-LOCATION`.INFORMATION_SCHEMA.CAPACITY_COMMITMENTS_BY_PROJECT
   WHERE project_id = 'ADMIN_PROJECT_ID' AND slot_count = 100

Where:

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

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

bq

Use the bq ls command with the --capacity_commitment flag to list the commitments for an administration project.

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

Where:

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

Renew a commitment

For annual commitments, you can choose your commitment renewal plan option before the commitment end date.

Console

New commitments

You can choose your renewal plan option during commitment creation.

Existing commitments

You can change your renewal plan choice for existing annual commitments.

  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 Slot commitments tab.

  4. Find the commitment you want to edit.

  5. Expand the Actions option.

  6. Click Edit.

bq

To change the renewal plan choice for an existing commitment, use the bq update command with the --renewal_plan option.

bq update \
  --project_id=ADMIN_PROJECT_ID \
  --location=LOCATION \
  --renewal_plan=PLAN_TYPE \
  --capacity_commitment COMMITMENT_ID

Where:

  • ADMIN_PROJECT_ID is the project ID.
  • LOCATION is the location of the project.
  • PLAN_TYPE is the plan type. One of: FLEX, MONTHLY, ANNUAL.
  • COMMITMENT_ID is the ID of the commitment. To get the ID, see View purchased commitments.

Convert a commitment to longer duration

You can choose to convert your commitment to a longer-duration commitment type at any time:

  • You can convert your flex slots to a monthly or annual commitment type.
  • You can convert your monthly commitment to an annual commitment.

bq

To convert a commitment, use the bq update command with the --renewal_plan option. As soon as you update your commitment, you are charged the rate associated with the new plan, and the end date resets.

bq update \
--project_id=ADMIN_PROJECT_ID \
--location=LOCATION \
--plan=PLAN_TYPE \
--renewal_plan=RENEWAL_PLAN \
--capacity_commitment COMMITMENT_ID

Where:

  • ADMIN_PROJECT_ID is the project ID.
  • LOCATION is the location of the project.
  • PLAN_TYPE is the new plan type. One of: FLEX, MONTHLY, ANNUAL.
  • RENEWAL_PLAN is the renewal plan type.
  • COMMITMENT_ID is the ID of the commitment. To get the ID, see View purchased commitments.

Split a commitment

You can split your commitment into two commitments of the same plan and the same commitment end date. This can be useful if you want to renew part of a commitment. For example, if you have an annual commitment of 1,000 slots, you could split it and renew 500 slots at the annual rate, while converting 500 slots to flex slots after the end date.

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 Slot commitments tab.

  4. Select the commitment that you want to split.

  5. Click Split.

  6. In the Split commitment page, use the Configure split slider to select how many slots go into each split, in increments of 100 slots.

  7. Click Split to split the commitment. The new commitment is listed in the Slot commitments tab.

bq

Use the bq command-line tool to split commitments.

bq update \
--project_id=ADMIN_PROJECT_ID \
--location=LOCATION \
--split \
--slots=SLOTS_TO_SPLIT \
--capacity_commitment COMMITMENT_ID

Where:

  • ADMIN_PROJECT_ID is the project ID.
  • LOCATION is the location of the project.
  • SLOTS_TO_SPLITis the number of slots to split from the original commitment into a new commitment.
  • COMMITMENT_ID is the ID of the commitment. To get the ID, see View purchased commitments.

Merge two commitments

You can merge multiple commitments of the same plan into one commitment. The end date of the combined commitment is the maximum end date of the original commitments. If any of the commitments have an earlier end date, they are extended to the later date and you are charged a prorated amount for those 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 the Slot commitments tab.

  4. Select the commitments that you want to merge.

  5. Click Merge.

  6. In the Merge commitments page, review the details of the merge and click Merge. The new merged commitment is listed in the Slot commitments tab.

bq

Use the bq command-line tool to merge commitments. The following command merges two commitments into one commitment.

bq update \
--project_id=ADMIN_PROJECT_ID \
--location=US \
--merge \
--capacity_commitment COMMITMENT1,COMMITMENT2

Where:

  • ADMIN_PROJECT_ID is the project ID.
  • COMMITMENT1 is the first commitment to merge.
  • COMMITMENT2 is the second commitment to merge.

Delete a commitment

A capacity commitment is eligible for deletion based on its commitment duration.

The commitment end date is shown in the Cloud Console. You can delete your capacity commitment after the end date. First, make sure there are enough unallocated slots. If not, you must decrease the number of slots in a reservation or remove reservations entirely.

Console

To delete a capacity commitment, complete the following steps:

  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 Slot commitments tab.

  4. Find the commitment you want to delete.

  5. Expand the Actions option.

  6. Click Delete.

  7. Type "REMOVE" and click Proceed.

SQL

Use the DROP CAPACITY statement to delete a capacity commitment.

  DROP CAPACITY `ADMIN_PROJECT_ID.region-LOCATION.COMMITMENT_ID`

Where:

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

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

bq

Use the bq rm command with the --capacity_commitment flag to delete a capacity commitment.

bq rm \
--project_id=ADMIN_PROJECT_ID \
--location=LOCATION \
--capacity_commitment COMMITMENT_ID

Where:

Working with reservations

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

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

Update the --ignore_idle_slots setting

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

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.

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

Working with assignments

Assign an organization to 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 in the table of reservations

  5. Expand the Actions option.

  6. Click Create assignment.

  7. In the Create an assignment section, click Browse.

  8. Browse or search for the organization and select it.

  9. Click Create.

SQL

Use the CREATE ASSIGNMENT statement to assign an organization to a reservation.

  CREATE ASSIGNMENT `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME.ASSIGNMENT_ID`
   AS JSON """{
   "assignee": "organizations/ORGANIZATION_ID",
   "job_type": "JOB_TYPE"
   }"""

Where:

  • ADMIN_PROJECT_ID is the ID of the project that owns the reservation resource.
  • LOCATION is the location of the project.
  • RESERVATION_NAME is the name of the reservation.
  • ASSIGNMENT_ID is the ID of the assignment. It must be unique to the project and location. It must start and end with a lower case letter or a number and contain only lower case letters, numbers and dashes.
  • JOB_TYPE is the type of job to assign to this reservation. One of: QUERY, PIPELINE, or ML_EXTERNAL.
  • ORGANIZATION_ID is the organization ID.

    For more information about the CREATE ASSIGNMENT statement, see CREATE ASSIGNMENT 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_assignment flag to assign an organization's jobs to a reservation.

bq mk \
  --project_id=ADMIN_PROJECT_ID \
  --location=LOCATION \
  --reservation_assignment \
  --reservation_id=RESERVATION_NAME \
  --job_type=JOB_TYPE \
  --assignee_id=ORGANIZATION_ID \
  --assignee_type=ORGANIZATION

Where:

  • ADMIN_PROJECT_ID is the ID of the project that owns the reservation resource.
  • LOCATION is the location of the project.
  • RESERVATION_NAME is the name of the reservation.
  • JOB_TYPE is the type of job to assign to this reservation. One of: QUERY, PIPELINE, or ML_EXTERNAL.
  • ORGANIZATION_ID is the organization ID.

When you create a reservation assignment, wait at least 5 minutes before running a query. Otherwise the query might be billed using on-demand pricing.

Assign a project or folder to 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 in the table of reservations.

  5. Expand the Actions option.

  6. Click Create assignment.

  7. In the Create an assignment section, click Browse.

  8. Browse or search for the project or folder and select it.

  9. Click Create.

SQL

Use the CREATE ASSIGNMENT statement to assign an project to a reservation.

   CREATE ASSIGNMENT `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME.ASSIGNMENT_ID`
   AS JSON """{
   "assignee": "projects/PROJECT_ID",
   "job_type": "JOB_TYPE"
   }"""

Where:

  • ADMIN_PROJECT_ID is the ID of the project that owns the reservation resource.
  • LOCATION is the location of the project.
  • RESERVATION_NAME is the name of the reservation.
  • ASSIGNMENT_ID is the ID of the assignment. It must be unique to the project and location. It must start and end with a lower case letter or a number and contain only lower case letters, numbers and dashes.
  • JOB_TYPE is the type of job to assign to this reservation. One of: QUERY, PIPELINE, or ML_EXTERNAL.
  • PROJECT_ID is the ID of the project to assign to this reservation.

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

bq

Use the bq mk command with the --reservation_assignment flag to assign jobs to a reservation.

bq mk \
  --project_id=ADMIN_PROJECT_ID \
  --location=LOCATION \
  --reservation_assignment \
  --reservation_id=RESERVATION_NAME \
  --job_type=JOB_TYPE \
  --assignee_id=PROJECT_ID \
  --assignee_type=PROJECT

Where:

  • ADMIN_PROJECT_ID is the ID of the project that owns the reservation resource.
  • LOCATION is the location of the project.
  • RESERVATION_NAME is the name of the reservation.
  • JOB_TYPE is the type of job to assign to this reservation. One of: QUERY, PIPELINE, or ML_EXTERNAL.
  • PROJECT_ID is the ID of the project to assign to this reservation.

When you create a reservation assignment, wait at least 5 minutes before running a query. Otherwise the query might be billed using on-demand pricing.

Assign a project to None

None assignments represent the absence of an assignment. Projects assigned to None use on-demand pricing.

SQL

Use the CREATE ASSIGNMENT statement to assign a reservation to none.

  CREATE ASSIGNMENT `ADMIN_PROJECT_ID.region-LOCATION.none.ASSIGNMENT_ID`
   AS JSON """{
   "assignee": "projects/PROJECT_ID",
   "job_type": "JOB_TYPE"
   }"""

Where:

  • ADMIN_PROJECT_ID is the ID of the project that owns the reservation resource.
  • LOCATION is the location of the project.
  • ASSIGNMENT_ID is the ID of the assignment. It must be unique to the project and location. It must start and end with a lower case letter or a number and contain only lower case letters, numbers and dashes.
  • JOB_TYPE is the type of job to assign to this reservation. One of: QUERY, PIPELINE, or ML_EXTERNAL.
  • PROJECT_ID is the ID of the project to assign to this reservation.

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

bq

Use the bq mk command with the --reservation_assignment flag to assign a project to None.

bq mk \
  --project_id=ADMIN_PROJECT_ID \
  --location=LOCATION \
  --reservation_assignment \
  --reservation_id=none \
  --job_type=QUERY \
  --assignee_id=PROJECT_ID \
  --assignee_type=PROJECT

Where:

  • ADMIN_PROJECT_ID is the ID of the project that owns the assignment resource.
  • LOCATION is the location of the project.
  • PROJECT_ID is the ID of the project to assign to None.

Assign slots to BigQuery ML workloads

Certain BigQuery ML queries use services that are external to BigQuery. You can assign reserved slots for these external services.

bq

Use the bq mk command with the --reservation_assignment flag and set the --job_type flag to ML_EXTERNAL.

bq mk \
  --project_id=ADMIN_PROJECT_ID \
  --location=LOCATION \
  --reservation_assignment \
  --reservation_id=RESERVATION_NAME \
  --job_type=ML_EXTERNAL\
  --assignee_id=PROJECT_ID \
  --assignee_type=PROJECT

Where:

  • ADMIN_PROJECT_ID is the ID of the project that owns the reservation resource.
  • LOCATION is the location of the project.
  • RESERVATION_NAME is the name of the reservation.
  • PROJECT_ID is the ID of the project to assign to this reservation.

Find a project's reservation assignment

You can find out if your project, folder, or organization is assigned to a reservation by doing the following:

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. In the table of reservations, expand a reservation to see what resources are assigned to that reservation, or use the Filter field to filter by resource name.

SQL

Use the INFORMATION_SCHEMA.ASSIGNMENTS_BY_PROJECT view find which reservation your project's query jobs are assigned to.

  SELECT assignment_id
    FROM `region-LOCATION`.INFORMATION_SCHEMA.ASSIGNMENTS_BY_PROJECT
    WHERE project_id = 'ADMIN_PROJECT_ID'
     AND assignee_id = 'PROJECT_ID'
     AND job_type = 'JOB_TYPE'

Where:

  • ADMIN_PROJECT_ID is the ID of the project that owns the reservation resource.
  • LOCATION is the location of the project.
  • RESERVATION_NAME is the name of the reservation.
  • JOB_TYPE is the job type. One of: QUERY, PIPELINE, or ML_EXTERNAL.
  • PROJECT_ID is the ID of the project.

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

bq

Use the bq show command with the --reservation_assignment flag to find which reservation your project's query jobs are assigned to.

bq show \
  --project_id=ADMIN_PROJECT_ID \
  --location=LOCATION \
  --reservation_assignment \
  --job_type=JOB_TYPE \
  --assignee_id=PROJECT_ID \
  --assignee_type=PROJECT

Where:

  • ADMIN_PROJECT_ID is the ID of the project that owns the reservation resource.
  • LOCATION is the location of the project.
  • RESERVATION_NAME is the name of the reservation.
  • JOB_TYPE is the job type. One of: QUERY, PIPELINE, or ML_EXTERNAL.
  • PROJECT_ID is the ID of the project.

Move an assignment to a different reservation

You can move an assignment from one reservation to another reservation.

bq

Use the bq update command to move the assignment.

bq update \
  --project_id=ADMIN_PROJECT_ID \
  --location=LOCATION \
  --reservation_assignment \
  --destination_reservation_id=DESTINATION_RESERVATION \
  ASSIGNMENT_ID

Where:

  • ADMIN_PROJECT_ID is the ID of the project that owns the reservation resource.
  • LOCATION is the location of the project.
  • DESTINATION_RESERVATION is the reservation to move the assignment to.
  • ASSIGNMENT_ID is the ID of the assignment. To get the assignment ID, see List a project's reservation assignment.

Remove a project from a reservation

You can remove projects from reservations by removing the reservation assignments. If a project is not assigned to any reservations, it inherits any assignments in its parent folders or organizations, or else uses on-demand pricing if there are no parent assignments.

To remove a project from 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. In the table of reservations, expand the reservation to find the project.

  5. Expand the Actions option.

  6. Click Delete.

SQL

Use the DROP ASSIGNMENT statement to delete an assignment.

  DROP ASSIGNMENT
    `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME.ASSIGNMENT_ID`

Where:

  • ADMIN_PROJECT_ID is the ID of the project that owns the reservation resource.
  • LOCATION is the location of the project.
  • RESERVATION_NAME is the name of the reservation.
  • ASSIGNMENT_ID is the ID of the assignment. To get the assignment ID, see List a project's reservation assignment.

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

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

bq

Use the bq rm command with the --reservation_assignment flag to remove a project from a reservation.

bq rm \
--project_id=ADMIN_PROJECT_ID \
--location=LOCATION \
--reservation_assignment ASSIGNMENT_ID

Where:

Troubleshooting reservations

This section describes troubleshooting steps that you might find helpful if you run into problems using BigQuery Reservations.

Purchased slots are pending

Slots are subject to available capacity. When you purchase slots and BigQuery allocates them, then the Status column shows a check mark. If BigQuery can't allocate the requested slots immediately, then the Status column remains pending. You might have to wait several hours for the slots to become available. If you need access to slots sooner, try the following:

  1. Delete the pending commitment.
  2. Purchase a new commitment for a smaller number of slots. Depending on capacity, the smaller commitment might become active immediately.
  3. Purchase the remaining slots as a separate committment. These slots might show as pending in the Status column, but they generally become active within a few hours.
  4. Optional: When both commitments are available, you can merge them into a single commitment, as long as you purchased the same plan for both.

If a slot commitment fails or takes a long time to complete, consider using on-demand pricing temporarily. With this solution, you might need to run critical queries on a different project that's not assigned to any reservations, or you might need to remove the project assignment altogether.