Reserve BI Engine capacity

When you use BigQuery BI Engine, your charges are based on the BI Engine capacity you purchased for your project. Reservations are charged per Gb/hour, priced per region according to on-demand pricing. If you have a capacity-based commitment, you will be eligible for discount of up to 100% based on the active commitment. For details see capacity-based pricing.

You purchase BigQuery BI Engine capacity by creating a reservation. The reservation is attached to a project you identify when the reservation is created. BI Engine uses this capacity to determine how much data can be cached. For more information about the maximum reservation size for BI Engine, see Quotas and limits.

Required roles

To get the permissions that you need to create and delete reservations, ask your administrator to grant you the BigQuery Resource Admin (roles/bigquery.resourceAdmin) IAM role on the project. For more information about granting roles, see Manage access.

You might also be able to get the required permissions through custom roles or other predefined roles.

Create a reservation

To reserve on-demand BI Engine capacity, follow these steps:

Console

  1. On the BigQuery page, in Administration, go to the BI Engine page.

    Go to BI Engine

  2. Click Create reservation.

  3. On the Create reservation page, for Step 1:

    • Verify your project name.
    • Choose your location. The location should match the location of the datasets you are querying.
    • Adjust the slider to the amount of memory capacity you're reserving. The following example sets the capacity to 2 GB. The current maximum is 250 GB.

      BI Engine capacity location

  4. Click Next.

  5. Optionally, enter the Table ID for preferred tables and click Next.

  6. For Step 3, review your reservation details, and then click Create.

After you confirm your reservation, the details are displayed on the Reservations page.

SQL

Sets the options for BI Engine reservation capacity.

  1. In the Google Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. In the query editor, enter the following statement:

    ALTER BI_CAPACITY `PROJECT_ID.LOCATION_ID.default`
    SET OPTIONS (
      size_gb = VALUE,
      preferred_tables = <ARRAY<STRING>>);
    

    Replace the following:

    • PROJECT_ID: optional ID of the project that will benefit from BI Engine acceleration. If omitted, the default project is used.
    • LOCATION_ID: the location where data needs to be cached, prefixed with region-. Examples: region-us, region-us-central1.
    • VALUE: the INT64 size of the reservation for BI Engine capacity in gigabytes, 1 to 250 GB. Setting VALUE replaces the existing value if there is one. Setting to NULL clears the value for that option.
    • STRING: optional list of tables to which acceleration should be applied. Format: project.dataset.table or dataset.table. If the project is omitted, then the default project is used.

  3. Click Run.

For more information about how to run queries, see Run an interactive query.

bq

Use the bq update command and supply the data definition language (DDL) statement as the query parameter:

bq --project_id=PROJECT_ID update \
    --bi_reservation_size=SIZE \
    --location=LOCATION \
    --reservation

Replace the following:

  • PROJECT_ID: the ID of your project
  • SIZE: the reservation memory capacity in gigabytes, 1 to 250 GB
  • LOCATION: the location of the dataset you are querying

Estimate and measure capacity

To estimate capacity requirements for a BI Engine reservation, follow these steps:

  1. View the TABLE_STORAGE view information schema to determine the logical storage size of the table, and use that for your initial BI Engine reservation. For example, for queries against a table with 200GiB of data, we recommend that you start with a 200GiB BI Engine reservation. More selective queries that only use a subset of available fields or partitions could start with smaller reservation size.
  2. Run all of the queries that need optimization and that were created in the same project and region as the BI Engine reservation. The goal is to approximate the workload that you need to optimize. The increased load requires more memory to handle queries. Data is loaded into BI Engine after the query is received.
  3. Compare your BI Engine RAM reservation to the number of bytes used, reservation/used_bytes in the Cloud Monitoring bigquerybiengine metrics.
  4. Adjust your reservation capacity based upon the results. In many use cases, a smaller reservation can accelerate the majority of your queries, conserving money and resources. For more information about Monitoring for BI Engine, see BI Engine monitoring.

The following factors affect BI Engine reservation size:

  • BI Engine only caches the frequently accessed columns and rows that are required to process the query.
  • When a reservation is fully used, BI Engine tries to offload the least recently used data to free up capacity for new queries.
  • If multiple computationally intensive queries are using the same dataset, then BI Engine loads additional copies of the data to redistribute and optimize response times.

Delete a reservation

To delete a capacity reservation, follow these steps:

Console

  1. On the BigQuery page, in Administration go to the BI Engine page.

    Go to BI Engine

  2. In the Reservations section, locate your reservation.

  3. In the Actions column, click the icon to the right of your reservation and choose Delete.

  4. In the Delete reservation? dialog, enter Delete and then click DELETE.

SQL

Sets the options on BI Engine capacity.

  1. In the Google Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. In the query editor, enter the following statement:

    ALTER BI_CAPACITY `PROJECT_ID.LOCATION_ID.default`
    SET OPTIONS (
      size_gb = 0);
    

    Replace the following:

    • PROJECT_ID: optional ID of the project that will benefit from BI Engine acceleration. If omitted, the default project is used.
    • LOCATION_ID: the location where data needs to be cached, prefixed with region-. Examples: region-us, region-us-central1.

    When you delete all capacity reservations in a project, BI Engine is disabled for that project.

  3. Click Run.

For more information about how to run queries, see Run an interactive query.

bq

Use the bq update command and supply the DDL statement as the query parameter.

bq --project_id="PROJECT_ID" \
update --reservation
    --bi_reservation_size=0 \
    --location=LOCATION

Replace the following:

  • PROJECT_ID: the ID of your project
  • LOCATION: the location of the dataset you are querying

View resource metadata

You can get information about your BI Engine capacity by querying the INFORMATION_SCHEMA tables. BigQuery provides the following INFORMATION_SCHEMA views:

What's next