BI Engine 可與任何商業智慧 (BI) 工具整合，包括 Looker、Tableau、Power BI 和自訂應用程式，加快資料探索和分析的速度。
BI Engine 用途
BI Engine 可大幅加快許多 SQL 查詢的速度，包括用於 BI 資訊主頁的查詢。如要有效加速，請找出對查詢至關重要的資料表，然後將其標示為偏好資料表。如要使用 BI Engine，請建立預留項目，定義 BI Engine 專用的儲存空間容量。您可以讓 BigQuery 根據專案的使用模式決定要快取哪些資料表，也可以標記特定資料表，避免其他流量干擾加速作業。
您可以建立使用 BI Engine 保留項目的專案，確保特定查詢集一律加快速度。為此，您必須確保該專案中的 BI Engine 預留項目大小足以與這些查詢中使用的所有資料表大小相符，並將這些資料表指定為 BI Engine 的偏好資料表。請只在該專案中執行需要加速的查詢。
盡量減少加入運算
BI Engine 最適合用於預先彙整或預先彙整的資料，以及少量彙整資料。當一個連接一端的資料量很大，而另一端的資料量較小時，這一點尤其正確，例如當您查詢與小型維度資料表連接的大型事實資料表時。您可以將 BI Engine 與具象化檢視畫面結合，執行彙整作業，產生單一大型平面表格。如此一來，就不必對每個查詢執行相同的彙整作業。
[[["容易理解","easyToUnderstand","thumb-up"],["確實解決了我的問題","solvedMyProblem","thumb-up"],["其他","otherUp","thumb-up"]],[["難以理解","hardToUnderstand","thumb-down"],["資訊或程式碼範例有誤","incorrectInformationOrSampleCode","thumb-down"],["缺少我需要的資訊/範例","missingTheInformationSamplesINeed","thumb-down"],["翻譯問題","translationIssue","thumb-down"],["其他","otherDown","thumb-down"]],["上次更新時間：2025-09-04 (世界標準時間)。"],[[["\u003cp\u003eBI Engine is an in-memory analysis service that speeds up SQL queries in BigQuery by caching frequently used data, improving query performance without manual tuning.\u003c/p\u003e\n"],["\u003cp\u003eBI Engine integrates directly with the BigQuery API and can be used with various BI tools and custom applications, accelerating data exploration and analysis, particularly for dashboards.\u003c/p\u003e\n"],["\u003cp\u003eThe system uses vectorized processing and advanced data encodings to optimize performance and data compression in the in-memory layer.\u003c/p\u003e\n"],["\u003cp\u003eBI Engine works seamlessly with BigQuery features like authorized views, column-level security, and data masking, but does not support queries with wildcards or certain BigQuery features like external tables and non-SQL user-defined functions.\u003c/p\u003e\n"],["\u003cp\u003eUsers can optimize BI Engine's performance by partitioning tables, using materialized views, and designating preferred tables for acceleration, as well as monitoring usage statistics to understand its impact.\u003c/p\u003e\n"]]],[],null,["# What is BI Engine?\n==================\n\nBigQuery BI Engine is a fast, in-memory analysis service that accelerates many\nSQL queries in BigQuery by intelligently caching the data you use most\nfrequently. BI Engine can accelerate SQL queries from any source,\nincluding those written by data visualization tools, and can manage cached\ntables for on-going optimization. This lets you improve query performance\nwithout manual tuning or data tiering. You can use [clustering](/bigquery/docs/clustered-tables)\nand [partitioning](/bigquery/docs/partitioned-tables) to further optimize the\nperformance of large tables with BI Engine.\n\nFor example, if your dashboard only displays the last quarter's data, then\nconsider partitioning your tables by time so only the latest partitions are\nloaded into memory. You can also combine the benefits of [materialized\nviews](/bigquery/docs/materialized-views-intro) and BI Engine.\nThis works particularly well when the materialized views are used to join and\nflatten data to optimize their structure for BI Engine.\n\nBI Engine provides the following advantages:\n\n1. **BigQuery API:** BI Engine directly integrates with the BigQuery API. Any BI solution or custom application that works with the BigQuery API through standard mechanisms such as [REST](/bigquery/docs/reference/rest) or [JDBC and ODBC drivers](/bigquery/docs/reference/odbc-jdbc-drivers) can use BI Engine without modification.\n2. **Vectorized runtime:** With the BI Engine, BigQuery uses a modern technique called *vectorized processing*. Using vectorized processing in an execution engine makes more efficient use of modern CPU architecture, by operating on batches of data at a time. BI Engine also uses advanced data encodings, specifically, dictionary and run-length encoding, to further compress the data that's stored in the in-memory layer.\n3. **Seamless integration:** BI Engine works with BigQuery features and metadata, including authorized views, column and row level security, and data masking.\n4. **Reservations:** BI Engine reservations manage memory allocation at the project location level. BI Engine caches specific columns or partitions that are queried, prioritizing those in tables marked as preferred.\n\n### BI Engine architecture\n\nBI Engine integrate with any business intelligence (BI) tool,\nincluding such as Looker, Tableau,Power BI, and custom applications to\naccelerate data exploration and analysis.\n\nBI Engine use cases\n-------------------\n\nBI Engine can significantly accelerate many SQL queries, including\nthose used for BI dashboards. Acceleration is most effective if you identify\nthe tables that are essential to your queries and then mark them as [preferred\ntables](/bigquery/docs/bi-engine-preferred-tables). To use\nBI Engine, create a reservation that defines the storage\ncapacity dedicated to BI Engine. You can let\nBigQuery determine which tables to cache based on the project's\nusage patterns or you can mark specific tables to prevent other traffic\nfrom interfering with acceleration.\n\nBI Engine is useful in the following use cases:\n\n- **You use BI tools to analyze your data**: The BI Engine can accelerate BigQuery queries regardless of whether they run in the BigQuery console, client library, or through an API or an ODBC or JDBC connector. This can significantly improve the performance of dashboards connected to BigQuery through a built-in connection (API) or connectors.\n- **You have certain tables that are queried most frequently**: BI Engine lets you designate specific preferred tables to accelerate. This is helpful if you have a subset of tables that are queried more frequently or are used for high-visibility dashboards.\n\nBI Engine might not fit your needs in the following cases:\n\n- **You use wildcards in your queries**: Queries referencing wildcard tables are\n not supported by BI Engine and don't benefit from acceleration.\n\n- **You rely heavily on unsupported BigQuery features** : While\n BI Engine supports most [SQL functions and\n operators](/bigquery/docs/bi-engine-optimized-sql) when connecting business\n intelligence (BI) tools to BigQuery, there are [unsupported\n features](/bigquery/docs/bi-engine-optimized-sql#unsupported-features),\n including external tables and non-SQL user-defined functions.\n\nConsiderations for BI Engine\n----------------------------\n\nConsider the following when deciding how to configure BI Engine:\n\n### Ensure acceleration for specific queries\n\nYou can ensure a particular set of queries always gets accelerated by creating a\nseparate project with a BI Engine reservation. To do so, you\nshould ensure that the BI Engine reservation in that project is\nlarge enough to match the size of all tables used in those queries and\ndesignate those tables as [preferred\ntables](/bigquery/docs/bi-engine-preferred-tables) for BI Engine.\nOnly those queries that need to be accelerated should be run in that project.\n\n### Minimize your joins\n\nBI Engine works best with pre-joined or pre-aggregated, and with\ndata in a small number of joins. This is\nparticularly true when one side of the join is large and the others are much\nsmaller such as when you query a large fact table joined with a small dimension\ntable. You can combine BI Engine with [materialized\nviews](/bigquery/docs/materialized-views-intro) that which perform joins\nto produce a single large, flat table. In this way, the same joins don't have\nto be performed on every query.\n\n### Understand the impact of BI Engine\n\nYou can better understand how your workloads benefit from\nBI Engine by [reviewing the usage statistics in\nCloud Monitoring](/bigquery/docs/bi-engine-intro#limitations)\nor querying the [INFORMATION_SCHEMA](/bigquery/docs/bi-engine-monitor#information_schema)\nin BigQuery. Be sure to disable the **Use cached results**\noption in BigQuery to get the most accurate comparison. For more\ninformation, see [Use cached query results](/bigquery/docs/cached-results).\n\nLimitations\n-----------\n\nQueries that contain the\n[`VECTOR_SEARCH` function](/bigquery/docs/reference/standard-sql/search_functions#vector_search)\naren't accelerated by [BigQuery BI Engine](/bigquery/docs/bi-engine-intro).\n\nQuotas and limits\n-----------------\n\nSee [BigQuery quotas and limits](/bigquery/quotas#biengine-limits)\nfor quotas and limits that apply to BI Engine.\n\nPricing\n-------\n\nFor information on BI Engine pricing, see the\n[BigQuery Pricing](/bigquery/pricing#bi_engine_pricing) page.\n\nQuery optimization and acceleration\n-----------------------------------\n\n| **Note:** For BI Engine maximum reservation size, see [quotas and limits](/bigquery/quotas#biengine-limits).\n\nBigQuery, and by extension BI Engine, breaks down\nthe query plan that's produced for a SQL query into subqueries. A subquery\ncontains a number of operations, such as scanning, filtering, or aggregating\ndata, and is often the unit of execution on a shard.\n\nWhile all of BigQuery's supported SQL queries are correctly\nexecuted by the BI Engine, only certain subqueries are optimized. In\nparticular, BI Engine is most optimized for leaf-level subqueries\nthat scan the data from storage, and perform operations such as filter, compute,\naggregation, order-by, and certain types of joins. Other subqueries that are not\nyet fully accelerated by BI Engine revert back to\nBigQuery for execution.\n\nBecause of this selective optimization, simpler business intelligence or\ndashboard-type queries benefit the most from BI Engine (resulting\nin fewer subqueries) because the majority of the execution time is spent on\nleaf-level subqueries that process raw data.\n\nWhat's next\n-----------\n\n- Learn about [BI Engine optimized functions](/bigquery/docs/bi-engine-optimized-sql).\n- To learn how to create your BI Engine reservation, see [Reserve\n BI Engine capacity](/bigquery/docs/bi-engine-reserve-capacity).\n- For information designating preferred tables, see [BI Engine\n preferred tables](/bigquery/docs/bi-engine-preferred-tables).\n- To understand your utilization of BI Engine, see [Monitor BI Engine with Cloud Monitoring](/bigquery/docs/bi-engine-monitor)."]]
