🫶
BigqueryとLookerstudioの監視まとめ
背景
監視周りの情報を久しぶりに調べなおしたので簡単にまとめた
目的
- みなさんも簡単に監視を始められるようにする
Caveat
一部どうしても置き換えてもらう必要のある変数がある
gcp_project_id = hoge
gcp_dataset_id = fuga
この記事では基本的に Bigquery の INFORMATION_SCHEMA を使う
INFORMATION_SCHEMA はアップデートも頻繁にあるらしい、ので動かない場合はコメントくださるとうれしい
内容
テーブルの最終更新日
SELECT * FROM `gcp_project_id.gcp_dataset_id`.__TABLES__
実は上は非推奨らしいので、下を使うこと
SELECT * FROM `gcp_project_id.gcp_dataset_id`..INFORMATION_SCHEMA.PARTITIONS
データセットのレコード総数とデータ総量
SELECT
table_schema, SUM(total_rows) AS total_rows, ROUND(SUM(total_logical_bytes) / (1024 * 1024), 2) AS size_mb, ROUND(SUM(total_logical_bytes) / (1024 * 1024 * 1024), 2) AS size_gb
FROM
`region-asia-northeast1`.INFORMATION_SCHEMA.TABLE_STORAGEGROUP BY
table_schema
SELECT * FROM `region-asia-northeast1`.INFORMATION_SCHEMA.SCHEMATASELECT * FROM `gcp_dataset_id`.INFORMATION_SCHEMA.TABLES
10日以上アクセスがないデータセット
SELECT
s.schema_name, j.latest_creation_timeFROM
`region-asia-northeast1`.INFORMATION_SCHEMA.SCHEMATA AS s
LEFT JOIN (
SELECT
referenced_table.dataset_id AS dataset_id, MAX(creation_time) AS latest_creation_time
FROM
`region-asia-northeast1`.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 -- ジョブメタデータにアクセス履歴がない、または最終クエリ実行日より10日以上経過したデータセット
WHERE
j.latest_creation_time IS NULL
OR j.latest_creation_time < TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL 10 DAY) ;
高額クエリ
SELECT
user_email, SUM(total_bytes_processed) AS total_bytes_processed, -- Byte to TB
SUM(total_bytes_processed) / 1024 / 1024 / 1024 /1024 AS total_TB_processed, -- TB to Dollar
-- asia-northeast1を想定し$6/TB
SUM(total_bytes_processed) / 1024 / 1024 / 1024 /1024 * 6.0 AS Charges_Dollar,FROM
`region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECTWHERE
DATE(creation_time) BETWEEN DATE_ADD(CURRENT_DATE('Asia/Tokyo'), INTERVAL -30 DAY ) AND CURRENT_DATE('Asia/Tokyo')
GROUP BY 1ORDER BY 2 DESC
特定のデータセット/テーブルを参照しているクエリの取得
SELECT
user_email, COUNT(user_email) AS query_count
FROM
`region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECTWHERE
DATE(creation_time) BETWEEN DATE_ADD(CURRENT_DATE('Asia/Tokyo'), INTERVAL -30 DAY )
AND CURRENT_DATE('Asia/Tokyo')
AND REGEXP_CONTAINS(query, r'`gcp_dataset_id.*`')
GROUP BY
1ORDER BY
2 DESC
頻繁に参照されているテーブルの把握
SELECT
t.project_id, t.dataset_id, t.table_id, COUNT(1) AS ref_nums -- テーブルの参照回数
FROM
`region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT j
-- referenced_tables カラムの配列を行へ変換
CROSS JOIN
UNNEST(j.referenced_tables) t
WHERE
-- creation_time がパーティショニング カラムとなっているため、
-- コスト節約のために、このカラムでの絞り込みを行うことを推奨
DATE(j.creation_time) = CURRENT_DATE()
AND j.job_type = 'QUERY' -- クエリ ジョブのみを検索対象
AND j.statement_type = 'SELECT' -- select クエリのみを検索対象
AND j.state = 'DONE' -- 実行が完了したジョブのみを検索対象
AND j.error_result IS NULL -- 正常終了したジョブのみを検索対象
GROUP BY
t.project_id, t.dataset_id, t.table_idORDER BY
ref_nums DESC
LIMIT
5
長時間稼働クエリ
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
`gcp_project_id.gcp_dataset_id`.INFORMATION_SCHEMA.JOBS_BY_PROJECTWHERE
DATE(creation_time,'Asia/Tokyo') = CURRENT_DATE('Asia/Tokyo')
AND TIMESTAMP_DIFF(
IF
(end_time IS NULL, CURRENT_TIMESTAMP(),end_time), creation_time,minute) > 60ORDER BY
total_exe_minites desc
定額スロット数の概算見積もり
デフォルトではオンデマンド設定になっているはずで、毎月 1TiB までは無料で使える
一部のユーザーにとっては最適なスロット数を計算することでコスト最適化ができる
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-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECTGROUP BY
DATE(TIMESTAMP_TRUNC(creation_time, MONTH), "Asia/Tokyo")
ただし BigQuery Slot Recommender という公式のサービスがあるのでこっちを使った方がいいもしれない
INFORMATION SCHEMAを実体化
下の参考文献にも書いてあるように INFORMATION SCHEMA は過去180日間分しか保持しない
よってそれ以上の期間で監視したい場合は実体化して保持しておこう
Data retention history of the past 180 days.
Lookerstudioごとのクエリ量
CREATE TEMP FUNCTION
GetLabel(labels ANY TYPE, label_key STRING) AS ( (
SELECT
l.value FROM
UNNEST(labels) l
WHERE
l.key = label_key) );WITH
job_list AS (
SELECT
GetLabel(labels, "looker_studio_report_id") AS report_id, total_bytes_billed, FROM
`region-asia-northeast1`.INFORMATION_SCHEMA.JOBS WHERE
DATE(creation_time, "Asia/Tokyo") BETWEEN DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 30 DAY)
AND DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 0 DAY)
AND GetLabel(labels, 'requestor') = 'looker_studio' )
SELECT
report_id, SUM(total_bytes_billed)/1024/1024/1024 AS total_gb_billed
FROM
job_list
GROUP BY
report_id
エラーが出たクエリ
前提条件として cloud logging の sink の作成が必要
SELECT
protopayload_auditlog.authenticationInfo.principalEmail, resource.labels.project_id, timestamp AS err_timestamp, protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.query AS err_query, protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatus.error.message AS err_message
FROM
logging.cloudaudit_googleapis_com_data_accessWHERE
protopayload_auditlog.methodName = 'jobservice.jobcompleted' AND severity = 'ERROR' AND timestamp >= TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -1 day)
その他の便利クエリ
公式が紹介しているクエリも非常に便利なものが多いので一度見て欲しい
https://cloud.google.com/blog/ja/products/data-analytics/bigquery-audit-logs-pipelines-analysis
Lookerstudioの弱アクセスログ
lookerstudio に ga4 の設定を行うことができ、どのページがよく閲覧されているかがわかる
https://support.google.com/looker-studio/answer/7410792?hl=en
Lookerstudioの強アクセスログ
上のログだけではたとえば誰が見たかはわからないので特定の部署等の需要は把握しずらい
そこでこちらのログを使うことで「いつ、誰が、どこから、どのレポートへアクセスしたか」などを確認できる。エクスポートなどのイベントのログも取れるようだ
しかし、強い権限が必要なので社内の情シスに依頼が必要だと思われる、私はそうだった
怖い話
最近Twitterで話題になってたので貼っておく
参考
Discussion