Run a query
This document shows you how to run a query in BigQuery and understand how much data the query will process before execution by performing a dry run.
Types of queries
You can query BigQuery data by using one of the following query job types:
- Interactive query jobs. By default, BigQuery runs interactive (on-demand) query jobs as soon as possible.
Continuous query jobs (Preview). With these jobs, the query runs continuously, letting you analyze incoming data in BigQuery in real time and then write the results to a BigQuery table, or export the results to Bigtable or Pub/Sub. You can use this capability to perform time sensitive tasks, such as creating and immediately acting on insights, applying real time machine learning (ML) inference, and building event-driven data pipelines.
Batch query jobs. With these jobs, BigQuery queues each batch query on your behalf and then starts the query when idle resources are available, usually within a few minutes.
You can run query jobs by using the following methods:
- Compose and run a query in the Google Cloud console.
- Run the
bq query
command in the bq command-line tool. - Programmatically call the
jobs.query
orjobs.insert
method in the BigQuery REST API. - Use the BigQuery client libraries.
By default, BigQuery runs your queries as interactive query jobs, which are run as soon as possible. BigQuery dynamically computes the concurrent query limit based on resource availability and favors running more concurrent interactive queries than batch queries. Once you reach the concurrent query limit, additional queries wait in a queue. For more information, see query queues.
BigQuery saves query results to either a temporary table (default) or permanent table. When you specify a permanent table as the destination table for the results, you can choose whether to append or overwrite an existing table, or create a new table with a unique name.
Required roles
To get the permissions that you need to run a query job, ask your administrator to grant you the following IAM roles:
-
BigQuery Job User (
roles/bigquery.jobUser
) on the project. -
BigQuery Data Viewer (
roles/bigquery.dataViewer
) on all tables and views that your query references. To query views, you also need this role on all underlying tables and views. If you're using authorized views or authorized datasets, you don't need access to the underlying source data.
For more information about granting roles, see Manage access.
These predefined roles contain the permissions required to run a query job. To see the exact permissions that are required, expand the Required permissions section:
Required permissions
The following permissions are required to run a query job:
-
bigquery.jobs.create
on the project from which the query is being run, regardless of where the data is stored. -
bigquery.tables.getData
on all tables and views that your query references. To query views, you also need this permission on all underlying tables and views. If you're using authorized views or authorized datasets, you don't need access to the underlying source data.
You might also be able to get these permissions with custom roles or other predefined roles.
Troubleshooting
Access Denied: Project [project_id]: User does not have bigquery.jobs.create
permission in project [project_id].
This error occurs when a principal lacks permission to create a query jobs in the project.
Resolution: An administrator must grant you the bigquery.jobs.create
permission on the project you are querying. This permission is required in
addition to any permission required to access the queried data.
For more information about BigQuery permissions, see Access control with IAM.
Run an interactive query
To run an interactive query, select one of the following options:
Console
Go to the BigQuery page.
Click
Compose a new query.In the query editor, enter a valid GoogleSQL query.
For example, query the BigQuery public dataset
usa_names
to determine the most common names in the United States between the years 1910 and 2013:SELECT name, gender, SUM(number) AS total FROM `bigquery-public-data.usa_names.usa_1910_2013` GROUP BY name, gender ORDER BY total DESC LIMIT 10;
Optional: Specify the destination table and location for the query results:
- In the query editor, click More, and then click Query settings.
- In the Destination section, select Set a destination table for query results.
- For Dataset, enter the name of an existing dataset for the
destination table—for example,
myProject.myDataset
. - For Table Id, enter a name for the destination table—for
example,
myTable
. If the destination table is an existing table, then for Destination table write preference, select whether to append or overwrite the table with the query results.
If the destination table is a new table, then BigQuery creates the table when you run your query.
In the Additional settings section, click the Data location menu, and then select an option.
In this example, the
usa_names
dataset is stored in the US multi-region location. If you specify a destination table for this query, the dataset that contains the destination table must also be in the US multi-region. You cannot query a dataset in one location and write the results to a table in another location.Click Save.
Click
Run.If you don't specify a destination table, the query job writes the output to a temporary (cache) table.
You can now explore the query results in the Results tab of the Query results pane.
Optional: To sort the query results by column, click
Open sort menu next to the column name and select a sort order. If the estimated bytes processed for the sort is more than zero, then the number of bytes is displayed at the top of the menu.Optional: To see visualization of your query results, go to the Chart tab. You can zoom in or zoom out of the chart, download the chart as a PNG file, or toggle the legend visibility.
In the Chart configuration pane, you can change the chart type (line, bar, or scatter) and configure the measures and dimensions of the chart. Fields in this pane are prefilled with the initial configuration inferred from the destination table schema of the query. The configuration is preserved between following query runs in the same query editor. Dimensions support
INTEGER
,INT64
,FLOAT
,FLOAT64
,NUMERIC
,BIGNUMERIC
,TIMESTAMP
,DATE
,DATETIME
,TIME
, andSTRING
data types, while measures supportINTEGER
,INT64
,FLOAT
,FLOAT64
,NUMERIC
, andBIGNUMERIC
data types.Optional: In the JSON tab, you can explore the query results in the JSON format, where the key is the column name and the value is the result for that column.
bq
-
In the Google Cloud console, activate Cloud Shell.
At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.
Use the
bq query
command. In the following example, the--use_legacy_sql=false
flag lets you use GoogleSQL syntax.bq query \ --use_legacy_sql=false \ 'QUERY'
Replace QUERY with a valid GoogleSQL query. For example, query the BigQuery public dataset
usa_names
to determine the most common names in the United States between the years 1910 and 2013:bq query \ --use_legacy_sql=false \ 'SELECT name, gender, SUM(number) AS total FROM `bigquery-public-data.usa_names.usa_1910_2013` GROUP BY name, gender ORDER BY total DESC LIMIT 10;'
The query job writes the output to a temporary (cache) table.
Optionally, you can specify the destination table and location for the query results. To write the results to an existing table, include the appropriate flag to append (
--append_table=true
) or overwrite (--replace=true
) the table.bq query \ --location=LOCATION \ --destination_table=TABLE \ --use_legacy_sql=false \ 'QUERY'
Replace the following:
LOCATION: the region or multi-region for the destination table—for example,
US
In this example, the
usa_names
dataset is stored in the US multi-region location. If you specify a destination table for this query, the dataset that contains the destination table must also be in the US multi-region. You cannot query a dataset in one location and write the results to a table in another location.You can set a default value for the location using the .bigqueryrc file.
TABLE: a name for the destination table—for example,
myDataset.myTable
If the destination table is a new table, then BigQuery creates the table when you run your query. However, you must specify an existing dataset.
If the table isn't in your current project, then add the Google Cloud project ID using the format
PROJECT_ID:DATASET.TABLE
—for example,myProject:myDataset.myTable
. If--destination_table
is unspecified, a query job is generated that writes the output to a temporary table.
API
To run a query using the API, insert a new job
and populate the query
job configuration property. Optionally 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.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
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 authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
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 authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
To run a query with a proxy, see Configuring a proxy.
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.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
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.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
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.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
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.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
Run a continuous query
Running a continuous query job requires additional configuration. For more information, see Create continuous queries.
Run a batch query
To run a batch query, select one of the following options:
Console
Go to the BigQuery page.
Click
Compose a new query.In the query editor, enter a valid GoogleSQL query.
For example, query the BigQuery public dataset
usa_names
to determine the most common names in the United States between the years 1910 and 2013:SELECT name, gender, SUM(number) AS total FROM `bigquery-public-data.usa_names.usa_1910_2013` GROUP BY name, gender ORDER BY total DESC LIMIT 10;
Click
More, and then click Query settings.In the Resource management section, select Batch.
Optional: Specify the destination table and location for the query results:
- In the Destination section, select Set a destination table for query results.
- For Dataset, enter the name of an existing dataset for the
destination table—for example,
myProject.myDataset
. - For Table Id, enter a name for the destination table—for
example,
myTable
. If the destination table is an existing table, then for Destination table write preference, select whether to append or overwrite the table with the query results.
If the destination table is a new table, then BigQuery creates the table when you run your query.
In the Additional settings section, click the Data location menu, and then select an option.
In this example, the
usa_names
dataset is stored in the US multi-region location. If you specify a destination table for this query, the dataset that contains the destination table must also be in the US multi-region. You cannot query a dataset in one location and write the results to a table in another location.
Click Save.
Click
Run.If you don't specify a destination table, the query job writes the output to a temporary (cache) table.
Optional: To sort the query results by column, click
Open sort menu next to the column name and select a sort order. If the estimated bytes processed for the sort is more than zero, then the number of bytes is displayed at the top of the menu.Optional: To see visualization of your query results, go to the Chart tab. You can zoom in or zoom out of the chart, download the chart as a PNG file, or toggle the legend visibility.
In the Chart configuration pane, you can change the chart type (line, bar, or scatter) and configure the measures and dimensions of the chart. Fields in this pane are prefilled with the initial configuration inferred from the destination table schema of the query. The configuration is preserved between following query runs in the same query editor. Dimensions support
INTEGER
,INT64
,FLOAT
,FLOAT64
,NUMERIC
,BIGNUMERIC
,TIMESTAMP
,DATE
,DATETIME
,TIME
, andSTRING
data types, while measures supportINTEGER
,INT64
,FLOAT
,FLOAT64
,NUMERIC
, andBIGNUMERIC
data types.
bq
-
In the Google Cloud console, activate Cloud Shell.
At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.
Use the
bq query
command and specify the--batch
flag. In the following example, the--use_legacy_sql=false
flag lets you use GoogleSQL syntax.bq query \ --batch \ --use_legacy_sql=false \ 'QUERY'
Replace QUERY with a valid GoogleSQL query. For example, query the BigQuery public dataset
usa_names
to determine the most common names in the United States between the years 1910 and 2013:bq query \ --batch \ --use_legacy_sql=false \ 'SELECT name, gender, SUM(number) AS total FROM `bigquery-public-data.usa_names.usa_1910_2013` GROUP BY name, gender ORDER BY total DESC LIMIT 10;'
The query job writes the output to a temporary (cache) table.
Optionally, you can specify the destination table and location for the query results. To write the results to an existing table, include the appropriate flag to append (
--append_table=true
) or overwrite (--replace=true
) the table.bq query \ --batch \ --location=LOCATION \ --destination_table=TABLE \ --use_legacy_sql=false \ 'QUERY'
Replace the following:
LOCATION: the region or multi-region for the destination table—for example,
US
In this example, the
usa_names
dataset is stored in the US multi-region location. If you specify a destination table for this query, the dataset that contains the destination table must also be in the US multi-region. You cannot query a dataset in one location and write the results to a table in another location.You can set a default value for the location using the .bigqueryrc file.
TABLE: a name for the destination table—for example,
myDataset.myTable
If the destination table is a new table, then BigQuery creates the table when you run your query. However, you must specify an existing dataset.
If the table isn't in your current project, then add the Google Cloud project ID using the format
PROJECT_ID:DATASET.TABLE
—for example,myProject:myDataset.myTable
. If--destination_table
is unspecified, a query job is generated that writes the output to a temporary table.
API
To run a query using the API, insert a new job
and populate the query
job configuration property. Optionally 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 and set the value to BATCH
.
Poll for results by calling
getQueryResults
.
Poll until jobComplete
equals true
. Check for errors and warnings in the
errors
list.
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 authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
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.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
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.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
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.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
Short query optimized mode
Short query optimized mode is intended to improve the overall latency of short
queries that are common in workloads such as dashboards or data exploration. It
executes the query and returns the results inline for SELECT
statements.
Queries using short query optimized mode don't create a job when executed unless
BigQuery determines that a job creation is necessary to complete
the query.
To enable short query optimized mode, set the jobCreationMode
field of the
QueryRequest
instance to JOB_CREATION_OPTIONAL
in the
jobs.query
request body.
When the value of this field is set to JOB_CREATION_OPTIONAL
,
BigQuery determines if the query can use the new short query
optimized mode. If so, BigQuery executes the query and returns
all results in the rows
field of the response. Since a job isn't created for
this query, BigQuery doesn't return a jobReference
in the
response body. Instead, it returns a queryId
field which you can use to get
insights about the query using the INFORMATION_SCHEMA.JOBS
view. Since no
job is created, there is no jobReference
that can be passed to
jobs.get
and
jobs.getQueryResults
APIs to lookup short queries.
If BigQuery determines that a job is required to complete the
query, a jobReference
is returned. You can inspect the job_creation_reason
field in INFORMATION_SCHEMA.JOBS
view to determine
the reason that a job was created for the query. In this case, you should use
jobs.getQueryResults
to fetch the results when the query is complete.
When using the JOB_CREATION_OPTIONAL
value, you shouldn't assume that
jobReference
field is always present in the response. You should check if the
field exists before accessing it.
Short query optimized mode also includes a query result cache that improves the
performance of repeated queries when underlying data doesn't change. When you
specify useQueryCache: true
(the default value is true
if not specified) and
jobCreationMode: JOB_CREATION_OPTIONAL
in
QueryRequest
,
BigQuery attempts to serve the results from the cache. Note that
caching is done as a best effort.
To run a query using short query optimized mode, select one of the following options:
Console
Go to the BigQuery page.
Click
SQL QUERY.In the query editor, enter a valid GoogleSQL query.
For example, query the BigQuery public dataset
usa_names
to determine the most common names in the United States between the years 1910 and 2013:SELECT name, gender, SUM(number) AS total FROM `bigquery-public-data.usa_names.usa_1910_2013` GROUP BY name, gender ORDER BY total DESC LIMIT 10;
Click
More, and then click Short query optimized under Choose query mode. Click CONFIRM to confirm the choice.Click
Run.
bq
-
In the Google Cloud console, activate Cloud Shell.
At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.
Use the
bq query
command and specify the--job_creation_mode=JOB_CREATION_OPTIONAL
flag. In the following example, the--use_legacy_sql=false
flag lets you use GoogleSQL syntax.bq query \ --rpc=true \ --use_legacy_sql=false \ --job_creation_mode=JOB_CREATION_OPTIONAL \ --location=LOCATION \ 'QUERY'
Replace QUERY with a valid GoogleSQL query, and replace LOCATION with a valid region where the dataset is located. For example, query the BigQuery public dataset
usa_names
to determine the most common names in the United States between the years 1910 and 2013:bq query \ --rpc=true \ --use_legacy_sql=false \ --job_creation_mode=JOB_CREATION_OPTIONAL \ --location=us \ 'SELECT name, gender, SUM(number) AS total FROM `bigquery-public-data.usa_names.usa_1910_2013` GROUP BY name, gender ORDER BY total DESC LIMIT 10;'
The query job returns the output inline in the response.
API
To run a query in short query mode using the API, run a query synchronously
and populate the QueryRequest
property. Include the jobCreationMode
property and set its value to JOB_CREATION_OPTIONAL
.
Check the response. If jobComplete
equals true
and jobReference
is empty, read the results from the rows
field. You can also get the queryId
from the response.
If jobRefernence
is present, you can check jobCreationReason
for why a job was created by BigQuery. Poll for results by calling
getQueryResults
.
Poll until jobComplete
equals true
. Check for errors and warnings in the
errors
list.
Java
Available version: 2.37.1 and up
Requires setting QUERY_PREVIEW_ENABLED=true
environment variable.
Example: Linux or macOS
export QUERY_PREVIEW_ENABLED=TRUE
Example: Windows
$env:QUERY_PREVIEW_ENABLED=TRUE
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 authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
To run a query with a proxy, see Configuring a proxy.
Python
Available version: 3.21.0 and up
Requires setting QUERY_PREVIEW_ENABLED=true
environment variable.
Example: Linux or macOS
export QUERY_PREVIEW_ENABLED=TRUE
Example: Windows
$env:QUERY_PREVIEW_ENABLED=TRUE
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.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
Node
Available version: 7.6.1 and up
Requires setting QUERY_PREVIEW_ENABLED=true
environment variable.
Example: Linux or macOS
export QUERY_PREVIEW_ENABLED=TRUE
Example: Windows
$env:QUERY_PREVIEW_ENABLED=TRUE
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.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
Go
Available version: 1.58.0 and up
Requires setting QUERY_PREVIEW_ENABLED=true
environment variable
Example: Linux or macOS
export QUERY_PREVIEW_ENABLED=TRUE
Example: Windows
$env:QUERY_PREVIEW_ENABLED=TRUE
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 authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
JDBC Driver
Available version: JDBC v1.6.1
Requires setting JobCreationMode=2
in the connection string.
jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;JobCreationMode=2;Location=US;
Quotas
For information about quotas regarding interactive and batch queries, see Query jobs.
View the number of interactive and batch queries
You can view the number of interactive and batch queries by using the
INFORMATION_SCHEMA.JOBS_BY_PROJECT
view.
The following example uses the INFORMATION_SCHEMA.JOBS_BY_PROJECT
view to get
the number of interactive and batch queries that were run in the last 7 hours:
SELECT
priority,
COUNT(*) active_jobs,
FROM
`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 hour)
AND end_time IS NULL
AND job_type = 'QUERY'
GROUP BY priority
The INFORMATION_SCHEMA.JOBS_BY_PROJECT
view uses the priority
field to
signify if a query is INTERACTIVE
or BATCH
. For more information, see
Schema.
Dry run
A dry run in BigQuery provides the following information:
- estimate of charges in on-demand mode
- validation of your query
- approximate bytes processed by your query in capacity mode
Dry runs don't use query slots, and you are not charged for performing a dry run. You can use the estimate returned by a dry run to calculate query costs in the pricing calculator.
Perform a dry run
To perform a dry run, do the following:
Console
Go to the BigQuery page.
Enter your query in the query editor.
If the query is valid, then a check mark automatically appears along with the amount of data that the query will process. If the query is invalid, then an exclamation point appears along with an error message.
bq
Enter a query like the following using the --dry_run
flag.
bq query \ --use_legacy_sql=false \ --dry_run \ 'SELECT COUNTRY, AIRPORT, IATA FROM `project_id`.dataset.airports LIMIT 1000'
For a valid query, the command produces the following response:
Query successfully validated. Assuming the tables are not modified, running this query will process 10918 bytes of data.
API
To perform a dry run by using the API, submit a query job with
dryRun
set to true
in the
JobConfiguration
type.
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 authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
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 authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
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.
To authenticate to BigQuery, set up Application Default Credentials.
For more information, see
Set up authentication for client libraries.
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.
To authenticate to BigQuery, set up Application Default Credentials.
For more information, see
Set up authentication for client libraries.
Python
Set the
QueryJobConfig.dry_run
property to True
.
Client.query()
always returns a completed
QueryJob
when provided a dry run query configuration.
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.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
What's next
- Learn how to manage query jobs.
- Learn how to view query history.
- Learn how to save and share queries.
- Learn about query queues.