Work with reservation assignments
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.
Create reservation assignments
To use the slots that you purchase, you create an assignment which assigns a project, folder, or organization to a slot reservation.
Projects use the single most specific reservation in the resource hierarchy to which they are assigned. A folder assignment overrides an organization assignment, and a project assignment overrides a folder assignment. Folder and organization assignments are not available to standard edition reservations.
In order to create an assignment on a reservation, the reservation must fulfill at least one of the following criteria:
It is configured with a non-zero amount of assigned baseline slots.
It is configured with a non-zero amount of autoscaling slots.
It is configured to use idle slots, and there are available idle slots within the project.
If you attempt to assign a resource to a reservation that doesn't meet at least
one of these criteria, you receive the following
message: Assignment is pending, your project will be executed as on-demand.
You can assign a resource to a failover reservation, but the assignment pends in the secondary location.
Required permissions
To create a reservation assignment, you need the following Identity and Access Management (IAM) permission:
bigquery.reservationAssignments.create
on the administration project and the assignee.
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.
Assign an organization to a reservation
Console
In the Google Cloud console, go to the BigQuery page.
In the navigation panel, go to the Capacity Management section.
Click the Reservations tab.
Find the reservation in the table of reservations
Expand the
Actions option.Click Create assignment.
In the Create an assignment section, click Browse.
Browse or search for the organization and select it.
In the Job Type section, select a job type to assign for this reservation. Options include the following:
QUERY
CONTINUOUS
(Preview)PIPELINE
BACKGROUND
ML_EXTERNAL
For more information about job types, see reservation assignments. This default value is
QUERY
.Click Create.
SQL
To assign an organization to a reservation, use the
CREATE ASSIGNMENT
DDL statement.
In the Google Cloud console, go to the BigQuery page.
In the query editor, enter the following statement:
CREATE ASSIGNMENT `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME.ASSIGNMENT_ID` OPTIONS ( assignee = 'organizations/ORGANIZATION_ID', job_type = 'JOB_TYPE');
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 reservationASSIGNMENT_ID
: the ID of the assignmentThe ID must be unique to the project and location, start and end with a lowercase letter or a number, and contain only lowercase letters, numbers, and dashes.
ORGANIZATION_ID
: the organization IDJOB_TYPE
: the type of job to assign to this reservation, such asQUERY
,CONTINUOUS
(Preview),PIPELINE
,BACKGROUND
, orML_EXTERNAL
Click
Run.
For more information about how to run queries, see Run an interactive query.
bq
To assign an organization's jobs to a reservation, use the bq mk
command
with the --reservation_assignment
flag:
bq mk \ --project_id=ADMIN_PROJECT_ID \ --location=LOCATION \ --reservation_assignment \ --reservation_id=RESERVATION_NAME \ --assignee_id=ORGANIZATION_ID \ --job_type=JOB_TYPE \ --assignee_type=ORGANIZATION
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 reservationORGANIZATION_ID
: the organization IDJOB_TYPE
: the type of job to assign to this reservation, such asQUERY
,CONTINUOUS
(Preview),PIPELINE
,BACKGROUND
, orML_EXTERNAL
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
In the Google Cloud console, go to the BigQuery page.
In the navigation panel, go to the Capacity Management section.
Click the Reservations tab.
Find the reservation in the table of reservations.
Expand the
Actions option.Click Create assignment.
In the Create an assignment section, click Browse.
Browse or search for the project or folder and select it.
In the Job Type section, select a job type to assign for this reservation. Options include the following:
QUERY
CONTINUOUS
(Preview)PIPELINE
BACKGROUND
ML_EXTERNAL
For more information about job types, see reservation assignments. This default value is
QUERY
.Click Create.
SQL
To assign a project to a reservation, use the
CREATE ASSIGNMENT
DDL statement.
In the Google Cloud console, go to the BigQuery page.
In the query editor, enter the following statement:
CREATE ASSIGNMENT `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME.ASSIGNMENT_ID` OPTIONS( assignee="projects/PROJECT_ID", job_type="JOB_TYPE");
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 reservationASSIGNMENT_ID
: the ID of the assignmentThe ID must be unique to the project and location, start and end with a lowercase letter or a number, and contain only lowercase letters, numbers, and dashes.
PROJECT_ID
: the ID of the project to assign to the reservationJOB_TYPE
: the type of job to assign to this reservation, such asQUERY
,CONTINUOUS
(Preview),PIPELINE
,BACKGROUND
, orML_EXTERNAL
Click
Run.
For more information about how to run queries, see Run an interactive query.
bq
To assign jobs to a reservation, use the bq mk
command with the
--reservation_assignment
flag:
bq mk \ --project_id=ADMIN_PROJECT_ID \ --location=LOCATION \ --reservation_assignment \ --reservation_id=RESERVATION_NAME \ --assignee_id=PROJECT_ID \ --job_type=JOB_TYPE \ --assignee_type=PROJECT
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 reservationPROJECT_ID
: the ID of the project to assign to this reservationJOB_TYPE
: the type of job to assign to this reservation, such asQUERY
,CONTINUOUS
(Preview),PIPELINE
,BACKGROUND
, orML_EXTERNAL
Terraform
Use the
google_bigquery_reservation_assignment
resource.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
The following example assigns a project to the reservation named
my-reservation
:
To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.
Prepare Cloud Shell
- Launch Cloud Shell.
-
Set the default Google 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 Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.
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.
To make a project that only uses idle
slots, create a
reservation with 0
slots assigned to it, then follow the prior steps to assign the project to that
reservation.
Assign a project to none
Assignments to none
represent the absence of an assignment. Projects assigned
to none
use on-demand pricing.
SQL
To assign a project to none
, use the
CREATE ASSIGNMENT
DDL statement.
In the Google Cloud console, go to the BigQuery page.
In the query editor, enter the following statement:
CREATE ASSIGNMENT `ADMIN_PROJECT_ID.region-LOCATION.none.ASSIGNMENT_ID` OPTIONS( assignee="projects/PROJECT_ID", job_type="QUERY");
Replace the following:
LOCATION
: the location of jobs that should use on-demand pricingASSIGNMENT_ID
: the ID of the assignmentThe ID must be unique to the project and location, start and end with a lowercase letter or a number, and contain only lowercase letters, numbers, and dashes.
PROJECT_ID
: the ID of the project to assign to the reservation
Click
Run.
For more information about how to run queries, see Run an interactive query.
bq
To assign a project to none
, use the bq mk
command with the
--reservation_assignment
flag:
bq mk \ --location=LOCATION \ --reservation_assignment \ --reservation_id=none \ --job_type=QUERY \ --assignee_id=PROJECT_ID \ --assignee_type=PROJECT
Replace the following:
LOCATION
: the location of jobs that should use on-demand pricingPROJECT_ID
: the ID of the project to assign tonone
Assign slots to BigQuery ML workloads
The following BigQuery ML model types use external services:
You can assign reserved slots to queries using these services by using the ML_EXTERNAL
assignment type. If no ML_EXTERNAL
assignment type is found, the query job runs as on-demand.
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
Replace the following:
ADMIN_PROJECT_ID
: the ID of the project that owns the reservation resourceLOCATION
: the location of the reservationRESERVATION_NAME
: the name of the reservationPROJECT_ID
: the ID of the project to assign to this reservation
Find reservation assignments
Required permissions
To search for a reservation assignment for a given project, folder, or organization, you need the following Identity and Access Management (IAM) permission:
bigquery.reservationAssignments.list
on the administration project.
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.
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
In the Google Cloud console, go to the BigQuery page.
In the navigation panel, go to the Capacity Management section.
Click the Reservations tab.
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
To find which reservation your project's query jobs are assigned to, query
the INFORMATION_SCHEMA.ASSIGNMENTS_BY_PROJECT
view.
In the Google Cloud console, go to the BigQuery page.
In the query editor, enter the following statement:
SELECT assignment_id FROM `region-LOCATION`.INFORMATION_SCHEMA.ASSIGNMENTS_BY_PROJECT WHERE assignee_id = 'PROJECT_ID' AND job_type = 'JOB_TYPE';
Replace the following:
LOCATION
: the location of reservations to viewADMIN_PROJECT_ID
: the project ID of the administration project that owns the reservation resourcePROJECT_ID
: the ID of the project to assign to the reservationJOB_TYPE
: the type of job to assign to this reservation, such asQUERY
,CONTINUOUS
(Preview),PIPELINE
,BACKGROUND
, orML_EXTERNAL
Click
Run.
For more information about how to run queries, see Run an interactive query.
bq
To find which reservation your project's query jobs are assigned to, use the
bq show
command with the --reservation_assignment
flag:
bq show \ --project_id=ADMIN_PROJECT_ID \ --location=LOCATION \ --reservation_assignment \ --job_type=JOB_TYPE \ --assignee_id=PROJECT_ID \ --assignee_type=PROJECT
Replace the following:
ADMIN_PROJECT_ID
: the ID of the project that owns the reservation resourceLOCATION
: the location of reservations to viewJOB_TYPE
: the type of job to assign to this reservation, such asQUERY
,CONTINUOUS
(Preview),PIPELINE
,BACKGROUND
, orML_EXTERNAL
PROJECT_ID
: the ID of the project
Update reservation assignments
Move an assignment to a different reservation
You can move an assignment from one reservation to another reservation.
To move a reservation assignment, you need the following Identity and Access Management (IAM) permissions on the administration project and the assignee.
bigquery.reservationAssignments.create
bigquery.reservationAssignments.delete
Each of the following predefined IAM roles includes these permissions:
BigQuery Admin
BigQuery Resource Admin
BigQuery Resource Editor
For more information about IAM roles in BigQuery, see Predefined roles and permissions.
To move an assignment, use the bq update
command:
bq update \ --project_id=ADMIN_PROJECT_ID \ --location=LOCATION \ --reservation_assignment \ --destination_reservation_id=DESTINATION_RESERVATION \ ADMIN_PROJECT_ID:LOCATION.RESERVATION_NAME.ASSIGNMENT_ID
Replace the following:
ADMIN_PROJECT_ID
: the ID of the project that owns the reservation resourceLOCATION
: the location of the new reservationRESERVATION_NAME
: the reservation to move the assignment fromDESTINATION_RESERVATION
: the reservation to move the assignment toASSIGNMENT_ID
: the ID of the assignmentTo get the assignment ID, see List a project's reservation assignment.
Delete reservation assignments
You can remove a project from a reservation by deleting the reservation assignment. 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.
When you delete a reservation assignment, the jobs executing with slots from that reservation continue to run until completion.
Required permissions
To delete a reservation assignment, you need the following Identity and Access Management (IAM) permission:
bigquery.reservationAssignments.delete
on the administration project and the assignee.
Each of the following predefined IAM roles includes this permission:
BigQuery Admin
BigQuery Resource Admin
BigQuery Resource Editor
Remove a project from a reservation
To remove a project from a reservation:
Console
In the Google Cloud console, go to the BigQuery page.
In the navigation panel, go to the Capacity Management section.
Click the Reservations tab.
In the table of reservations, expand the reservation to find the project.
Expand the
Actions option.Click Delete.
SQL
Use the
DROP ASSIGNMENT
DDL statement.
In the Google Cloud console, go to the BigQuery page.
In the query editor, enter the following statement:
DROP ASSIGNMENT `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME.ASSIGNMENT_ID`;
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 reservationASSIGNMENT_ID
: the ID of the assignmentTo find the assignment ID, see List a project's reservation assignment.
Click
Run.
For more information about how to run queries, see Run an interactive query.
bq
To remove a project from a reservation, use the bq rm
command with the
--reservation_assignment
flag:
bq rm \ --project_id=ADMIN_PROJECT_ID \ --location=LOCATION \ --reservation_assignment RESERVATION_NAME.ASSIGNMENT_ID
Replace the following:
ADMIN_PROJECT_ID
: the ID of the project that owns the reservation resourceLOCATION
: the location of the reservationRESERVATION_NAME
: the name of the reservationASSIGNMENT_ID
: the ID of the assignmentTo get the assignment ID, see Find a project's reservation assignment.