Monitor BI Engine
BigQuery BI Engine works with Google Cloud services to help monitor and troubleshoot BI Engine use. Looker Studio with BigQuery BI Engine accelleration is fully supported by BigQuery monitoring, including INFORMATION_SCHEMA, execution graph, and Cloud Monitoring metrics.
Cloud Monitoring
BigQuery BI Engine integrates with Cloud Monitoring so you can monitor aggregated BI Engine use in aggregate and configure alerts. For information on using Monitoring to create dashboards for your BI Engine metrics, see Creating charts in the Monitoring documentation.
The following metrics are provided for BigQuery BI Engine:
Resource | Metric | Details |
---|---|---|
BigQuery Project | Reservation Total Bytes | Total capacity allocated to one Google Cloud project |
BigQuery Project | Reservation Used Bytes | Total capacity used in one Google Cloud project |
BigQuery Project | BI Engine Top Tables Cached Bytes | Cache usage per table. This metric displays the top N tables per region report usage. |
You can use Cloud Monitoring to view the traffic for Looker Studio data sources that were cached by BI Engine. For a complete list of available Google Cloud metrics, see Google Cloud metrics.
Looker Studio BigQuery native integration doesn't provide per-model metrics. Instead exposes data source and report IDs in the INFORMATION_SCHEMA tables with BigQuery API monitoring.
Query statistics for BI Engine
This section explains how to find query statistics to help monitor, diagnose, and troubleshoot BI Engine use.
BI Engine acceleration modes
With BI Engine acceleration enabled, your query can run in any one of these four modes:
BI_ENGINE_DISABLED |
BI Engine disabled the acceleration.
biEngineReasons specifies a more detailed reason. The query was
run using the BigQuery execution engine. |
PARTIAL_INPUT |
Part of the query input was accelerated using BI Engine. As
described in
Query optimization and acceleration, a query plan is generally broken down
into multiple input stages. BI Engine supports the common types
of subquery patterns that are typically used in dashboarding. If the query
consists of multiple input stages, only a few of which fall under the
supported use cases, then BI Engine runs the unsupported stages
using the normal BigQuery engine without acceleration. In this
situation, BI Engine returns a PARTIAL
acceleration code, and uses biEngineReasons to populate the reason
for not accelerating other input stages. |
FULL_INPUT |
All input stages of the query were accelerated using BI Engine. |
FULL_QUERY |
The entire query were accelerated using BI Engine. |
View BigQuery API job statistics
Detailed statistics on BI Engine are available through the BigQuery API.
To fetch the statistics associated with BI Engine accelerated queries, run the following bq command-line tool command:
bq show --format=prettyjson -j job_id
If the project is enabled for BI Engine acceleration, then the
output produces a new field, biEngineStatistics
. Here is a sample job
report:
"statistics": {
"creationTime": "1602175128902",
"endTime": "1602175130700",
"query": {
"biEngineStatistics": {
"biEngineMode": "DISABLED",
"biEngineReasons": [
{
"code": "UNSUPPORTED_SQL_TEXT",
"message": "Detected unsupported join type"
}
]
},
For more information about the BiEngineStatistics
field, see the
Job reference.
BigQuery INFORMATION_SCHEMA statistics
BI Engine acceleration statistics are included in the
BigQuery INFORMATION_SCHEMA
views as part of the INFORMATION_SCHEMA.JOBS_BY_*
views in the
bi_engine_statistics
column.
For example, this query returns the bi_engine_statistics
for all of the
current projects' jobs for the last 24 hours:
SELECT
creation_time,
job_id,
bi_engine_statistics
FROM
`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
AND CURRENT_TIMESTAMP()
AND job_type = "QUERY"
Use the following format to specify
regionality for the
project-id
, region
, and views
in the INFORMATION_SCHEMA
view:
`PROJECT_ID`.`region-REGION_NAME`.INFORMATION_SCHEMA.VIEW
View Looker Studio information schema details
You can track which Looker Studio reports and data sources are
used by BigQuery by viewing the
INFORMATION_SCHEMA.JOBS
view.
When BigQuery native integration is enabled, every
Looker Studio query creates an entry with report_id
and
datasource_id
labels. Those IDs appear at the end of
Looker Studio URL when opening a report or data source page.
For example, report with URL
https://lookerstudio.google.com/navigation/reporting/my-report-id-123
has the
have a report ID of "my-report-id-123"
.
The following examples show how to view reports and data sources:
Find report and data source URL for each Looker Studio BigQuery job
-- Standard labels used by Looker Studio. DECLARE requestor_key STRING DEFAULT 'requestor'; DECLARE requestor_value STRING DEFAULT 'looker_studio'; CREATE TEMP FUNCTION GetLabel(labels ANY TYPE, label_key STRING) AS ( (SELECT l.value FROM UNNEST(labels) l WHERE l.key = label_key) ); CREATE TEMP FUNCTION GetDatasourceUrl(labels ANY TYPE) AS ( CONCAT("https://lookerstudio.google.com/datasources/", GetLabel(labels, 'looker_studio_datasource_id')) ); CREATE TEMP FUNCTION GetReportUrl(labels ANY TYPE) AS ( CONCAT("https://lookerstudio.google.com/reporting/", GetLabel(labels, 'looker_studio_report_id')) ); SELECT job_id, GetDatasourceUrl(labels) AS datasource_url, GetReportUrl(labels) AS report_url, FROM `region-us`.INFORMATION_SCHEMA.JOBS jobs WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND GetLabel(labels, requestor_key) = requestor_value LIMIT 100;
View jobs produced by using a report and data source
-- Specify report and data source id, which can be found in the end of Looker Studio URLs. DECLARE user_report_id STRING DEFAULT '*report id here*'; DECLARE user_datasource_id STRING DEFAULT '*datasource id here*'; -- Standard labels Looker Studio uses in native integration. DECLARE requestor_key STRING DEFAULT 'requestor'; DECLARE requestor_value STRING DEFAULT 'looker_studio'; DECLARE datasource_key STRING DEFAULT 'looker_studio_datasource_id'; DECLARE report_key STRING DEFAULT 'looker_studio_report_id'; CREATE TEMP FUNCTION GetLabel(labels ANY TYPE, label_key STRING) AS ( (SELECT l.value FROM UNNEST(labels) l WHERE l.key = label_key) ); SELECT creation_time, job_id, FROM `region-us`.INFORMATION_SCHEMA.JOBS jobs WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND GetLabel(labels, requestor_key) = requestor_value AND GetLabel(labels, datasource_key) = user_datasource_id AND GetLabel(labels, report_key) = user_report_id ORDER BY 1 LIMIT 100;
Cloud Logging
You can view log information about BI Engine by viewing it on the Cloud Logging page of the Google Cloud console. How you view log information for BI Engine depends upon the tool that is querying the data:
- For Looker Studio queries without Looker Studio
native integration enabled: You can view log information on the
Cloud Logging page with a payload of
protoPayload.serviceName="bigquerybiengine.googleapis.com"
. - For all other traffic: You can view log information on the
Cloud Logging page with a payload of
protoPayload.serviceName="bigquery.googleapis.com"
.
What's next
- Learn more about Cloud Monitoring.
- Learn more about Monitoring charts.
- Learn more about Monitoring alerts.
- Learn more about Cloud Logging.