ASSIGNMENT_CHANGES view

The INFORMATION_SCHEMA.ASSIGNMENT_CHANGES view contains a 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.

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