ASSIGNMENT_CHANGES view
The INFORMATION_SCHEMA.ASSIGNMENT_CHANGES
view contains a near real-time list
of all changes to assignments within the administration project. Each row
represents a single change to a single assignment. For more information about
reservation, see Introduction to Reservations
.
Required permission
To query the INFORMATION_SCHEMA.ASSIGNMENT_CHANGES
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.ASSIGNMENT_CHANGES
view has the following
schema:
| Column name | Data type | Value |
|---|---|---|
change_timestamp
|
TIMESTAMP
|
Time when the change occurred. |
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
or QUERY
. |
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
. |
action
|
STRING
|
Type of event that occurred with the assignment. Can be CREATE
, UPDATE
, or DELETE
. |
user_email
|
STRING
|
Email address of the user or subject of the workforce identity
federation
that made the change. google
for changes
made by Google. NULL
if the email address is unknown. |
state
|
STRING
|
State of the assignment. Can be PENDING
or ACTIVE
. |
Data retention
This view contains current assignments and deleted assignments that are kept for a maximum of 41 days after which they are removed from the view.
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.ASSIGNMENT_CHANGES[_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`.
Examples
Find the latest changes to an assignment
The following example displays the user who has made the latest assignment update to a particular assignment within a specified date.
SELECT user_email , change_timestamp , reservation_name , assignment_id FROM `region-us` . INFORMATION_SCHEMA . ASSIGNMENT_CHANGES WHERE change_timestamp BETWEEN '2021-09-30' AND '2021-10-01' AND assignment_id = 'assignment_01' ORDER BY change_timestamp DESC LIMIT 1 ;
The result is similar to the following:
+--------------------------------+-----------------------+--------------------+-----------------+ | user_email | change_timestamp | reservation_name | assignment_id | +--------------------------------+-----------------------+--------------------+-----------------+ | cloudysanfrancisco@gmail.com |2021-09-30 09:30:00 UTC| my_reservation | assignment_01 | +--------------------------------+-----------------------+--------------------+-----------------+
Identify the assignment status of a reservation at a specific point in time
The following example displays all of the active assignments of a reservation at a certain point in time.
SELECT reservation_name , assignee_id , assignee_type , job_type FROM `region- REGION ` . INFORMATION_SCHEMA . ASSIGNMENT_CHANGES WHERE reservation_name = RESERVATION_NAME AND change_timestamp < TIMESTAMP QUALIFY ROW_NUMBER () OVER ( PARTITION BY assignee_id , job_type ORDER BY change_timestamp DESC ) = 1 AND action != 'DELETE' ;
Replace the following:
-
REGION: the region where your reservation is located -
RESERVATION_NAME: the name of the reservation that the assignment uses -
TIMESTAMP: the timestamp representing the specific point in time at which the list of assignments is checked
The result is similar to the following:
+-------------------------+---------------------------+---------------+----------+ | reservation_name | assignee_id | assignee_type | job_type | +-------------------------+---------------------------+---------------+----------+ | test-reservation | project_1 | PROJECT | QUERY | | test-reservation | project_2 | PROJECT | QUERY | +-------------------------+---------------------------+---------------+----------+
Identify the assignment status of a reservation when a particular job was executed
To display the assignments that were active when a certain job was executed, use the following example.
SELECT reservation_name , assignee_id , assignee_type , job_type FROM `region- REGION ` . INFORMATION_SCHEMA . ASSIGNMENT_CHANGES WHERE reservation_name = RESERVATION_NAME AND change_timestamp < ( SELECT creation_time FROM PROJECT_ID . `region- REGION ` . INFORMATION_SCHEMA . JOBS WHERE job_id = JOB_ID ) QUALIFY ROW_NUMBER () OVER ( PARTITION BY assignee_id , job_type ORDER BY change_timestamp DESC ) = 1 AND action != 'DELETE' ;
Replace the following:
-
REGION: the region where your reservation is located -
RESERVATION_NAME: the name of the reservation that the assignment uses -
PROJECT_ID: the ID of your Google Cloud project where the job was executed -
JOB_ID: the job ID against which the assignment status was checked
