ASSIGNMENTS view
The INFORMATION_SCHEMA.ASSIGNMENTS
view contains a near real-time list of all
current assignments within the administration project. Each row represents a
single, current assignment. A current assignment is either pending or active and
has not been deleted. For more information about reservation, see Introduction
to Reservations.
Required permission
To query the INFORMATION_SCHEMA.ASSIGNMENTS
view, you need the
bigquery.reservationAssignments.list
Identity and Access Management (IAM) permission for
the project.
Each of the following predefined IAM roles includes the required
permission:
roles/bigquery.resourceAdmin
roles/bigquery.resourceEditor
roles/bigquery.resourceViewer
roles/bigquery.user
roles/bigquery.admin
For more information about BigQuery permissions, see Access control with IAM.
Schema
The INFORMATION_SCHEMA.ASSIGNMENTS
view has the following
schema:
Column name | Data type | Value |
---|---|---|
ddl |
STRING |
The DDL statement used to create this assignment. |
project_id |
STRING |
ID of the administration project. |
project_number |
INTEGER |
Number of the administration project. |
assignment_id |
STRING |
ID that uniquely identifies the assignment. |
reservation_name |
STRING |
Name of the reservation that the assignment uses. |
job_type |
STRING |
The type of job that can use the reservation. Can be
PIPELINE , QUERY , CONTINUOUS ,
ML_EXTERNAL , or BACKGROUND . |
assignee_id |
STRING |
ID that uniquely identifies the assignee resource. |
assignee_number |
INTEGER |
Number that uniquely identifies the assignee resource. |
assignee_type |
STRING |
Type of assignee resource. Can be organization ,
folder or project . |
Scope and syntax
Queries against this view must include a region qualifier. If you do not specify a regional qualifier, metadata is retrieved from all regions. The following table explains the region scope for this view:
View name | Resource scope | Region scope |
---|---|---|
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.ASSIGNMENTS[_BY_PROJECT] |
Project level | REGION |
- Optional:
PROJECT_ID
: the ID of your Google Cloud project. If not specified, the default project is used.
REGION
: any dataset region name.
For example, region-us
.
Example
To run the query against a project other than your default project, add the project 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 assigned reservations.
- 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 its slot capacity. This information is useful for debugging job performance by comparing the project's slot usage with the slot capacity of the reservation assigned to that project.
SELECT reservation.reservation_name, reservation.slot_capacity FROM `RESERVATION_ADMIN_PROJECT.region-REGION_NAME`. INFORMATION_SCHEMA.ASSIGNMENTS_BY_PROJECT assignment INNER JOIN `RESERVATION_ADMIN_PROJECT.region-REGION_NAME`. INFORMATION_SCHEMA.RESERVATIONS_BY_PROJECT AS reservation ON (assignment.reservation_name = reservation.reservation_name) WHERE assignment.assignee_id = "PROJECT_ID" AND job_type = "QUERY";