Running Interactive and Batch Query Jobs

This document describes how to run interactive (on-demand) and batch query jobs.

Required permissions

Jobs are actions that BigQuery executes on your behalf to load data, export data, query data, or copy data.

When you use the BigQuery web UI or CLI to load, export, query, or copy data, a job resource is automatically created, scheduled, and run. You can also programmatically create a load, export, query, or copy job. When you create a job programmatically, BigQuery schedules and runs the job for you.

Because jobs can potentially take a long time to complete, they execute asynchronously and can be polled for their status. Shorter actions, such as listing resources or getting metadata are not managed by a job resource.

Running a query job requires bigquery.jobs.create permissions. In order for the query job to complete successfully, the user or group must also have access to the dataset containing the tables referenced by the query.

You can set bigquery.jobs.create permissions at the project level by granting any of the following predefined IAM roles:

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

If you grant a user or group the bigquery.user role at the project level, by default, no access is granted to any of the datasets, tables, or views in the project. bigquery.user gives users the ability to create their own datasets and to run query jobs against datasets they have been given access to. If you assign the bigquery.user or bigquery.jobUser role, you must also assign access controls to each dataset the user or group needs to access that wasn't created by the user.

When you assign access to a dataset, there are 3 options:

The minimum access required for a user to run a query is "Can view".

For more information on IAM roles in BigQuery, see Access Control.

Running interactive queries

By default, BigQuery runs interactive (on-demand) query jobs, which means that the query is executed as soon as possible. Interactive queries count towards your concurrent rate limit and your daily limit.

Query results are always saved to either a temporary or permanent table. You can choose whether to append or overwrite data in an existing table or whether to create a new table if none exists with the same name.

To run an interactive query that writes to a temporary table:

Web UI

  1. Go to the BigQuery web UI.
    Go to the BigQuery web UI

  2. Click Compose query.

  3. Enter a valid BigQuery SQL query in the New Query text area.

  4. Click Show Options.

  5. (Optional) For Processing Location, click Unspecified and choose your data's location.

  6. Click Run query.

This creates a query job that writes the output to a temporary table.

Command-line

Enter the bq query command and include your query text. Supply the --location flag and set the value to your location.

You can specify the following optional flags. This list includes some of the most common flags. For a complete list of query command flags, see bq query in the bq command-line tool reference.

Specify the:

  • --destination_table flag to create a permanent table based on the query results. To write the query results to a table that is not in your default project, add the project ID to the dataset name in the following format: [PROJECT_ID]:[DATASET]. If --destination_table is unspecified, a query job is generated that writes the output to a temporary (cache) table.
  • --append_table flag to append the query results to a destination table.
  • --destination_kms_key flag to use a Cloud KMS key to encrypt the destination table data.
  • --use_legacy_sql=false flag to use standard SQL syntax. You can set a default syntax for the command-line tool using the .bigqueryrc file.
  • --label flag to apply a label to the query job in the form [KEY]:[VALUE]. Repeat this flag to specify multiple labels.
  • --max_rows or -n flag to specify the number of rows to return in the query results.
  • --maximum_bytes_billed flag to limit the bytes billed for the query. If the query goes beyond the limit, it fails (without incurring a charge). If not specified, the bytes billed is set to the project default.
  • --udf_resource flag to load and evaluate a code file to be used as a user-defined function resource. You can specify a Cloud Storage URI or the path to a local code file. Repeat this flag to specify multiple files.

Enter the following command to run an interactive query using standard SQL syntax:

bq --location=[LOCATION] query --use_legacy_sql=false '[QUERY]'

Where:

  • [LOCATION] is the name of the location where the query is processed. The --location flag is optional. For example, if you are using BigQuery in the Tokyo region, you can set the flag's value to asia-northeast1. You can set a default value for the location using the .bigqueryrc file.
  • [QUERY] is a query in standard SQL syntax.

Examples:

Enter the following command to write interactive query results to a destination table named mytable in mydataset. The dataset is in your default project. The query retrieves data from a the USA Name Data public dataset.

