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 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. Click Create.

SQL

Use the CREATE ASSIGNMENT statement to assign an organization to a reservation.

  CREATE ASSIGNMENT `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME.ASSIGNMENT_ID`
   AS JSON """{
   "assignee": "organizations/ORGANIZATION_ID",
   "job_type": "JOB_TYPE"
   }"""

Where:

  • ADMIN_PROJECT_ID is the ID of the project that owns the reservation resource.
  • LOCATION is the location of the project.
  • RESERVATION_NAME is the name of the reservation.
  • ASSIGNMENT_ID is the ID of the assignment. 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.
  • JOB_TYPE is the type of job to assign to this reservation. One of: QUERY, PIPELINE, or ML_EXTERNAL.
  • ORGANIZATION_ID is the organization ID.

    For more information about the CREATE ASSIGNMENT statement, see CREATE ASSIGNMENT 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 --reservation_assignment flag to assign an organization's jobs to a reservation.

bq mk \
  --project_id=ADMIN_PROJECT_ID \
  --location=LOCATION \
  --reservation_assignment \
  --reservation_id=RESERVATION_NAME \
  --job_type=JOB_TYPE \
  --assignee_id=ORGANIZATION_ID \
  --assignee_type=ORGANIZATION

Where:

  • ADMIN_PROJECT_ID is the ID of the project that owns the reservation resource.
  • LOCATION is the location of the project.
  • RESERVATION_NAME is the name of the reservation.
  • JOB_TYPE is the type of job to assign to this reservation. One of: QUERY, PIPELINE, or ML_EXTERNAL.
  • ORGANIZATION_ID is the organization ID.

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 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. Click Create.

SQL

Use the CREATE ASSIGNMENT statement to assign an project to a reservation.

   CREATE ASSIGNMENT `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME.ASSIGNMENT_ID`
   AS JSON """{
   "assignee": "projects/PROJECT_ID",
   "job_type": "JOB_TYPE"
   }"""

Where:

  • ADMIN_PROJECT_ID is the ID of the project that owns the reservation resource.
  • LOCATION is the location of the project.
  • RESERVATION_NAME is the name of the reservation.
  • ASSIGNMENT_ID is the ID of the assignment. 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.
  • JOB_TYPE is the type of job to assign to this reservation. One of: QUERY, PIPELINE, or ML_EXTERNAL.
  • PROJECT_ID is the ID of the project to assign to this reservation.

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

bq

Use the bq mk command with the --reservation_assignment flag to assign jobs to a reservation.

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

Where:

  • ADMIN_PROJECT_ID is the ID of the project that owns the reservation resource.
  • LOCATION is the location of the project.
  • RESERVATION_NAME is the name of the reservation.
  • JOB_TYPE is the type of job to assign to this reservation. One of: QUERY, PIPELINE, or ML_EXTERNAL.
  • PROJECT_ID is the ID of the project to assign to this reservation.

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

None assignments represent the absence of an assignment. Projects assigned to None use on-demand pricing.

SQL

Use the CREATE ASSIGNMENT statement to assign a reservation to none.

  CREATE ASSIGNMENT `ADMIN_PROJECT_ID.region-LOCATION.none.ASSIGNMENT_ID`
   AS JSON """{
   "assignee": "projects/PROJECT_ID",
   "job_type": "JOB_TYPE"
   }"""

Where:

  • ADMIN_PROJECT_ID is the ID of the project that owns the reservation resource.
  • LOCATION is the location of the project.
  • ASSIGNMENT_ID is the ID of the assignment. 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.
  • JOB_TYPE is the type of job to assign to this reservation. One of: QUERY, PIPELINE, or ML_EXTERNAL.
  • PROJECT_ID is the ID of the project to assign to this reservation.

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

bq

Use the bq mk command with the --reservation_assignment flag to assign a project to None.

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

Where:

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

bq

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

Where:

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

Use the INFORMATION_SCHEMA.ASSIGNMENTS_BY_PROJECT view find which reservation your project's query jobs are assigned to.

  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'

Where:

  • ADMIN_PROJECT_ID is the ID of the project that owns the reservation resource.
  • LOCATION is the location of the project.
  • RESERVATION_NAME is the name of the reservation.
  • JOB_TYPE is the job type. One of: QUERY, PIPELINE, or ML_EXTERNAL.
  • PROJECT_ID is the ID of the project.

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

bq

Use the bq show command with the --reservation_assignment flag to find which reservation your project's query jobs are assigned to.

bq show \
  --project_id=ADMIN_PROJECT_ID \
  --location=LOCATION \
  --reservation_assignment \
  --job_type=JOB_TYPE \
  --assignee_id=PROJECT_ID \
  --assignee_type=PROJECT

Where:

  • ADMIN_PROJECT_ID is the ID of the project that owns the reservation resource.
  • LOCATION is the location of the project.
  • RESERVATION_NAME is the name of the reservation.
  • JOB_TYPE is the job type. One of: QUERY, PIPELINE, or ML_EXTERNAL.
  • PROJECT_ID is 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 administation 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.

bq

Use the bq update command to move the assignment.

bq update \
  --project_id=ADMIN_PROJECT_ID \
  --location=LOCATION \
  --reservation_assignment \
  --destination_reservation_id=DESTINATION_RESERVATION \
  ASSIGNMENT_ID

Where:

  • ADMIN_PROJECT_ID is the ID of the project that owns the reservation resource.
  • LOCATION is the location of the project.
  • DESTINATION_RESERVATION is the reservation to move the assignment to.
  • ASSIGNMENT_ID is 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 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 statement to delete an assignment.

  DROP ASSIGNMENT
    `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME.ASSIGNMENT_ID`

Where:

  • ADMIN_PROJECT_ID is the ID of the project that owns the reservation resource.
  • LOCATION is the location of the project.
  • RESERVATION_NAME is the name of the reservation.
  • ASSIGNMENT_ID is the ID of the assignment. To get the assignment ID, see List a project's reservation assignment.

For more information about the DROP ASSIGNMENT statement, see DROP ASSIGNMENT statement in the data control language (DCL) statement.

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

bq

Use the bq rm command with the --reservation_assignment flag to remove a project from a reservation.

bq rm \
--project_id=ADMIN_PROJECT_ID \
--location=LOCATION \
--reservation_assignment ASSIGNMENT_ID

Where: