LookerのSystem ActivityとBigQueryのINFORMATION_SCHEMAを突合する

本記事は ZOZO Advent Calendar 2024 シリーズ 4 の 19 日目の記事です。

概要

今回は、Looker を運用する中でダッシュボードやクエリの実行状況を可視化し、利用状況を把握するために、Looker のSystem Activity と BigQuery の INFORMATION_SCHEMA を無理矢理突合するために試行錯誤した話を紹介します。

本記事執筆時点での Looker version は 24.20.39 です。

System ActivityとINFORMATION_SCHEMAを突合する理由

Looker の System Activity には、ダッシュボードと Explore が含まれます。特に System Activity Explore にはダッシュボードよりも詳細なデータが含まれており、クエリの平均実行時間やエラー情報などを取得できます。具体的に参照可能な値については下記公式サイトを参照ください。

しかし、単体で利用した場合、どのダッシュボードが具体的にどの程度の BigQuery リソースを消費しているかまでは把握しづらい点があります。

一方、BigQuery の INFORMATION_SCHEMA を利用すると、クエリの実行時間、処理バイト数、クエリタイプなどのメタデータが取得可能です。これらを突合することで、以下のような詳細な分析が可能になります。

• Looker経由で実行されたBigQueryクエリのコストやリソース消費の正確な把握
• ダッシュボードやExploreの利用状況の分析と最適化
• 高コストなクエリや低利用のリソースの特定

完全一致する結合キーがない問題

ところが、ここで重要な問題があります。
System Activity では INFORMATION_SCHMA でジョブを一意に識別するためのjob_idを保持していません。

ただ Explore名 がクエリの実行中に生成される CTE（例：WITH explore_name AS (...)）に含まれているため、それを結合キーとして使用することにします。
つまり本記事は、Looker 上で Explore を使っていることが大前提（直接クエリを記述していない） なのと、もし複数 Explore を参照している構造の場合はもう一工夫必要ということになります。

実例：Looker ユーザーごとの参照テーブル情報の抽出

Looker ユーザーごとに、いつどの BigQuery テーブルにアクセスしたか を特定する具体例を以下に示します。
Looker Connection にサービスアカウントを使っている場合、BigQuery の INFORMATION_SCHMA 上の user_emailにはサービスアカウントが格納されますが、Looker の System Activity の情報と突合することで、Looker ユーザー単位での参照ログ情報を取得することが可能になります。

必要なデータセットの準備

BigQuery の INFORMATION_SCHMA は権限さえあれば特に準備は不要です。一方で Looker の System Activity は BigQuery に連携する必要があります。
継続的なパイプラインを実装する場合は、株式会社LegalOn Technologiesさんのこちらの記事が非常に参考になります。

今回は簡易的な検証として、手動で Looker の System Activity を Google Drive に出力し、外部テーブルとして参照してみます。

1. System Activity Explore で 日付× Explore ごとの参照ユーザーを抽出

ディメンションは以下を選択します。

• Query / Explore
• User / Name
• History / Most Recent Query Run at Date

日付は似たような項目としてQuery / Created Dateがありますが、これは最初に同様のクエリを作成した日付で、最新のクエリ実行日付ではないことに注意です。

2. 抽出結果を Google Drive に CSV として出力

3. Google Drive 上の CSV を外部テーブルとして作成

突合するクエリ

必要なデータセットが準備できたら、以下のようなクエリを実行します。

-- INFORMATION_SCHEMAから参照テーブル情報を取得
with referenced_tables as (
    select
      -- クエリ内のWITH句からExplore名を抽出
      trim(regexp_extract(query.query, r'WITH+(.*)+AS')) as explore_name,
      referenced_table.project_id as project_id,
      referenced_table.dataset_id as dataset_id,
      referenced_table.table_id as table_id
    from `プロジェクト名`.`リージョン名`.INFORMATION_SCHEMA.JOBS_BY_PROJECT -- BigQuery のINFORMATION_SCHEMA を環境に応じて指定
    where
        statement_type != 'SCRIPT'
        and date(creation_time, 'Asia/Tokyo') between date_sub(
            current_date('Asia/Tokyo'), interval 7 day -- 必要に応じて期間を変更
        ) and current_date('Asia/Tokyo')
    group by 1,2,3,4
)

-- Lookerの履歴データと参照テーブル情報を結合
select
    Most_Recent_Query_Run_at_Date, -- 最終実行日時
    Name, -- Lookerのユーザー名
    Email, -- Lookerのメールアドレス
    Explore, -- Explore名
    array_agg(struct(project_id, dataset_id, table_id)) as Referenced_Tables -- 参照されたテーブル情報を構造化データとして集約
from `xxx.xxx.xxx` -- Looker の System Activity を出力した外部テーブルを環境に応じて指定
left join referenced_tables
    on Explore = explore_name
group by 1,2,3,4

まとめ

本記事では Looker のSystem Activity と BigQuery の INFORMATION_SCHEMA を突合する方法を紹介しました。
勿論、Looker のSystem Activity には様々な情報が含まれていますが、たとえば BigQuery の INFORMATION_SCHEMA 上の ジョブごとの使用 Slot と合わせて分析したいケースもあると思います。BigQuery の INFORMATION_SCHEMA を Looker に連携してそちら側でモデリングするという手段もありますが、もしよりスマートな方法や追加のアイデアがあれば、ぜひコメントで教えてください！