Managing jobs

After you submit a BigQuery job, you can view job data, list jobs, cancel a job, or rerun a job.

When a job is submitted, it can be in one of three states:

  • PENDING: The job is scheduled and waiting to begin running.
  • RUNNING: The job is in progress.
  • DONE: The job is completed. In some cases, BigQuery reports this state as SUCCESS (if the job completed without errors) or FAILURE (if the job completed with errors).

Viewing job data

You can view job data and metadata by using the Cloud Console, the bq command-line tool, and the API. This data includes details such as the job type, the job state, and the user who ran the job.

Required permissions

At a minimum, in order to get job data and metadata, you must be granted bigquery.jobs.get permissions. The following predefined IAM role includes bigquery.jobs.get permissions:

  • bigquery.admin

If you grant an account the bigquery.admin role, the user can view all job data in the project no matter who submitted the job.

The following roles are granted bigquery.jobs.get permissions for self-created jobs. These users can only view job data for jobs they submit:

  • bigquery.user
  • bigquery.jobUser

For more information on IAM roles and permissions in BigQuery, see Access control.

Viewing information about jobs

To view information about a job:

Console

  1. In the navigation pane, click Job history, or click Query history to see information about query jobs.

  2. Click Personal history to view details about your jobs. Click Project history to view details about all jobs run in the project.

bq

Issue the bq show command with the -j flag and a job ID.

When you supply the job ID, you can use the fully qualified ID or the short form. For example, job IDs listed in the Cloud Console are fully qualified, that is, they include the project and location:

my-project-1234:US.bquijob_123x456_123y123z123c

Job IDs in the command-line tool are listed using the short form. Project ID and location are not included:

bquijob_123x456_123y123z123c

To specify the job location, supply the --location flag and set the value to your location. This flag is optional if you use the fully qualified job ID. If you include the --location flag and you're using the fully qualified job ID, the --location flag is ignored.

The following command requests information about a job:

bq --location=location show -j job_id

Replace the following:

  • location is optional. Location is the name of the location where the job runs. For example, if you are using BigQuery in the Tokyo region, set the flag's value to asia-northeast1. You can set a default value for the location using the .bigqueryrc file.
  • job_id is the ID of the job.

Examples:

The following command gets summary information about job US.bquijob_123x456_123y123z123c running in myproject.

bq show -j myproject:US.bquijob_123x456_123y123z123c

The output looks like the following:

 Job Type    State      Start Time      Duration      User Email       Bytes Processed   Bytes Billed   Billing Tier   Labels
 ---------- --------- ----------------- ---------- ------------------- ----------------- -------------- -------------- --------
 extract    SUCCESS   06 Jul 11:32:10   0:01:41    user@example.com

To see full job details, enter:

bq show --format=prettyjson -j myproject:US.bquijob_123x456_789y123z456c

The output looks like the following:

{
  "configuration": {
    "extract": {
      "compression": "NONE",
      "destinationUri": "[URI removed]",
      "destinationUris": [
        "[URI removed]"
      ],
      "sourceTable": {
        "datasetId": "github_repos",
        "projectId": "bigquery-public-data",
        "tableId": "commits"
      }
    }
  },
  "etag": "\"[etag removed]\"",
  "id": "myproject:bquijob_123x456_789y123z456c",
  "jobReference": {
    "jobId": "bquijob_123x456_789y123z456c",
    "projectId": "[Project ID removed]"
  },
  "kind": "bigquery#job",
  "selfLink": "https://bigquery.googleapis.com/bigquery/v2/projects/federated-testing/jobs/bquijob_123x456_789y123z456c",
  "statistics": {
    "creationTime": "1499365894527",
    "endTime": "1499365894702",
    "startTime": "1499365894702"
  },
  "status": {
    "errorResult": {
      "debugInfo": "[Information removed for readability]",
      "message": "Operation cannot be performed on a nested schema. Field: author",
      "reason": "invalid"
    },
    "errors": [
      {
        "message": "Operation cannot be performed on a nested schema. Field: author",
        "reason": "invalid"
      }
    ],
    "state": "DONE"
  },
  "user_email": "user@example.com"
}

API

