Project Administration Queries

Overview

These two queries are about the project rather than the data in it. Both read BigQuery's own metadata rather than the UltraCart tables, so neither returns any merchant data.

Query Table Size in GB

Reports the on-disk size of every table in a dataset. __TABLES__ is BigQuery metadata rather than table data, so the query itself costs nothing to run.

select 
table_id, ROUND(sum(size_bytes) / (1024 * 1024 * 1024), 2) as size_in_gb
from
`my-data-warehouse.my_dataset.__TABLES__`
group by table_id

Finding Expensive Queries in a Project

Lists the 100 costliest queries run in a project over a date range, with gigabytes processed, an estimated cost at $5.00 per TB, and how many times each query ran. Set the project name and the date range before running it.

WITH job_rows as (
SELECT 
  query,
  jobs.total_bytes_processed/1024/1024/1024.0 AS gb_processed, 
  jobs.total_bytes_billed/1024/1024/1024/1024 * 5.000 AS job_cost_usd
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT jobs
  WHERE jobs.creation_time >= '2023-08-01' AND jobs.creation_time < '2023-09-01'
  AND job_type = 'QUERY'     # remove this condition for all job types
  AND project_id = 'my-project-name-here' # adjust this to your project name
  ORDER BY jobs.total_bytes_billed DESC
)
select query, sum(gb_processed) as gb_processed, ROUND(sum(job_cost_usd), 2) as job_cost, count(*) as execution_count From job_rows group by query order by sum(job_cost_usd) desc LIMIT 100