Interpreting BigQuery BI Engine Metrics
What is BI Engine?
BigQuery BI Engine is a fast, in-memory analysis service that accelerates many SQL queries in BigQuery by intelligently caching the data that is used most frequently. BI Engine can accelerate SQL queries from any source, including those written by data visualization tools, and can manage cached tables for on-going optimization.
Learn more about: What is BI Engine? | BigQuery | Google Cloud
BI Engine provides the following benefits
- In-memory intelligent caching to accelerate analytical queries
- Higher throughput for well optimized queries
- Easy to manually scale the capacity as required by the use-case
Reserving BI Engine capacity
It is very straightforward to reserve or edit BI Engine capacity by a click of a button.
The reservation is attached to a project identified when the reservation is created.
For more information on: Reserving BI Engine capacity | BigQuery | Google Cloud
Once we reserve BI Engine capacity, the queries in the project start getting accelerated via BI Engine.
After running a SQL query, it is observed that BI Engine was able to accelerate the query from the query job information.
But how can we understand the impact of BI Engine at a deeper level?
Interpreting BI Engine utilization
We can use the below ways to analyse & interpret BI Engine usage & performance.
1. Monitoring Dashboards
We can use monitoring dashboards for better understanding of the utilization of BI Engine.
For example: we could create a chart in Cloud Monitoring plotting the:
bigquerybiengine.googleapis.com/reservation/used_bytes over the
bigquerybiengine.googleapis.com/reservation/total_bytes.
For more information please refer -Monitor BI Engine with Cloud Monitoring | BigQuery | Google Cloud
For a complete list of available Google Cloud metrics, see Google Cloud metrics.
Insights from this can help:
- Check the total BI Engine cache capacity that is used in one Google Cloud project
- Analyse the capacity required as per utilization patterns and scale it accordingly
- Understand cache usage per table. This metric displays the top N tables per region report usage. For more information details here
- Analyse the need to add any tables in preferred tables
Alerting
We have reserved the BI Engine capacity for the project and location that’s running SQL queries from BQ and other BI applications. While it is expected for the BI Engine utilization to fluctuate, we may want to proactively monitor the peaks that have observed a high utilization for a significant time period due to reasons such as:
- Monitoring and alerting when BI Engine utilization goes above 90%.
- Reservation utilization of specific tables going extremely under or above expected value.
To ensure that the team is notified when the BI Engine utilization is consistently crossing such high thresholds, we can create the following alerting policy:
- We can create alerts on a specific project or location we want to monitor utilization threshold going above or under a specific value.
In the below example, we have added alerts for metric: BigQuery Project — Reservation used bytes >90%
2. We can create alerts on a specific table if we want to monitor utilization threshold going above/ under a specific value or absent in Top N values to indicate & analyse the cause of why it is not getting accelerated as intended.
In the below example, we have added alerts for a specific table for metric: BigQuery Project — Reservation used bytes by table — metric absence
2. INFORMATION_SCHEMA
We can also view Acceleration statistics from INFORMATION_SCHEMA. To better understand the reasons for BI Engine acceleration leverage the statistics in INFORMATION_SCHEMA.
For more details, please refer: Monitor BI Engine with Cloud Monitoring | BigQuery | Google Cloud
Following are examples of some reason codes-
Useful links
Below are some useful resources you can use for more information related to BI Engine.
Pricing: Pricing | BigQuery: Cloud Data Warehouse
Google public documentation:
Optimized SQL functions and operators | BigQuery | Google Cloud
Method: projects.locations.updateBiReservation | BigQuery | Google Cloud
Google cloud blogs:
Accelerate your Looker dashboards with BigQuery BI Engine | Google Cloud Blog
Medium Blogs:
Google BI Engine Statistics Dashboard | by Philipp Heinrich | Google Cloud — Community | Medium