Call jobs.get and provide the jobId and projectId parameters. (Optional) Supply the location parameter and set the value to the location where the job runs. This parameter is optional if you use the fully qualified job ID that includes the location, for example, my-project-1234:US.bquijob_123x456_123y123z123c.

Go

Before trying this sample, follow the Go setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Go API reference documentation. 

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
)

// getJobInfo demonstrates retrieval of a job, which can be used to monitor
// completion or print metadata about the job.
func getJobInfo(w io.Writer, projectID, jobID string) error {
	// projectID := "my-project-id"
	// jobID := "my-job-id"
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	job, err := client.JobFromID(ctx, jobID)
	if err != nil {
		return err
	}

	status := job.LastStatus()
	state := "Unknown"
	switch status.State {
	case bigquery.Pending:
		state = "Pending"
	case bigquery.Running:
		state = "Running"
	case bigquery.Done:
		state = "Done"
	}
	fmt.Fprintf(w, "Job %s was created %v and is in state %s\n",
		jobID, status.Statistics.CreationTime, state)
	return nil
}

Java

Before trying this sample, follow the Java setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Java API reference documentation. 

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobId;

// Sample to get a job
public class GetJob {

  public static void runGetJob() {
    // TODO(developer): Replace these variables before running the sample.
    String jobName = "MY_JOB_NAME";
    getJob(jobName);
  }

  public static void getJob(String jobName) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      JobId jobId = JobId.of(jobName);
      Job job = bigquery.getJob(jobId);
      System.out.println("Job retrieved successfully");
    } catch (BigQueryException e) {
      System.out.println("Job not retrieved. \n" + e.toString());
    }
  }
}

Node.js

Before trying this sample, follow the Node.js setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Node.js API reference documentation. 

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function getJob() {
  // Get job properties.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const jobId = "existing-job-id";

  // Create a job reference
  const job = bigquery.job(jobId);

  // Retrieve job
  const [jobResult] = await job.get();

  console.log(jobResult.metadata.jobReference);
}

Python

Before trying this sample, follow the Python setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Python API reference documentation. 

# TODO(developer): Uncomment the lines below and replace with your values.
# from google.cloud import bigquery
# client = bigquery.Client()
# job_id = 'bq-job-123x456-123y123z123c'  # replace with your job ID
# location = 'us'                         # replace with your location

job = client.get_job(job_id, location=location)  # API request

# Print selected job properties
print("Details for job {} running in {}:".format(job_id, location))
print(
    "\tType: {}\n\tState: {}\n\tCreated: {}".format(
        job.job_type, job.state, job.created
    )
)

Listing jobs in a project

Your project maintains your job history for all jobs created in the past six months.

You can view your BigQuery job history in the following ways:

  • Using the Google Cloud Console.
  • Using the bq command-line tool.
  • Calling the jobs.list API method.
  • Using the client libraries.

The job history includes jobs that are in the RUNNING state and jobs that are DONE (indicated by reporting the state as SUCCESS or FAILURE).

Required permissions

At a minimum, to list jobs, you must be granted bigquery.jobs.list permissions. The following predefined IAM roles include bigquery.jobs.list permissions:

  • bigquery.user
  • bigquery.admin

The following role is granted bigquery.jobs.list permissions only for self-created jobs. Entities granted this role can only list jobs they submit:

  • bigquery.jobUser

When you are granted bigquery.jobs.list permissions, you can list all jobs in a project, but the details and metadata are redacted for jobs submitted by other users. bigquery.jobs.list permissions lets you see full details for self-created jobs.

To list all jobs, including details for jobs created by other users, you must be granted bigquery.jobs.listAll permissions. Only the bigquery.admin and bigquery.resourceAdmin roles have bigquery.jobs.listAll permissions.

For more information about IAM roles and permissions in BigQuery, see Predefined roles and permissions.

Listing jobs

When you list jobs in a project, you do not need to provide a location. Currently, jobs are listed for all locations.

To list jobs in a project:

