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 flat-rate commitment, you will be eligible for discount of up to 100% based on active commitment. For details see flat-rate pricing.
You purchase 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.
Create a reservation
To reserve on-demand BI Engine capacity:
Console
In the Google Cloud console, under Administration go to the BI Engine page.
Click
Create reservation.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.
Click Next.
Optionally, specify the Table ID for preferred tables
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 BigQuery BI Engine reservation capacity.
In the Google Cloud console, go to the BigQuery page.
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 withregion-
. Examples:region-us
,region-us-central1
.VALUE
: theINT64
size of the reservation for BI Engine capacity in gigabytes, 1 to 250 GB. SettingVALUE
replaces the existing value if there is one. Setting toNULL
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.
Click
Run.
For more information about how to run queries, see Running interactive queries.
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 is the reservation memory capacity in gigabytes, 1 to 250 GB
- LOCATION is the location of the dataset you are querying
Delete a reservation
To delete a capacity reservation:
Console
In the Google Cloud console, under Administration go to the BI Engine page.
In the Reservations section, locate your reservation.
In the Actions column, click the
icon to the right of your reservation and choose Delete.In the Delete reservation? dialog, enter Delete and then click DELETE.
SQL
Sets the options on BigQuery BI Engine capacity.
In the Google Cloud console, go to the BigQuery page.
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 withregion-
. Examples:region-us
,region-us-central1
.
When you delete all capacity reservations in a project, BI Engine is disabled for that project.
Click
Run.
For more information about how to run queries, see Running interactive queries.
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
Where:
- PROJECT_ID is the ID of your project.
- LOCATION is 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:
INFORMATION_SCHEMA.BI_CAPACITIES
contains metadata about the current state of BI Engine capacity.INFORMATION_SCHEMA.BI_CAPACITY_CHANGES
contains a history of changes to the BI Engine capacity.
What's next
- For an overview of BigQuery BI Engine, see Introduction to BigQuery BI Engine.
- Learn more about BigQuery BI Engine pricing.
- Quickstart using Looker Studio with BI Engine.