Get bytes queried for entire BigQuery project history

user_5573294 0
Viewed 352

In our Project History tab, we have hundreds of queries from our daily analytics pipelines. I am working on a review of our BigQuery billing and analyzing the cost of queries here seems like the place to start.

enter image description here

However, there is no column for bytes processed or for cost. We can click the ... to show job details with billing, but this is not efficient or useful for assessing costs of hundreds of queries.

The accepted answer Is it possible to retrieve full query history and correlate its cost in google bigquery?, which is:

SELECT query, total_bytes_processed 
FROM 'region-us'.INFORMATION_SCHEMA.JOBS_BY_PROJECT 
WHERE 
project_id = 'you_project_id' AND user_email = 'my@eamil.com'

This is not so helpful. I've run this query for our project and for some reason it only shows a handful of queries for the last month, despite our pipelines running hundreds of queries daily. I've even removed the project_id and email filters to ensure that I was not filtering away results, and still only a handful of queries...

How can I get the cost / bytes queried for all queries in the Project History tab? Any why might the majority of our queries be missing from the JOB_BY_PROJECT query?

google-bigquery
1 Answers

TL;DR: It's not possible

According to the public document on INFORMATION_SCHEMA, it is limited to “currently running jobs, as well as the history of jobs completed in the past 180 days.”. You can run bq ls via BQ command line to check if you still get the same results with querying INFORMATION_SCHEMA.

There has been a recent issue where heavy queries are not showing up by querying, you can try querying again to see if the queries appear now. But if issue persists, you can try reading and exporting the audit log (if only you have set this up previously). Audit log for the job also contains information about billed bytes, and more detail and examples can be found from BQ audit logs public document.

Also if you need something permanent, I would recommend to set up a way to store historical data either from audit log or information schema to query on. You can check these related posts on setting up the audit log and querying from it and more examples on querying on it.

user_147336690
Related