BI Engine preferred tables

The BigQuery BI Engine preferred tables feature lets you limit BI Engine acceleration to a specified set of tables. Queries to all other tables use regular BigQuery slots. For example, with preferred tables you can accelerate only the tables and dashboards that you identify as important to your business.

If there is not enough RAM in the project to hold all of the preferred tables, BI Engine offloads partitions and columns that haven't been accessed recently. This process frees memory for new queries that need acceleration.

Specify preferred tables

To specify a set of preferred tables, you can use the following options:

  • The Google Cloud console
  • A data definition language (DDL) statement in SQL

Specify preferred tables in the Google Cloud console

You can specify a set of preferred tables when you create a new reservation or by editing an existing reservation. You can choose tables from within the project that contains the BI Engine reservation, or any other BigQuery table.

Specify preferred tables for new reservations

To specify a set of tables for acceleration when you create a new reservation, follow these steps:

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

    Go to BigQuery

  2. In the BigQuery navigation menu, click BI Engine.

  3. Click Create reservation.

  4. On the Create reservation page, specify options for Location and GiB of Capacity, and then click Next.

    The Preferred Tables (Optional) section is displayed.

  5. In the Table Id field, specify the table that you 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. Click Next.

  7. To create a reservation with preferred tables, click Create.

Specify preferred tables for existing reservations

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

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

    Go to BigQuery

  2. In the BigQuery navigation menu, click BI Engine.

    If your project is configured for preferred tables, a set of tables is displayed in the Preferred Tables column.

    image

  3. On the row for the reservation that you want to edit, click the icon in the Actions column, and then select Edit.

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

Specify preferred tables using SQL

You can use the ALTER BI_CAPACITY SET OPTIONS DDL statement to modify a set of preferred tables and the reservation size:

ALTER BI_CAPACITY `<PROJECT_ID>.region-<REGION>.default`
SET OPTIONS (
  size_gb = 100,
  preferred_tables =
    ['TABLE_PROJECT_ID.DATASET.TABLE1',
    'TABLE_PROJECT_ID.DATASET.TABLE2']);

Replace the following:

  • TABLE_PROJECT_ID: the Google Cloud project ID that contains the accelerated table
  • DATASET: the dataset that contains the accelerated table
  • TABLE#: the table that you want to accelerate

Verify with SQL

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

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

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

image

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

SELECT
  *
FROM
  `<PROJECT_ID>.region-<REGION>.INFORMATION_SCHEMA.BI_CAPACITY_CHANGES`
ORDER BY
  change_timestamp DESC
LIMIT 3;

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

results rows with change_timestamp project_id project_number

Limitations

BI Engine preferred tables have the following limitations:

  • You cannot add views into the preferred tables reservation list. BI Engine preferred tables only support tables.
  • Queries to materialized views are only accelerated if both the materialized views and their base tables are in the preferred tables list.
  • Specifying partitions or columns for acceleration is not supported.
  • JSON type columns are unsupported and are not accelerated by BI Engine.
  • All of the tables in a JOIN query must be in the preferred tables list to be accelerated. If even one table is not part of the preferred list, then the query cannot use BI Engine.
  • Public datasets are not supported in the Google Cloud console. To add a public table as a preferred table, use the API or the DDL.

Pricing

For information on BI Engine pricing, see the BI Engine pricing page.

What's next