ASSIGNMENT_CHANGES view

TheINFORMATION_SCHEMA.ASSIGNMENT_CHANGES view contains a near real-time listof all changes to assignments within the administration project. Each rowrepresents a single change to a single assignment. For more information aboutreservation, seeIntroduction to Reservations.

For more information about BigQuery permissions, seeAccess control with IAM.

Schema

TheINFORMATION_SCHEMA.ASSIGNMENT_CHANGES view has the followingschema:

For stability, we recommend that you explicitly list columns in your information schema queries instead ofusing a wildcard (SELECT *). Explicitly listing columns prevents queries frombreaking if the underlying schema changes.

Data retention

This view contains current assignments and deleted assignments that arekept for a maximum of 41 days after which they are removed from the view.

Scope and syntax

Queries against this view must include aregion qualifier.If you don't specify a regional qualifier, metadata is retrieved from allregions. The following table explains the region scope for this view:

• Optional:PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.
• REGION: anydataset region name. For example,`region-us`.

  Note: You must usea region qualifier to queryINFORMATION_SCHEMA views. The location of the query execution must match the region of theINFORMATION_SCHEMA view.

Examples

Find the latest changes to an assignment

The following example displays the user who has made the latest assignmentupdate to a particular assignment within a specified date.

SELECTuser_email,change_timestamp,reservation_name,assignment_idFROM`region-us`.INFORMATION_SCHEMA.ASSIGNMENT_CHANGESWHEREchange_timestampBETWEEN'2021-09-30'AND'2021-10-01'ANDassignment_id='assignment_01'ORDERBYchange_timestampDESCLIMIT1;

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 ata certain point in time.

SELECTreservation_name,assignee_id,assignee_type,job_typeFROM`region-REGION`.INFORMATION_SCHEMA.ASSIGNMENT_CHANGESWHEREreservation_name=RESERVATION_NAMEANDchange_timestamp<TIMESTAMPQUALIFYROW_NUMBER()OVER(PARTITIONBYassignee_id,job_typeORDERBYchange_timestampDESC)=1ANDaction!='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.

SELECTreservation_name,assignee_id,assignee_type,job_typeFROM`region-REGION`.INFORMATION_SCHEMA.ASSIGNMENT_CHANGESWHEREreservation_name=RESERVATION_NAMEANDchange_timestamp<(SELECTcreation_timeFROMPROJECT_ID.`region-REGION`.INFORMATION_SCHEMA.JOBSWHEREjob_id=JOB_ID)QUALIFYROW_NUMBER()OVER(PARTITIONBYassignee_id,job_typeORDERBYchange_timestampDESC)=1ANDaction!='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