トップ > BigQuery > 無駄打ちになっているBigQueryのスケジュールクエリを列挙する

無駄打ちになっているBigQueryのスケジュールクエリを列挙する

BigQuery

背景: 無駄なスケジュールクエリを撲滅したい

  • スケジュールクエリは便利
    • エンジニアでない方でもクエリを手軽に定期的に実行できるようになる
  • 一方で、設定したけどしばらくすると生成したテーブルを全然見てない(=無駄打ちになっている)...というクエリが徐々に増えてくる
    • スロットやスキャン量も無駄になっており、余分なコストを払うことになってしまうため、検知しておきたい
  • 同様のモチベーションで以下のツールを作っていましたが、今回はスケジュールクエリに特化したバージョンです
    • ↓はGASやCRONなど色々なジョブ基盤に対応したものだった
    • 今回はスケジュールクエリに特化しているので、適用範囲は狭いものの設定箇所の同定などはよりやりやすくなっている

準備: スケジュールクエリの設定値をBigQueryのテーブルに詰め込む

Cloud Asset Inventoryでスケジュールクエリの設定がエクスポートできると話は簡単だったのですが、まだサポートされていない状況でした。よって、自分で何とかします...。

スケジュールクエリの設定がSQLでさっと引けるようになると後段の分析がしやすいので、bqコマンドをベースに設定値を引っこ抜けるようにします。以下のようなスクリプトを用意しました。

スケジュールクエリの設定をBigQueryのテーブルにアップロードするスクリプト

#!/bin/sh
set -eu -o pipefail
# スケジュールクエリの設定値をBigQueryのテーブルにアップロードするスクリプト

# アップロード先
OUTPUT_PROJECT_ID=""
OUTPUT_DATASET_ID=""
OUTPUT_TABLE_ID=""

SCHEDULE_QUERY_PROJECT_ID=""
OUTPUT_DATASET_ID=""
TRANSFER_CONFIG_FILENAME="transfer_config.tsv"

usage() {
  cat <<EOF
usage:
  $0 --schedule_query_project SCHEDULE_PROJECT --output_project PROJECT --output_dataset DATASET --output_table TABLE
EOF
  exit 1
}

flag_parse() {
  for i in $*; do
    case $i in
      "--schedule_query_project")
        SCHEDULE_QUERY_PROJECT_ID="1"
        ;;
      "--output_project")
        OUTPUT_PROJECT_ID="1"
        ;;
      "--output_dataset")
        OUTPUT_DATASET_ID="1"
        ;;
      "--output_table")
        OUTPUT_TABLE_ID="1"
        ;;

      "--help")
        usage
        ;;
      *)
        [[ "${SCHEDULE_QUERY_PROJECT_ID}" == "1" ]] && SCHEDULE_QUERY_PROJECT_ID=$i && continue
        [[ "${OUTPUT_PROJECT_ID}" == "1" ]] && OUTPUT_PROJECT_ID=$i && continue
        [[ "${OUTPUT_DATASET_ID}" == "1" ]] && OUTPUT_DATASET_ID=$i && continue
        [[ "${OUTPUT_TABLE_ID}" == "1" ]] && OUTPUT_TABLE_ID=$i && continue

        usage
        ;;
    esac
    shift
  done
}

