Issuing a query dry run

When you run a query in the CLI, you can use the --dry_run flag to estimate the number of bytes read by the query. You can also use the dryRun parameter when submitting a query job using the API or client libraries.

You can use the estimate returned by the dry run to calculate query costs in the pricing calculator. You are not charged for performing the dry run.

Permissions required

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 datasets that contain the tables or views referenced by the query. For information on dataset access controls, see Controlling access to datasets.

The following predefined Cloud IAM roles include bigquery.jobs.create permissions:

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

Note: Most users such as data analysts and data scientists should be granted the bigquery.user or bigquery.jobUser role to run query jobs. bigquery.admin is granted all BigQuery permissions. Alternatively, you can create a custom role that is granted all required permissions.

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 Cloud IAM roles in BigQuery, see Predefined roles and permissions.

Performing dry runs

You can perform a dry run for a query job by using:

• The --dry_run flag with the query command in the CLI
• The dryRun parameter in the job configuration when you use the API or client libraries

Performing a dry run

To perform a dry run:

Console

Currently, you cannot perform a dry run by using the Cloud Console.

Classic UI

Currently, you cannot perform a dry run by using the web UI.

CLI

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'
 

The command produces the following response:

Query successfully validated. Assuming the tables are not modified,
running this query will process 10918 bytes of data.

Note: If your query processes a small amount of data, you might need to convert the bytes that are processed from KB to MB. MB is the smallest measure used by the pricing calculator.

API

To perform a dry run by using the API, submit a query job with dryRun set to true in the job configuration.

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,
	COUNT(*) as name_count
FROM ` + "`bigquery-public-data.usa_names.usa_1910_2013`" + `
WHERE state = 'WA'
GROUP BY name`)
q.DryRun = true
// 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
}
// Dry run is not asynchronous, so get the latest status and statistics.
status := job.LastStatus()
if err != nil {
	return err
}
fmt.Printf("This query will process %d bytes\n", status.Statistics.TotalBytesProcessed)

Python

To perform a dry run using the Python client library, 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 .

View on GitHub Feedback

from google.cloud import bigquery

# TODO(developer): Construct a BigQuery client object.
# client = bigquery.Client()

job_config = bigquery.QueryJobConfig(dry_run=True, use_query_cache=False)

# Start the query, passing in the extra configuration.
query_job = client.query(
    (
        "SELECT name, COUNT(*) as name_count "
        "FROM `bigquery-public-data.usa_names.usa_1910_2013` "
        "WHERE state = 'WA' "
        "GROUP BY name"
    ),
    job_config=job_config,
)  # Make an API request.

# A dry run query completes immediately.
print("This query will process {} bytes.".format(query_job.total_bytes_processed))