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`

Replace the following:

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`.

Note: You must use a region qualifier to query INFORMATION_SCHEMA views. The location of the query execution must match the region of the INFORMATION_SCHEMA view.

Example

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  |
+--------------------------------+-----------------------+--------------------+-----------------+