Monitoring BigQuery jobs and costs with INFORMATION_SCHEMA.JOBS

Ready-to-run queries on INFORMATION_SCHEMA.JOBS for cost per user, the most expensive queries, repeated queries, cache hit rate, failures, the tables that cost the most, and slot usage over time, plus the traps that double-count script jobs.

Every query, load, export and copy is a job, and INFORMATION_SCHEMA.JOBS keeps 180 days of them per project and location: who ran it, the SQL, bytes processed and billed, slot time, whether it hit the cache, and any error. It is the place to find out where the BigQuery bill comes from. Querylab.io’s Query history and Usage statistics tabs show part of this without SQL: cost per job, cost by user, the ten most expensive queries and the cache-hit rate for a period (see Seeing usage over a period). Their SQL source runs a billed INFORMATION_SCHEMA query of the kind below. The queries below are ready to paste; each was checked against BigQuery. They read real job history, so they aren’t run on this page.

Change region-us to the location of your data. A region qualifier covers one location, and region-us doesn’t include us-central1. See INFORMATION_SCHEMA.

Two rules before you sum anything

• Exclude script parent jobs. A script is a parent job with statement_type = 'SCRIPT' whose totals include all its child jobs, which appear as rows of their own. Summing every row counts script costs twice. Filter statement_type != 'SCRIPT' (or parent_job_id IS NULL to count each script once by its parent).
• Filter on creation_time. The view is partitioned by it, so a filter limits what the query reads. Without one, a busy project’s job history is a large table to scan.

total_bytes_billed is what on-demand pricing charges for; on capacity pricing it is informational and the cost is in total_slot_ms.

Cost per user, last 30 days

SELECT
  user_email,
  COUNT(*) AS queries,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4), 3) AS tib_billed,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4) * 6.25, 2) AS approx_usd
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
GROUP BY user_email
ORDER BY tib_billed DESC;

$6.25 per TiB is the US on-demand price; the free 1 TiB a month isn’t subtracted. Service accounts show up as users too, which is how you find a pipeline or a BI tool that scans too much.

The most expensive queries, last 7 days

SELECT
  job_id,
  user_email,
  creation_time,
  ROUND(total_bytes_billed / POW(1024, 3), 1) AS gib_billed,
  ROUND(total_slot_ms / 1000 / 60, 1) AS slot_minutes,
  LEFT(query, 200) AS query_start
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
ORDER BY total_bytes_billed DESC
LIMIT 20;

The same query run again and again

A single expensive query is easy to spot. A dashboard tile that runs a moderately expensive query every few minutes is not. query_info.query_hashes.normalized_literals is a hash of the query text that ignores comments, parameter values and literals, so it groups every run of the same query shape:

SELECT
  query_info.query_hashes.normalized_literals AS query_hash,
  COUNT(*) AS runs,
  ROUND(SUM(total_bytes_billed) / POW(1024, 3), 1) AS gib_billed,
  ANY_VALUE(LEFT(query, 200)) AS example
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
GROUP BY query_hash
ORDER BY gib_billed DESC
LIMIT 20;

The fix for a repeated query is usually a materialized view, a smaller summary table refreshed on a schedule, or making the query cacheable (see below).

Cache hit rate

SELECT
  DATE(creation_time) AS day,
  COUNTIF(cache_hit) AS cached,
  COUNT(*) AS queries,
  ROUND(COUNTIF(cache_hit) / COUNT(*), 2) AS cache_hit_ratio
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY)
  AND job_type = 'QUERY'
GROUP BY day
ORDER BY day;

A low ratio on dashboards often means the queries use CURRENT_DATE() or change their text on each run. See Query cache.

Which tables cost the most to read

referenced_tables lists the tables each query read. Attributing the query’s bytes to each of its tables is approximate when a query joins several, but it shows which tables need partitioning or clustering:

SELECT
  t.project_id || '.' || t.dataset_id || '.' || t.table_id AS table_path,
  COUNT(*) AS queries,
  ROUND(SUM(j.total_bytes_billed) / POW(1024, 3), 1) AS gib_billed
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS j,
  UNNEST(j.referenced_tables) AS t
WHERE j.creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND j.job_type = 'QUERY'
GROUP BY table_path
ORDER BY gib_billed DESC
LIMIT 20;

Failures

SELECT
  error_result.reason,
  COUNT(*) AS failures,
  ANY_VALUE(error_result.message) AS example_message
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND error_result IS NOT NULL
GROUP BY reason
ORDER BY failures DESC;

The reasons and what to do about each are in Common errors.

Slot usage over time

JOBS has one row per job. JOBS_TIMELINE has one row per job per second, which shows how many slots were busy at each moment. Average slots per minute over the last day:

SELECT
  TIMESTAMP_TRUNC(period_start, MINUTE) AS minute,
  ROUND(SUM(period_slot_ms) / 1000 / 60, 1) AS avg_slots
FROM `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT
WHERE job_creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND period_start >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
GROUP BY minute
ORDER BY minute;

Slot-milliseconds in a minute divided by 60,000 is the average number of slots in use. This is the number to compare with a reservation size when deciding between on-demand and capacity pricing. See Slots and On-demand vs capacity pricing.

Views and permissions

Each query on these views is billed at least 10 MB and isn’t cached. In Querylab.io the byte estimate appears before you run a query, which catches most expensive queries before they show up in this history.