Find who created a table in BigQuery

user_2303708 0
Viewed 949

Is there a way to identify, somewhat easily, the user that created each table in BigQuery? I've tried checking the INFORMATION_SCHEMA.TABLES but it doesn't appear that the information is in there.

Update

Just to build on the answer by Daniel below, this will get you the most recently created version of each table as well as filtering only for tables that currently exist.

SELECT x.* EXCEPT(is_current) 
FROM (
    SELECT destination_table.dataset_id as table_schema,
           destination_table.table_id   as table_name,
           user_email,
           creation_time,
           CASE WHEN RANK() OVER(PARTITION BY destination_table.dataset_id, 
                                              destination_table.table_id 
                                 ORDER BY creation_time DESC) = 1 THEN 1 
                ELSE 0 
           END as is_current,
    FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
    WHERE statement_type IN ('CREATE_TABLE', 'CREATE_TABLE_AS_SELECT')
    ORDER BY creation_time DESC) x
INNER JOIN `region-us`.INFORMATION_SCHEMA.TABLES y ON x.table_schema = y.table_schema AND 
                                                      x.table_name = y.table_name
WHERE is_current = 1
ORDER BY 1,2;
google-bigquery
1 Answers

You should be able to run the following:

select * 
from `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE statement_type LIKE 'CREATE_TABLE%'
order by creation_time desc
;

The table includes the user_email that executed the statement. For more information on the JOBS_BY_* view here is the documentation: https://cloud.google.com/bigquery/docs/information-schema-jobs

UPDATE

Based on shollyman's feedback here are some additional considerations For tables create via a job_type of QUERY there are some additional statement_types you may want to look for, the list can be found here: https://cloud.google.com/bigquery/docs/reference/auditlogs/rest/Shared.Types/BigQueryAuditMetadata.QueryStatementType

Additionally there are other job types that are defined that can create a table. For example scenarios where the job_type is COPY or LOAD.

The view only contains data for the past 180 days so anything beyond that you would have to persist the audit logs to either cloud storage or big query.

user_144009980
Related