Work with slot reservations
The BigQuery Reservation API lets you purchase dedicated slots (called commitments), create pools of slots (called reservations), and assign projects, folders, and organizations to those reservations.
Reservations allow you to assign a dedicated number of slots
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:
bigquery.reservations.create
on the administration project that maintains ownership of the commitments.
Each of the following predefined IAM roles includes this permission:
BigQuery Resource Editor
BigQuery Resource Admin
For more information about IAM roles in BigQuery, see Predefined roles and permissions.
Create a reservation with dedicated slots
Select one of the following options:
Console
In the Trusted Cloud console, go to the BigQuery page.
In the navigation menu, click Capacity management.
Click Create reservation.
In the Reservation name field, enter a name for the reservation.
In the Location list, select the location. If you select a BigQuery Omni location, your edition option is limited to the Enterprise edition.
In the Edition list, select the edition. BigQuery edition features such as autoscaling are only available within an edition. For more information, see Introduction to BigQuery editions.
In the Max reservation size selector list, select the maximum reservation size.
Optional: In the Baseline slots field, enter the number of baseline slots for the reservation.
The number of available autoscaling slots is determined by subtracting the Baseline slots value from the Max reservation size. For example, if you create a reservation with 100 baseline slots and a max reservation size of 400, your reservation has 300 autoscaling slots. For more information about baseline slots, see Using reservations with baseline and autoscaling slots.
To disable idle slot sharing and use only the specified slot capacity, click the Ignore idle slots toggle.
To expand the Advanced settings section, click the
expander arrow.Optional: To set the target job concurrency, click the Override automatic target job concurrency toggle to on and enter the Target Job Concurrency.
The breakdown of slots is displayed in the Cost estimate table. A summary of the reservation is displayed in the Capacity summary table.
Click Save.
The new reservation is visible in the Slot reservations tab.
SQL
To create a reservation, use the
CREATE RESERVATION
DDL statement.
In the Trusted Cloud console, go to the BigQuery page.
In the query editor, enter the following statement:
CREATE RESERVATION `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME` OPTIONS ( slot_capacity = NUMBER_OF_BASELINE_SLOTS, edition = EDITION, autoscale_max_slots = NUMBER_OF_AUTOSCALING_SLOTS);
Replace the following:
ADMIN_PROJECT_ID
: the project ID of the administration project that owns the reservation resourceLOCATION
: the location of the reservation. If you select a BigQuery Omni location, your edition option is limited to the Enterprise edition.RESERVATION_NAME
: the name of the reservationThe name can contain only lowercase alphanumeric characters or dashes, must start with a letter and must not end with a dash, and the maximum length is 64 characters.
NUMBER_OF_BASELINE_SLOTS
: the number baseline of slots to allocate to the reservation. You cannot set theslot_capacity
option and theedition
option in the same reservation.EDITION
: the edition of the reservation. Assigning a reservation to an edition comes with feature and pricing changes. For more information, see Introduction to BigQuery editions.NUMBER_OF_AUTOSCALING_SLOTS
: the number of autoscaling slots assigned to the reservation. This is equal to the value of the max reservation size minus the number of baseline slots.
Click
Run.
For more information about how to run queries, see Run an interactive query.
bq
To create a reservation, use the bq mk
command with the --reservation
flag:
bq mk \ --project_id=ADMIN_PROJECT_ID \ --location=LOCATION \ --reservation \ --slots=NUMBER_OF_BASELINE_SLOTS \ --ignore_idle_slots=false \ --edition=EDITION \ --autoscale_max_slots=NUMBER_OF_AUTOSCALING_SLOTS \ --max_slots=MAXIMUM_NUMBER_OF_SLOTS --scaling_mode=SCALING_MODE RESERVATION_NAME
Replace the following:
ADMIN_PROJECT_ID
: the project IDLOCATION
: the location of the reservation. If you select a BigQuery Omni location, your edition option is limited to the Enterprise edition.NUMBER_OF_BASELINE_SLOTS
: the number of baseline slots to allocate to the reservationRESERVATION_NAME
: the name of the reservation. The name can contain only lowercase alphanumeric characters or dashes, must start with a letter and must not end with a dash, and the maximum length is 64 characters.EDITION
: the edition of the reservation. Assigning a reservation to an edition comes with feature and pricing changes. For more information, see Introduction to BigQuery editions.NUMBER_OF_AUTOSCALING_SLOTS
: the number of autoscaling slots assigned to the reservation. This is equal to the value of the max reservation size minus the number of baseline slots. This can't be configured with either the--max_slots
or--scaling_mode
flags.MAXIMUM_NUMBER_OF_SLOTS
: the maximum number of slots the reservation can consume. This value must be configured with the--scaling_mode
flag (Preview).SCALING_MODE
: the scaling mode of the reservation. The options areALL_SLOTS
,IDLE_SLOTS_ONLY
, orAUTOSCALE_ONLY
. This value must be configured with the--scaling_mode
flag (Preview).
For information about the --ignore_idle_slots
flag, see
Idle slots. The default
value is false
.
Terraform
Use the
google_bigquery_reservation
resource.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
The following example creates a reservation named my-reservation
:
To apply your Terraform configuration in a Trusted Cloud project, complete the steps in the following sections.
Prepare Cloud Shell
- Launch Cloud Shell.
-
Set the default Trusted Cloud project where you want to apply your Terraform configurations.
You only need to run this command once per project, and you can run it in any directory.
export GOOGLE_CLOUD_PROJECT=PROJECT_ID
Environment variables are overridden if you set explicit values in the Terraform configuration file.
Prepare the directory
Each Terraform configuration file must have its own directory (also called a root module).
-
In Cloud Shell, create a directory and a new
file within that directory. The filename must have the
.tf
extension—for examplemain.tf
. In this tutorial, the file is referred to asmain.tf
.mkdir DIRECTORY && cd DIRECTORY && touch main.tf
-
If you are following a tutorial, you can copy the sample code in each section or step.
Copy the sample code into the newly created
main.tf
.Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.
- Review and modify the sample parameters to apply to your environment.
- Save your changes.
-
Initialize Terraform. You only need to do this once per directory.
terraform init
Optionally, to use the latest Google provider version, include the
-upgrade
option:terraform init -upgrade
Apply the changes
-
Review the configuration and verify that the resources that Terraform is going to create or
update match your expectations:
terraform plan
Make corrections to the configuration as necessary.
-
Apply the Terraform configuration by running the following command and entering
yes
at the prompt:terraform apply
Wait until Terraform displays the "Apply complete!" message.
- Open your Trusted Cloud project to view the results. In the Trusted Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.
Python
Create a predictable reservation
Before you create a reservation with a maximum number of slots, you must first enable reservation-based fairness.
Enable reservation-based fairness
To enable reservation-based fairness, set the enable_reservation_based_fairness
flag to
true
.
To update the reservation-based fairness on a project, you need the bigquery.config.update
permission on the project
that maintains ownership of the reservations. The predefined BigQuery Admin
role includes this permission.
For more information about updating the default configuration of a project, see Manage configuration settings.
ALTER PROJECT `PROJECT_NAME` SET OPTIONS ( `region-LOCATION.enable_reservation_based_fairness`= true);
Replace the following:
- PROJECT_NAME: the project ID of the administration project
- LOCATION: the location of the reservation
Create a predictable reservation
To create a predictable reservation with a maximum number of slots, select one of the following options:
Console
In the Trusted Cloud console, go to the BigQuery page.
In the navigation panel, go to the Capacity management section, and then click Create reservation.
In the Reservation name field, enter a name for the reservation.
In the Location list, select the location. If you select a BigQuery Omni location, your edition option is limited to the Enterprise edition.
In the Edition list, select the edition. Autoscaling is only available within an edition. For more information, see Understand BigQuery editions.
In the Max reservation size selector list, select the maximum reservation size.
Optional: In the Baseline slots field, enter the number of baseline slots for the reservation.
The number of available autoscaling slots is determined by subtracting the Baseline slots value from the Max reservation size. For example, if you create a reservation with 100 baseline slots and a max reservation size of 400, your reservation has 300 autoscaling slots. For more information about baseline slots, see Using reservations with baseline and autoscaling slots.
To disable idle slot sharing and use only the specified slot capacity, click the Ignore idle slots toggle.
To expand the Advanced settings section, click the
expander arrow.- In the How to use idle slots? list, select the configuration option.
The breakdown of slots is displayed in the Cost estimate table. A summary of the reservation is displayed in the Capacity summary table.
Click Save.
The new reservation is visible in the Slot reservations tab.
bq
To create a predictable reservation, use the bq mk
command with the --reservation
flag and set the value of max_slots
and scaling_mode
:
bq mk \ --project_id=ADMIN_PROJECT_ID \ --location=LOCATION \ --reservation \ --slots=NUMBER_OF_BASELINE_SLOTS \ --ignore_idle_slots=false \ --edition=EDITION \ --max_slots=MAXIMUM_NUMBER_OF_SLOTS \ --scaling_mode=SCALING_MODE RESERVATION_NAME
Replace the following:
ADMIN_PROJECT_ID
: the project IDLOCATION
: the location of the reservation. If you select a BigQuery Omni location, your edition option is limited to the Enterprise edition.NUMBER_OF_BASELINE_SLOTS
: the number of baseline slots to allocate to the reservationRESERVATION_NAME
: the name of the reservationEDITION
: the edition of the reservation. Assigning a reservation to an edition comes with feature and pricing changes. For more information, see Introduction to BigQuery editions.MAXIMUM_NUMBER_OF_SLOTS
: the maximum number of slots the reservation can consume. This value must be configured with the--scaling_mode
flag (Preview).SCALING_MODE
: the scaling mode of the reservation. The options areALL_SLOTS
,IDLE_SLOTS_ONLY
, orAUTOSCALE_ONLY
. This value must be configured with the--scaling_mode
flag (Preview).
For information about the --ignore_idle_slots
flag, see
Idle slots. The default
value is false
.
To learn more about predictable reservations, see Predictable reservations.
Update reservations
You can make the following updates to a reservation:
- Change the reservation size by adding or removing slots.
- Configure whether queries in this reservation use idle slots.
- Change the amount of baseline or autoscaling slots allocated to a reservation.
- Set the target job concurrency.
To change the edition of a reservation, first delete the reservation, then create a reservation with the updated edition.
Required permissions
To update a reservation, you need the following Identity and Access Management (IAM) permission:
bigquery.reservations.update
on the administration 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
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
In the Trusted Cloud console, go to the BigQuery page.
In the navigation menu, click Capacity management.
Click the Slot reservations tab.
Find the reservation you want to update.
Expand the
Actions option.Click Edit.
In the Max reservation size selector dialog, enter the max reservation size.
In the Baseline slots field, enter the number of baseline slots.
To expand the Advanced settings section, click the
expander arrow.Optional: To set the target job concurrency, click the Override automatic target job concurrency toggle to on and enter the Target Job Concurrency.
Click Save.
SQL
To change the size of a reservation, use the
ALTER RESERVATION SET OPTIONS
data definition language (DDL) statement.
In the Trusted Cloud console, go to the BigQuery page.
In the query editor, enter the following statement:
ALTER RESERVATION `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME` SET OPTIONS ( slot_capacity = NUMBER_OF_BASELINE_SLOTS, autoscale_max_slots = NUMBER_OF_AUTOSCALING_SLOTS);
Replace the following:
ADMIN_PROJECT_ID
: the project ID of the administration project that owns the reservation resourceLOCATION
: the location of the reservation, for exampleeurope-west9
.RESERVATION_NAME
: the name of the reservation. The name can contain only lowercase alphanumeric characters or dashes, must start with a letter and must not end with a dash, and the maximum length is 64 characters.NUMBER_OF_BASELINE_SLOTS
: the number of baseline slots to allocate to the reservation.NUMBER_OF_AUTOSCALING_SLOTS
: the number of autoscaling slots assigned to the reservation. This is equal to the value of the max reservation size minus the number of baseline slots.
Click
Run.
For more information about how to run queries, see Run an interactive query.
bq
To update the size of a reservation, use the bq update
command with the
--reservation
flag:
bq update \ --project_id=ADMIN_PROJECT_ID \ --location=LOCATION \ --slots=NUMBER_OF_BASELINE_SLOTS \ --autoscale_max_slots=NUMBER_OF_AUTOSCALING_SLOTS \ --reservation RESERVATION_NAME
Replace the following:
ADMIN_PROJECT_ID
: the project IDLOCATION
: the location of the reservationNUMBER_OF_BASELINE_SLOTS
: the number of baseline slots to allocate to the reservationRESERVATION_NAME
: the name of the reservation. The name can contain only lowercase alphanumeric characters or dashes, must start with a letter and must not end with a dash, and the maximum length is 64 characters.NUMBER_OF_AUTOSCALING_SLOTS
: the number of autoscaling slots assigned to the reservation. This is equal to the value of the max reservation size minus the number of baseline slots.
Python
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.
To update a reservation, use the bq update
command with the --reservation
flag . 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
Replace the following:
ADMIN_PROJECT_ID
: the project IDLOCATION
: the location of the reservationRESERVATION_NAME
: the name of the reservation. The name can contain only lowercase alphanumeric characters or dashes, must start with a letter and must not end with a dash, and the maximum length is 64 characters.
List the idle slot configuration
To list the idle slots setting for a reservation, do the following:
SQL
Query the ignore_idle_slots
column of the
INFORMATION_SCHEMA.RESERVATIONS_BY_PROJECT
view.
In the Trusted Cloud console, go to the BigQuery page.
In the query editor, enter the following statement:
SELECT reservation_name, ignore_idle_slots FROM `ADMIN_PROJECT_ID.region-LOCATION`.INFORMATION_SCHEMA.RESERVATIONS_BY_PROJECT;
Replace the following:
ADMIN_PROJECT_ID
: the project ID of the administration project that owns the reservation resourcesLOCATION
: the location of the reservations
Click
Run.
For more information about how to run queries, see Run an interactive query.
bq
Use the bq ls
command with the --reservation
flag:
bq ls --reservation \ --project_id=ADMIN_PROJECT_ID \ --location=LOCATION
Replace the following:
ADMIN_PROJECT_ID
: the project ID of the administration project that owns the reservation resourcesLOCATION
: the location of the reservations
The ignoreIdleSlots
field contains the configuration setting.
Delete reservations
If you delete a reservation, any running jobs that use slots from that reservation fail. To prevent errors, allow running jobs to complete before you delete the reservation.
Required permissions
To delete a reservation, you need the following Identity and Access Management (IAM) permission:
bigquery.reservations.delete
on the administration 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
For more information about IAM roles in BigQuery, see Predefined roles and permissions.
Delete a reservation
Console
In the Trusted Cloud console, go to the BigQuery page.
In the navigation menu, click Capacity management.
Click the Reservations tab.
Find the reservation you want to delete.
Expand the
Actions option.Click Delete.
In the Delete reservation dialog, click Delete.
SQL
To delete a reservation, use the
DROP RESERVATION
DDL statement.
In the Trusted Cloud console, go to the BigQuery page.
In the query editor, enter the following statement:
DROP RESERVATION `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME`;
Replace the following:
ADMIN_PROJECT_ID
: the project ID of the administration project that owns the reservation resourceLOCATION
: the location of the reservationRESERVATION_NAME
: the ID of the reservation
Click
Run.
For more information about how to run queries, see Run an interactive query.
bq
To delete a reservation, use the bq rm
command with the --reservation
flag:
bq rm \ --project_id=ADMIN_PROJECT_ID \ --location=LOCATION \ --reservation RESERVATION_NAME
Replace the following:
ADMIN_PROJECT_ID
: the project ID of the administration project that owns the reservation resourceLOCATION
: the location of the reservationRESERVATION_NAME
: the name of the reservation. The name can contain only lowercase alphanumeric characters or dashes, must start with a letter and must not end with a dash, and the maximum length is 64 characters.
Python
Control access to reservations
To request support or provide feedback for this feature, contact bigquery-wlm-feedback@google.com.
You can control which users have access to specific
reservations. For a user to override a reservation on their query, they must
have the reservations.use
permission on that reservation.
Required permissions
To get the permission that
you need to specify a particular reservation for your job,
ask your administrator to grant you the
Resource Editor (roles/bigquery.resourceEditor
)
IAM role on the reservation resource.
For more information about granting roles, see Manage access to projects, folders, and organizations.
This predefined role contains the
reservations.use
permission,
which is required to
specify a particular reservation for your job.
You might also be able to get this permission with custom roles or other predefined roles.
Control access to a reservation
To manage access to a specific reservation resource, use the bq
set-iam-policy
command.
To manage access to multiple reservation resources, use the Trusted Cloud console to grant the BigQuery Resource Editor role on the project, folder, or organization. When you grant the role, use an IAM condition to allow access to the reservation resources when the specified conditions are met.
To control access to reservations, do one of the following:
Console
In the Trusted Cloud console, you can allow access to multiple reservation resources by using a condition.
In the Trusted Cloud console, go to the IAM page.
Select a project, folder, or organization.
To grant the
bigquery.resourceEditor
role to a principal who has a role on the reservation resources:On the View by principals tab, navigate to the appropriate principal or use the Filter option to find the principal.
Click
Edit principal.On the Assign roles page, click
Add roles.In the Search for roles field, enter
bigquery.resourceEditor
.Check the BigQuery Resource Editor option in the search results and then click Apply.
Click Save.
Alternatively, to grant the
bigquery.resourceEditor
role to a principal who doesn't have a role on the reservation resources:Click
Grant Access.On the Add principals page, in the New principals field, enter the principal's identifier — for example,
//iam.googleapis.com/locations/global/workforcePools/my-pool/subject/my-user@example.com
.Click
Add roles.In the Search for roles field, enter
bigquery.resourceEditor
.Check the BigQuery Resource Editor option in the search results and then click Apply.
In the BigQuery Resource Editor box, click Add condition.
On the Add condition page:
Enter values in the Title and Description fields.
In the Condition builder, add your condition. For example, to add a condition that grants the role to all reservation names that end with
/reservation1
, for Condition type, choose Name, for Operator, choose Ends with, and for Value, enter/reservation1
.Click Save.
Click Save.
bq
In the bq command-line tool, you can grant access to an individual reservation resource.
To grant access to a reservation, use the bq
set-iam-policy
command:
bq set-iam-policy RESOURCE FILE_NAME
Replace the following:
RESOURCE
: the name of the reservation resource. For example,projects/project1/locations/US/reservations/reservation1
.FILE_NAME
: the file that contains the policy in JSON format. The format should follow the IAM policy structure for allow policies. For example:{ "bindings": [ { "members": [ "user:my-user@example.com" ], "role": "roles/bigquery.resourceEditor" } ], "etag": "BwUjMhCsNvY=", "version": 1 }
For more information about IAM, see Manage access to other resources.