Reserve BI Engine capacity

You purchase BI Engine capacity by creating a reservation.BI Engine is only available for projects with a supported edition.Reservations are measured in GiB of memory. The reservation is attached to aproject and region you identifywhen the reservation is created. BI Engine uses this capacity tocache data. For information about the maximum reservation size forBI Engine, seeQuotas and limits.

When you use BI Engine, your charges are based on theBI Engine capacity you purchased for your project. BI Engine reservationsare charged per GiB/hour, priced per region, seeBI Enginepricing.

Reservation project:BI Engine reservations are managed by the billing project's ID. When purchasing a reservation, ensure that you specify the billing project ID and the region that will be used to query the data. This is not necessarily the same project that contains the dataset.

Required roles

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

You might also be able to get the required permissions throughcustom roles or otherpredefined roles.

Create a reservation

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

Console

  1. On the BigQuery page, inAdministration, go to theBI Engine page.

    Go to BI Engine

    Note: If prompted to enableBigQuery Reservation API, clickEnable.

  2. ClickCreate reservation.

  3. On theCreate reservation page, forStep 1:

  4. ClickNext.

  5. Preferred tables (optional).Preferred tableslet you limit BI Engine acceleration to a specified set oftables. All other tables use regular BigQuery slots.

    In theTable Id field, specify the table thatyou want to accelerate using the pattern:PROJECT.DATASET.TABLE.

    Replace the following:

    • PROJECT: your Google Cloud project ID
    • DATASET: the dataset
    • TABLE: the table that you want to accelerate

  6. ClickNext.

  7. ForStep 3, review your reservation details, and then clickCreate.

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

SQL

Use theALTER BI_CAPACITY SET OPTIONS DDL statement tocreate or modify a BI Engine reservation.

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

    Go to BigQuery

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

    ALTERBI_CAPACITY`PROJECT_ID.LOCATION_ID.default`SETOPTIONS(size_gb=VALUE,preferred_tables=['TABLE_PROJECT_ID.DATASET.TABLE1','TABLE_PROJECT_ID.DATASET.TABLE2']);

    Replace the following:

    • PROJECT_ID: the optional ID of the project that will benefit from BI Engine acceleration. If omitted, the default project is used.
    • LOCATION_ID: thelocation where data needs to be cached, prefixed withregion-. Examples:region-us,region-us-central1.
    • VALUE: theINT64 size of the reservation for BI Engine capacity in gigabytes, 1 to 250 GB. You canrequest an increase of the maximum reservation capacity for your projects. Reservation increases are available in most regions, and can take from 3 days to one week to process. SettingVALUE replaces the existing value if there is one. Setting toNULL clears the value for that option.
    • TABLE_PROJECT_ID.DATASET.TABLE: the optional list ofpreferred tables to which acceleration should be applied. Format:TABLE_PROJECT_ID.DATASET.TABLE orDATASET.TABLE. If the project is omitted, then the default project is used.

  3. ClickRun.

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

bq

Use thebq updatecommand and supply thedata definition language (DDL) statement as the query parameter:

bq--project_id=PROJECT_IDupdate\--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 to250 GB. You canrequest an increaseof the maximum reservation capacity for your projects. Reservationincreases are available in most regions, and can take from 3 days toone week to process.
  • 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 theTOTAL_LOGICAL_BYTES viewto determine the logical size of the table, and use that for yourinitial BI Engine reservation. For example:

    SELECTSUM(TOTAL_LOGICAL_BYTES)/1024.0/1024.0/1024.0ASlogical_size_gbFROM`region-us.INFORMATION_SCHEMA.TABLE_STORAGE`WHERETABLE_NAMEINUNNEST(["Table1","Table2"]);

    For example, for queries against a set of tables that contain a total of200GiB of data, as a best practice you can start with a 200GiBBI Engine reservation. More selective queries that only use asubset of available fields or partitions could start with a smallerreservation size.

  2. Run all of the queries that need optimization and that were created in thesame project and region as the BI Engine reservation.The goal is to approximate the workload that you need to optimize. Theincreased load requires more memory to handle queries. Data is loadedinto BI Engine after the query is received.

  3. Compare your BI Engine RAM reservation to the number of bytesused,reservation/used_bytes in theCloud Monitoringbigquerybiengine 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 aboutMonitoring for BI Engine, seeBI Engine monitoring.

