🌟

意外と使える!こんなところにもBigQuery

はじめに

フェズでSREをしている三井です
SREを名乗っていますが、クラウド全体のコストやセキュリティの管理もミッションとしています
フェズではBigQueryを主に購買データ分析等の用途で利用していますが、BigQueryにはクラウド管理の用途として利用できる機能もあります
便利に使えば運用担当者の負荷を下げる事もできるので、今回はその機能をご紹介しようと思います
 

コスト (Cloud Billing)

従量課金のクラウドを利用する上で、コストを正しく把握することは必要不可欠です
Google Cloudは、組織毎に Cloud Billing でProjectやサービスレベルでコストを把握する事ができます
Cloud Billing自体にはレポートやダッシュボード機能があり、ある程度詳細な請求データを可視化することができます
ですが、Cloud Billingの閲覧にはGoogle Cloudの権限を持つ必要があり、非エンジニアや経営層レベルに共有するのは少し難しいです

Cloud Billing データを BigQuery にエクスポートする

Cloud Billing データを BigQuery にエクスポートする | Google Cloud
BigQuery への Cloud Billing のエクスポート機能を使用することで、指定した BigQuery データセットに Google Cloud の詳細な課金データ(使用量、費用予測、料金データなど)を終日、自動的にエクスポートできます。これにより、BigQuery から Cloud Billing データにアクセスして、詳細な分析を行うことが可能になります。また、 Looker Studio などのツールを使用してデータを可視化することもできます。このエクスポート メソッドでは、データを JSON ファイルにエクスポートすることもできます。 タイミングが重要です。分析のニーズに合わせて、より包括的な Google Cloud 課金データのセットにアクセスできるようにするには、 Cloud 請求先アカウントを作成する と同時に BigQuery への Cloud Billing データのエクスポートを有効にすることをおすすめします。 BigQuery への請求データのエクスポートに影響する可能性がある 制限事項 をご確認ください。 コストを効率的に管理し、報告することは、財務管理に欠かせない作業です。これは、数十億ドル規模の事業の場合も、小規模な予算の場合も同じことです。Google Cloud のコストと使用量についてデータドリブンな意思決定を行うには、その判断に必要なデータを収集することから始める必要があります。 以下のタスクについては、このセクションのガイドをご覧ください。 Cloud Billing データの収集を開始するには、 BigQuery への Cloud Billing データのエクスポートを有効にする 必要があります。 設定ガイド
Cloud Billingには詳細な請求データをBigQueryにエクスポートする機能があります
BigQueryにエクスポートした請求データをBIツールでダッシュボード化して共有できれば、管理者が都度レポートを作成する手間を省くことができます
エクスポート手順は上記公式ドキュメントに記載があるので省略しますが、GUIからポチポチするだけで設定可能です

エクスポート先の課金データテーブル

  • 標準の使用料金テーブル : アカウント ID、請求書の日付、サービス、SKU、プロジェクト、ラベル、ロケーション、費用、使用量、クレジット、調整、通貨など
課金データテーブルは上記の3種類が用意されています
スキーマ情報は各リンク先で参照できるので、利用用途に応じて必要なデータテーブルを選択してください
フェズでは要件として 標準の使用料金テーブル で十分だったので、有効化した結果以下のようなテーブルが作成されました
※ 有効化する前の請求データは エクスポートされない のでご注意ください
 

請求データを分析する

請求データがエクスポートできたら、あとはBigQueryにクエリを実行するだけです
公式ドキュメント にサンプルクエリが紹介されていますのでご参照ください
例えば簡単な例として、Projectごとに前月の請求額を算出したい 場合は以下のようなクエリを実行すればOKです
もう少し実戦的な例として、企業の部門やグループごとにコストを按分したい ケースがあるかと思います
例えば以下のように、Projectのコストが組織のどの部門やグループに紐づくかを台帳的に管理するテーブル( Name: )を事前に用意します
このテーブルと請求データテーブルをJOINさせれば、部門やグループごとに請求コストが算出できます
 

