BigQueryはビッグデータを価値ある知見に変える万能なデータウェアハウスですが、コストはあっという間に膨らみます。詳細解説シリーズの第1回として、効率的な活用法をお伝えします。
BigQueryのコストとパフォーマンスを最適化する新ガイドのご紹介
複数のBigQueryデータセットを抱え、複数のアナリストチームがクエリを実行する環境では、コストが急に跳ね上がることは珍しくありません。とはいえ、それは避けられない宿命ではありません。
最新のebook**『The BigQuery Optimization Handbook: Preparing to Save』**では、DoiTのSenior Cloud Architectでありデータスペシャリストでもある Sayle Matthews が、BigQueryでコストを抑えつつ成果を最大化し、Google Cloudの請求額を予測しやすくするための知見を、シリーズ第1弾としてお届けします。
本ebookで取り上げるテーマ:
- 基本を押さえる
- よくあるクエリのミスを避ける
- コストの高いクエリを見極める
基本を押さえる
データウェアハウスである BigQuery はフルマネージドサービスで、機械学習、地理空間分析、ビジネスインテリジェンスといった機能が標準で組み込まれており、ビッグデータの管理と分析を支援します。一方で、初期設計を疎かにすると思わぬコストが発生しがちです。BigQueryのコスト対策に着手する前に、押さえておくべき基本があります。まずはスロットと料金モデルから見ていきましょう。
スロット
BigQueryの計算処理は「スロット」と呼ばれる単位で動いています。スロットとは、メモリを伴うvCPUのことです。理論上、割り当てられて利用可能なスロットが多いほど、クエリの実行は速くなります。標準スロットは100単位での購入となり、1か月または1年のcommitment契約のもとで購入します。
スロットには、公式ドキュメントには明記されていない「データシャッフル」という役割もあります。シャッフルとは、処理済みのデータを別の場所へ再配分し、現在または次のクエリプランのステップを高速に実行できるようにする仕組みです。プロジェクトに割り当てられたスロットの最大60%が、任意のタイミングでシャッフル用スロットとして機能します。シャッフルはクエリ効率を高める一方で、クエリの実行を直接前進させない処理に貴重なスロットを消費します。
料金モデル
料金モデルはオンデマンドかフラットレートのいずれかです。デフォルトはオンデマンド料金で、クエリでスキャンしたデータ1TBあたり5ドルが課金されます。多くの企業にとって、これがBigQueryコストを左右する最大の要因です。
フラットレート(スロット料金)はBigQueryのスキャンに定額料金を設定する方式で、その代わりにパフォーマンスが制限される可能性があります。利用可能なスロット数を事前購入分に限定し、1TBあたり5ドルのスキャン料金を撤廃する仕組みです。
フラットレート料金では、Flexスロットと呼ばれるスロットプールをアーキテクチャに追加し、既存のスロットを増強または置き換えることもできます。Flexスロットはより短期で柔軟なcommitmentが可能で、最低60秒から、いつでもキャンセルしてスロットを解放できるオプションが用意されています。
オンデマンドと同等のパフォーマンスを実現するには100スロットを20セット購入する必要があり、月額40,000ドル、または1年契約で34,000ドルのコストがかかります。BigQueryのスキャンコストでこれと同等以上の支出がない限り、2,000スロットの購入は費用対効果に見合いません。
コストを把握する
コストを最適化するには、まずプロジェクトのデータ使用状況を分析する必要があります。そのためには、プロジェクトやデータセット内のクエリデータへアクセスできることが前提です。
方法は2つあります。監査ログのシンクと、INFORMATION_SCHEMAテーブルの利用です。データ内容がより充実しているため、監査ログのシンクが推奨されます**。** DoiT Cloud Intelligence™のBigQuery Lens機能をご利用のDoiTのお客様は、すでに環境内に監査ログのシンクが設定されています。詳しくはこちらをご覧ください。
よくあるクエリのミスを避ける
本当に必要なデータを取り出すクエリの話に入る前に、BigQueryのクエリを書く際に起こりがちなミスをいくつか見ておきましょう。いずれもクエリの処理時間を長引かせ、必要以上のコストを生む原因になります。
1. SELECT *
これはおそらく、BigQueryクエリで余計なコストが発生する最大の原因です。テーブルやビューのすべての列を取得する必要がある場面はほとんどありません。BigQueryの料金はクエリでスキャンしたデータ量に基づくため、選択範囲を絞ってコストを抑えましょう。
2. 不要または大規模なJOIN
OLAP戦略に重点を置くデータウェアハウス(BigQueryなど)では、データベースのスキーマを非正規化するのがベストプラクティスです。これによりデータ構造がフラットになり、従来のリレーショナルデータベースと比べてJOINの回数を減らせます。
3. CROSS JOIN
BigQueryでもCROSS JOINが必要なユースケースはありますが、クエリの最も内側の処理として実行すると問題が起きやすく、最終的に出力されるデータ量より_はるかに_多くのデータを取り込んでしまいます。
4. 共通テーブル式(CTE)の誤った使い方
共通テーブル式(CTE)はSQLコードを大幅にシンプルにできる便利な仕組みです。ただし、1つのクエリ内で同じCTEを複数回参照すると、その都度CTEのクエリが実行され、データの読み取り料金も参照回数分発生します。
5. WHERE句でパーティションを使わない
パーティションは、コスト削減と読み取りパフォーマンス最適化の両面で、BigQueryの最も重要な機能の1つです。それにもかかわらず省略されがちで、クエリに不要なコストを上乗せする原因になります。
6. 過度に複雑なビューの使用
複雑すぎるビューはパフォーマンスを低下させる原因になります。ビュー内のロジックがあまりに込み入っている場合は、別テーブルで事前計算するか、マテリアライズドビューに置き換えてパフォーマンスを高める方が適しているでしょう。
7. 小さなINSERTの多用
少数のレコードをテーブルに挿入する場合は、小さなINSERTを何度も繰り返すのではなく、バッチでまとめて挿入しましょう。
8. DMLステートメントの過剰利用
BigQueryを従来のRDBMSのように扱い、データを思いつくままに作り直していると、DMLステートメントを過剰に使いがちです。代わりに、最新であることを示すタイムスタンプを付けて新しい行を追加し、履歴が不要であれば古い行を定期的に削除する「追記型モデル」がおすすめです。
コストの高いクエリを見極める
本ebookでは、コストの高いクエリを洗い出すために必要なSQLファイルを格納したGitHubリポジトリへのリンクを掲載しています。中心となるクエリは次の3つです。
- 全体で最もコストのかかるクエリ
- 個別で最もコストのかかるクエリ
- 最もコストを発生させているユーザー
リポジトリに含まれるその他のクエリ
GitHubリポジトリには、Looker由来のクエリの特定、クエリの実行回数、特定ラベル付きクエリのコスト算出など、より特殊な用途向けのクエリも収録しています。
パフォーマンスに問題のあるクエリを見つける
コストの高いクエリを把握できたら、次は必要以上のリソースを消費し、期待どおりのパフォーマンスが出ていないクエリを洗い出します。これらはコストの高いクエリと重なることが多く、ある程度の重複が生じる場合もあります。
パフォーマンスチューニングについては、本シリーズの今後の回で、あまり知られていないBigQueryパフォーマンスの落とし穴とその対処法に焦点を当て、より掘り下げて取り上げる予定です。
本シリーズ初回の最後にご紹介するのは、より一般的な情報やメタデータを取得するGitHubリポジトリ内の一連のクエリです。
- ジョブタイプ別クエリ
- 同時実行クエリ
- クエリ件数
次のステップ
『The BigQuery Optimization Handbook: Preparing to Save』をダウンロードしてご活用ください。本回以降のシリーズはかなりのボリュームになるため、BigQueryプロジェクトで監査ログのシンクを早めに設定し、時間をかけてデータを蓄積しておくことをおすすめします。そうしておけば、ご紹介するクエリやシリーズの内容をより有効に活用できます。なお、DoiT Cloud IntelligenceのBigQuery Lens機能をすでにご利用のDoiTのお客様の環境では、監査ログのシンクは設定済みです。