INFORMATION_SCHEMAを使ってクエリコストをモニタリングする

クエリコストのモニタリング

BigQuery の INFORMATION_SCHEMA を使ったクエリコスト計測について説明していきます。

BigQuery のコスト

BigQuery の料金には次の 2 つの主要な要素があります。

分析料金：クエリの処理にかかる費用
ストレージ料金：BigQuery に読み込むデータを保存する費用

よって、コストの削減も 2 通りの考え方があります。

定額料金の利用：定額料金（スロット数）を見積もる
ストレージの削除：削除可能な不要なストレージ（データセット）を見つける

そこで、INFORMATION_SCHEMA のメタデータを使い、クエリコストの計測を試しました。

定額スロット数の概算見積もり

BigQuery スロットは、BigQuery で SQL クエリを実行するために使用される仮想 CPU です。BigQuery では、クエリのサイズと複雑さに応じて、クエリに必要なスロット数が自動的に計算されます。

オンデマンド料金モデルまたはフラットレート料金モデルのいずれかを選択できます。

フラットレート料金モデル：予約するスロットの数を明示的に選択します。クエリはその容量内で実行され、デプロイされる 1 秒ごとに容量に対して継続的に支払います。たとえば、BigQuery スロットを 2,000 購入した場合、集計したクエリはいつでも 2,000 仮想 CPU のみに制限されます。この容量はそれを解除するまで保持され、集計したクエリを削除するまで 2,000 スロットに対して支払います。
オンデマンド料金モデル：一時的なバースト容量を備えたプロジェクトごとのスロットの割り当てが適用されます。オンデマンドモデルを使用するほとんどのユーザーにとって、デフォルトで割り当てられるスロットの容量は十分です。ワークロードによっては、より多くのスロットにアクセスできるようにすることでクエリパフォーマンスが向上します。

必要以上にスロットを確保してたくさんお金を払うことを避けるために、定額料金を選択することもできます。BigQuery を定額料金で利用する際にはスロットを 100 単位で任意の量だけ購入し、その購入量に応じて金額が決まります。

よって、定額スロット数の見積もりが把握できれば、クエリ実行ごとで課金されるオンデマンドより定額料金への移行、並びにスロット購入数の見積りを期待できます。

月平均のスロット使用率は、公式ドキュメントのサンプル SQL を参考にして取得してみました。

SELECT
  DATE(TIMESTAMP_TRUNC(creation_time, MONTH), "Asia/Tokyo") AS month,
  COUNT(1),
  SUM(total_slot_ms)
    / (1000*60*60*24*
        EXTRACT(DAY FROM
          DATE_SUB(
            DATE_ADD(
              DATE(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), MONTH), "Asia/Tokyo"),
              INTERVAL 1 MONTH
            ),
            INTERVAL 1 DAY
          )
        )) AS avg_slots
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
GROUP BY
  DATE(TIMESTAMP_TRUNC(creation_time, MONTH), "Asia/Tokyo")
;

上記を実行することによって、毎月のスロット数使用率を確認することができます。

また、あるプロジェクトにおけるユーザー別のスキャン量（GB）とスロット数も、下記の構文を実行すれば取得可能です。

SELECT
  date (creation_time,
    'Asia/Tokyo') AS query_date_jst,
  user_email,
  sum ( total_slot_ms) AS total_slot_ms,
  sum ( total_bytes_billed / (1024 * 1024 * 1024)) AS total_gigabytes_billed,
  -- クエリコストはUSリージョンであれば $5.00 / TB
  sum ( total_bytes_billed / (1024 * 1024 * 1024 * 1024)) * 5.00 AS total_cost_usd
FROM
  `project_name`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  total_bytes_billed >0
  AND creation_time BETWEEN @from_time
  AND @to_time
GROUP BY
  1,
  2
ORDER BY
  total_gigabytes_billed DESC

ストレージのモニタリング

データセット内テーブルのレコード数を把握できれば、毎月のストレージ料金の概算やレコード数の増加による料金増加の見積もりができると考えます。下記の構文を実行することによって、データセットのレコード総数とデータ総量を取得できます。

SELECT
  dataset_id,
  FORMAT("%'d", SUM(row_count)) AS row_count,
  ROUND(SUM(size_bytes) /(1024 * 1024 * 1024 * 1024), 2) AS size_tb
FROM
  データセットID.__TABLES__
GROUP BY
  dataset_id;

また、長時間アクセスしていないデータセットを洗い出せば、データセットの削除やデータセットを移行することでストレージを削減し、料金削減にも繋がります。

SELECT
  s.schema_name,
  j.latest_creation_time
FROM
  `region-us.INFORMATION_SCHEMA.SCHEMATA` AS s
LEFT JOIN (
  SELECT
    referenced_table.dataset_id AS dataset_id,
    MAX(creation_time) AS latest_creation_time
  FROM
    `region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT`,
    UNNEST(referenced_tables) AS referenced_table
  WHERE
    referenced_table.table_id != '__TABLES__'
  GROUP BY
    referenced_table.dataset_id ) AS j
ON
  j.dataset_id = s.schema_name
WHERE j.latest_creation_time IS NULL
  OR j.latest_creation_time < TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL 90 DAY);