Introduction to BI Engine

BigQuery BI Engine is a fast, in-memory analysis service that accelerates manySQL queries in BigQuery by intelligently caching the data you use mostfrequently. BI Engine can accelerate SQL queries from any source,including those written by data visualization tools, and can manage cachedtables for ongoing optimization. This lets you improve query performancewithout manual tuning or data tiering. You canclusterandpartition tables to further optimizeBI Engine performance for large tables.

For example, if your dashboard only displays the last quarter's data, thenyou could partition your tables by time so only the latest partitions areloaded into memory. You can also combine the benefits ofmaterializedviews and BI Engine.This works particularly well when the materialized views are used to join andflatten data to optimize their structure for BI Engine.

BI Engine provides the following advantages:

• BigQuery API compatibility: BI Engine directly integrates with theBigQuery API. Any BI solution or custom application that workswith the BigQuery API through standard mechanisms such asRESTorJDBC and ODBC driverscan use BI Engine without modification.
• Vectorized runtime: Using vectorized processing in an execution enginemakes more efficient use of modern CPU architecture, by operating on batchesof data at a time. BI Engine also uses advanced dataencodings, specifically dictionary run-length encoding, to further compressthe data that's stored in the in-memory layer.
• Seamless integration: BI Engine works withBigQuery features and metadata, including authorized views,column-level security, and data masking.
• Reservation allocations: BI Engine reservations separatelymanage memory allocation for each project and region. BI Engineonly caches the queried, required parts of columns and partitions. You canspecify which tables use BI Engine acceleration withpreferred tables.

In most organizations, BI Engine is enabled by a billingadministrator who must reserve capacity for BI Engineacceleration with an appropriateedition.To learn more, seeReserve BI Engine capacity.

BI Engine use cases

BI Engine can significantly accelerate many SQL queries, includingthose used for BI dashboards. Acceleration is most effective if you identify thetables that are essential to your queries, and then mark them aspreferredtables. To useBI Engine, you create a reservation in a region and specify itssize. You can let BigQuery determine which tables to cache basedon the project's usage patterns or you can specify tables to prevent othertraffic from interfering with their acceleration.

BI Engine is useful in the following use cases:

• You use BI tools to analyze your data: BI Engine accelerateBigQuery queries whether they run in theBigQuery console, a BI tool such as Looker Studio orTableau, or a client library, API, or an ODBC or JDBC connector. This cansignificantly improve the performance of dashboards connected toBigQuery through a built-in connection (API) or connectors.
• You have frequently queried tables:BI Engine lets you designate preferred tables toaccelerate. This is helpful if you have a subset of tables that are queriedmore frequently or are used for high-visibility dashboards.

BI Engine might not fit your needs in the following cases:

• You use wildcards in your queries: Queries referencing wildcard tables arenot supported by BI Engine and don't benefit from acceleration.
• You require BigQuery features unsupported by BI Engine: While BI Engine supports most SQL functions and operators,BI Engine unsupported featuresinclude external tables, row-level security, and non-SQL user-defined functions.

Considerations for BI Engine

Consider the following when deciding how to configure BI Engine:

Ensure acceleration for specific queries

To ensure a set of queries are accelerated, create a separate project with adedicated BI Engine reservation. First,estimate the computecapacity required for your queries,then designate those tables aspreferred tablesfor BI Engine.

Minimize your joins

BI Engine works best for pre-joined or pre-aggregated data, andfor queries with a small number of joins. This is particularly true when oneside of the join is large and the others are much smaller, such as when youquery a large fact table joined with smaller dimension tables. You can combineBI Engine withmaterialized views,which perform joins to produce a single large, flat table. In this way, the samejoins aren't performed for each query. Stale materialized views are recommendedfor optimal query performance.

Understand the impact of BI Engine

To understand your use of BI Engine, seeMonitor BI Engine with Cloud Monitoring,or query theINFORMATION_SCHEMA.BI_CAPACITIESandINFORMATION_SCHEMA.BI_CAPACITY_CHANGESviews. Be sure to disable theUse cached results option inBigQuery to get the most accurate comparison. For moreinformation, seeUse cached query results.

Preferred tables

BI Engine preferred tables let you limit BI Engineacceleration to a specified set of tables. Queries to all other tables useregular BigQuery slots. For example, with preferred tables youcan accelerate only the tables and dashboards that you identify as important toyour 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 beenaccessed recently. This process frees memory for new queries that needacceleration.

Preferred tables limitations

BI Engine preferred tables have the following limitations:

• You cannot add views to the preferred tables reservation list.BI Engine preferred tables only support tables.
• Queries to materialized views are only accelerated if both thematerialized 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.
• Queries that access multiple tables are only accelerated if all tables arepreferred tables. For example, all tables in a query with aJOIN must bein the preferred tables list to be accelerated. If even one table is notin the preferred list, then the query cannot use BI Engine.
• Public datasets are not supported in the Google Cloud console. To add apublic table as a preferred table, use the API or the DDL.

