データ分析

BI Engine による BigQuery クエリのパフォーマンスの強化方法

2022年7月7日

https://storage.googleapis.com/gweb-cloudblog-publish/images/BI_Engine.max-2600x2600.jpg

Google Cloud Japan Team

※この投稿は米国時間 2022 年 6 月 25 日に、Google Cloud blog に投稿されたものの抄訳です。

BigQuery BI Engine は、BigQuery に直接組み込まれた高速のメモリ分析サービスです。ユーザーは BigQuery に保存されているデータを短い応答時間と高い同時実行性で分析して、特定の BigQuery SQL クエリを加速できるようになります。BI Engine は、クエリ結果の代わりにデータをキャッシュに保存するため、データのさまざまな側面を確認するときに、同じデータの異なるクエリが高速化されます。BI Engine を BigQuery ストリーミングで使用することにより、書き込み速度やデータの更新頻度を損なうことなく、ストリーミングデータをリアルタイムでデータ分析できます。

BI Engine のアーキテクチャ

BI Engine SQL インターフェースにより、BI Engine のサポートが、Looker、Tableau、Power BI、カスタムアプリケーションなどの BigQuery と連携して、データ探索と分析を高速化するビジネスインテリジェンス（BI）ツールに拡張されます。BI Engine を使用すると、パフォーマンス、スケール、セキュリティ、またはデータの更新頻度を損なうことなく、高機能かつインタラクティブなダッシュボードとレポートを好きな BI ツールで作成できます。BI Engine SQL インターフェースについての詳細は、こちらをご参照ください。

次の図は、BI Engine の更新されたアーキテクチャを示しています。

https://storage.googleapis.com/gweb-cloudblog-publish/images/1_BI_Engine.max-1200x1200.jpg

ここで示されているのは、BI Engine の容量の予約を使用して作成された Looker ダッシュボード（上）と予約なしの同じダッシュボード（下）の簡単な例です。このダッシュボードは、BigQuery の一般公開データセット「bigquery-public-data.chicago_taxi_trips」から作成され、 total_trip コストの総計と、total_trip コストの対数平均を経時的に分析しています。

https://storage.googleapis.com/gweb-cloudblog-publish/original_images/2_BI_Engine.gif

過去 5 年間の total_trip コスト

BI Engine は、クエリを解決して、予約の容量を最大化できるだけの最小量のデータをキャッシュに保存します。ビッグデータでビジネスインテリジェンスを実行することは、一筋縄ではいかない場合があります。

これは、同じ一般公開データセット、「bigquery-public-data.chicago_taxi_trips.taxi_trips」に対するクエリで、予約済みの BigQuery スロットがある場合とない場合の BI Engine のパフォーマンスを示しています。

クエリの例

読み込んでいます...

SELECT
 (DATE(trip_end_timestamp , 'America/Chicago')) AS trip_end_timestamp_date,
 (DATE(trip_start_timestamp , 'America/Chicago')) AS trip_start_timestamp_date,
 COALESCE(SUM(CAST(trip_total AS FLOAT64)), 0) AS sum_trip_total,
 CONCAT ('Hour :',(DATETIME_DIFF(trip_end_timestamp,trip_start_timestamp,DAY) * 1440) ,' , ','Day :',(DATETIME_DIFF(trip_end_timestamp,trip_start_timestamp,DAY)) ) AS trip_time,
 CASE WHEN
   ROUND(fare + tips + tolls + extras) = trip_total THEN 'Tallied'
   WHEN ROUND(fare + tips + tolls + extras) < trip_total THEN 'Tallied Less'
   WHEN ROUND(fare + tips + tolls + extras) > trip_total THEN 'Tallied More'
   WHEN (ROUND(fare + tips + tolls + extras) = 0.0 AND trip_total = 0.0) THEN 'Tallied 0'
   ELSE 'N/A' END AS trip_total_tally,
 REGEXP_REPLACE(TRIM(company),'null','N/A') as company,
 CASE WHEN
 TRIM(payment_type) = 'Unknown' THEN 'N/A'
 WHEN payment_type IS NULL THEN 'N/A' ELSE payment_type END AS payment_type
 FROM
  `bigquery-public-data.chicago_taxi_trips.taxi_trips`
 GROUP BY
   1,
   2,
   4,
   5,
   6,
   7
