Go to Qiita Advent Calendar Top
LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

Day 7

ZOZO Advent Calendar 2025

@skmtkhtin株式会社ZOZO

BigQuery Scheduled Queriesで「ゆるめの監視」

Last updated at Posted at 2025-12-06

目次

  1. はじめに
  2. なぜScheduled Queriesを選んだか
  3. 実装したシステムの概要
  4. ハマったポイントと解決策
  5. 「ゆるめの監視」のための設計パターン
  6. まとめ

1. はじめに

BigQuery Scheduled Queries（定期クエリ）は、データの品質監視やビジネスKPIの異常検知に使えるサーバーレスなツールです。

しかし、すべての監視用途に向いているわけではありません。本記事では、「ゆるめの監視」 という観点でScheduled Queriesを活用した際の実践的なハマりポイントと解決策を紹介します。

この記事で扱うこと

  • Scheduled Queriesが向いている「ゆるめの監視」とは何か
  • 実際に遭遇したハマりポイント
  • 失敗を前提とした設計パターン

想定読者

  • BigQuery Scheduled Queriesを使ったデータ監視を検討している方
  • Terraformでインフラを管理している方
  • 「そこまでリアルタイムじゃなくていいんだけど」という監視を実装したい方

「ゆるめの監視」とは？

本記事で扱う「ゆるめの監視」は以下のような特徴を持ちます：

  • たまに失敗してもOK: 一時的なエラーで監視が止まっても、次の実行でカバーできる
  • 簡易リトライ: 処理対象の時間範囲をオーバーラップさせることで、前回の失敗をカバー
  • リアルタイム性は不要: 数分〜数時間の遅延は許容できる

逆に、以下のような要件がある場合はScheduled Queriesは不向きです：

  • 秒〜分単位のリアルタイム監視が必要
  • 厳密なリトライ制御が必要
  • 金銭に関わる重要な処理

そのような場合は、ワークフローサービスなど、リトライ制御が可能な別の仕組みを推奨します。

2. なぜScheduled Queriesを選んだか

開発・運用しているサービスにおいて、AlloyDBとBigQuery間のデータ整合性を監視する必要がありました。

Scheduled Queriesの魅力

  1. 手軽にSlack通知できる

    • Cloud MonitoringのLog-based Alertを使えば、Pub/SubやFunctionsなしでSlackに通知可能
    • エラーメッセージの詳細を直接Slackに載せたい場合は、Pub/Sub + Cloud Run functionsで実装も可能

  2. エラーデータの蓄積が簡単

    • SQLでテーブルに書き込むだけ
    • ビューで重複を除いたり、直近の必要な部分だけをダッシュボードから参照したりできる

  3. 「ゆるめの監視」に十分

    • データ品質監視では、厳密なリトライは不要なケースが多い
    • 実行間隔を短くして処理対象をオーバーラップさせれば、簡易的なリトライが実現できる

監視の目的

今回監視したのは以下の2つのデータソース間の整合性です：

  • AlloyDB: source_table（上流処理の結果）
  • BigQuery: target_table（下流処理の結果）

理想的には、AlloyDBに上流処理の結果が記録されたら、BigQueryにも下流処理の結果が記録されているはずです。この連携が途切れていたら、システムの異常として検知します。

3. 実装したシステムの概要

システム構成

監視クエリの概要

BigQuery Scheduled Queriesで以下のロジックを実装：

  1. AlloyDBからデータ取得: EXTERNAL_QUERYを使って2時間前〜1時間前のデータを取得
  2. BigQueryとの照合: LEFT JOINで整合性をチェック
  3. 異常検知: 未連携レコードがあればSELECT ERROR()でエラーを発生
  4. 監視結果の記録: 異常レコードを日付サフィックス付きテーブルに保存

ポイント: SELECT ERROR()を使うことで、クエリ自体を失敗させ、Cloud Loggingにエラーログを出力します。このログをCloud Monitoringで監視してSlack通知します。

簡略化したSQL例

