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
At a minimum, to run a query job, you must be
granted bigquery.jobs.create
permissions. In order for the query job to
complete successfully, you must also be granted access to the tables or views
referenced by the query. Access to the tables or views can be granted at the
following levels, listed in order of range of resources allowed (largest to
smallest):
- at a high level in the Google Cloud resource hierarchy such as the project, folder, or organization level
- at the dataset level
- at the table level
The following predefined IAM roles include bigquery.jobs.create
permissions:
bigquery.user
bigquery.jobUser
bigquery.admin
In addition, if a user has bigquery.datasets.create
permissions, when that
user creates a dataset, they are granted bigquery.dataOwner
access to it.
bigquery.dataOwner
access gives the user the ability to query tables and views
in the dataset.
For more information on 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.