ORDER BY
 1 DESC,
 2 ,
 4 DESC,
 5 ,
 6 ,
 7
LIMIT 5000

上記のクエリは、次の組み合わせで実行されました。

BigQuery スロット予約 / BI Engine 予約がない場合、そのクエリでは、予約ありの実行と比較して、平均スロットが 7.6 倍、ジョブのランタイムが 6.3 倍、増加しました（結果の最終統計）。
BI Engine 予約がなしで、BigQuery スロット予約がある場合、のクエリでは、予約がある場合の実行と比較して、平均スロットが 6.9 倍、ジョブのランタイムが 5.9 倍、増加しました（結果の最終統計）。
BI エンジンの予約があり、BigQuery スロットの予約がない場合、そのクエリでは、平均スロットが 1.5 増え、ジョブは 1 秒以内（868 ミリ秒）で完了しました。
BI Engine 予約と BigQuery スロット予約の両方がある場合、平均 23 のスロットのみが使用され、結果に示されているように、ジョブは 1 秒以内に完了しました。これは、平均スロットとランタイムにおいて、他のすべてのオプションと比較して、最も費用体効果に優れています（平均スロットが 23.27、ランタイムが 855 ミリ秒）。

https://storage.googleapis.com/gweb-cloudblog-publish/images/3_BI_Engine.max-2000x2000.jpg

INFORMATION_SCHEMA は、データセット、ルーティン、テーブル、ビュー、ジョブ、予約、ストリーミングデータに関するメタデータへのアクセスを可能にする一連のビューです。INFORMATION_SCHEMA.JOBS_BY_* ビューをクエリすると、BigQuery ジョブに関するリアルタイムのメタデータを取得できます。このビューには、現在実行中のジョブと、過去 180 日間に完了したジョブの履歴が含まれます。

bi_engine_statistics とスロット数を決定するためのクエリです。スキーマについての詳細情報は、こちらでご確認いただけます。

読み込んでいます...

SELECT
 project_id,
 job_id,
 reservation_id,
 job_type,
 TIMESTAMP_DIFF(end_time, creation_time, MILLISECOND) AS job_duration_mseconds,
 CASE
 WHEN job_id = 'bquxjob_54033cc8_18164d54ada' THEN  'YES_BQ_RESERV_NO_BIENGINE'
 WHEN job_id = 'bquxjob_202f17eb_18149bb47c3' THEN 'NO_BQ_RESERV_NO_BIENGINE'
 WHEN job_id = 'bquxjob_404f2321_18164e0f801' THEN 'YES_BQ_RESERV_YES_BIENGINE'
WHEN job_id = 'bquxjob_48c8910d_18164e520ac' THEN 'NO_BQ_RESERV_YES_BIENGINE' ELSE 'NA'  END as query_method,
 bi_engine_statistics,
 -- ジョブ別の平均スロット使用率を計算するには、
-- total_slot_ms をジョブの持続期間（ミリ秒単位）で割ります
 SAFE_DIVIDE(total_slot_ms,(TIMESTAMP_DIFF(end_time, start_time, MILLISECOND))) AS avg_slots
FROM
region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT
where  creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 80 DAY) AND CURRENT_TIMESTAMP()
AND end_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP()
ANd job_id in ('bquxjob_202f17eb_18149bb47c3','bquxjob_54033cc8_18164d54ada','bquxjob_404f2321_18164e0f801','bquxjob_48c8910d_18164e520ac')
ORDER BY avg_slots DESC