Console

  1. In the navigation pane, click Job History.

  2. In the Personal history section, your jobs are listed by creation time with the most recent jobs at the top. The list includes jobs only for the current user. To see all jobs for a project, click Project history. (If you aren't the project owner you may not have permission to view all jobs for a project.)

bq

Issue the bq ls command with one of the following flags:

  • -j is used to identify jobs as the resource to list.
  • --all or -a lists jobs from all users. To see full (unredacted) details for all jobs, you must have bigquery.jobs.listAll permissions.
  • --min_creation_time is used to list jobs after a supplied timestamp value.
  • --max_creation_time is used to list jobs before a supplied timestamp value.
  • -n limits the results. By default, you are limited to 100,000 results.
bq ls -j -a \
--min_creation_time integer1 \
--max_creation_time integer2 \
-n integer3 \
project_id

Replace the following:

  • integer1 is an integer that represents a timestamp.
  • integer2 is an integer that represents a timestamp.
  • integer3 is an integer that indicates the number of jobs returned.
  • project_id is the ID of the project that contains the jobs that you're listing. If you set a default project, you do not need to provide the project_id parameter.

Examples:

The following command lists all jobs for the current user. Running this command requires bigquery.jobs.list permissions.

bq ls -j myproject

The following command lists all jobs for all users. Running this command requires bigquery.jobs.listAll permissions.

bq ls -j -a myproject

The following command lists the 10 most recent jobs in myproject:

bq ls -j -a -n 10 myproject

The following command lists all jobs submitted before Oct 18, 2018, at 4:04:53 PM. This timestamp (in milliseconds) is equivalent to the following integer value: 1539903893000.

bq ls -j --max_creation_time 1539903893000

API

Call jobs.list and provide the projectId parameter. To list jobs for all users, set the allUsers parameter to true. Setting allUsers to true requires bigquery.jobs.listAll permissions.

Go

Before trying this sample, follow the Go setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Go API reference documentation. 

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
	"google.golang.org/api/iterator"
)

// listJobs demonstrates iterating through the BigQuery jobs collection.
func listJobs(w io.Writer, projectID string) error {
	// projectID := "my-project-id"
	// jobID := "my-job-id"
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	it := client.Jobs(ctx)
	// List up to 10 jobs to demonstrate iteration.
	for i := 0; i < 10; i++ {
		j, err := it.Next()
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		state := "Unknown"
		switch j.LastStatus().State {
		case bigquery.Pending:
			state = "Pending"
		case bigquery.Running:
			state = "Running"
		case bigquery.Done:
			state = "Done"
		}
		fmt.Fprintf(w, "Job %s in state %s\n", j.ID(), state)
	}
	return nil
}

Java

Before trying this sample, follow the Java setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Java API reference documentation. 

import com.google.api.gax.paging.Page;
import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Job;

// Sample to get list of jobs
public class ListJobs {

  public static void runListJobs() {
    listJobs();
  }

  public static void listJobs() {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      Page<Job> jobs = bigquery.listJobs(BigQuery.JobListOption.pageSize(10));
      if (jobs == null) {
        System.out.println("Dataset does not contain any jobs.");
        return;
      }
      jobs.getValues().forEach(job -> System.out.printf("Success! Job ID: %s", job.getJobId()));
    } catch (BigQueryException e) {
      System.out.println("Jobs not listed in dataset due to error: \n" + e.toString());
    }
  }
}

Node.js

Before trying this sample, follow the Node.js setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Node.js API reference documentation. 

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function listJobs() {
  // Lists all jobs in current GCP project.

  // List the 10 most recent jobs in reverse chronological order.
  //  Omit the max_results parameter to list jobs from the past 6 months.
  const options = {maxResults: 10};
  const [jobs] = await bigquery.getJobs(options);

  console.log('Jobs:');
  jobs.forEach(job => console.log(job.id));
}

Python

Before trying this sample, follow the Python setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Python API reference documentation. 


from google.cloud import bigquery

import datetime

# Construct a BigQuery client object.
client = bigquery.Client()

# List the 10 most recent jobs in reverse chronological order.
# Omit the max_results parameter to list jobs from the past 6 months.
print("Last 10 jobs:")
for job in client.list_jobs(max_results=10):  # API request(s)
    print("{}".format(job.job_id))

# The following are examples of additional optional parameters:

# Use min_creation_time and/or max_creation_time to specify a time window.
print("Jobs from the last ten minutes:")
ten_mins_ago = datetime.datetime.utcnow() - datetime.timedelta(minutes=10)
for job in client.list_jobs(min_creation_time=ten_mins_ago):
    print("{}".format(job.job_id))

