Read your own job history
BigQuery keeps a record of every query you run: the bytes it processed, the bytes it was billed for and the tables it read. You can read those records with SQL, so you see what a query cost instead of guessing.
How it works
A job is one piece of work BigQuery does for you. Each query is a job. A script is a job too, and each statement of it that runs a query is a child job. The JOBS_BY_USER view has one row for each job you ran. A child job carries the id of its script in parent_job_id, and inside a script @@script.job_id is that same id. A statement can therefore list the child jobs that finished before it. The statement that reads the view is not one of the child jobs it lists. BigQuery can take a moment to list a job that finished a second ago, so on a real run the newest child can be missing from the list.
- Read the view as
region-us.INFORMATION_SCHEMA.JOBS_BY_USER. The region is part of the name.region-usis the US multi-region, and a location such asus-central1has a qualifier of its own. - The columns used here are
parent_job_id,creation_time,total_bytes_processed,total_bytes_billedandreferenced_tables.referenced_tablesholds one entry for each table the job read.ARRAY_LENGTH(referenced_tables)counts them, andARRAY_TO_STRING(ARRAY(SELECT t.table_id FROM UNNEST(referenced_tables) AS t ORDER BY t.table_id), ', ')writes their names in one cell. - The view has no order of its own, so add
ORDER BY.creation_timegives the order the jobs ran in. total_bytes_processedis what the query read.total_bytes_billedis what BigQuery charges for. On-demand billing rounds each table a query reads up to 10 MiB, which is 10,485,760 bytes, so the join oforder_itemsandproductsis billed 20 MiB. A query that only counts the rows of a table withCOUNT(*)processes 0 bytes and is billed 0. These bills are for a run that BigQuery does not answer from its cache.- Grouping
ordersbycustomer_idprocesses 1,600 bytes, and a first run is billed 10 MiB. The estimate you see before you run shows the 1,600. The bill of that first run is the 10 MiB. - The script has a row of its own, with
statement_typeSCRIPT. While the script is running, that row hasNULLbytes and the stateRUNNING. Once it has finished, its bytes are the sum of its children, so a total overSELECTrows andSCRIPTrows counts the same bytes twice. - Outside a script,
@@script.job_idisNULL. To read your own earlier queries there, filter on time, for exampleWHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY). In a session with no earlier queries this returns no rows. BigQuery keeps about 180 days of jobs.
Common mistake
Without WHERE parent_job_id = @@script.job_id the last statement lists every job you have run, and the script's own row comes with them. Filtering on job_id = @@script.job_id goes wrong the other way: it finds only the script's row, with NULL bytes, and none of its queries.
Worked example
SELECT COUNT(*) AS customers FROM learn.shop.customers;
SELECT state, COUNT(*) AS customers FROM learn.shop.customers GROUP BY state;
SELECT statement_type, total_bytes_processed, total_bytes_billed
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
WHERE parent_job_id = @@script.job_id
ORDER BY creation_time;The two queries above the last statement are its child jobs, listed in the order they ran. The last statement is not one of them.
The count processes 0 bytes and is billed 0. The grouped read processes 80 bytes, the state column, and is billed the 10 MiB minimum.