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.

Note: To create reservation assignments for continuous query jobs (jobs that use the CONTINUOUS job type), you must enroll in the feature preview.

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

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the navigation panel, go to the Capacity Management section.

3. Click the Reservations tab.

4. Find the reservation in the table of reservations

5. Expand the Actions option.

6. Click Create assignment.

7. In the Create an assignment section, click Browse.

8. Browse or search for the organization and select it.

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

10. Click Create.

SQL

To assign an organization to a reservation, use the CREATE ASSIGNMENT DDL statement.

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. 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 resource
   • LOCATION: the location of the reservation
   • RESERVATION_NAME: the name of the reservation

   • ASSIGNMENT_ID: the ID of the assignment

     The 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 ID
   • JOB_TYPE: the type of job to assign to this reservation, such as QUERY, CONTINUOUS (Preview), PIPELINE, BACKGROUND, or ML_EXTERNAL

3. 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 resource
• LOCATION: the location of the reservation
• RESERVATION_NAME: the name of the reservation
• ORGANIZATION_ID: the organization ID
• JOB_TYPE: the type of job to assign to this reservation, such as QUERY, CONTINUOUS (Preview), PIPELINE, BACKGROUND, or ML_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

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the navigation panel, go to the Capacity Management section.

3. Click the Reservations tab.

4. Find the reservation in the table of reservations.

5. Expand the Actions option.

6. Click Create assignment.

7. In the Create an assignment section, click Browse.

8. Browse or search for the project or folder and select it.

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

10. Click Create.

SQL

To assign a project to a reservation, use the CREATE ASSIGNMENT DDL statement.

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. 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 resource
   • LOCATION: the location of the reservation
   • RESERVATION_NAME: the name of the reservation

   • ASSIGNMENT_ID: the ID of the assignment

     The 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
   • JOB_TYPE: the type of job to assign to this reservation, such as QUERY, CONTINUOUS (Preview), PIPELINE, BACKGROUND, or ML_EXTERNAL

3. 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 resource
• LOCATION: the location of the reservation
• RESERVATION_NAME: the name of the reservation
• PROJECT_ID: the ID of the project to assign to this reservation
• JOB_TYPE: the type of job to assign to this reservation, such as QUERY, CONTINUOUS (Preview), PIPELINE, BACKGROUND, or ML_EXTERNAL

Terraform

Use the google_bigquery_reservation_assignment resource.

Note: To create BigQuery objects using Terraform, you must enable the Cloud Resource Manager API.

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:

resource "google_bigquery_reservation" "default" {
  name              = "my-reservation"
  location          = "us-central1"
  slot_capacity     = 100
  edition           = "ENTERPRISE"
  ignore_idle_slots = false # Use idle slots from other reservations
  concurrency       = 0     # Automatically adjust query concurrency based on available resources
  autoscale {
    max_slots = 200 # Allow the reservation to scale up to 300 slots (slot_capacity + max_slots) if needed
  }
}

data "google_project" "project" {}

resource "google_bigquery_reservation_assignment" "default" {
  assignee    = "projects/${data.google_project.project.project_id}"
  job_type    = "QUERY"
  reservation = google_bigquery_reservation.default.id
}

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell

1. Launch Cloud Shell.

2. 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).

1. In Cloud Shell, create a directory and a new file within that directory. The filename must have the .tf extension—for example main.tf. In this tutorial, the file is referred to as main.tf.

   mkdir DIRECTORY && cd DIRECTORY && touch main.tf

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

3. Review and modify the sample parameters to apply to your environment.
4. Save your changes.
5. 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

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

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

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

Note: Terraform samples typically assume that the required APIs are enabled in your Google Cloud project.

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.

Note: A project can be assigned to at most one reservation in a single region.

Assign a project to none

Assignments to none represent the absence of an assignment. Projects assigned to none use on-demand pricing.

Note: Assignments to none are supported for QUERY jobs only.

SQL

To assign a project to none, use the CREATE ASSIGNMENT DDL statement.

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. 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 pricing

   • ASSIGNMENT_ID: the ID of the assignment

     The 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

