BiqQuery — Extract All Query Executed In last ´N´ Days At Project Level.

Introduction

Some time we struggle while doing investigation over BigQuery cost analysis, during data auditing, any accidental query executions or mishandling of BigQuery best practices. We need a dashboard to investigate who has executed which query and what volume of data is been processed for each query executions.

Here is a solution which can help you to have a track of all queries which was executed in last ´N´days.

Query

SELECT
   job_id,
   start_time,
   user_email,
   total_bytes_processed,
   query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY) 
 AND CURRENT_TIMESTAMP()
 AND job_type = "QUERY"
 AND end_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY) AND CURRENT_TIMESTAMP()
ORDER BY total_bytes_processed DESC