Limitations

To use BI Engine, your organization must purchaseBI Engine capacity by creating a BI Enginereservation with a supported edition. For more information, seeUnderstand BigQuery editions.

In addition, BigQuery BI Engine has the following limitations.

Joins

BI Engine accelerates certain types of join queries. Accelerationhappens on leaf-level subqueries withINNER andLEFT OUTER JOINS,where a large fact table is joined with up to four smaller, "dimension" tables.Small dimension tables have the following restrictions:

• Less than 5 million rows
• Size limit:
  • Unpartitioned tables: 5 GiB or less
  • Partitioned tables: Referenced partitions 1 GB or less

Window functions

Window functions,also known as analytical functions, have the following limitations whenaccelerated by BigQuery BI Engine:

• The input stages are accelerated by BigQuery BI Engine if they don't havewindow functions. In this caseINFORMATION_SCHEMA.JOBS view reportsbi_engine_statistics.acceleration_mode asFULL_INPUT.
• The input stages of queries with window functions in their input stagesare accelerated by BI Engine, but don't have the limitationsdescribed inBI Engine Window functions limitations.In that case, the input stages or the full query is executed inBI Engine. In this caseINFORMATION_SCHEMA.JOBS view reportsbi_engine_statistics.acceleration_mode asFULL_INPUT orFULL_QUERY.

For more information about theBiEngineStatistics field, see theJob reference.

BI Engine window functions limitations

Queries with window functions only run in BI Engine if all of thefollowing conditions are true:

• The query scans exactly one table.
  • The table is not partitioned.
  • The table has less than 5 million rows.
• The query has noJOIN operators.
• The scanned table size times the number of window function operators doesnot exceed 300 MiB.

Two window functions with identicalOVER clauses and thesame direct inputs can share the same window function operator. For example:

• SELECT ROW_NUMBER() OVER (ORDER BY x), SUM(x) OVER (ORDER BY x)FROM my_table has only one window function operator.
• SELECT ROW_NUMBER() OVER (ORDER BY x), SUM(x) OVER (PARTITION BYy ORDER BY x) FROM my_table has two window function operators because thetwo functions have differentOVER clauses.
• SELECT ROW_NUMBER() OVER (ORDER BY x) FROM (SELECT SUM(x) OVER(ORDER BY x) AS x FROM my_table) has two window function operators becausethe two functions have different direct inputs although theirOVER clausesappear the same.

Supported window functions

The following referenced window functions are supported:

• ANY_VALUE
• AVG
• BIT_AND
• BIT_OR
• BIT_XOR
• CORR
• COUNT
• COUNTIF
• COVAR_POP
• COVAR_SAMP
• CUME_DIST
• DENSE_RANK
• FIRST_VALUE
• LAG
• LAST_VALUE
• LEAD
• LOGICAL_AND
• LOGICAL_OR
• MAX
• MIN
• NTH_VALUE
• NTILE
• PERCENT_RANK
• PERCENTILE_CONT
• PERCENTILE_DISC
• RANK
• ROW_NUMBER
• ST_CLUSTERDBSCAN
• STDDEV_POP
• STDDEV_SAMP
• STDDEV
• STRING_AGG
• SUM
• VAR_POP
• VAR_SAMP
• VARIANCE

If window functions aren't supported, then you might see the following error:

Analytic function is incompatible with other operators or its inputs are too large

Other BI Engine limitations

BI Engine acceleration is not available for the following features:

• JavaScript UDFs
• External tables, including BigLake tables
• Querying JSON data - Error message:JSON native type is not supported
• Writing results to a permanent BigQuery table
• Tables containing upserts that useBigQuery change data capture
• Transactions
• Queries that return more than 1 GiB of data. For latency-sensitiveapplications, a response size of less than 1 MiB is recommended.
• Row-level security
• Queries that use theSEARCH functionor are optimized bysearch indexes

Work-around for unsupported features

While some SQL features are not supported in BigQuery BI Engine,there is an available workaround:

1. Write a query in BigQuery.
2. Save the results of the query to a table.
3. Schedule your query to update the table on a regular basis. An hourly or daily refresh rate works best. Refreshing every minute might invalidate the cache too frequently.
4. Reference this table in your performance-critical queries.

Quotas and limits

SeeBigQuery quotas and limitsfor quotas and limits that apply to BI Engine.

Pricing

You incur costs for the reservation that you create for BI Enginecapacity. For information on BI Engine pricing, see theBigQuery Pricing page.

What's next

• To learn how to create your BI Engine reservation, seeReserveBI Engine capacity.
• For information designating preferred tables, seeBI Enginepreferred tables.
• To understand your utilization of BI Engine, seeMonitorBI Engine with Cloud Monitoring.
• Learn aboutBI Engine optimizedfunctions
• Learn how to use BI Engine with the following:
  • Looker Studio
  • Tableau