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.

Working with commitments

Purchase slots

Caution: Before purchasing slots, understand the details of the commitment plans and pricing.

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 the BigQuery page

2. In the navigation panel, go to the Reservations section.

3. Click the Buy slots button.

4. Select the commitment duration.

5. Turn on Default organization to flat-rate if you want to assign your entire organization to the slots that you are about to purchase. Optionally, you can turn off this option and assign individual projects as shown in the Assign organization to reservation section.

6. Select the location.

7. Enter the number of slots.

8. Review your order and confirm it by entering CONFIRM. To complete your purchase, select PURCHASE.

9. After the capacity is provisioned, the requested Capacity Commitment has a green status.

   Note: Slots are usually provisioned quickly, but in rare cases it can take several hours. If you have a critical workload that you expect to have increased demand, we recommend reserving slots at least one day in advance.

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

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 the BigQuery page

2. Click the Reservations section.

3. Click the Slot commitments tab.

   

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

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 the BigQuery page

2. Click the Reservations section.

3. Click the Slot commitments tab.

4. For the commitment you want to edit, click Actions and then 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 the BigQuery page

2. Click the Reservations 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 the BigQuery page

2. Click the Reservations 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.

Caution: This operation cannot be undone.

Console

To delete a capacity commitment, complete the following steps:

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

   Go to the BigQuery page

2. Click the Reservations section.

3. Click the Slot commitments tab.

4. For the commitment you want to delete, click Actions and then Delete.

5. Type "REMOVE" and press Proceed.

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:

• ADMIN_PROJECT_ID is the project ID.
• LOCATION is the location of the project.
• COMMITMENT_ID is the ID of the commitment. To get the ID, see View purchased commitments.

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 the BigQuery page

2. Click the Reservations section.

3. Click the Create Reservation button.

4. In the Reservation name field, enter prod.

5. In the Location field, select US.

6. For the number of slots, enter 500.

7. Click Save.

The new prod reservation is visible in the overview table.

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.

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 the BigQuery page

2. Click the Reservations section.

3. Select the prod reservation previously created and click the edit button.

4. Enter the new reservation size: 1,000 slots.

5. Click Save.

6. The updated prod reservation size is visible in the list of reservations.

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.

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 in-flight jobs to complete before deleting the reservation.

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.

Working with assignments

Assign an organization to a reservation

Console

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

   Go to the BigQuery page

2. In the navigation panel, go to the Reservations section.

3. Click the Assignments tab.

4. In the Search and create assignments section, click the Browse button and select your organization.

5. In the Reservation picker, select the reservation name ending with default.

6. Click Create.

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.

Assign a project to a reservation

Console

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

   Go to the BigQuery page

2. Click the Reservations section.

3. Click the Assignments tab.

4. In the Search and create assignments section, click the Browse button and select your prod project.

5. In the Reservation picker, select the reservation name ending with prod.

6. Click Create.

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.

Assign a project to None

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

Note: None assignments are supported for QUERY jobs only.

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.

Note: Slots used by these jobs are not preemptible; that is, they aren't available for other jobs running in the reservation. These jobs don't use idle slots from other reservations. For more information, see Idle slots.

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.

List 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 the BigQuery page

2. Click the Reservations section.

3. Click the Assignments tab.

4. Click the project picker and select the desired project, folder, or organization.

5. If the reservation assignment exists, it appears below the picker.

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 the BigQuery page

2. Click the Reservations section.

3. Click the Assignments tab.

4. For the assignment you want to delete, click Actions, and then Delete.

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:

• 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. To get the assignment ID, see List a project's reservation assignment.

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.