bq --location=US query --destination_table mydataset.mytable --use_legacy_sql=false 'SELECT name,number FROM `bigquery-public-data.usa_names.usa_1910_current` WHERE gender = "M" ORDER BY number DESC'

Enter the following command to write interactive query results to a destination table named mytable in mydataset. The dataset is in myotherproject, not your default project. The query retrieves data from a non-partitioned table — the USA Name Data public dataset.

bq --location=US query --destination_table myotherproject:mydataset.mytable --use_legacy_sql=false 'SELECT name,number FROM `bigquery-public-data.usa_names.usa_1910_current` WHERE gender = "M" ORDER BY number DESC'

For more information, see Using the bq command-line tool.

API

To run a query using the API, insert a new job and populate the jobs#configuration.query property. Specify your location in the location property in the jobReference section of the job resource.

Poll for results by calling getQueryResults. Poll until jobComplete equals true. Check for errors and warnings in the errors list.

C#

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

using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryQuery
{
    public void Query(
        string projectId = "your-project-id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        string query = @"
            SELECT name FROM `bigquery-public-data.usa_names.usa_1910_2013`
            WHERE state = 'TX'
            LIMIT 100";
        BigQueryJob job = client.CreateQueryJob(
            sql: query,
            parameters: null,
            options: new QueryOptions { UseQueryCache = false });
        // Wait for the job to complete.
        job.PollUntilCompleted();
        // Display the results
        foreach (BigQueryRow row in client.GetQueryResults(job.Reference))
        {
            Console.WriteLine($"{row["name"]}");
        }
    }
}

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 .

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")

q := client.Query(
	"SELECT name FROM `bigquery-public-data.usa_names.usa_1910_2013` " +
		"WHERE state = \"TX\" " +
		"LIMIT 100")
// Location must match that of the dataset(s) referenced in the query.
q.Location = "US"
job, err := q.Run(ctx)
if err != nil {
	return err
}
status, err := job.Wait(ctx)
if err != nil {
	return err
}
if err := status.Err(); err != nil {
	return err
}
it, err := job.Read(ctx)
for {
	var row []bigquery.Value
	err := it.Next(&row)
	if err == iterator.Done {
		break
	}
	if err != nil {
		return err
	}
	fmt.Println(row)
}

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 .

// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
String query = "SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";
QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(query).build();

// Print the results.
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
  for (FieldValue val : row) {
    System.out.printf("%s,", val.toString());
  }
  System.out.printf("\n");
}

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 .

// Imports the Google Cloud client library
const BigQuery = require('@google-cloud/bigquery');

// Creates a client
const bigquery = new BigQuery();

const query = `SELECT name
  FROM \`bigquery-public-data.usa_names.usa_1910_2013\`
  WHERE state = 'TX'
  LIMIT 100`;
const options = {
  query: query,
  // Location must match that of the dataset(s) referenced in the query.
  location: 'US',
};

// Runs the query as a job
const [job] = await bigquery.createQueryJob(options);
console.log(`Job ${job.id} started.`);

// Waits for the query to finish
const [rows] = await job.getQueryResults();

// Prints the results
console.log('Rows:');
rows.forEach(row => console.log(row));

PHP

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

use Google\Cloud\BigQuery\BigQueryClient;
use Google\Cloud\Core\ExponentialBackoff;

/** Uncomment and populate these variables in your code */
// $projectId = 'The Google project ID';
// $query = 'SELECT id, view_count FROM `bigquery-public-data.stackoverflow.posts_questions`';

$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$jobConfig = $bigQuery->query($query);
$job = $bigQuery->startQuery($jobConfig);

$backoff = new ExponentialBackoff(10);
$backoff->execute(function () use ($job) {
    print('Waiting for job to complete' . PHP_EOL);
    $job->reload();
    if (!$job->isComplete()) {
        throw new Exception('Job has not yet completed', 500);
    }
});
$queryResults = $job->queryResults();

$i = 0;
foreach ($queryResults as $row) {
    printf('--- Row %s ---' . PHP_EOL, ++$i);
    foreach ($row as $column => $value) {
        printf('%s: %s' . PHP_EOL, $column, json_encode($value));
    }
}
printf('Found %s row(s)' . PHP_EOL, $i);

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
# client = bigquery.Client()