# Use all_users to include jobs run by all users in the project.
print("Last 10 jobs run by all users:")
for job in client.list_jobs(max_results=10, all_users=True):
    print("{} run by user: {}".format(job.job_id, job.user_email))

# Use state_filter to filter by job state.
print("Last 10 jobs done:")
for job in client.list_jobs(max_results=10, state_filter="DONE"):
    print("{}".format(job.job_id))

Canceling jobs

You can cancel a RUNNING or PENDING job in the following ways:

  • Using the Cloud Console.
  • Using the bq command-line tool.
  • Using the BQ.JOBS.CANCEL system procedure in a SQL query.
  • By calling the jobs.cancel API method.
  • Using the client libraries.

Note that not all job types can be canceled. If the job cannot be canceled, an error is returned.

Even if the job can be canceled, success is not guaranteed. The job might have completed by the time the cancel request is submitted, or the job might be in a stage where it cannot be canceled.

Required permissions

At a minimum, to cancel a job, you must be granted bigquery.jobs.update permissions. The following predefined IAM role includes bigquery.jobs.update permissions:

  • bigquery.admin

If you grant an account the bigquery.admin role, the user can cancel any eligible job, no matter who submitted it.

The following roles can cancel self-created jobs. These users can only cancel jobs they submit:

  • bigquery.user
  • bigquery.jobUser

For more information about IAM roles and permissions in BigQuery, see Predefined roles and permissions.

Canceling a job

To cancel a job:

Console

  1. In the navigation pane, click Job history.

  2. In the Personal history section, click the job you're canceling. The most recent jobs appear at the top of the list.

  3. In the job details, click Cancel job.

SQL

Use the BQ.JOBS.CANCEL system procedure:

  CALL BQ.JOBS.CANCEL('job_id');

Replace job_id with the ID of the job you're canceling.

If you are in a different project but in the same region as the job you want to cancel, you must also include the project ID:

  CALL BQ.JOBS.CANCEL('project_id.job_id');

Replace the following:

  • project_id is the ID of the project that contains the job that you're canceling.
  • job_id is the ID of the job that you're canceling.

The procedure returns immediately, and BigQuery cancels the job shortly afterward. If the job has already succeeded or failed, the procedure has no effect.

bq

Issue the bq cancel command with the job_id parameter. You can request cancellation and return immediately by using the --nosync flag. By default, cancellation requests wait for completion.

When you supply the job ID, you can use the fully qualified ID or the short form. For example, job IDs listed in the Cloud Console are fully qualified, that is, they include the project and location:

my-project-1234:US.bquijob_123x456_123y123z123c

Job IDs in the bq command-line tool are listed using the short form. Project ID and location are not included:

bquijob_123x456_123y123z123c

To specify the job location, supply the --location flag and set the value to your location. This flag is optional if you use the fully qualified job ID. If you include the --location flag and you're using the fully qualified job ID, the --location flag is ignored.

The following command requests job cancellation and waits for completion. If the fully qualified job ID is supplied, the --location flag is ignored:

bq --location=location cancel job_id

The following command requests job cancellation and returns immediately. If the fully qualified job ID is supplied, the --location flag is ignored:

bq --location=location --nosync cancel job_id

Replace the following:

  • location is optional. Location is the name of the location where the job runs. For example, if you are using BigQuery in the Tokyo region, set the flag's value to asia-northeast1. You can set a default value for the location using the .bigqueryrc file.
  • job_id is the ID of the job that you're canceling. If you copy the job ID from the Cloud Console, the project ID and location are included in the job ID. For example, my-project-1234:US.bquijob_123x456_123y123z123c.

Examples:

The following command cancels job my-project-1234:US.bquijob_123x456_123y123z123c running in the US multi-region location in my-project-1234 and waits for completion. Because the fully qualified job ID is used, the location flag is not supplied.

bq cancel my-project-1234:US.bquijob_123x456_123y123z123c

The following command cancels job bquijob_123x456_123y123z123c running in the US multi-region location in my-project-1234 and waits for completion. Because the short form of the job ID is used, the --location flag is supplied.

bq --location=US cancel bquijob_123x456_123y123z123c