このモニタリングから、BI クエリのパフォーマンスを改善する最も効果的な方法は、BigQuery スロット予約とともに BI ENGINE 予約を使用することです。クエリのパフォーマンスとスループットが向上し、使用するスロット数も少なくなります。BI Engine の容量を予約すると、プロジェクトのスロットを節約できます。

BigQuery BI Engine は、ビジネスインテリジェンス（BI）ツールを BigQuery に接続するときに、標準 SQL 関数と演算子を最適化します。BI Engine の最適化される SQL 関数と演算子は、こちらでご確認いただけます。

Cloud Monitoring で BI Engine をモニタリングする

BigQuery BI Engine は Cloud Monitoring と統合されているため、BI Engine の指標をモニタリングしてアラートを構成できます。

Monitoring を使用して BI Engine の指標のグラフを作成する方法については、Monitoring ドキュメントのグラフの作成をご覧ください。

https://storage.googleapis.com/gweb-cloudblog-publish/images/4_BI_Engine.max-1200x1200.jpg

BI Engine の予約なしで同じクエリを実行したところ、15.47 GB が処理されたことがわかりました。

https://storage.googleapis.com/gweb-cloudblog-publish/images/5_BI_Engine.max-2000x2000.jpg

BI Engine の容量の予約後、[BIE Reservation Used Bytes] ダッシュボードの [Monitoring] で、圧縮率が約 11.74 倍（15.47 GB / 1.317 MB）になりました。ただし、圧縮はデータに大きく依存します。主に圧縮はデータのカーディナリティに依存します。お客様は、データにテストを実行して、圧縮率を決定する必要があります。

https://storage.googleapis.com/gweb-cloudblog-publish/images/6_BI_Engine.max-800x800.jpg

モニタリング指標の「Reservation Total Bytes」は、BI Engine の容量の予約に関する情報を提供し、「Reservation Used Bytes」は、used_bytes の合計に関する情報を提供します。お客様は、この 2 つの指標を活用して、予約に適した容量を導きだせます。

プロジェクトに BI Engine の容量が予約されている場合、BigQuery で実行されているクエリは、BI Engine を使用して、互換性のあるサブクエリのパフォーマンスを高速化します。クエリの高速化のレベルは、次のいずれかのモードにあたります。

BI Engine Mode FULL - BI Engine コンピューティングが使用され、クエリのリーフステージが加速します。ただし、必要なデータがメモリ内にあるか、ディスクからスキャンする必要がある場合があります。BI Engine コンピューティングが使用されている場合でも、BQ スロットがクエリの一部に使用されていることもあります。クエリが複雑になるほど、より多くのスロットが使用されます。このモードでは、BI Engine すべてのリーフステージ（さらに場合によってはすべてのステージ）が実行されます。

BI Engine Mode PARTIAL - BI Engine は、互換性のあるサブクエリを高速化し、BigQuery は BI Engine と互換性のないサブクエリを処理します。また、このモードにより BI Engine モードをフルに使用していない理由（bi-engine-reason）も提供されます。BI Engine でリーフステージの一部と BigQuery でその残りのステージが実行されます。

BI Engine Mode DISABLED - BI Engine は、高速化と互換性のないサブクエリを処理する場合、すべてのリーフステージが BigQuery で処理されます。このモードにより BI Engine モードをフルまたは部分的に使用していない理由（bi-engine-reason）も提供されます。

https://storage.googleapis.com/gweb-cloudblog-publish/images/7_BI_Engine.max-1000x1000.jpg

定額料金の予約を購入いただいた場合、BI Engine の容量（GB）が、月額定額料金の一部として提供されることにご注意ください。2,000 スロットの年契約コミットメントでは、最大 100 GB の BI Engine の容量を無料で利用できます。BI Engine は、BI クエリに対して処理されるスロット数を減らすため、自由に使用できる容量に加えて、BI Engine の容量を少し補充し、購入するスロット数を減らすことで、使うスロット数をさらに増やさずに要件を満たせる場合があります。