BEGIN
    DECLARE error_count INT64;
    DECLARE ts_start STRING DEFAULT FORMAT_TIMESTAMP('%F %T+00', TIMESTAMP_SUB(@run_time, INTERVAL 2 HOUR));
    DECLARE ts_end   STRING DEFAULT FORMAT_TIMESTAMP('%F %T+00', TIMESTAMP_SUB(@run_time, INTERVAL 1 HOUR));

    -- AlloyDBからデータ取得
    CREATE TEMP TABLE tmp_source_data AS
    SELECT id, uuid, timestamp_col
    FROM EXTERNAL_QUERY('projects/project-a/locations/us/connections/alloydb_connection',
        'SELECT id, CAST(uuid AS TEXT) AS uuid, timestamp_col FROM source_table WHERE ...');

    -- データが存在しない場合はエラー
    SET error_count = (SELECT COUNT(1) FROM tmp_source_data);
    IF error_count = 0 THEN
        SELECT ERROR('[Monitor] データが見つかりません');
    END IF;

    -- BigQueryとの整合性チェック（未連携レコードを抽出）
    CREATE TEMP TABLE unprocessed_records AS
    SELECT src.*
    FROM tmp_source_data src
    LEFT JOIN `project-datamart.dataset.target_table` tgt
        ON src.uuid = tgt.uuid
        AND tgt.created_at >= TIMESTAMP_SUB(@run_time, INTERVAL 2 HOUR)
    WHERE tgt.uuid IS NULL;

    -- 未連携レコードがあればエラー
    SET error_count = (SELECT COUNT(1) FROM unprocessed_records);
    IF error_count > 0 THEN
        -- 監視結果テーブルに記録
        INSERT INTO `project-a.monitoring.data_consistency_YYYYMMDD`
        SELECT *, CURRENT_TIMESTAMP AS detected_at FROM unprocessed_records;

        SELECT ERROR(FORMAT('[Monitor] %d 件の未連携レコードがあります', error_count));
    END IF;
END;

Terraformでの管理

resource "google_bigquery_data_transfer_config" "monitoring" {
  project              = local.monitoring.project
  display_name         = "data-consistency-monitoring"
  location             = "US"
  data_source_id       = "scheduled_query"
  schedule             = "every 1 hours from 00:00 to 14:00"  # UTC
  service_account_name = google_service_account.monitoring.email
  disabled             = false

  params = {
    query = templatefile("${path.module}/sql/monitoring.sql.tpl", {
      env = local.env
    })
  }

  email_preferences {
    enable_failure_email = true
  }
}

4. ハマったポイントと解決策

4-1. absent_over_timeでのUTC/JST時刻計算（Tips）

クエリが「実行されていない」ことを検知するために、absent_over_timeを使ったアラートを設定する際、監視しない時間帯（例: JST 0:00〜9:00は監視しない）を設定したいケースがあります。

Cloud MonitoringのPromQLはUTCで動作するため、JSTで考えたい時間帯（例: JST 9:00〜23:00だけ監視）を設定するには、時刻変換が便利です。

実装例

PromQLで時刻計算を行い、監視しない時間帯を除外します：

resource "google_monitoring_alert_policy" "query_not_running" {
  display_name = "Scheduled Query Not Running"

  conditions {
    condition_prometheus_query_language {
      duration = "0s"
      query    = <<EOT
        absent_over_time(logging_googleapis_com:log_entry_count{
          monitored_resource="bigquery_dts_config",
          config_id="${basename(google_bigquery_data_transfer_config.monitoring.name)}",
          severity="INFO"
        }[5400s])
        unless ((hour() + 9) % 24 < 9 or (hour() + 9) % 24 >= 23)
      EOT
    }
  }

  alert_strategy {
    auto_close = "3600s"
  }
}

時刻計算の解説:

  • hour(): 現在のUTC時刻（0〜23）
  • (hour() + 9) % 24: UTCをJSTに変換
  • < 9 or >= 23: JST 0:00〜9:00と23:00〜24:00を除外

ポイント:

  • [5400s]は90分間（5400秒）ログが無いことを検知
  • スケジュールが1時間ごとなので、余裕を持って90分に設定
  • auto_closeを短めに設定して、次回の検知を可能にする

4-2. クロスプロジェクト実行の権限設定

試したこと: 別プロジェクトでクエリを実行

当初、以下のような構成を試しました：

  • プロジェクトA: Scheduled Queryを管理
  • プロジェクトB: 実際にクエリを実行（BigQueryのスロット管理のため）

ハマったポイント

クロスプロジェクトでScheduled Queryを実行する場合、以下のような権限が複雑に絡み合います：

  1. bigquery.jobs.create: プロジェクトBでクエリを実行する権限
  2. iam.serviceAccounts.actAs: （CIから設定するため）サービスアカウントのimpersonation権限
  3. iam.serviceAccounts.getAccessToken: アクセストークンを取得する権限
  4. Monitoring Alert Policyの作成・編集権限も別プロジェクトに必要

さらに、組織レベルでiam.disableCrossProjectServiceAccountUsageを無効にする必要もあり、かなり複雑でした。
Alert Policyは、notificationの設定も別途用意する必要があり、気軽にprojectを切り替えればいい話ではありませんでした。