The following command cancels job bquijob_123x456_123y123z123c running in the US multi-region location in my-project-1234 and returns immediately. Because the fully qualified job ID is used, the --location flag is not supplied.

bq --nosync cancel my-project-1234:US.bquijob_123x456_123y123z123c

API

Call jobs.cancel and provide the jobId and projectId parameters. Supply the location parameter and set the value to the location where the job runs.

Go

Before trying this sample, follow the Go setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Go API reference documentation. 

import (
	"context"
	"fmt"

	"cloud.google.com/go/bigquery"
)

// cancelJob demonstrates how a job cancellation request can be issued for a specific
// BigQuery job.
func cancelJob(projectID, jobID string) error {
	// projectID := "my-project-id"
	// jobID := "my-job-id"
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	job, err := client.JobFromID(ctx, jobID)
	if err != nil {
		return nil
	}
	return job.Cancel(ctx)
}

Java

Before trying this sample, follow the Java setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Java API reference documentation. 

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobId;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.QueryJobConfiguration;
import java.util.UUID;

// Sample to cancel a job
public class CancelJob {

  public static void runCancelJob() {
    // TODO(developer): Replace these variables before running the sample.
    String query = "SELECT country_name from `bigquery-public-data.utility_us.country_code_iso`";
    cancelJob(query);
  }

  public static void cancelJob(String query) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      // Specify a job configuration to set optional job resource properties.
      QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(query).build();

      // The location and job name are optional,
      // if both are not specified then client will auto-create.
      String jobName = "jobId_" + UUID.randomUUID().toString();
      JobId jobId = JobId.newBuilder().setLocation("us").setJob(jobName).build();

      // Create a job with job ID
      bigquery.create(JobInfo.of(jobId, queryConfig));

      // Get a job that was just created
      Job job = bigquery.getJob(jobId);
      if (job.cancel()) {
        System.out.println("Job canceled successfully");
      } else {
        System.out.println("Job was not canceled");
      }
    } catch (BigQueryException e) {
      System.out.println("Job was not canceled.\n" + e.toString());
    }
  }
}

Node.js

Before trying this sample, follow the Node.js setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Node.js API reference documentation. 

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function cancelJob() {
  // Attempts to cancel a job.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const jobId = "existing-job-id";

  // Create a job reference
  const job = bigquery.job(jobId);

  // Attempt to cancel job
  const [apiResult] = await job.cancel();

  console.log(apiResult.job.status);
}

Python

Before trying this sample, follow the Python setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Python API reference documentation. 

# TODO(developer): Uncomment the lines below and replace with your values.
# from google.cloud import bigquery
# client = bigquery.Client()
# job_id = 'bq-job-123x456-123y123z123c'  # replace with your job ID
# location = 'us'                         # replace with your location

job = client.cancel_job(job_id, location=location)

Repeating a job

It is not possible to rerun a job using the same job ID. Instead, you create a new job with the same configuration. When you submit the new job in the Cloud Console or the bq command-line tool, a new job ID is assigned. When you submit the job using the API or client libraries, you must generate a new job ID.

Required permissions

At a minimum, to run a job, you must be granted bigquery.jobs.create permissions. The following predefined IAM roles include bigquery.jobs.create permissions:

  • bigquery.user
  • bigquery.jobUser
  • bigquery.admin

For more information about IAM roles and permissions in BigQuery, see Predefined roles and permissions.

Rerunning a job

To repeat a job:

Console

To repeat a query job:

  1. In the navigation pane, click Query history.

  2. In the Personal history or Project history section, click the query you want to rerun, and then click Open query in editor.

  3. Click Run.

To repeat a load job:

  1. In the navigation pane, click Job history.

  2. In the Personal history or Project history section, click the job you want to repeat. The most recent jobs appear at the top of the list.

  3. In the job details, click Repeat load job.

bq

Issue your command again and BigQuery automatically generates a job with a new job ID.

API

There is no single-call method to repeat a job; if you want to repeat a specific job:

  1. Call jobs.get to retrieve the resource for the job to repeat.

  2. Remove the id, status, and statistics field. Change the jobId field to a new value generated by your client code. Change any other fields as necessary.

  3. Call jobs.insert with the modified resource and the new job ID to start the new job.