identify the identify the user updated the table

[sharontmathew]

I have a dataset and couple to tabes in BigQuery, One of the table has been updated by the random user.
what is the SQL command to find the user and the query ran to update the table?

[joelvilca · Accepted solution]

Hi @sharontmathew , 

I hope I can help you with this information. To access the change log in Bigquery you have to access the JOBS View in this way:

SELECT
  user_email,
  job_id,
  creation_time,
  end_time,
  query
FROM
  `your_project_id.region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
WHERE
  job_type = 'QUERY'
  AND state = 'DONE'
  AND query LIKE '%UPDATE your_table_name%'
ORDER BY
  creation_time DESC

This query filters the Jobs in INFORMATION_SCHEMA.JOBS_BY_PROJECT  to find queries that updated the table you mention (UPDATE your_table_name).

Here the reference documentation :

https://cloud.google.com/bigquery/docs/information-schema-jobs?hl=es-419#required_role