# main
[[ $# == "0" ]] && usage
flag_parse $*

rm "${TRANSFER_CONFIG_FILENAME}" || true

bq --format json  ls --project_id ${SCHEDULE_QUERY_PROJECT_ID} --transfer_config --transfer_location="us" | jq -crM '.[] | .name' | while read -r transfer_config_name; do
  echo "${transfer_config_name}"
  # transfer_config=$(bq --format json show --project_id "${PROJECT_ID}" --transfer_config "${transfer_config_name}")

  TMP_FILE=tmp.json
  bq --format json show --project_id "${SCHEDULE_QUERY_PROJECT_ID}" --transfer_config "${transfer_config_name}" > "${TMP_FILE}"

  transfer_config_id=$(cat "${TMP_FILE}" | jq -r '.name')
  display_name=$(cat "${TMP_FILE}" | jq -r '.displayName')
  dataset_name=$(cat "${TMP_FILE}" | jq -r '.destinationDatasetId')
  table_name=$(cat "${TMP_FILE}" | jq -r '.params.destination_table_name_template')
  email=$(cat "${TMP_FILE}" | jq -r '.ownerInfo.email')
  disabled=$(cat "${TMP_FILE}" | jq -r '.disabled')

  echo "${transfer_config_id}\t${display_name}\t${SCHEDULE_QUERY_PROJECT_ID}\t${dataset_name}\t${table_name}\t${email}\t${disabled}" >> "${TRANSFER_CONFIG_FILENAME}"
  rm "${TMP_FILE}"
done

bq load --autodetect \
  --project_id "${OUTPUT_PROJECT_ID}" \
  --source_format CSV --field_delimiter="\t" \
  --replace --null_marker=null \
  "${OUTPUT_PROJECT_ID}:${OUTPUT_DATASET_ID}.${OUTPUT_TABLE_ID}" \
  "${TRANSFER_CONFIG_FILENAME}" \
  "transfer_config_id:STRING,display_name:STRING,project_name:STRING,dataset_name:STRING,table_name:STRING,email:STRING,disabled:BOOLEAN"

rm "${TRANSFER_CONFIG_FILENAME}"

こんな感じで動かすと、スケジュールクエリの設定がBigQueryのテーブルにアップロードされて準備が完了です。

./not_in_use_scheduled_queries --schedule_query_project schedule_project --output_project my-project --output_dataset my_dataset --output_table transfer_config

ちなみに組織内でスケジュールクエリによるスロット消費量が多い上位のプロジェクトは以下のSQLで出すことができます。スケジュールクエリによって発行されたジョブにはラベルが付与されているので、それを使うとさっと出せます。

SELECT
  project_id,
  SUM(total_slot_ms / (1000 * 60 * 60)) AS total_slot_hour
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION,
  UNNEST(labels) AS labels
WHERE
  DATE(creation_time, "Asia/Tokyo") > DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 14 DAY)
  AND labels.key = "data_source_id"
  AND labels.value = "scheduled_query"
  AND reservation_id IS NOT NULL
GROUP BY
  project_id
ORDER BY
  total_slot_hour DESC
LIMIT
  100

無駄打ちになっているスケジュールクエリを列挙する

準備ができたので、無駄打ちになっているスケジュールクエリをSQLで列挙していきます。例によって、INFORMATION_SCHEMAとの合わせ技です。SQL内では以下のことをやってます。

  • スケジュールクエリの設定値を元にdestination tableを割り出す
    • BigQuery Scriptingはdestination tableが設定されないので、今回の調査では漏れてしまうことに注意です
  • 直近(90日以内)に参照されたテーブルIDを取得
  • スケジュールクエリのdestination tableとINFORMATION_SCHEMAのdestination tableが合致するものをJOIN
    • is_referedカラムを使うと、最近使われていないものだけに絞り込みができます
    • あるスケジュールクエリでどれだけスロットが利用されているか、スキャン量が発生しているかをData Studioなどで簡単に分析できます
WITH transfer_config AS (
  SELECT
    * EXCEPT(disabled)
  FROM
    `my-project.my_dataset.transfer_config`
  WHERE
    disabled IS NOT TRUE
    AND project_name IS NOT NULL
    AND dataset_name IS NOT NULL
    AND table_name IS NOT NULL
),
referenced_tables AS ( -- 90日以内に参照されたテーブルのid一覧
  SELECT
    DISTINCT referenced_tables.project_id || ":" || referenced_tables.dataset_id || "." || referenced_tables.table_id AS table_id
  FROM
    `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION,
    UNNEST(referenced_tables) AS referenced_tables
  WHERE
    DATE(creation_time, "Asia/Tokyo") > DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 90 DAY)
)
SELECT
  transfer_config.project_name || ":" || transfer_config.dataset_name || "." || transfer_config.table_name IN (
    SELECT table_id FROM referenced_tables WHERE table_id IS NOT NULL
  ) AS is_refered,
  total_slot_ms / (1000 * 60 * 60) AS total_slot_hour,
  project_name || ":" || dataset_name || "." || table_name AS destination_table_name,
  "https://console.cloud.google.com/bigquery/scheduled-queries/locations/us/configs/" || REGEXP_EXTRACT(transfer_config_id, "^projects/" || project_number || "/locations/us/transferConfigs/(.*?)$") || "/runs?project=" || project_name AS url,
  *
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
RIGHT OUTER JOIN
  transfer_config
ON
  transfer_config.project_name = destination_table.project_id
  AND transfer_config.dataset_name = destination_table.dataset_id
  AND transfer_config.table_name = destination_table.table_id
WHERE
  DATE(creation_time, "Asia/Tokyo") > DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 14 DAY)
  AND project_id IN (SELECT project_name FROM transfer_config WHERE project_name IS NOT NULL)
ORDER BY
  creation_time DESC

あとはBIツールで可視化して、生成したテーブルは使われていないのにスロット消費量の多いスケジュールクエリの設定者(↑のクエリで分かりますし、設定画面のURLもカラムに入ってます)にヒアリングに行くなりすればよいですね。

Google Cloudではじめる実践データエンジニアリング入門[業務で使えるデータ基盤構築]

Google Cloudではじめる実践データエンジニアリング入門[業務で使えるデータ基盤構築]

Amazon