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
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
In the Cloud Console, go to the BigQuery page.
In the navigation panel, go to the Reservations section.
Click the Buy slots button.
Select the commitment duration.
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.
Select the location.
Enter the number of slots.
Review your order and confirm it by entering
CONFIRM
. To complete your purchase, selectPURCHASE
.After the capacity is provisioned, the requested Capacity Commitment has a green status.
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
In the Cloud Console, go to the BigQuery page.
Click the Reservations section.
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.
In the Cloud Console, go to the BigQuery page.
Click the Reservations section.
Click the Slot commitments tab.
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
In the Cloud Console, go to the BigQuery page.
Click the Reservations section.
Click the Slot commitments tab.
Select the commitment that you want to split.
Click Split.
In the Split commitment page, use the Configure split slider to select how many slots go into each split, in increments of 100 slots.
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_SPLIT
is 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
In the Cloud Console, go to the BigQuery page.
Click the Reservations section.
Click the Slot commitments tab.
Select the commitments that you want to merge.
Click Merge.
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:
In the Cloud Console, go to the BigQuery page.
Click the Reservations section.
Click the Slot commitments tab.
For the commitment you want to delete, click Actions and then Delete.
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
In the Cloud Console, go to the BigQuery page.
Click the Reservations section.
Click the Create Reservation button.
In the Reservation name field, enter
prod
.In the Location field, select
US
.For the number of slots, enter
500
.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
In the Cloud Console, go to the BigQuery page.
Click the Reservations section.
Select the
prod
reservation previously created and click the edit button.Enter the new reservation size: 1,000 slots.
Click Save.
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
In the Cloud Console, go to the BigQuery page.
In the navigation panel, go to the Reservations section.
Click the Assignments tab.
In the Search and create assignments section, click the Browse button and select your organization.
In the Reservation picker, select the reservation name ending with
default.
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
, orML_EXTERNAL
.ORGANIZATION_ID
is the organization ID.
Assign a project to a reservation
Console
In the Cloud Console, go to the BigQuery page.
Click the Reservations section.
Click the Assignments tab.
In the Search and create assignments section, click the Browse button and select your prod project.
In the Reservation picker, select the reservation name ending with
prod.
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
, orML_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.
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 toNone
.
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.
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
In the Cloud Console, go to the BigQuery page.
Click the Reservations section.
Click the Assignments tab.
Click the project picker and select the desired project, folder, or organization.
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
, orML_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
In the Cloud Console, go to the BigQuery page.
Click the Reservations section.
Click the Assignments tab.
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:
- Delete the pending commitment.
- Purchase a new commitment for a smaller number of slots. Depending on capacity, the smaller commitment might become active immediately.
- 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.
- 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.