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 runs on your behalf to load data, export data, query data, or copy data.

When you use the console, the classic BigQuery web UI, or the 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

Note: Most users such as data analysts and data scientists should be granted bigquery.user or bigquery.jobUser. bigquery.admin has full permissions across all datasets in a project.

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:

• "Can view" maps to the bigquery.dataViewer role for that dataset.
• "Can edit" maps to the bigquery.dataEditor role for that dataset.
• "Is owner" maps to the bigquery.dataOwner role for that dataset.

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:

Console

1. Open the BigQuery web UI in the GCP Console.
   Go to the BigQuery web UI

2. Click Compose new query.

   

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

4. (Optional) To change the data processing location, click More, then Query settings. Under Processing location, click Auto-select and choose your data's location. Finally, click Save to update the query settings.

5. Click Run.

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

Classic 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:

Note: These examples use the --location=US flag because you are querying a public dataset. The BigQuery public datasets are stored in the US multi-region location. Because the public datasets are stored in the US, you cannot write public data query results to a table in another region, and you cannot join tables in public datasets with tables in another region.

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 .

View on GitHub Feedback

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 .

View on GitHub Feedback

// 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 .

View on GitHub Feedback

// 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 .

View on GitHub Feedback

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

async function query() {
  // Queries the Shakespeare dataset with the cache disabled.

  // Create a client
  const bigqueryClient = 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',
  };

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

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

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

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 .

View on GitHub Feedback

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 .

View on GitHub Feedback

# 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 .

View on GitHub Feedback

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:

Console

1. Open the BigQuery web UI in the GCP Console.
   Go to the BigQuery web UI

2. Click the Compose new query button.

   

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

4. Click the More button, then Query settings.

   

5. Select the Batch option in the Job priority section.

   

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

7. Click Save to update the query settings.

8. Click the Run button.

Classic 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:

Note: These examples use the --location=US flag because you are querying a public dataset. The BigQuery public datasets are stored in the US multi-region location. Because the public datasets are stored in the US, you cannot write public data query results to a table in another region, and you cannot join tables in public datasets with tables in another region.

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 .

View on GitHub Feedback

	// 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.

View on GitHub

// 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 .

View on GitHub Feedback

# 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))