結論: シンプルに自プロジェクトで実行

最終的に、シンプルに動かすことを優先して、自プロジェクト内で実行することにしました。

理由：

  • 「ゆるめの監視」という用途では、スロット競合はそこまで問題にならない
  • 権限設定の複雑さがメンテナンスコストになる
  • 問題が発生したら、その時点でスロット購入などを検討すればよい

教訓: 最初から完璧を目指さず、シンプルな構成で始めることが重要です。

5. 「ゆるめの監視」のための設計パターン

Scheduled Queriesは標準でリトライ機能がないため、失敗を前提とした設計が重要です。

5-1. 処理対象をオーバーラップさせる

「前回の実行が失敗しても、次の実行でカバーできる」ように設計します。

悪い例（脆弱）:

-- 直近15分だけを監視（前回失敗したらカバーできない）
WHERE timestamp_col >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 15 MINUTE)

良い例（自己修復）:

-- 過去1時間を監視（前回失敗してもカバーできる）
WHERE timestamp_col >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
  AND timestamp_col < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 0 HOUR)

このパターンなら、10:00の実行が失敗しても、10:15の実行時に10:00〜10:15の分もまとめてチェックしてくれます。

5-2. 実行間隔を短くする

実行間隔を短くすることで、障害の検知を早くし、失敗時の影響を小さくします。

推奨:

  • 重要度が高い監視: 15分〜1時間ごと
  • 日次レポート系: 1日1回

今回は1時間ごとに実行し、2時間前〜1時間前のデータを監視することで、1時間のオーバーラップを確保しています。

5-3. 失敗アラートを設定する

クエリが失敗したことを検知するアラートを設定します：

resource "google_monitoring_alert_policy" "query_error" {
  display_name = "Scheduled Query Error"

  conditions {
    condition_matched_log {
      filter = <<EOT
        resource.type="bigquery_dts_config"
        AND resource.labels.config_id="${basename(google_bigquery_data_transfer_config.monitoring.name)}"
        AND severity="ERROR"
        AND jsonPayload.message:"[Monitor]"
      EOT
    }
  }

  notification_channels = [google_monitoring_notification_channel.slack.id]

  alert_strategy {
    auto_close = "1800s"  # 次回検知のため短めに設定
    notification_rate_limit {
      period = "3600s"  # 1時間に1回まで通知
    }
  }
}

ポイント:

  • jsonPayload.message:"[Monitor]"で、監視用のエラーだけをフィルタ
  • auto_closeを短めに設定して、次回の検知を可能にする
  • notification_rate_limitで通知の頻度を制限

5-4. SELECT ERROR()とabsent_over_timeの2段構え

実運用では、2種類のアラートを設定します：

  1. SELECT ERROR(): データ異常を検知（業務影響のある異常）
  2. absent_over_time: クエリが実行されていないことを検知（システム異常）
┌─────────────────────┐
│ Scheduled Query     │
└──────┬──────────────┘
       │
       ├─→ データ異常時: SELECT ERROR() → Cloud Monitoring → Slack（即時通知）
       ├─→ 監視結果を記録: INSERT結果テーブル（事後分析用）
       └─→ クエリ未実行: absent_over_time → Cloud Monitoring → Slack

この2段構えで、データ異常とシステム異常の両方を検知できます。

6. まとめ

BigQuery Scheduled Queriesは、「ゆるめの監視」に非常に有効なツールです。

ハマりポイントまとめ

問題 解決策・Tips
クロスプロジェクト実行が複雑 シンプルに自プロジェクトで実行することを選択
absent_over_timeの時刻計算 (hour() + 9) % 24でJSTに変換

Scheduled Queriesの向き・不向き

向いている:

  • データ品質監視（NULL、重複、整合性チェック）
  • ビジネスKPIの異常検知（売上、ユーザー数など）
  • たまに失敗してもOKな「ゆるめの監視」
  • 処理対象をオーバーラップさせられる監視

向いていない:

  • 秒〜分単位のリアルタイム監視
  • 厳密なリトライ制御が必要な処理
  • 金銭に関わる重要な処理
  • インフラのヘルスチェック（CPU、メモリなど）

おわりに

Scheduled Queriesは設定は簡単ですが、本番運用を考えると意外と奥が深いツールです。

特に、「失敗を前提とした設計」 が重要です。処理対象をオーバーラップさせることで、リトライ機能がなくても安定した監視システムを構築できます。

本記事が、同じ問題に直面する方の助けになれば幸いです！

参考資料

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?