ASSIGNMENTS view

TheINFORMATION_SCHEMA.ASSIGNMENTS view contains a near real-time list of allcurrent assignments within the administration project. Each row represents asingle, current assignment. A current assignment is either pending or active andhas not been deleted. For more information about reservation, seeIntroductionto Reservations.

Note: The view namesINFORMATION_SCHEMA.ASSIGNMENTS andINFORMATION_SCHEMA.ASSIGNMENTS_BY_PROJECT are synonymous and can beused interchangeably.

Required permission

To query theINFORMATION_SCHEMA.ASSIGNMENTS view, you need thebigquery.reservationAssignments.list Identity and Access Management (IAM) permission forthe project.Each of the following predefined IAM roles includes the requiredpermission:

  • roles/bigquery.resourceAdmin
  • roles/bigquery.resourceEditor
  • roles/bigquery.resourceViewer
  • roles/bigquery.user
  • roles/bigquery.admin

For more information about BigQuery permissions, seeAccess control with IAM.

Schema

TheINFORMATION_SCHEMA.ASSIGNMENTS view has the followingschema:

Column nameData typeValue
ddlSTRINGThe DDL statement used to create this assignment.
project_idSTRINGID of the administration project.
project_numberINTEGERNumber of the administration project.
assignment_idSTRINGID that uniquely identifies the assignment.
reservation_nameSTRINGName of the reservation that the assignment uses.
job_typeSTRINGThe type of job that can use the reservation. Can bePIPELINE,QUERY,CONTINUOUS,ML_EXTERNAL, orBACKGROUND.
assignee_idSTRINGID that uniquely identifies the assignee resource.
assignee_numberINTEGERNumber that uniquely identifies the assignee resource.
assignee_typeSTRINGType of assignee resource. Can beorganization,folder orproject.

For stability, we recommend that you explicitly list columns in your information schema queries instead ofusing a wildcard (SELECT *). Explicitly listing columns prevents queries frombreaking if the underlying schema changes.

Scope and syntax

Queries against this view must include aregion qualifier.If you don't specify a regional qualifier, metadata is retrieved from allregions. The following table explains the region scope for this view:

View nameResource scopeRegion scope
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.ASSIGNMENTS[_BY_PROJECT]
Project levelREGION
Replace the following:
  • Optional:PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.
  • REGION: anydataset region name. For example,`region-us`.

    Note: You must usea region qualifier to queryINFORMATION_SCHEMA views. The location of the query execution must match the region of theINFORMATION_SCHEMA view.

Example

To run the query against a project other than your default project, add theproject ID in the following format:

`PROJECT_ID`.`region-REGION_NAME`.INFORMATION_SCHEMA.ASSIGNMENTS
.

Replace the following:

  • PROJECT_ID: the ID of the project to which you have assignedreservations.
  • REGION_NAME: the name of the region.

For example,`myproject`.`region-us`.INFORMATION_SCHEMA.ASSIGNMENTS.

The following example gets a project's currently assigned reservation and itsslot capacity. This information is useful for debugging job performance bycomparing the project's slot usage with the slot capacity of the reservationassigned to that project.

SELECTreservation.reservation_name,reservation.slot_capacityFROM`RESERVATION_ADMIN_PROJECT.region-REGION_NAME`.INFORMATION_SCHEMA.ASSIGNMENTS_BY_PROJECTassignmentINNERJOIN`RESERVATION_ADMIN_PROJECT.region-REGION_NAME`.INFORMATION_SCHEMA.RESERVATIONS_BY_PROJECTASreservationON(assignment.reservation_name=reservation.reservation_name)WHEREassignment.assignee_id="PROJECT_ID"ANDjob_type="QUERY";

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.