はじめに

こんにちは！Data Strategy teamでデータエンジニアをしているshota.imazekiです。

今回はBigQueryでのINFORMATION_SCHEMAを用いたBigQueryデータ監視というテーマでブログを書いていこうと思います。

BigQueryを利用していく上で「クエリが実行できなくなった」「データが古いまま更新されていない」「使われていないデータがある」などの様々な運用上の課題があるかと思います。それをINFORMATION_SCHEMAで使って簡単に解決していこうという話です。

BASEの分析基盤

まず前提としてBASEの分析基盤を簡単に紹介します。BASEではDWHにBigQueryを採用しています。BIツールとしてはLookerを導入していて、Dailyでデータが同期されるようになっています。ワークフロー管理にはAirflowを利用しており、データ連携部分にはEmbulkやS3 エクスポート機能などを使ってます。連携部分の詳細はこちらのテックブログに書いております。BigQueryはLookerから以外にもデータアナリストなどの分析者が直接クエリを実行することもあります。

データ監視における課題

BASEにおけるデータ監視関連の課題は現状、以下の3点があります。

データが連携されていない、更新されないデータが連携され続けている
分析に使われていないデータが連携されている
重いクエリが実行されている

1. データが連携されていない、更新されないデータが連携され続けている

データソース側やデータ連携部分で何かしらの障害が起きて、更新されるべきデータが更新されなくなっていることがあります。またこの逆で、プロダクト側での機能の廃止や外部サービスの利用停止などによって、既に更新されることのないデータを更新し続けているパターンもあります。同じデータで更新し続けている状況です。これらについてはユーザー側からの連絡や不定期の点検時に発見することが多く、対応が遅くなってしまうことが問題でした。

TABLE_STORAGEを使えば簡単にテーブル件数を確認することができます。

select distinct
  project_id,
  table_schema, -- データセット名
  table_name,
  total_rows, -- テーブル件数
from [project_name].`region-asia-northeast1`.INFORMATION_SCHEMA.TABLE_STORAGE

このテーブルを使ってDailyのデータ連携バッチ実行後などにテーブル件数を記録しておくことで、件数の変化を簡単に追うことができます。Window関数などを使って前日との件数比較を行い、同じ件数であればアラートを鳴らすということも可能になります。BASEではLookerのアラート機能を使ってSlackへ通知しています。

2. 分析に使われていないデータが連携されている

BASEでは基本的にはELT形式で一度、BigQueryにデータを連携してからデータマートなどへの加工を行っています。その中には特定プロジェクトのために一時的に作られて、PJ自体は終了したがそのまま放置されているデータマートなどもあります(それによって不要になった元データもあるでしょう)。不要になったデータマートやその加工処理はコストパフォーマンスの観点から削除をした方がいいと考えていますが、こちらの発見も遅れることが多かったため、もっと早く簡単に検知できないかと考えました。

JOBS_BY_PROJECT(ORGANIZATIONなどもある), TABLESを使うことで使われてないテーブルを洗い出すことができます。

JOBYS_BY_PROJECTを使って実行されているクエリから、どのテーブルが利用されているかを見ることができます。クエリの実行履歴については一定期間以内(180日程度)までしか保持されないのでもっと遡りたい時は実行履歴を保存しておくと良いでしょう。

select
  j.user_email,
  j.query,
  t.project_id,
  t.dataset_id,
  t.table_id,
  ROUND(j.total_bytes_processed / (1024*1024*1024), 2) bytes_processed_in_gb, -- 処理されるGB数も分かる
from [project_name].`region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT j
  cross join unnest(j.referenced_tables) t
where
    -- 成功したREAD文のみを取り出す
  j.job_type = 'QUERY'
  and j.statement_type = 'SELECT'
  and j.state = 'DONE'
  and j.error_result is null

その後、TABLESから全テーブルの一覧を取り出し、差分を見ることで使われていないテーブルを洗い出すことができます。

select distinct
  table_catalog, -- プロジェクト名
  table_schema, -- データセット名
  table_name
from [project_name].`region-asia-northeast1`.INFORMATION_SCHEMA.TABLES

3. 重いクエリが実行されている

前提としてBASEでは以下のような制限をBigQueryに設定しています。

GCPプロジェクト単位でクエリ走査量の制限: Query usage per day
ユーザー単位でのクエリ走査量の制限: Query usage per day per user

またLookerからの1クエリ単位での走査量の制限も入れています。

これらの制限によって基本的には重いクエリが実行されてもコストが莫大に膨れ上がったり、コスト制限によって利用できなくなったといった問題は防げています。しかし、1ユーザー単位で見た時には数回重いクエリを実行しただけでBigQueryが利用できなくなってしまうこともあり、コスト的にも分析体験としても好ましくありません。

したがって重いクエリが実行された場合にそれを通知できるようにし、クエリの改善もしくはデータマートの構築などを提案できるようにしました。

2同様、JOBS_BY_PROJECT(ORGANIZATIONなどもある)を利用することで重いクエリを洗い出すことができます。

select
  j.user_email,
  j.query,
  t.project_id,
  t.dataset_id,
  t.table_id,
  ROUND(j.total_bytes_processed / (1024*1024*1024), 2) bytes_processed_in_gb, -- 処理されるGB数も分かる
from [project_name].`region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT j
  cross join unnest(j.referenced_tables) t
where
    -- 例: 100GB以上の走査量を取り出すクエリ
  (j.total_bytes_processed / (1024*1024*1024) > 100

Lookerのアラート機能を用いることで、定期的に(数分単位や1時間単位など)重いクエリが実行されたかを確認し、Slackに通知することにしています。

またLookerからはサービスアカウントを用いてクエリを実行する都合上、誰か1人がLookerからQuery usage per day per userの上限に引っかかるくらい使ってしまうと他のLookerユーザーにも影響が出てしまいます。その検知も簡単に行えるようにしました。上記のJOBS_BY_PROJECTテーブルなどを日単位でtotal_bytes_processedをsumしたりすることで設定した閾値を超えた場合に通知することができるようになります。

おわりに

INFORMATION_SCHEMAを用いることで分析基盤の運用がより便利になる事例を紹介してきました。正直、Data observabilityツール(elementaryなど)を用いた方がよりDWHの改善などには繋がると思いますが、気軽に始めるならINFORMATION_SCHEMAを使った方が良いかと考えています。BASEでもelementaryの導入などは進めていきたいとは思っていて、このような分析基盤の改善を一緒に行っていくメンバーを募集しています。ご興味のある方は気軽にご応募ください！

A-1.Tech_データエンジニア / BASE株式会社

明日のBASEアドベントカレンダーはosashimiさんの記事です。お楽しみに！