データ基盤の管理に役立つ監視用のSQLを紹介します

Analytics Engineerの吉田(id:syou6162)です。BigQueryを中心に10X社内のデータ関連の管理をしています。10Xに入社してそろそろ一年になろうかとしていますが、データ基盤を適切に管理 / 運用するためにSQLによる監視を少しずつ取り入れています。この記事では、具体的にどのようなSQLを書いて監視しているのか紹介したいと思います。

なお、SQLを使ったデータ基盤の監視自体については私の前職のTech Blogで詳細に書いていますので、そちらを参照してください。

本エントリはこれをベースに「dbtをフルに活用している10Xの環境向けに入れた監視」や「BigQueryの最近のアップデートに伴ない新しく入れた監視」にフォーカスを絞って紹介しようと思います。

dbtの運用に関連する監視
- 更新されていないテーブルを検知する
- ストレージが巨大になり過ぎているテーブルを検知する
BigQueryの新プラン導入に伴なう監視
- BigQuery Editions導入に伴なうスロット利用量に対する監視
- Physical storage課金の対象になっていないデータセットを洗い出す
まとめ

dbtの運用に関連する監視

更新されていないテーブルを検知する

10Xではdbtを積極的に活用しています。dbtではテーブルなどのリソース作成や更新をSQLを使ってコード管理できますが、SQLを削除した場合でもBigQuery上のテーブルを削除してくれるわけではありません。SQL削除後に忘れずにテーブル側も削除しないと、ずっとテーブルが残ってしまうことになります。これは以下の2点からも課題になります。

ストレージコスト
- SQLを削除する場合、ほとんどはその後はそのテーブルを見ないことを意味します
- 誰からも見られないテーブルをそのままにしておくのはストレージコストの観点で無駄です
データセキュリティ
- 参照されることがほぼないとは言え、不必要なテーブルが残ったままになっているのはデータセキュリティの観点からもよくありません
- 定期的に不要なテーブルを棚卸しする必要がありますし、削除すべき対象の一覧は機械的にさっと出せるようにしたいです

削除すべきテーブルが出てきた場合、以下のSQLで監視して速やかに棚卸しをするようにしています。これはINFORMATION_SCHEMA.TABLE_STORAGEビューを使うと簡単に分かります。

SELECT
  COUNT(DISTINCT table_name) AS not_updated_tables_count
FROM
  `my-project`.`region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
WHERE
  # プロジェクト全体での適用は難しい場合が多いので、データセットを限定しながら少しずつ管理対象を拡大していくのがオススメ
  table_schema IN ("my_dataset_1", "my_dataset_2", "my_dataset_3")
  AND DATE_DIFF(CURRENT_DATE("Asia/Tokyo"), DATE(storage_last_modified_time, "Asia/Tokyo"), DAY) >= 7

なお、データがテーブルに最後に書き込まれた時刻を取りたい場合はstorage_last_modified_timeを使うのがよいとna0さんに教えていただいたものを採用してます。アドバイス、ありがとうございました。

BigQueryのクエリジョブにおいて、データがテーブルに最後に書き込まれた時刻を利用するなら、INFORMATION_SCHEMA.TABLE_STORAGEビューのSTORAGE_LAST_MODIFIED_TIME
TIMESTAMP列を使うのがお手頃ですねhttps://t.co/v8tNTxRtBA https://t.co/tFGFnaM7iz
— na0 (@na0fu3y) 2023年7月4日

ストレージが巨大になり過ぎているテーブルを検知する

イベントやログなどのテーブルを参照する場合、テーブルサイズが非常に大きいこともあり

新規でテーブルを参照する場合は全期間を対象にする
テーブルがすでに存在するのであれば、参照する期間を直近のみにする

という処理をしたいことがよくあるかと思います。dbtではadapter.get_relationを使うことで、テーブルに限らずリソースがすでに存在するかを判断することができます。よって、こういったSQLがよく登場します。

{%- set already_exists = adapter.get_relation(this.database, this.schema, this.identifier) is not none -%}

select
  ...
from
  {{ source('my_dataset', 'my_events') }}
where
  {%- if already_exists %}
  _TABLE_SUFFIX > format_date('%Y%m%d', date_sub(current_date(), interval 3 day))
  {%- endif %}

これは便利ではあるのですが、上記のSQL内で使われているjinjaのマクロは変数が未定義であっても、そのまま動作してしまいます。変数が未定義の場合だと

毎日巨大なテーブルが参照され
その結果の巨大なテーブルがincrementalなテーブルとして積み重なり
最終的には数百TBある巨大なテーブルが出現してしまう

ということがありました。恐しいですね...。

こういった巨大なテーブルができてしまった場合でも早期に気付けるように、INFORMATION_SCHEMA.TABLE_STORAGEビューを使って監視するようにしています。

SELECT
  table_schema AS dataset_name,
  table_name,
  SUM(active_logical_bytes / POW(1024, 4)) AS active_logical_tera_bytes,
FROM
  `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT
