こんにちは。香田です。
BigQueryのボトルネック調査時や料金モデル(オンデマンド、定額)の見直しに必要となる
スロット使用状況の確認方法について解説していきます。
スロットの確認方法はいくつかあるけど、どれが確認しやすいんだっけ？
と気になったのでまとめてみました。
BigQueryのスロットとは？
BigQueryのスロットとは、SQLクエリの実行に必要な演算能力の単位で、
クエリのサイズと複雑さに基づいて、クエリごとに必要なスロットの数を自動的に計算します。
スロットの割り当て数は、オンデマンド料金でプロジェクトあたり最大2,000スロットになります。
スロット使用状況の監視について
BigQuery スロットの使用状況はおおきく3つの方法で確認できます。
- Stackdriver Monitoringで確認
- Stackdriver Loggingの監査ログをBigQueryに転送して確認
- INFORMATION_SCHEMA のジョブ ビューで確認
Stackdriver Monitoringで確認
Stackdriver Monitoringの[ダッシュボード]、[BigQuery]、[Slot Utilization]より
プロジェクトで利用可能なスロットの合計数等をチャートで確認できます。
プロジェクト全体の使用状況を見る時に有効ですね。
Stackdriver Loggingの監査ログをBigQueryに転送して確認
Stackdriver Loggingに転送されている監査ログをBigQueryに転送します。
まずは転送先のデータセットとBigQueryに転送するシンクを作成します。
- データセットを作成
bq mk --location <location> <project_id>:bq_auditlog- シンクを作成
gcloud logging sinks create bq-auditlog bigquery.googleapis.com/projects/<project_id>/datasets/bq_auditlog \
--log-filter='protoPayload.metadata."@type"="type.googleapis.com/google.cloud.audit.BigQueryAuditMetadata"'次にシンクの書き込みIDをデータセットに追加し、BigQueryデータ編集者の役割を付与します。
- 書き込みIDに指定されたサービスアカウントを確認
gcloud logging sinks describe bq-auditlog- データセットのアクセス制御をjsonへ出力
bq show --format=prettyjson bq_auditlog > bq_auditlog.json- 出力したjsonファイルのaccessセクションに下記のように確認したサービスアカウントとroleを追記します
{
"role": "WRITER",
"userByEmail": "xxxxxxxx@gcp-sa-logging.iam.gserviceaccount.com"
},- 更新したjsonファイルをもとにデータセットのアクセス制御を更新
bq update --source bq_auditlog.json bq_auditlogシンクの設定完了後しばらくすると作成したデータセットにログが転送され、クエリが実行可能になります。
- USリージョンの平均スロット使用率を調査する
SELECT
SUM(CAST(JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,
"$.jobChange.job.jobStats.totalSlotMs") AS INT64)) / (1000*60*60*24*7) AS avg_slots
FROM
`<project_id>.bq_auditlog.cloudaudit_googleapis_com_data_access_yyyymmdd`
WHERE
JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,
"$.jobChange.job.jobConfig.type") = "QUERY"
AND JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,
"$.jobChange.job.jobStatus.jobState") = "DONE"
AND resource.labels.location="US"
AND TIMESTAMP(JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,
"$.jobChange.job.jobStats.endTime")) BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
AND CURRENT_TIMESTAMP()INFORMATION_SCHEMAのジョブビューで確認
BigQueryのINFORMATION_SCHEMAを使用すると、データセット、ジョブ、テーブル等のメータデータへアクセスできます。
ジョブのメタデータに関するビューは3つあり、必要な権限や含まれるクエリ結果は下記になります。
| ビュー名 | 必要な権限 | クエリ結果 |
|---|---|---|
| INFORMATION_SCHEMA.JOBS_BY_USER | Project Viewer BigQuery User | 現在のプロジェクトで現在のユーザーが送信した全てのジョブ |
| INFORMATION_SCHEMA.JOBS_BY_PROJECT | Project Owner BigQuery Admin | 現在のプロジェクトが送信した全てのジョブ |
| INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION | Organization Owner Organization Admin | 現在のプロジェクトに関連付けられている組織が送信した全てのジョブ |
INFORMATION_SCHEMAのジョブに関するビューを使用することで、クエリを大量に発行しているユーザーや費用がかかっているクエリについて分析できます。
注意点としてはINFORMATION_SCHEMAに対するクエリにも処理料金は発生し、クエリの結果はキャッシュに保存されません。
- USリージョンの平均スロット使用率を調査する
SELECT
SUM(total_slot_ms) / (1000*60*60*24*7) AS avg_slots
FROM
`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
job_type = "QUERY"
AND state = "DONE"
AND end_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
AND CURRENT_TIMESTAMP()さいごに
INFORMATION_SCHEMAのジョブビューはデフォルトで用意されており、ベータ版ではありますが現状はこちらを使用したほうが簡単でいいのではないでしょうか。
監査ログのシンク設定やエクスポート先BigQueryの料金も発生しないので簡単に試せます。
よかったら是非お試しください。
最後までご覧頂きありがとうございました！