Lookerから実行されるBigQueryのクエリコストを分析可能にする方法

はじめに

株式会社LegalOn Technologies でデータエンジニアリングをしている鈴木です。

LegalOn Technologies ではBIツールにLooker（https://cloud.google.com/looker）を採用しており、分析用のデータウェアハウスにはBigQueryを採用しています。

この環境を利用して弊社主力製品の各種KPIをダッシュボード化するのに日々取り組んでいます。

本ブログではLookerとBigQueryを併用する上での両者を組み合わせたコストモニタリングの方法を紹介したいと思います。

背景

弊社ではBigQueryはオンデマンド料金で利用しています。

Lookerの活用が進むにつれてBigQueryのクエリコストも増加傾向にあり、さらに直近1年で月額コストが2倍以上に増えてきており、看過できない状況にあります。

LookerではSystem Activity（https://cloud.google.com/looker/docs/system-activity-pages）という管理者向けに用意されたLooker上の利用履歴をExploreで操作して分析できる機能が用意されています。

このSystem Activityを利用して、実行されたExplore・クエリ本文・利用者等が特定可能ですが、クエリコストについてはSystem Activityから追うことが出来ません。

一方クエリコストについてはBigQueryのAuditLogを利用することで確認が可能ですが、AuditLog上はLookerのConnection単位のサービスアカウントまでの情報しか辿れず、Looker上のExploreやダッシュボード・利用者の情報がありません。

また、System Activity のデータとBigQuery AuditLogのデータはデータソースの違いがあり、Lookerから直接紐づける事は出来ません。

今回ご紹介する方法を用いる事で、互いのデータを紐づけた分析を可能にします。

その上で、紐づけたデータを用いて弊社でどのような分析をして活用しているのかもご紹介致します。

システム構成

今回のシステムは以下のアーキテクチャ図の構成で実装されています。

1. Looker SystemActivityからCloud Storageへ

System Activity上のHistory ExploreにはSlugというフィールドがあります。

これはBigQuery上のクエリのJobを紐づける為のIDとなっており、SlugとExplore・Dashboardに関するフィールドの組み合わせを、Lookに保存します。

例えばダッシュボードでは以下のようなフィールドの組み合わせで用意しています。

Slug
実行時のtimestamp
ダッシュボード閲覧時のユニークID
ダッシュボードID
ダッシュボードタイトル
ユーザーID
ユーザー名

2. Cloud StorageからBigQueryへ

Lookが用意出来たら、弊社では過去に紹介した Looker System Activity を低コストなサーバレスイベント駆動パイプラインで BigQuery へ取り込むのパイプラインがあり、これを利用してBigQueryへデータを定期的に転送します。

3. Cloud LoggingからBigQueryへ

BigQueryのクエリ履歴はCloudLoggingに存在しています。これをCloud Loggingのlog sinkの機能を利用してBigQueryへ転送します。

以下は実際のSink設定時の例です。

loggingのフィルター条件(Inclusion filter)を resource.type = bigquery_resource とすることで、BigQueryのログだけに絞り、転送先(Service)をBigQuery datasetと設定します。

BigQueryに転送されたデータの中を確認すると、 protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.labels というカラムがあり、その中に key = looker-context-history_slug のvalue値にLooker上のSlugが入っています。

4. Looker上でのExplore化とダッシュボード化

1と2の作業で用意出来たBigQueryテーブルをLooker上でviewとして用意します。

Exploreで互いのSlugで結合してダッシュボード化します。

以下はExplore化している例で、AuditLogのexploreに対して daily_dashboard_history というviewをslug_idで結合しています。

explore: cloudaudit_googleapis_com_data_access {
  label: "Auditlog Data Access"
  join: cloudaudit_googleapis_com_data_access__job_configuration_labels {
    relationship: one_to_many
    sql: LEFT JOIN UNNEST(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.labels) AS cloudaudit_googleapis_com_data_access__job_configuration_labels ;;
  }

  join: daily_dashboard_history {
    relationship: one_to_one
    sql_on:
      ${cloudaudit_googleapis_com_data_access__job_configuration_labels.key} = 'looker-context-history_slug'
      AND ${cloudaudit_googleapis_com_data_access__job_configuration_labels.value} = ${daily_dashboard_history.slug_id}
    ;;
  }
}

ダッシュボード化内容の紹介

以下はダッシュボードの可視化と分析例です。

ダッシュボード毎のコスト傾向からコストネックになっているExploreを特定する
上記のように特定のダッシュボードの増加傾向がわかり、pivotの一番左のダッシュボードは2024-09あたりから増え始めており、コスト改善を提案出来そうな傾向が見て取れます。

また、ダッシュボードをフィルタに加えて構成されるExplore単位のコスト増加傾向も特定出来ます。以下のように赤枠部分のExploreがコスト増に繋がっているかが特定できます。

特定後はExploreを改善したり、ダッシュボード上のフィルタの改善等の方針を検討できそうです。
Looker ユーザー毎のクエリコストを見てヘビーユーザーが使うExploreを特定する
ユーザー毎のクエリスキャン量をまずは確認します。

ここから気になったユーザーをフィルタして、よく利用するExploreをチェックします。

更にExploreもフィルタし、ダッシュボードで使われているのかどうかを特定します。以下のようにダッシュボードタイトルがnullの場合はダッシュボード以外でも利用されている事がわかり、ExploreページかLookでの活用等が調べられます。

弊社では上記を含めて以下の分類でクエリスキャン量をモニタリングしています。

ダッシュボード
Explore
Look
Looker model
ユーザー
所属

所属について弊社では別途SaaSツールで社員の所属部門情報を連携していますが、詳細は本ブログでは割愛致します。

ダッシュボードを活用した取り組み

用意したダッシュボードを使って毎月のコストの状況を出し、別途用意している分析環境の活用状況・予算状況と合わせてデータ基盤部門で確認する機会を設けています。

この取り組みの主な狙いは以下になります。

Lookerの利用状況とBigQueryコストのバランスが取れているか
分析環境の予算に対してコストのバランスが取れているか
上記バランスを鑑みて今後のタスクの優先度判断の要因に利用。

まとめ

今回ご紹介した方法ではクエリコストにフォーカスしていますが、AuditLogにはクエリコスト以外にもクエリの時間やスロット数等の有用な情報も含まれています。また、SystemActivityにはSQL Runnerや、ユーザーのグループ・ライセンス情報等もあります。これらの情報も活用することでより良いLookerの活用推進につながる事が期待できます。

別施策ですが弊社では他にデータ品質についてもモニタリング施策を進めており、オブザーバビリティツールを使った取り組みについても今後ご紹介したいと思っております。

仲間募集

本記事の内容はデータエンジニアの活動によるものですが、現在アナリティクスエンジニアを絶賛募集しています。ご興味ある方のご応募お待ちしております。

herp.careers

謝辞

当ブログは Looker System Activity を低コストなサーバレスイベント駆動パイプラインで BigQuery へ取り込むの機構があった為実現が容易に出来る物となっております、田中さんありがとうございました。

LegalOn Technologies Engineering Blog

LegalOn Technologies 開発チームによるブログです。