こんにちは。　code poet です。

はじめに

データの効率的な管理とコストの最適化を意識する場面ってありますよね。BigQuery 追加されたプレビュー機能 INFORMATION_SCHEMA のビューは、プロジェクトのデータ処理を最適化するための方法を提供します。本記事では、この機能を紹介し、活用方法について解説します。

目次

INFORMATION_SCHEMA.INSIGHTSとは
INFORMATION_SCHEMA.RECOMMENDATIONSとその活用方法
INFORMATION_SCHEMA.RECOMMENDATIONS_BY_ORGANIZATIONとその活用方法
参考文献

INFORMATION_SCHEMA.INSIGHTSとは

INFORMATION_SCHEMA.INSIGHTSは、現在のプロジェクト内で利用可能なBigQueryの推奨事項に関する洞察を提供するビューです。このビューは、Recommendation Hubから取得されたすべてのBigQueryインサイトを一元的に表示し、データセットのパーティショニングやクラスタリング、マテリアライズドビューの推奨事項、ロール推奨などの情報を含みます。

コスト削減のためのアクティブなインサイトの確認

以下のクエリ例は、コストカテゴリでアクティブなインサイトに関連する推奨事項を3つ表示します。推奨事項に基づく月次の節約見込み（GB単位のストレージ削減量、スロット時間の削減量）も一緒に表示されます。

WITH
  -- BigQueryの推奨事項に関するインサイトを取得するクエリ
  insights AS (
  SELECT
    *
  FROM
    `region-us`.INFORMATION_SCHEMA.INSIGHTS),
  -- BigQueryの推奨事項そのものに関するデータを取得するクエリ
  recs AS (
  SELECT
    recommender,
    recommendation_id,
    additional_details
  FROM
    `region-us`.INFORMATION_SCHEMA.RECOMMENDATIONS)
SELECT
  -- 推奨を行ったRecommenderのタイプ
  recommender,
  -- 対象リソースの名前
  target_resources,
  -- 毎月推定される保存データ量（GB単位）を計算
  LAX_INT64(recs.additional_details.overview.bytesSavedMonthly) / POW(1024, 3) AS est_gb_saved_monthly,
  -- 毎月推定されるスロット時間の節約量を計算（時間単位）
  LAX_INT64(recs.additional_details.overview.slotMsSavedMonthly) / (1000 * 3600) AS slot_hours_saved_monthly,
  -- インサイトが観察された期間を日数に変換
  insights.additional_details.observation_period_seconds / 86400 AS observation_period_days,
  -- インサイトが最後に更新された日時
  last_updated_time
FROM
  -- インサイトと推奨事項を結合
  insights
JOIN
  recs
ON
  -- 推奨事項IDがインサイトに含まれていることを確認
  recommendation_id IN UNNEST(associated_recommendation_ids)
WHERE
  -- 状態が「ACTIVE」であることを確認
  state = 'ACTIVE' AND
  -- カテゴリが「COST」であることを確認
  category = 'COST'
LIMIT
  3;

このクエリにより、推奨事項に関連するリソースと、観察期間中にどれだけのコスト削減が可能かが確認できます。

INFORMATION_SCHEMA.RECOMMENDATIONSとその活用方法

INFORMATION_SCHEMA.RECOMMENDATIONSビューは、現在のプロジェクト内のすべてのBigQuery推奨事項に関するデータを提供します。このビューを使用することで、データセットのパーティショニングやクラスタリング、マテリアライズドビューの作成に関する推奨事項を確認できます。

プロジェクト全体のコスト削減推奨事項

以下のクエリ例は、プロジェクト全体でスロット時間を節約する推奨事項を表示し、推奨事項の優先度に基づいてソートします。

  -- 推奨の種類、対象リソース、推定の月間保存容量（GB）、推定の月間保存スロット時間、および最終更新時間を選択
SELECT
  recommender,
  -- 推奨の種類（例: パーティションとクラスタリング、マテリアライズドビューなど）
  target_resources,
  -- 推奨の対象となるリソース（例: テーブル名、プロジェクト名など）
  LAX_INT64(additional_details.overview.bytesSavedMonthly) / POW(1024, 3) AS est_gb_saved_monthly,
  -- 推定の月間保存容量（GB）
  LAX_INT64(additional_details.overview.slotMsSavedMonthly) / (1000 * 3600) AS slot_hours_saved_monthly,
  -- 推定の月間保存スロット時間（時間単位）
  last_updated_time  -- 推奨が最後に更新された日時
FROM
  `region-us`.INFORMATION_SCHEMA.RECOMMENDATIONS_BY_PROJECT  -- プロジェクト単位での推奨を格納しているビューを指定
WHERE
  primary_impact.category = 'COST'  -- コスト削減に関連する推奨のみをフィルタリング
  AND state = 'ACTIVE'  -- 現在アクティブな状態の推奨のみをフィルタリング
ORDER BY
  slot_hours_saved_monthly DESC  -- 月間保存スロット時間が多い順に並び替え
LIMIT
  3;  -- 結果を上位3件に制限

このクエリにより、最も効果的なコスト削減が見込まれる推奨事項を簡単に確認でき、リソースの最適化が行いやすくなります。

INFORMATION_SCHEMA.RECOMMENDATIONS_BY_ORGANIZATIONとその活用方法

INFORMATION_SCHEMA.RECOMMENDATIONS_BY_ORGANIZATIONビューは、組織内のすべてのプロジェクトに対するBigQuery推奨事項に関するデータを提供します。このビューを活用することで、組織全体のデータ最適化を検討する際に活用できます。

組織全体のコスト削減推奨事項

以下のクエリ例では、組織全体のコスト削減推奨事項を表示します。

SELECT
  -- プロジェクトIDを選択します
  project_id,
  -- 月ごとのバイト削減量をギガバイト単位に変換して、推定値を表示します
  LAX_INT64(additional_details.overview.bytesSavedMonthly) / POW(1024, 3) AS est_gb_saved_monthly,
  -- 月ごとのスロット時間削減量を時間単位に変換して、推定値を表示します
  LAX_INT64(additional_details.overview.slotMsSavedMonthly) / (1000 * 3600) AS slot_hours_saved_monthly,
  -- 推奨事項が最後に更新された時間を表示します
  last_updated_time
FROM
  -- データを取得するテーブルを指定します。region-usリージョンのRECOMMENDATIONS_BY_ORGANIZATIONビューからデータを取得します
  `region-us`.INFORMATION_SCHEMA.RECOMMENDATIONS_BY_ORGANIZATION
WHERE
  -- 'google.bigquery.materializedview.Recommender'リコメンダーによる推奨事項のみをフィルタリングします
  recommender = 'google.bigquery.materializedview.Recommender'
  -- 結果の上位3件のみを取得します
LIMIT
  3;

このクエリにより、組織全体でどのプロジェクトが最も効果的にデータを削減できるかが分かります。

まとめ

今回は、最新のリリースに関する3つのビュー「INFORMATION_SCHEMA.INSIGHTS」「INFORMATION_SCHEMA.RECOMMENDATIONS」「INFORMATION_SCHEMA.RECOMMENDATIONS_BY_ORGANIZATION」について解説しました。これらのビューを活用することで、データの最適化やコスト削減に役立てていきたいですね。

参考文献