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

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 Access control.

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

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 .

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

# from google.cloud import bigquery
# client = bigquery.Client()

job_config = bigquery.QueryJobConfig()
job_config.dry_run = True
job_config.use_query_cache = False
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"
    ),
    # Location must match that of the dataset(s) referenced in the query.
    location="US",
    job_config=job_config,
)  # API request

# A dry run query completes immediately.
assert query_job.state == "DONE"
assert query_job.dry_run

print("This query will process {} bytes.".format(query_job.total_bytes_processed))
Oliko tästä sivusta apua? Kerro mielipiteesi

Palautteen aihe:

Tämä sivu
BigQuery
Tarvitsetko apua? Siirry tukisivullemme.