Purchase and manage slot capacity

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.

A capacity commitment is a purchase of BigQuery compute capacity for some minimum duration of time.

Commitments are a regional resource. Commitments purchased in one region or multi-region cannot be used in any other regions or multi-regions. Commitments cannot be moved between regions or between regions and multi-regions.

Enable the Reservations API

The BigQuery Reservation API is distinct from the existing BigQuery API and must be enabled independently. For more information, see Enabling and disabling APIs.

• The name of the API is "BigQuery Reservations API"
• The endpoint for the BigQuery Reservation API is bigqueryreservation.googleapis.com.

Note: If you want to prevent anyone in your organization from enabling the BigQuery Reservation API, contact support.

Purchase slots

To purchase slots, you create a capacity commitment.

Required permissions

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

• bigquery.capacityCommitments.create on the administation project that maintains ownership of the commitments.

Each of the following predefined IAM roles includes this permission:

• BigQuery Admin
• BigQuery Resource Admin

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

Create a capacity commitment

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

Commitments are a regional resource. Commitments purchased in one region or multi-region cannot be used in any other regions or multi-regions. Commitments cannot be moved between regions or between regions and multi-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 duration, which specifies your commitment plan.

   2. If you are purchasing an Annual commitment, select the Renewal plan that you want to take effect when the commitment expires:

      1. Do not renew and convert to a monthly commitment (Default). When the annual commitment expires, it converts to a monthly commitment.
      2. Renew annually. When the annual commitment expires, it renews for another year as an annual commitment.
      3. Do not renew and convert to flex slots. When the annual commitment expires, it converts to a flex slots commitment.

      For more information, see Commitment plans.

   3. Select the Location.

   4. Enter the Number of slots you want to purchase.

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

   

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

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=true \
  --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 capacity commitments

Required permissions

To view commitments, you need the following Identity and Access Management (IAM) permission:

• bigquery.capacityCommitments.list on the administation project that maintains ownership of the commitments.

Each of the following predefined IAM roles includes this permission:

• BigQuery Admin
• BigQuery Resource Admin
• BigQuery Resource Editor
• BigQuery Resource Viewer
• BigQuery User

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

View capacity commitments by project

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.

   

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=true \
 --location=LOCATION \
 --project_id=ADMIN_PROJECT_ID

Where:

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

Update capacity commitments

You can make the following updates to a capacity commitment:

• Renew an existing commitment.
• Convert a commitment to a commitment plan with a longer duration.
• Split a commitment into two commitments.
• Merge two commitments into a single commitment.

Required permissions

To update capacity commitments, you need the following Identity and Access Management (IAM) permission:

• bigquery.capacityCommitments.update on the administation project that maintains ownership of the commitments.

Each of the following predefined IAM roles includes this permission:

• BigQuery Admin
• BigQuery Resource Admin

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

Renew a commitment

Annual commitments have a renewal plan, which you specify when you create or convert to an annual commitment. You can change your annual commitment's renewal plan at any time before the commitment end date.

Console

You can change your renewal plan for an annual commitment as follows:

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. Click Actions, and then select the Edit renewal plan option.

6. Select the new renewal plan.

bq

To change the renewal plan choice for an annual commitment, use the bq update command with the --capacity_commitment and --renewal_plan flags.

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

Where:

• ADMIN_PROJECT_ID is the project ID.
• LOCATION is the location of the project.
• PLAN_TYPE is the renewal 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 commitment to a monthly or annual commitment.
• You can convert your monthly commitment to an annual commitment.

As soon as you update your commitment, you are charged the rate associated with the new plan, and the end date resets.

bq

To convert a commitment, use the bq update command with the --plan flag.

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

Where:

• ADMIN_PROJECT_ID is the project ID.
• LOCATION is the location of the project.
• PLAN_TYPE is the new plan type; either MONTHLY or ANNUAL.
• RENEWAL_PLAN is the renewal plan. This applies only if the PLAN_TYPE is ANNUAL. If the PLAN_TYPE is MONTHLY, omit this flag.
• 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. 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 off 300 slots into a new commitment, leaving 700 slots in the original commitment. You could then renew 700 slots at the annual rate, and convert 300 slots to flex slots after the end date.

When you split a commitment, the new commitment has the same plan and the same commitment end date as the original commitment.

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=true \
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 into one commitment. The merging commitments must all be of the same type (FLEX, MONTHLY, or ANNUAL). 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=LOCATION \
--merge=true \
--capacity_commitment=true \
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

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

Required permissions

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

• bigquery.capacityCommitments.delete on the administation project that maintains ownership of the commitments.

Each of the following predefined IAM roles includes this permission:

• BigQuery Admin
• BigQuery Resource Admin

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

Deleting a capacity commitment

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

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

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=true \
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 you want to delete. To get the ID, see View purchased commitments.

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.