Primer on BigQuery Cost and Performance Optimizations
Overview
Below is a list of all queries that are contained in the GitHub repository for this series of articles along with a short description of them. Note that there will be a query for using the INFORMATION_SCHEMA views and then two for using the BigQuery audit log sinks. In the latter there will be a general query, labeled by “_general” in the filename, that will search all projects in the sink and then a query that searches on a per-project basis, which will not have any extra suffixes in the filename.
I will be updating these as new queries are added. With that said any requests for queries can be made in the comments and those will be added when time permits.
How to Read This List
Due to Medium not supporting tables natively, which would be the logical way to present this data, I have broken this up into a bulleted list as a comfortable compromise on readability versus elegance.
The first line will be the filename bolded and italicized, followed on the next line by a description of the query, and lastly the links in bullet points below that.
The Query List
- billing_recommendation_per_query.sql
Returns each query in the time period with a recommendation on if the query would run more efficiently under a flat-rate or on-demand billing plan. This is built around slot count and bytes scanned to determine a recommendation.
- INFORMATION_SCHEMA
- Audit Log
- Audit Log General - concurrent_queries_by_minute.sql
Builds a time-series over the interval on a per-minute basis and returns the number of concurrent queries that were run during that time frame.
- INFORMATION_SCHEMA
- Audit Log
- Audit Log General - concurrent_queries_by_second.sql
Builds a time-series over the interval on a per-second basis and returns the number of concurrent queries that were run during that time frame.
- INFORMATION_SCHEMA
- Audit Log
- Audit Log General - general_job_information.sql
Returns a subset of general information for each job run during the timeframe.
- INFORMATION_SCHEMA
- Audit Log
- Audit Log General - load_job_information.sql
Returns general information for each load job run during the timeframe.
- INFORMATION_SCHEMA
- Audit Log
- Audit Log General - load_slots_per_minute.sql
Builds a time-series over the interval on a per-minute basis and returns the number of slots used by load jobs that were run during that time frame.
- INFORMATION_SCHEMA
- Audit Log
- Audit Log General - load_slots_per_second.sql
Builds a time-series over the interval on a per-second basis and returns the number of slots used by load jobs that were run during that time frame.
- INFORMATION_SCHEMA
- Audit Log
- Audit Log General - longest_running_queries.sql
Returns all queries run over the interval and their pricing data sorted by the longest running queries to the shortest.
- INFORMATION_SCHEMA
- Audit Log
- Audit Log General - looker_job_information.sql
Returns general information for each job run during the timeframe that was associated with a Looker service account.
- INFORMATION_SCHEMA
- Audit Log
- Audit Log General - query_counts.sql
Returns every query run during the time frame along with the count of runs as well sorted by most runs to least runs.
- INFORMATION_SCHEMA
- Audit Log
- Audit Log General - query_job_information.sql
Returns general information for each query job run during the timeframe.
- INFORMATION_SCHEMA
- Audit Log
- Audit Log General - query_slots_per_minute.sql
Builds a time-series over the interval on a per-minute basis and returns the number of slots used by query jobs that were run during that time frame.
- INFORMATION_SCHEMA
- Audit Log
- Audit Log General - query_slots_per_second.sql
Builds a time-series over the interval on a per-second basis and returns the number of slots used by query jobs that were run during that time frame.
- INFORMATION_SCHEMA
- Audit Log
- Audit Log General - slots_by_day.sql
Builds a time-series over the interval on a per-day basis and returns the number of slots used by all jobs that were run during that time frame.
- INFORMATION_SCHEMA
- Audit Log
- Audit Log General - slots_by_hour.sql
Builds a time-series over the interval on a per-hour basis and returns the number of slots used by all jobs that were run during that time frame.
- INFORMATION_SCHEMA
- Audit Log
- Audit Log General - slots_by_minute.sql
Builds a time-series over the interval on a per-minutes basis and returns the number of slots used by all jobs that were run during that time frame.
- INFORMATION_SCHEMA
- Audit Log
- Audit Log General - slots_by_minute_and_user.sql
Builds a time-series over the interval on a per-minute basis and returns the number of slots used by all jobs that were run during that time frame and grouping them by the user running them.
- INFORMATION_SCHEMA
- Audit Log
- Audit Log General - slots_by_second.sql
Builds a time-series over the interval on a per-seconds basis and returns the number of slots used by all jobs that were run during that time frame.
- INFORMATION_SCHEMA
- Audit Log
- Audit Log General - table_query_counts.sql
Returns the number of jobs that hit a table during the specified time frame. Note that if a table exists and isn’t hit by any jobs it will not appear in this query.
- INFORMATION_SCHEMA
- Audit Log
- Audit Log General - top_billed_labels.sql
Returns the costs on a per-label basis that is applied to jobs over the specified time frame. This list is sorted from most expensive labels to least.
- INFORMATION_SCHEMA
- Audit Log
- Audit Log General - top_billed_queries.sql
Returns the query jobs run over the specified time frame sorted by their billed cost from most expensive to least.
Note that this may contain duplicates if a job is run multiple times with the same parameters.
- INFORMATION_SCHEMA
- Audit Log
- Audit Log General - top_billed_queries_deduplicated.sql
Returns the query jobs run over the specified time frame sorted by their billed cost from most expensive to least.
Note this job will deduplicate jobs and return only a single row per job. It will run significantly slower than the above query.
- INFORMATION_SCHEMA
- Audit Log
- Audit Log General - top_complex_queries.sql
Returns query jobs sorted by the most complex to least complex. Complexity is defined as how many slots were used to complete processing.
- INFORMATION_SCHEMA
- Audit Log
- Audit Log General - top_cost_users.sql
Returns the users that initiated jobs and how much they spent ordered by most cost to least cost. Note this will also contain service accounts.
- INFORMATION_SCHEMA
- Audit Log
- Audit Log General - top_costly_queries.sql
Returns the query jobs run over the specified time frame with their total cost accounting for multiple runs in the cost calculation. Results are sorted from the most expensive to least.
- INFORMATION_SCHEMA
- Audit Log
- Audit Log General
