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

--

--

--

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

Love podcasts or audiobooks? Learn on the go with our new app.

How did Airtel develop a management system that deals with Fraud smartly and addresses…

Do You Use Data To Understand Your User Or To Control Them?

Grafana with PostgreSQL — Data Visualization with open-source tool

The Future of Data Science, Data Engineering, and Tech

What are Bollinger Bands and Stochastic? Working together defined.

Summary from Gartner Data & Analytics Summit London 2019

Analyzing Donald Trump and Joe Biden Tweets using Natural Language Processing

What Fintech Innovation Lab Gave Us

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Vibhor Gupta

Vibhor Gupta

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

More from Medium

A Data Engineer an Agile Coach and a Fish walk into a bar…

Classify & Protect Sensitive Data based on Role Based Access Control in BigQuery

Optimize costs in BigQuery — 9 solutions

How Is Gen-3 BI In Cloud Platform Transforming Businesses?