Stay organized with collections Save and categorize content based on your preferences.

Work with reservation assignments

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.

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.

Required permissions

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

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
    • PIPELINE
    • 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`
    AS JSON '{"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 project
    • 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, PIPELINE, or ML_EXTERNAL

  3. Click Run.

For more information about how to run queries, see Running interactive queries.

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 project
  • 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, PIPELINE, 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
    • PIPELINE
    • 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`
    AS JSON '{"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 project
    • 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, PIPELINE, or ML_EXTERNAL

  3. Click Run.

For more information about how to run queries, see Running interactive queries.

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 project
  • 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, PIPELINE, 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 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.

  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`
    AS JSON '{"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 project
    • 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, PIPELINE, or ML_EXTERNAL

  3. Click Run.

For more information about how to run queries, see Running interactive queries.

bq

To assign a project to none, use the bq mk command with the --reservation_assignment flag:

bq mk \
    --project_id=ADMIN_PROJECT_ID \
    --location=LOCATION \
    --reservation_assignment \
    --reservation_id=none \
    --job_type=QUERY \
    --assignee_id=PROJECT_ID \
    --assignee_type=PROJECT

Replace the following:

  • ADMIN_PROJECT_ID: the ID of the project that owns the assignment resource
  • LOCATION: the location of the project
  • PROJECT_ID: the ID of the project to assign to none

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.

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 resource
  • LOCATION: the location of the project
  • RESERVATION_NAME: the name of the reservation
  • PROJECT_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.search on the assigned resource.

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
        project_id = 'ADMIN_PROJECT_ID'
        AND assignee_id = 'PROJECT_ID'
        AND job_type = 'JOB_TYPE';
    

    Replace the following:

    • LOCATION: the location of the project
    • 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, PIPELINE, or ML_EXTERNAL

  3. Click Run.

For more information about how to run queries, see Running interactive queries.

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 resource
  • LOCATION: the location of the project
  • JOB_TYPE: the type of job to assign to this reservation, such as QUERY, PIPELINE, 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 \
    ASSIGNMENT_ID

Replace the following:

  • ADMIN_PROJECT_ID: the ID of the project that owns the reservation resource
  • LOCATION: the location of the project
  • 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.

Required permissions

To delete a reservation assignment, you need the following Identity and Access Management (IAM) permission:

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:

  3. Click Run.

For more information about how to run queries, see Running interactive queries.

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 ASSIGNMENT_ID

Replace the following: