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

--

--

Vibhor Gupta

Vibhor Gupta

11 Followers

Hi, I am a Certified Google Cloud Data engineer. I use Medium platform to share my experience with other members of Medium network.