BigQuery best practices: Controlling costs

This page describes best practices for controlling costs in BigQuery.

Avoid SELECT *

Best practice: Query only the columns that you need.

Using SELECT * is the most expensive way to query data. When you use SELECT *, BigQuery does a full scan of every column in the table.

If you are experimenting with data or exploring data, use one of the data preview options instead of SELECT *.

Applying a LIMIT clause to a SELECT * query does not affect the amount of data read. You are billed for reading all bytes in the entire table, and the query counts against your free tier quota.

Instead, query only the columns you need. For example, use SELECT * EXCEPT to exclude one or more columns from the results.

If you do require queries against every column in a table, but only against a subset of data, consider:

• Materializing results in a destination table and querying that table instead
• Partitioning your tables by date and querying the relevant partition; for example, WHERE _PARTITIONDATE="2017-01-01" only scans the January 1, 2017 partition

Sample data using preview options

Best practice: Don't run queries to explore or preview table data.

If you are experimenting with or exploring your data, you can use table preview options to view data for free and without affecting quotas.

BigQuery supports the following data preview options:

• In the GCP Console or the classic web UI, on the table details page, click the Preview tab to sample the data.
• In the CLI, use the bq head command and specify the number of rows to preview.
• In the API, use tabledata.list to retrieve table data from a specified set of rows.

Price your queries before running them

Best practice: Before running queries, preview them to estimate costs.

Queries are billed according to the number of bytes read. To estimate costs before running a query:

• View the query validator in the GCP Console or the classic web UI
• Use the Google Cloud Platform Pricing Calculator
• Perform a dry run by using the:
  • --dry_run flag in the CLI
  • dryRun parameter when submitting a query job using the API

Using the query validator

When you enter a query in the GCP Console or the classic web UI, the query validator verifies the query syntax and provides an estimate of the number of bytes read. You can use this estimate to calculate query cost in the pricing calculator.

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 that is used by the pricing calculator.

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.

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
# 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))

Using the pricing calculator

To estimate query costs in the Google Cloud Platform Pricing Calculator, enter the number of bytes that are processed by the query as MB, GB, TB, or PB. If your query processes less than 1 TB, the estimate is $0 because BigQuery provides 1 TB of on-demand query processing free per month.

Limit query costs by restricting the number of bytes billed

Best practice: Use the maximum bytes billed setting to limit query costs.

You can limit the number of bytes billed for a query using the maximum bytes billed setting. When you set maximum bytes billed, if the query will read bytes beyond the limit, the query fails without incurring a charge.

If a query fails because of the maximum bytes billed setting, an error like the following is returned:

Error: Query exceeded limit for bytes billed: 1000000. 10485760 or higher required.

To set the maximum bytes billed:

• In the classic BigQuery web UI, enter an integer in the Maximum Bytes Billed field in the query options. Currently, the GCP Console does not support the Maximum Bytes Billed option.

• In the CLI, use bq query command with the --maximum_bytes_billed flag.

  bq query --maximum_bytes_billed=1000000 \
  --use_legacy_sql=false \
  'SELECT
     word
   FROM
     `bigquery-public-data`.samples.shakespeare'
  

• In the API, set the maximumBytesBilled property in the query job configuration.

LIMIT doesn’t affect cost

Best practice: Do not use a LIMIT clause as a method of cost control.

Applying a LIMIT clause to a query does not affect the amount of data that is read. It merely limits the results set output. You are billed for reading all bytes in the entire table as indicated by the query.

The amount of data read by the query counts against your free tier quota despite the presence of a LIMIT clause.

View costs using a dashboard and query your audit logs

Best practice: Create a dashboard to view your billing data so you can make adjustments to your BigQuery usage. Also consider streaming your audit logs to BigQuery so you can analyze usage patterns.

You can export your billing data to BigQuery and visualize it in a tool such as Google Data Studio. For a tutorial on creating a billing dashboard, see Visualize GCP billing using BigQuery and Google Data Studio.

You can also stream your audit logs to BigQuery and analyze the logs for usage patterns such as query costs by user.

Partition data by date

Best practice: Partition your tables by date.

If possible, partition your BigQuery tables by date. Partitioning your tables allows you to query relevant subsets of data which improves performance and reduces costs.

For example, when you query partitioned tables, use the _PARTITIONTIME pseudo column to filter for a date or a range of dates. The query processes data only in the partitions that are specified by the date or range.

Materialize query results in stages

Best practice: If possible, materialize your query results in stages.

If you create a large, multi-stage query, each time you run it, BigQuery reads all the data that is required by the query. You are billed for all the data that is read each time the query is run.

Instead, break your query into stages where each stage materializes the query results by writing them to a destination table. Querying the smaller destination table reduces the amount of data that is read and lowers costs. The cost of storing the materialized results is much less than the cost of processing large amounts of data.

Consider the cost of large result sets

Best practice: If you are writing large query results to a destination table, use the default table expiration time to remove the data when it's no longer needed.

Keeping large result sets in BigQuery storage has a cost. If you don't need permanent access to the results, use the default table expiration to automatically delete the data for you.

For more information, see storage pricing.

Use streaming inserts with caution

Best practice: Use streaming inserts only if your data must be immediately available.

There is no charge for loading data into BigQuery. There is a charge, however, for streaming data into BigQuery. Unless your data must be immediately available, load your data rather than streaming it.