query = (
    'SELECT name FROM `bigquery-public-data.usa_names.usa_1910_2013` '
    'WHERE state = "TX" '
    'LIMIT 100')
query_job = client.query(
    query,
    # Location must match that of the dataset(s) referenced in the query.
    location='US')  # API request - starts the query

for row in query_job:  # API request - fetches results
    # Row values can be accessed by field name or index
    assert row[0] == row.name == row['name']
    print(row)

Ruby

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

require "google/cloud/bigquery"

def query
  bigquery = Google::Cloud::Bigquery.new
  sql = "SELECT name FROM `bigquery-public-data.usa_names.usa_1910_2013` " +
        "WHERE state = 'TX' " +
        "LIMIT 100"

  # Location must match that of the dataset(s) referenced in the query.
  results = bigquery.query sql do |config|
    config.location = "US"
  end

  results.each do |row|
    puts row.inspect
  end
end

Running batch queries

BigQuery also offers batch queries. BigQuery queues each batch query on your behalf, and starts the query as soon as idle resources are available, usually within a few minutes. If BigQuery hasn't started the query within 24 hours, BigQuery changes the job priority to interactive.

Batch queries don't count towards your concurrent rate limit, which can make it easier to start many queries at once. Batch queries use the same resources as interactive (on-demand) queries. If you use flat-rate pricing batch queries and interactive queries share your allocated slots.

To run a batch query:

Web UI

  1. Go to the BigQuery web UI.
    Go to the BigQuery web UI

  2. Click the Compose query button.

  3. Enter a valid BigQuery SQL query in the New Query text area.

  4. Click the Show Options button.

  5. Select the Batch option in the Query Priority section.

  6. (Optional) For Processing Location, click Unspecified and choose your data's location.

  7. Click the Run query button.

Command-line

Enter the bq query command and include your query text. Specify the --batch flag to run a batch query. Supply the --location flag and set the value to your location.

You can specify the following optional flags. This list includes some of the most common flags. For a complete list of query command flags, see bq query in the bq command-line tool reference.

Specify the:

  • --destination_table flag to create a permanent table based on the query results. To write the query results to a table that is not in your default project, add the project ID to the dataset name in the following format: [PROJECT_ID]:[DATASET]. If --destination_table is unspecified, a query job is generated that writes the output to a temporary (cache) table.
  • --append_table flag to append the query results to a destination table.
  • --destination_kms_key flag to use a Cloud KMS key to encrypt the destination table data.
  • --use_legacy_sql=false flag to use standard SQL syntax. You can set a default syntax for the command-line tool using the .bigqueryrc file.
  • --label flag to apply a label to the query job in the form [KEY]:[VALUE]. Repeat this flag to specify multiple labels.
  • --max_rows or -n flag to specify the number of rows to return in the query results.
  • --maximum_bytes_billed flag to limit the bytes billed for the query. If the query goes beyond the limit, it fails (without incurring a charge). If not specified, the bytes billed is set to the project default.
  • --udf_resource flag to load and evaluate a code file to be used as a user-defined function resource. You can specify a Cloud Storage URI or the path to a local code file. Repeat this flag to specify multiple files.

Enter the following command to run a batch query using standard SQL syntax:

bq --location=[LOCATION] query --batch --use_legacy_sql=false '[QUERY]'

Where:

  • [LOCATION] is the name of the location where the query is processed. The --location flag is optional. For example, if you are using BigQuery in the Tokyo region, you can set the flag's value to asia-northeast1. You can set a default value for the location using the .bigqueryrc file.
  • [QUERY] is a query in standard SQL syntax.

Examples:

Enter the following command to write batch query results to a destination table named mytable in mydataset. The dataset is in your default project. The query retrieves data from a the USA Name Data public dataset.

bq --location=US query --batch --destination_table mydataset.mytable --use_legacy_sql=false 'SELECT name,number FROM `bigquery-public-data.usa_names.usa_1910_current` WHERE gender = "M" ORDER BY number DESC'