3. 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 pricing
• PROJECT_ID: the ID of the project to assign to none

Terraform

Use the google_bigquery_reservation_assignment resource.

Note: To create BigQuery objects using Terraform, you must enable the Cloud Resource Manager API.

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

data "google_project" "project" {}

resource "google_bigquery_reservation_assignment" "default" {
  assignee    = "projects/${data.google_project.project.project_id}"
  job_type    = "QUERY"
  reservation = "projects/${data.google_project.project.project_id}/locations/us/reservations/none"
}

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell

1. Launch Cloud Shell.

2. 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).

1. In Cloud Shell, create a directory and a new file within that directory. The filename must have the .tf extension—for example main.tf. In this tutorial, the file is referred to as main.tf.

   mkdir DIRECTORY && cd DIRECTORY && touch main.tf

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

3. Review and modify the sample parameters to apply to your environment.
4. Save your changes.
5. 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

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

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

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

Note: Terraform samples typically assume that the required APIs are enabled in your Google Cloud project.

Assign slots to BigQuery ML workloads

The following sections provide information on reservation assignment requirements for BigQuery ML models. You can create these reservation assignments by following the procedures in Assign an organization to a reservation or Assign a project or folder to a reservation.

External models

The following BigQuery ML model types use external services:

• Autoencoder
• AutoML
• Boosted tree
• Deep Neural Network (DNN)
• Random forest
• Wide-and-Deep Network

You can assign reserved slots to queries using these services by creating a reservation assignment that uses the ML_EXTERNAL job type. If no reservation assignment with an ML_EXTERNALjob type is found, the query job runs using on-demand pricing.

For external model training jobs, the slots in the reservation assignment are used for preprocessing, training, and postprocessing steps. During training, the slots aren't preemptible, but during preprocessing and postprocessing, idle slots can be used.

Matrix factorization models

To create a matrix factorization model you must create a reservation that uses the BigQuery Enterprise or Enterprise Plus edition, and then create a reservation assignment that uses the QUERY job type.

Other model types

For BigQuery ML models that aren't external models or matrix factorization models, you can assign reserved slots to queries using these services by creating a reservation assignment that uses the QUERY job type. If no reservation assignment with a QUERY job type is found, the query job runs using on-demand pricing.

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

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the navigation panel, go to the Capacity Management section.

3. Click the Reservations tab.

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

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. 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 view
   • ADMIN_PROJECT_ID: the project ID of the administration project that owns the reservation resource
   • PROJECT_ID: the ID of the project to assign to the reservation
   • JOB_TYPE: the type of job to assign to this reservation, such as QUERY, CONTINUOUS (Preview), PIPELINE, BACKGROUND, or ML_EXTERNAL

3. Click Run.

For more information about how to run queries, see Run an interactive query.

bq

Caution: This command does not work in Cloud Shell. To use this command, run it from a local command line.

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 resource
• LOCATION: the location of reservations to view
• JOB_TYPE: the type of job to assign to this reservation, such as QUERY, CONTINUOUS (Preview), PIPELINE, BACKGROUND, or ML_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 resource
• LOCATION: the location of the new reservation
• RESERVATION_NAME: the reservation to move the assignment from
• DESTINATION_RESERVATION: the reservation to move the assignment to

• ASSIGNMENT_ID: the ID of the assignment

  To 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

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the navigation panel, go to the Capacity Management section.

3. Click the Reservations tab.

4. In the table of reservations, expand the reservation to find the project.

5. Expand the Actions option.

6. Click Delete.

SQL

Use the DROP ASSIGNMENT DDL statement.

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. 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 resource
   • LOCATION: the location of the reservation
   • RESERVATION_NAME: the name of the reservation

   • ASSIGNMENT_ID: the ID of the assignment

     To find the assignment ID, see List a project's reservation assignment.

3. 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 resource
• LOCATION: the location of the reservation
• RESERVATION_NAME: the name of the reservation

• ASSIGNMENT_ID: the ID of the assignment

  To get the assignment ID, see Find a project's reservation assignment.