👓

【GCP】bigqueryのリソース確認

2023/10/12に公開

Google Cloud

tech

概要

bigqueryのリソース確認を行う際に利用するクエリをまとめてみます。
リソースの競合状況や
コストの高いクエリが発行されているかなどを確認する際に役立ちます。

利用するメタデータ

今回はJOBS_BY_PROJECTを利用します。
出力内容の詳細は以下の公式ドキュメントから確認できます。

クエリ

実行中のクエリ

現在実行中のクエリを検出します。

SELECT
distinct
 job_id,
 FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', creation_time, 'Asia/Tokyo') AS jst_creation_time,
 FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', end_time, 'Asia/Tokyo') AS jst_end_time,
 user_email,
 query,
 state,
 trunc(total_bytes_processed/(1024*1024*1024), 1) as total_gb
FROM `プロジェクト名.region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
DATE(creation_time,'Asia/Tokyo') = DATE("2023-10-11") ★★★★★ 適宜日付を変更 ★★★★★
AND state = 'RUNNING'
order by jst_creation_time

SLOT競合が発生したクエリ

SLOT競合が発生したクエリを検出します。
SLOT競合が発生したかどうかはクエリ実行後にステータスが設定されるので
検出結果に実行中のクエリは含まれません。

SELECT
distinct
 job_id,
 FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', creation_time, 'Asia/Tokyo') AS jst_creation_time,
 FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', end_time, 'Asia/Tokyo') AS jst_end_time,
 user_email,
 query,
 trunc(total_bytes_processed/(1024*1024*1024), 1) as total_gb
FROM `プロジェクト名.region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT,unnest(query_info.performance_insights.stage_performance_standalone_insights) u
WHERE
DATE(creation_time,'Asia/Tokyo') = DATE("2023-10-11") ★★★★★ 適宜日付を変更 ★★★★★
AND cast(u.slot_contention AS STRING) = 'true'
--AND user_email = 'XXXXX@aaa.jp'　★★★★★　発行ユーザを限定したい場合はここでアカウントを指定 ★★★★★
order by jst_creation_time

タイムアウトしたクエリ

TIMEOUTしたクエリを検出します。
bigqueryではデフォルト6時間でTIMEOUTします。
TIMEOUT時間は設定により変更可能です。

SELECT
 job_id,
 FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', creation_time, 'Asia/Tokyo') AS jst_creation_time,
 FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', end_time, 'Asia/Tokyo') AS jst_end_time,
 user_email,
 query,
 query_info,
 state,
 error_result,
 trunc(total_bytes_processed/(1024*1024*1024), 1) as total_gb
FROM `プロジェクト名.region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
DATE(creation_time,'Asia/Tokyo') = DATE("2023-10-11") ★★★★★ 適宜日付を変更 ★★★★★
AND error_result.reason = 'timeout'

長時間稼働クエリ

当日の実行済・現在実行中のクエリの中から、
本クエリ発行時点で長時間稼働しているクエリを検出します。
実行中のクエリについては、実行開始時間から本クエリ発行までの時間で判断しています。

SELECT
distinct
 job_id,
 FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', creation_time, 'Asia/Tokyo') AS jst_creation_time,
 FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', end_time, 'Asia/Tokyo') AS jst_end_time,
 TIMESTAMP_DIFF(IF(end_time is null, CURRENT_TIMESTAMP(),end_time), creation_time,minute) AS total_exe_minites,
 state,
 user_email,
 query,
 trunc(total_bytes_processed/(1024*1024*1024), 1) as total_gb
FROM `プロジェクト名.region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
DATE(creation_time,'Asia/Tokyo') = CURRENT_DATE('Asia/Tokyo')
AND TIMESTAMP_DIFF(IF(end_time is null, CURRENT_TIMESTAMP(),end_time), creation_time,minute) > 60 ★★★★★ 適宜時間を指定 ★★★★★
order by total_exe_minites desc