BigQueryのコストとパフォーマンスを最適化するための入門ガイド
課題の概要
複数のBigQueryデータセットを運用している自社に、1か月前に新しいアナリストチームが加わりクエリを使い始めた——そんな状況を想像してみてください。ある朝、コーヒー（紅茶派の方もご安心を）を片手にGCPの請求書を開くと、BQの分析・ストレージコストが5,000%も跳ね上がっていることに気づきます。思わず飲み物を吹き出し、キーボードもモニターもびしょ濡れ、という事態になりかねません。本記事では、いくつかの方法を通じてBigQueryのコストを最適化し、こうした事態を未然に防ぐ、あるいは収束させるためのヒントをお伝えします。
基本を押さえる
BigQueryのコストに手をつける前に、いくつか準備しておくべきことと、押さえておきたい前提知識があります。
本シリーズ第1回ではこの部分を扱います。基本を理解して準備が整えば、第2回以降で実際の最適化作業に一直線で進めます。
まずはBigQueryのスロット、料金モデル、そしてコストを左右する要因について見ていきましょう。
スロット
BigQueryのコンピューティングの中核にあるのが、スロットと呼ばれる単位です。スロットとは、メモリを伴うvCPUのことで、いわばBigQuery処理専用のミニVMのようなものです。BigQueryでクエリを実行すると、そのクエリは割り当てられたスロット群で処理されます。理論上は、割り当て可能なスロットが多いほどクエリの実行は速くなります。
BigQueryジョブの処理過程で発生するほぼあらゆる動作は、このスロットが担います。
シャッフルスロット
BigQueryでは、公式には記載されていないものの、スロットが担う重要な仕事の一つに「データのシャッフル」があります。プロジェクトに割り当てられたスロットのうち、最大60%が任意のタイミングでシャッフルスロットとして動作します。
ごく簡単に言えば、シャッフルとは、現在または次のクエリプランのステップを高速に実行するために、処理済みデータを別の場所に再配置する処理です。仕組みをより深く理解したい方は、BigQueryアーキテクチャに関するGoogleのホワイトペーパーをご参照ください。シャッフルはクエリの効率化に役立つ一方で、貴重なスロットを「クエリの実行を直接前進させない処理」に使うことになります。
料金モデル
BigQueryには現在、オンデマンドと定額制という2つの料金モデルがあります。
オンデマンド料金
デフォルトはオンデマンド料金で、クエリ用に2,000スロットが割り当てられ、これとは別にロード、テーブルコピー、エクスポートジョブ用として、公開されていない数のスロットがプールとして用意されます。BigQuery全体で余剰スロットが生まれると、この2,000スロットのプールが拡張されることもありますが、保証されているわけではないため、当てにすべきではありません。参考までに、私がこれまで見た最大値は、短時間のバーストで約3,500スロットほどです。
このモデルでは、クエリでスキャンしたデータ1TBあたり5ドルの定額で課金されます。
多くの組織では、これがBigQueryコストの主要因となります。
定額料金
定額（またはスロット）料金は、パフォーマンスを犠牲にする可能性と引き換えに、BigQueryのスキャンに固定価格を設定する仕組みです。具体的には、利用可能なスロット数を事前購入分に制限し、TBあたり5ドルのスキャン料金をなくします。
スロット（後述するFlex Slotsを含む）は常に100単位で購入します。標準スロット（Flex Slots以外）は、1か月または1年の期間でcommitmentとして購入します。一度予約を購入すると簡単にはキャンセルできない点に注意してください。月次commitmentから年次commitmentへの変更は可能ですが、その逆はできません。
commitment終了時の挙動は両者で異なり、これまでに何度か仕様変更があった少々ややこしい部分です。今後また変更される可能性もあるため、ここではGoogleの公式ドキュメント（月次commitmentおよび年次commitment）へのリンクを記載するに留めます。期間終了の十分前に通知が届くよう、カレンダーにイベントを設定して適切に対応できるようにしておくことを強くおすすめします。
さて、定額料金モデルでは、スロットを増強したり、Flex Slotsと呼ばれる追加プールで完全に置き換えたりすることもできます。Flex Slotsは通常スロットと容量・機能ともに同等ですが、最低60秒からというより短く柔軟なcommitment期間を選べるのが特徴です。最低時間を超えればいつでも予約をキャンセルでき、その時点でスロットは消滅します。これにより、必要に応じてスロットを柔軟にスケールアップ／ダウンできます。Flex Slotsは、利用量のスパイクや、リソース集約型のジョブによって予約内のスロットを使い切ってしまう恐れがある場合など、短期間だけスロットを増やしたいときに活用されます。代表的な例が、米国最大のショッピングデーであるブラックフライデーやサイバーマンデーを迎える小売業者です。
本記事執筆時点で、100スロットの料金は1か月commitmentで2,000米ドル、1年commitmentでは月額1,700米ドルです。したがってオンデマンドと同等のパフォーマンスを得るには、100スロット単位を20個購入する必要があり、1か月commitmentでは月額40,000米ドル、1年commitmentでは月額34,000米ドルとなります。このため、BigQueryのスキャンコストが少なくともこの金額に達していなければ、2,000スロットを購入することは費用対効果が見合わないのが一般的です。
コスト把握のための計画
コスト最適化に着手する前に、対象プロジェクトの利用状況データを分析する必要があります。そのためには、プロジェクトやデータセットのクエリデータにアクセスできる状態にしておかなければなりません。
少し補足しておくと、本シリーズを一気に読み通せない方でも早めにデータの記録を開始できるよう、この準備は第1回で取り上げています。実際にここでの準備が活きてくるのは、第3回以降です。
方法は2つあります。監査ログシンクとINFORMATION_SCHEMAテーブルです。
おすすめは監査ログシンクです。データがはるかに豊富で、複数のプロジェクト・データセット・リージョンを横断的にカバーできます。ただし、まだ有効化されていない場合は、BigQuery上でジョブが実行されるにつれてデータが蓄積されるため、十分な量がたまるまで時間がかかります。なお、監査ログシンクには、データを保存するためのBigQueryストレージコストとして若干の費用が発生します。
一方、INFORMATION_SCHEMAビューは各データセット・プロジェクト・リージョンに既に存在しています。ただし監査ログシンクと異なり、データセットとリージョンの組み合わせごとに専用のテーブルセットが用意されているため、クエリを複数回実行する必要が出てくる場合があります。また、INFORMATION_SCHEMAテーブルには、監査ログテーブルに存在する情報の一部が含まれていない点にも注意が必要です。具体例は本記事後半のSQLクエリ内にコメントとして記載しており、監査ログシンクのクエリとは完全には一致しない箇所があります。
INFORMATION_SCHEMA方式を使う場合は、データはすでに存在するため、次の監査ログシンク設定のセクションは飛ばしていただいて構いません。ただし注意点として、監査ログシンクで収集できるデータはINFORMATION_SCHEMAビューよりも豊富です。一方で、シンクは設定した時点からデータの記録が始まるため、利用可能な状態になるまで通常のworkloadsをある程度の期間動かしておく必要があります。必要な期間は、実行されるworkloadsの内容や、自社にとって「通常」とされる実行頻度に大きく依存します。
注：現在DoiTのお客様で、当社のCloud Management Platform（CMP）のBigQuery Lensダッシュボードをご利用の場合、すでに環境内に監査ログシンクが設定されています。本記事で説明する操作にはそちらをご活用いただくことをおすすめします。詳細はこちらをご覧ください。
データセットの完全修飾名は
該当する場合は、次のセクションを飛ばして構いません。
監査ログシンクのセットアップ
監査ログシンクではなくINFORMATION_SCHEMAビューを利用する場合は、このセクションは飛ばしてください。
以下は監査ログシンクの設定手順です。これらのコマンドは、データセットを保存したいプロジェクトを指定したgcloud CLI環境で、ローカルホストまたはCloud Shellから実行するのが望ましいです。
まず以下のコマンドを実行します。シンク名、プロジェクトID、データセット名は適宜書き換えてください。
gcloud logging sinks create <Sink Name> bigquery.googleapis.com/projects/<Project ID>/datasets/<Dataset Name> \ — use-partitioned-tables \
— log-filter=’protoPayload.metadata."@type"="type.googleapis.com/google.cloud.audit.BigQueryAuditMetadata"’
このコマンドを実行すると、用途に合わせて作成されたサービスアカウントが出力されます。このサービスアカウントの完全なメールアドレスをメモしておいてください。
次に、作成したばかりのサービスアカウントに対し、BigQueryを使用する各プロジェクトでBigQuery編集者ロールを付与します。クラウドでは例によって複数の方法があるため、ここでは代表的な3パターン、プロジェクト単位、組織全体、組織内のフォルダ単位を紹介します。
なお、プロジェクト・フォルダ・組織のIDは、GCPコンソール上部の「プロジェクトピッカー」から確認できます。一覧のIDカラムに、対象リソースのIDが表示されます。
プロジェクト単位：
gcloud projects add-iam-policy-binding <Project ID> \
— member=<Service Account> — role=roles/bigquery.dataEditor
組織全体：
gcloud organizations add-iam-policy-binding <Organization ID> \
— -member=<Service Account> — role=roles/bigquery.dataEditor
フォルダ単位：
gcloud resource-manager folders add-iam-policy-binding <Folder ID> \
— -member=<Service Account> — role=roles/bigquery.dataEditor
ここまで完了すると、上記で指定したデータセットにクエリデータが保存され始めます。実行後に十分な量のデータを得るには、最低でも数日、できれば数週間ほど待つことをおすすめします。通常利用のデータが蓄積される期間が長いほど、利用状況のスパイクやパターンを発見しやすくなります。
INFORMATION_SCHEMAビュー
INFORMATION_SCHEMAビューは各データセット・プロジェクト・リージョンに既に存在していますが、監査ログシンクと異なり、データセットとリージョンの組み合わせごとに専用のテーブルセットが用意されているため、クエリを複数回実行する必要が出てくる場合があります。また、INFORMATION_SCHEMAビューでは、監査ログテーブルに存在する情報の一部が省かれています。具体例は本シリーズ後半のSQLクエリ内にコメントとして記載しており、監査ログシンクのクエリとは完全には一致しない箇所があります。
INFORMATION_SCHEMAビューの利点は、シンクを設定していなくてもデータがすでに存在している点です。
次回予告
以上で本シリーズ第1回は終了です。本記事は、シリーズ全体で解説する手法の土台となる内容となっています。