The following factors affect BI Engine reservation size:

  • BI Engine only caches the frequently accessed columns androws that are required to process the query.
  • When a reservation is fully used, BI Engine tries tooffload 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 toredistribute and optimize response times.

Modify a reservation

To modify an existing reservation, complete the following steps:

Console

To specify a set of tables for acceleration in an existing reservation, followthese steps:

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

    Go to BigQuery

  2. In the BigQuery navigation menu, clickBI Engine.

    If your project is configured for preferred tables, a set of tables isdisplayed in thePreferred Tables column.

    image

  3. On the row for the reservation that you want to edit, click the icon in theActions column, and then selectEdit.

  4. Adjust theGiB of Capacity slider to the amount of memory capacityyou're reserving. ClickNext.

  5. Preferred tables: To specify a set of tables for acceleration in anexisting reservation, in theTable Id field, specify the table thatyou want to accelerate using the pattern:PROJECT.DATASET.TABLE.

    Replace the following:

    • PROJECT: your Google Cloud project ID
    • DATASET: the dataset
    • TABLE: the table that you want to accelerate

    Changes can take up to ten seconds to take effect. Only tables in thepreferred tables list can use the BI Engine acceleration.

    ClickNext.

  6. Confirm your modified reservation. If you agree, clickUpdate.

SQL

You can use theALTER BI_CAPACITY SET OPTIONS DDL statementto create or modify a BI Engine reservation.

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

    Go to BigQuery

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

    ALTERBI_CAPACITY`PROJECT_ID.LOCATION_ID.default`SETOPTIONS(size_gb=VALUE,preferred_tables=[`TABLE_PROJECT_ID.DATASET.TABLE1`,`TABLE_PROJECT_ID.DATASET.TABLE2`]);

    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: thelocation where data needs to be cached, prefixed withregion-. Examples:region-us,region-us-central1.
    • VALUE: theINT64 size of the reservation for BI Engine capacity in gigabytes, 1 to 250 GB. You canrequest an increase of the maximum reservation capacity for your projects. Reservation increases are available in most regions, and can take from 3 days to one week to process. SettingVALUE replaces the existing value if there is one. Setting toNULL clears the value for that option.
    • TABLE_PROJECT_ID.DATASET.TABLE: optional list ofpreferred tables to which acceleration should be applied. Format:TABLE_PROJECT_ID.DATASET.TABLE orDATASET.TABLE. If the project is omitted, then the default project is used.

  3. ClickRun.

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

Delete a reservation

To delete a capacity reservation, follow these steps:

Console

  1. On the BigQuery page, inAdministration go to theBI Engine page.

    Go to BI Engine

  2. In theReservations section, locate your reservation.

  3. In theActions column, click the

  4. In theDelete reservation? dialog, enterDelete and thenclickDELETE.

SQL

Sets the options on BI Engine capacity.

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

    Go to BigQuery

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

    ALTERBI_CAPACITY`PROJECT_ID.LOCATION_ID.default`SETOPTIONS(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: thelocation where data needs to be cached, prefixed withregion-. Examples:region-us,region-us-central1.

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

  3. ClickRun.

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

bq

Use thebq update commandand 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

Verify BI Engine information

You can get information about your BI Engine capacity by queryingtheINFORMATION_SCHEMA tables.

Verify reservation status

To verify the status of your reservation, including a set of preferred tables,view theINFORMATION_SCHEMA.BI_CAPACITIES view using a SQL query. Forexample:

SELECT*FROM`<PROJECT_ID>.region-<REGION>.INFORMATION_SCHEMA.BI_CAPACITIES`;

In the Google Cloud console, the result of this SQL query looks similar to thefollowing:

image

View reservation changes

To view the history of changes for a particular reservation, use theINFORMATION_SCHEMA.BI_CAPACITY_CHANGES view using a SQL query. For example:

SELECT*FROM`<PROJECT_ID>.region-<REGION>.INFORMATION_SCHEMA.BI_CAPACITY_CHANGES`ORDERBYchange_timestampDESCLIMIT3;

In the Google Cloud console, the result of this SQL query looks similar to thefollowing:

results rows with change_timestamp project_id project_number

What's next

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2026-02-05 UTC.