BigQuery jobs

Every query, load, export and copy in BigQuery runs as a job with an ID, a state and a project that is billed. How jobs work, what the job ID tells you, how to list, cancel and repeat them, and why a failed query is still a finished job.

A job is the record BigQuery keeps for one piece of work. Every time you run a query, load data, export a table or copy a table, BigQuery creates a job, runs it asynchronously and keeps its details: type, state, bytes processed, who ran it, and the error if it failed. The job belongs to a project, and that project is billed.

The four job types

BigQuery has four job types: query, load, extract (export) and copy. In the job resource, the configuration names which type it is. Quick actions such as listing tables or reading a table’s metadata are not jobs.

A query job reads data and writes its result to a table. If you gave no destination table, the result goes to a temporary table that BigQuery keeps for about 24 hours, which is what makes cached results possible. A query that reads no tables, such as SELECT 1, is still a job.

A dry run is the one exception. It validates the query and reports bytes, and it doesn’t create a job. We checked this by dry-running a query with a fixed job ID and then asking for that job: “Not found”.

SELECT corpus, COUNT(*) AS lines
FROM `bigquery-public-data.samples.shakespeare`
GROUP BY corpus

Job states

A job is PENDING while it waits to be scheduled, RUNNING while it works, and DONE when it has stopped. DONE does not mean success. A failed job is also DONE, with an errorResult in its status. A job that has no errorResult succeeded, although a load job may still report nonfatal problems, such as a few rows it skipped, in status.errors.

Here is a query that fails:

SELECT 7 / 0 AS ratio

The query was valid, so BigQuery accepted it and created a job. Its status came back DONE with the error division by zero: 7 / 0, reason invalidQuery. Code that polls a job should read errorResult when the state is DONE rather than treat DONE as success.

The job ID

Every job has an ID and a location. The console shows the fully qualified form project:location.job_id, for example my-project-1234:US.bquijob_123x456_123y123z123c. The bq tool prints the short form, job_id alone, and uses --location or the default location to find it. A job ID is only unique together with its project and location. In Querylab.io, the Info view of a result shows the job id, location, bytes processed and billed, and slot time (see Results grid).

The project in the ID is the project that is billed. For a query job on on-demand pricing, the cost goes to the project that runs it, even if the data is in another project.

The client libraries generate a job ID for you, and you can supply your own, or a prefix. Job IDs are unique per project and location. We ran the same query twice with one fixed job ID. The first run worked. The second failed with Already Exists: Job <project>:US.<job_id>, without running the query again. A job ID you generate yourself can therefore act as an idempotency key: if a network error leaves you unsure whether a load was submitted, resubmitting with the same ID reports that the job already exists instead of loading twice.

Scripts make several jobs

A multi-statement query is one parent job with a child job for each statement. In our test, SELECT 1 AS a; SELECT 2 AS b; produced three rows in INFORMATION_SCHEMA.JOBS_BY_PROJECT: the parent with statement_type SCRIPT, and two children with parent_job_id set to the parent’s ID and statement_type SELECT. The result of the script is the result of the last statement that returns rows:

CREATE TEMP TABLE orders AS
SELECT 1 AS id, 40 AS total UNION ALL
SELECT 2, 15;

SELECT COUNT(*) AS order_count FROM orders;
SELECT SUM(total) AS revenue FROM orders;

The COUNT(*) result is not returned. Only the revenue is. Each child job has its own row, so a long script can be looked at statement by statement. You can delete the metadata of a parent job, which deletes its children too, but you can’t delete a child alone.

Listing jobs

BigQuery keeps six months of job history for all jobs in a project, across all locations. In the console it is under Job history in the Explorer, with a personal view and a project view. Querylab.io’s Query history tab lists the jobs your projects ran, from Querylab.io or any other tool, with bytes, cost and status, and searches by status, type, user and job id. In SQL, the INFORMATION_SCHEMA.JOBS views have one row per job, qualified by a region:

-- Not run: reads your project's job history
SELECT
  job_id,
  job_type,
  statement_type,
  state,
  total_bytes_billed
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
ORDER BY creation_time DESC
LIMIT 20;

bq ls -j lists jobs and bq show -j <job_id> shows one. Listing other users’ jobs needs more than the roles that run jobs. roles/bigquery.jobUser and roles/bigquery.user let you run jobs and see your own, and listing every job needs bigquery.jobs.listAll, which is in roles/bigquery.resourceAdmin and roles/bigquery.admin. Without it, details of other users’ jobs are redacted. For monitoring queries on cost and slots, see monitoring jobs and costs.

Cancelling a job

You can cancel a job that is PENDING or RUNNING. In Querylab.io the Run button turns into Cancel while a query runs. Elsewhere, cancel it from the console, with bq cancel, through the API, or in SQL:

-- Not run: cancels a job in the current project
CALL BQ.JOBS.CANCEL('job_id');

The procedure returns immediately and BigQuery does the cancellation after that. A cancel request can lose the race: the job may finish first, or sit in a stage that can’t be cancelled. Cancellation usually takes under a minute. Calling it with an ID that doesn’t exist fails with Not found: Job <project>:US.<job_id>.

Deleting and repeating

Job metadata can be deleted once the job is done, with bq rm -j <job_id>, for example to remove sensitive values that sit in a query’s text. A job can’t be repeated under the same job ID. Repeating one means submitting a new job with the same configuration: the console and bq assign a new ID, and with the API or a client library you generate one.

Jobs and locations

A query, load or extract job runs in the same location as the tables it references. This is the reason a job ID carries a location and why bq show -j needs --location for a short ID. See locations.

In Querylab.io, each query you run is a job in your project’s history, and the byte estimate you see before running is a dry run, which creates none.