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.

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