GROUP BY
  dataset_name, table_name
HAVING
  active_logical_tera_bytes >= 100 # ここは適当に変えて使ってください

この監視による通知がきた場合、対象のテーブルの作成ロジックを調査したり、git blameして作成者にロジックを修正依頼する、ということを行なっています。

BigQueryの新プラン導入に伴なう監視

ここ最近でBigQueryではBigQuery Editionsの登場やPhysical storage課金のGAなど大きめの変更が複数ありました。BigQueryのコストは10Xでも大きなものとなっているため、BigQuery EditionsやPhysical storage課金の導入を進めています。導入に際し、新規に監視したほうがよい項目があったので、それらを紹介したいと思います。

BigQuery Editions導入に伴なうスロット利用量に対する監視

10Xでは長らくBigQueryはオンデマンドを利用していました。しかし、2023/07/05以降、オンデマンドは課金単価が約25%値上げということもあり、BigQuery Editions導入を検討しました。BigQuery Editions導入によりコストが増加 / 減少するかは実際に動かすクエリのワークロードによって異なりますが、検証の結果、10XのワークロードにおいてはBigQuery Editionsを導入したほうがコストが下げられると判断したため、BigQuery Editionsをコストの大部分を占めるプロジェクトで導入しました。

オンデマンド利用時はデータのスキャン量に比例した課金体系でしたが、BigQuery Editionsでは処理に使ったスロット時間に比例した課金体系へと変化します。データのスキャン量はBigQueryのWebコンソールで実行前に表示されますが、現在のところスロット時間は実行前にはまだ表示されません。また、実行後でも奥まったところに表示されるため、ユーザーがスロット時間を気にしにくい形になっています。そのため、スロット量の監視を導入することにしました。

一つ目は短期間でスロットを大量に消費しているパターンの監視です。一時間毎にINFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATIONビューを叩くことで、バースト的に大量のスロットを消費しているユーザーがいないか監視しています。

WITH
  slot_in_second AS (
  SELECT
    period_start,
    user_email,
    CEIL(SUM(period_slot_ms) / 1000 / 100 ) * 100 AS total_slot_sec,
  FROM
    `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION
  WHERE
    job_creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), interval 60 minute)
    AND reservation_id IS NOT NULL # BigQuery Editionsを有効にしているものが対象
    AND user_email NOT LIKE "%iam.gserviceaccount.com"
  GROUP BY
    period_start,
    user_email
)
SELECT
  user_email,
  DATE_TRUNC(period_start, HOUR) AS period_start,
  ROUND(SUM(total_slot_sec) / 60 / 60, 2) AS total_slot_hour,
FROM
  slot_in_second
GROUP BY
  period_start,
  user_email
HAVING
  total_slot_hour >= 1000 # この閾値は適当に変更してください

バーストはしていないものの、長時間に渡って実行されておりその結果スロットの消費量も多くなっているパターンもありました。このパターンについては、別にSQLでのそのパターンも検知できるようにしています。

SELECT
  job_id,
  TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), MAX(start_time), MINUTE) AS running_minutes,
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
WHERE
  creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 60 minute)
  AND reservation_id IS NOT NULL -- BigQuery Editionsを有効にしているものが対象
  AND user_email NOT LIKE "%iam.gserviceaccount.com"
  AND state = "RUNNING"
GROUP BY
  job_id
HAVING
  running_minutes > 30 # この閾値は適当に変更してください

Physical storage課金の対象になっていないデータセットを洗い出す

BigQueryのPhysical storage課金は対象になるデータセットによって圧縮率が異なりますが、10X社内では6~8倍の圧縮率であることが多く、ストレージコストも非常に下げられる試算があったので、導入を進めています。

既存のデータセットについては一度Physical storageを有効にしてしまえばOKですが、問題は新規で作成されるデータセットです。プロジェクト内で新規に作成されるデータセットのdefaultをPhysical storageに設定することができないため、新規に作成されたデータセットはLogical storageの設定になってしまうことは注意が必要です。

そこで、INFORMATION_SCHEMA.SCHEMATA_OPTIONSビューとINFORMATION_SCHEMA.TABLE_STORAGEビューを使ってPhysical storageが有効になっていないデータセットの一覧を洗い出すようにしています。

WITH storage_billing_model_physical AS (
  SELECT
    catalog_name AS project_id,
    schema_name AS dataset_id
  FROM
    `my-project`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS
  WHERE
    option_name = "storage_billing_model"
    AND option_value = "PHYSICAL"
)
SELECT
  project_id,
  table_schema AS dataset_name,
FROM
  `my-project`.`region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
WHERE
  # まだstorage_billing_modelがphysicalになっていないデータセットのみ対象とする
  project_id || "." || table_schema NOT IN (SELECT project_id || "." || dataset_id FROM storage_billing_model_physical)
GROUP BY
  project_id,
  dataset_name