セキュリティ (Security Command Center)

以前 こちら の記事でもご紹介したのですが、フェズでは Security Command Center(SCC) を使って脆弱性の検知を実施しています
SCCは独自でダッシュボード機能を持っており、Google Cloudのコンソール上で可視化することは可能です
ですが、こちらも Cloud Billing と同様に閲覧にはGoogle Cloudの権限を持つ必要があり、関係者への共有がやはり難しいです

SCC検出結果を BigQuery にエクスポートする

SCCにも公式に検出結果をBigQueryにエクスポート機能があります
分析のために検出結果を BigQuery にエクスポートする | Security Command Center | Google Cloud
このページでは、分析のために組織の検出結果を BigQuery データセットにエクスポートするように、Security Command Center を構成する方法について説明します。 BigQuery は、Google Cloud のペタバイト規模の費用対効果に優れたフルマネージド型の分析データ ウェアハウスです。膨大な量のデータに対してほぼリアルタイムで分析を行うことができます。BigQuery を使用すると、新規および更新された検索結果にクエリを実行して、データをフィルタして必要なものを探し、カスタム レポートを生成できます。BigQuery の詳細については、 BigQuery のドキュメント をご覧ください。 この機能を有効にすると、Security Command Center に書き込まれた新しい検出結果が、ほぼリアルタイムで BigQuery テーブルにエクスポートされます。このデータを既存のワークフローに統合し、カスタム分析を作成できます。この機能を組織、フォルダ、プロジェクトのレベルで有効にして、要件に基づいて検出結果をエクスポートできます。 この機能は、Security Command Center の検出結果を BigQuery にエクスポートする際におすすめの方法です。これはフルマネージド ソリューションであり、手動操作やカスタムコードの作成が不要です。 この機能により、 source_id、、 でクラスタ化された findings テーブルに、新しい検出結果とその後の更新が新しい行として追加されます。 検出結果が更新されると、複数の検出レコードが作成されます。 source_id と finding_id の値は同じですが、 event_time の値は異なります。このデータセット構造を使用して、検出状態の変化を時系列で把握できます。 データセット内でエントリが重複している可能性があります。これらのエントリを解析するには、 最初のクエリ例に示すように、 DISTINCT 句を使用します。 各データセットには findings テーブルが含まれています。テーブルには次のフィールドがあります。 source_id
こちらもエクスポートする手順は上記の公式ドキュメントにある通りですが、この記事を書いている時点ではGUIではなくgcloudコマンドやSDKで有効化します

エクスポート先のデータテーブル

こちらもエクスポートを有効にすると自動的に以下のようなテーブルが作成されます
データセットの構造については 公式ドキュメント をご参照ください

SCC検出結果を分析する

エクスポートしたSCC検出結果を分析するためのクエリが 公式ドキュメント に紹介されています
例えば、最新のアクティブな検出結果 をProjectごとに出力したければ以下のようなクエリを実行すればOKです

BIツールとの連携

「BigQueryにエクスポートしたデータにクエリを実行して詳細な分析ができるよ」
というご紹介をしていますが、
フェズではBigQueryにエクスポートしたデータにLooker Studio から接続してダッシュボード化をしています
(ダッシュボードのご紹介は機密情報が多くモザイクだらけになるので割愛しました)
今回ご紹介したCloud BillingとSCCはどちらも独自にダッシュボードやレポート機能があり、可視化自体はBigQueryを利用しなくても実現可能です
ですが、非エンジニアや経営層に情報を伝えるためには可視化ツールやデータの切り口は、組織ごとにカスタマイズが必要になるケースが多いと思います
実際にフェズでもLooker Studioと連携する事で必要な人に必要な情報を提供する事が実現できました
BigQueryは色んなBIツールと連携可能なので、エクスポートしておくと何かと役に立つかもしれません

まとめ

ということで、BigQueryの実は便利な機能のご紹介でした
他にも自分が知らないだけでBigQueryを便利に使い倒す方法はきっとあるので、発見できたらまた記事にしようかなと思います