Enter the following command to write batch query results to a destination table named mytable in mydataset. The dataset is in myotherproject, not your default project. The query retrieves data from a non-partitioned table — the USA Name Data public dataset.

bq --location=US query --batch --destination_table myotherproject:mydataset.mytable --use_legacy_sql=false 'SELECT name,number FROM `bigquery-public-data.usa_names.usa_1910_current` WHERE gender = "M" ORDER BY number DESC'

For more information, see Using the bq command-line tool.

API

To run a query using the API, insert a new job and populate the jobs#configuration.query property. Specify your location in the location property in the jobReference section of the job resource.

When you populate the query job properties, include the configuration.query.priority property, with the value set to BATCH.

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 .

	// To run this sample, you will need to create (or reuse) a context and
	// an instance of the bigquery client.  For example:
	// import "cloud.google.com/go/bigquery"
	// ctx := context.Background()
	// client, err := bigquery.NewClient(ctx, "your-project-id")
	// Build an aggregate table.
	q := client.Query(`
		SELECT
  			corpus,
  			SUM(word_count) as total_words,
  			COUNT(1) as unique_words
		FROM ` + "`bigquery-public-data.samples.shakespeare`" + `
		GROUP BY corpus;`)
	q.Priority = bigquery.BatchPriority
	q.QueryConfig.Dst = client.Dataset(dstDatasetID).Table(dstTableID)

	// Start the job.
	job, err := q.Run(ctx)
	if err != nil {
		return err
	}
	// Job is started and will progress without interaction.
	// To simulate other work being done, sleep a few seconds.
	time.Sleep(5 * time.Second)
	status, err := job.Status(ctx)
	if err != nil {
		return err
	}

	state := "Unknown"
	switch status.State {
	case bigquery.Pending:
		state = "Pending"
	case bigquery.Running:
		state = "Running"
	case bigquery.Done:
		state = "Done"
	}
	// You can continue to monitor job progress until it reaches
	// the Done state by polling periodically.  In this example,
	// we print the latest status.
	fmt.Printf("Job %s in Location %s currently in state: %s\n", job.ID(), job.Location(), state)

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 .

To run a batch query, set the query priority to QueryJobConfiguration.Priority.BATCH when creating a QueryJobConfiguration.

// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
String query = "SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";
QueryJobConfiguration queryConfig =
    QueryJobConfiguration.newBuilder(query)
        // Run at batch priority, which won't count toward concurrent rate
        // limit.
        .setPriority(QueryJobConfiguration.Priority.BATCH)
        .build();

// Location must match that of the dataset(s) referenced in the query.
JobId jobId = JobId.newBuilder().setRandomJob().setLocation("US").build();
String jobIdString = jobId.getJob();

// API request - starts the query.
bigquery.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());

// Check on the progress by getting the job's updated state. Once the state
// is `DONE`, the results are ready.
Job queryJob = bigquery.getJob(
    JobId.newBuilder().setJob(jobIdString).setLocation("US").build());
System.out.printf(
    "Job %s in location %s currently in state: %s%n",
    queryJob.getJobId().getJob(),
    queryJob.getJobId().getLocation(),
    queryJob.getStatus().getState().toString());

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
# client = bigquery.Client()

job_config = bigquery.QueryJobConfig()
# Run at batch priority, which won't count toward concurrent rate limit.
job_config.priority = bigquery.QueryPriority.BATCH
sql = """
    SELECT corpus
    FROM `bigquery-public-data.samples.shakespeare`
    GROUP BY corpus;
"""
# Location must match that of the dataset(s) referenced in the query.
location = 'US'

# API request - starts the query
query_job = client.query(sql, location=location, job_config=job_config)

# Check on the progress by getting the job's updated state. Once the state
# is `DONE`, the results are ready.
query_job = client.get_job(
    query_job.job_id, location=location)  # API request - fetches job
print('Job {} is currently in state {}'.format(
    query_job.job_id, query_job.state))

Was this page helpful? Let us know how we did:

Send feedback about...

Need help? Visit our support page.