Running interactive and batch query jobs
This document describes how to run interactive (on-demand) and batch query jobs.
Query results are 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.
Required permissions
To run a query job, you need the
bigquery.jobs.create
Identity and Access Management (IAM) permission on the project that
runs the query job.
Each of the following predefined IAM roles includes the permissions that you need to run a query job:
roles/bigquery.admin
roles/bigquery.jobUser
roles/bigquery.user
You also need the bigquery.tables.getData
permission
on all tables and views that your query references. In addition, when querying
a view you need this permission on all underlying tables and views.
However, if you are using authorized views
or authorized datasets, you don't need
access to the underlying source data.
Each of the following predefined IAM roles includes the permission that you need on all tables and views that the query references:
roles/bigquery.admin
roles/bigquery.dataOwner
roles/bigquery.dataEditor
roles/bigquery.dataViewer
For more information about IAM roles in BigQuery, see Predefined roles and permissions.
Running interactive queries
By default, BigQuery runs interactive query jobs, which means that the query is executed as soon as possible. Interactive queries count toward your concurrent rate limit and your daily limit.
To run an interactive query that writes to a temporary table, follow these steps:
Console
In the Cloud console, open the BigQuery page.
Click Compose new query.
Enter a valid BigQuery SQL query in the Query editor text area.
(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.
Click Run.
This creates a query job that writes the output to a temporary table.
bq
Enter the bq query
command and include your query text.
(Optional) 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 Key Management Service key to encrypt the destination table data.--use_legacy_sql=false
flag to use standard SQL syntax. You can set a default syntax for thebq
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 toasia-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 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 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'
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.
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.
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.
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.
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.
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.
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.
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 in the BigQuery shared resource pool. This usually occurs 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, follow these steps:
Console
In the Cloud console, open the BigQuery page.
Click the Compose new query button.
Enter a valid SQL query in the Query editor text area.
Click the More button, then Query settings.
Select the Batch option in the Job priority section.
(Optional) For Processing location, click Unspecified and choose your data's location.
Click Save to update the query settings.
Click Run.
bq
Enter the bq query
command and include your query text. Specify the
--batch
flag to run a batch query.
(Optional) 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 Key Management Service key to encrypt the destination table data.--use_legacy_sql=false
flag to use standard SQL syntax. You can set a default syntax for thebq
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 toasia-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 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 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'
API
To run a query using the API, insert a new job
and populate the query
job configuration property. (Optional) 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.
Java
To run a batch query, set the query priority to QueryJobConfiguration.Priority.BATCH when creating a QueryJobConfiguration.
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.
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.
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.
What's next
- To learn how to manage query jobs, see Managing jobs.
- To learn how to view query history, see View job details.
- To learn how to save and share queries, see Saving and sharing queries.