BigQuery でアンチパターン認識ツールを使用してクエリ パフォーマンスの向上と費用の最適化を図る

※この投稿は米国時間 2023 年 6 月 10 日に、Google Cloud blog に投稿されたものの抄訳です。

BigQuery は、サーバーレスで費用対効果に優れたエンタープライズ データ ウェアハウスです。各種のクラウド環境で機能し、データに合わせたスケーリングも可能です。大規模なデータ集約型プラットフォームのどれにも言えることですが、ベスト プラクティスに従って非効率なアンチパターンを回避することは、パフォーマンスの向上と費用の節約に大きな効果を発揮します。

通常、SQL を最適化するには、エンジニアがかなりの時間をかけて、非常に複雑なクエリを解読し、パフォーマンスや効率の改善に向けてさまざまなアプローチを考案し、複数の最適化手法をテストする必要があります。最適化の手始めとして、まずはアンチパターンを修正するのがベストでしょう。簡単に変更を適用できて、パフォーマンスが大幅に向上するからです。

このようなアンチパターンを特定して修正するタスクを容易にするために、Google の Professional Services Organization（PSO）と Global Services Delivery（GSD）は BigQuery アンチパターン認識ツールを開発しました。これは、SQL クエリのスキャン、アンチパターンの特定、最適化に関する推奨事項の提供のプロセスを自動化するツールです。

BigQuery アンチパターン認識ツールとは

BigQuery アンチパターン認識ツールを使用すれば、一度に多数の SQL クエリにわたって、パフォーマンスに影響を与えるアンチパターンを簡単に特定できます。

このツールは ZetaSQL を利用し、BigQuery の SQL クエリを解析して抽象構文ツリー（AST）に変換してから、ツリーノードを走査してアンチパターンの存在を検出します。

このツールは、入力として次のような BigQuery の SQL クエリを受け取ります。

そして、次のような出力を生成します。

次のような最適化の可能性を調査します。

• 必要な列のみを選択する

• 複数の WITH 句参照を処理する

• 集計を含むフィルタ内でサブクエリを処理する

• LIMIT を使用して ORDER BY クエリを最適化する

• 文字列の比較を強化する

• JOIN パターンを改善する

• WHERE 句でのサブクエリ集計を回避する

このソリューションでは、次のような各種ソースからの読み取りがサポートされています。

• コマンドライン

• ローカル ファイル

• Cloud Storage ファイル

• ローカル フォルダ

• Cloud Storage フォルダ

• CSV（1 行に 1 つのクエリ）

• INFORMATION_SCHEMA

さらに、このソリューションでは、次のようなさまざまな出力先に結果を書き込むことができます。

• ターミナルにプリント

• CSV としてエクスポート

BigQuery テーブルへの書き込み

BigQuery アンチパターン認識ツールの使用

BigQuery アンチパターン認識ツールは GitHub でホストされています。以下に、コマンドラインを介してインライン クエリにツールを使用するためのクイック スタート手順を示します。このツールは、Cloud Run を利用してクラウド上にコンテナとしてデプロイすることもできます。

前提条件

• Linux OS

• JDK 11 以上がインストールされている

• Maven

• Docker

• gcloud CLI

クイックスタート: - 手順

1. ローカルマシンにリポジトリのクローンを作成します。

2. 「bigquery-antipattern-recognition」フォルダ内にツールのイメージを構築します。

3. シンプルなインライン クエリに対してツールを実行します。

4. コマンドライン インターフェースに次の出力結果が表示されます。

さらに、上述のツールで情報スキーマからクエリを読み取り、出力された推奨事項を BigQuery テーブルに読み込むことができます。

以下は、BigQuery テーブルにエクスポートされた BigQuery アンチパターン認識ツールの結果の例です。

使ってみる

BigQuery クエリの最適化と費用削減を開始する準備は整いましたか？こちらからツールをご確認いただけます。GitHub 経由でツールに貢献していただくことも可能です。

ご質問やフィードバック

Google は、このツールをお客様にとっていっそう便利なものにするために、新しい機能を積極的に取り入れています。ぜひご利用いただき、ご意見をお聞かせください。プロダクトに関するフィードバックや技術的なご質問は、bq-antipattern-eng@google.com までお寄せください。すでに BigQuery をご利用のお客様でツールに関する説明をご希望の方は、お気軽にお問い合わせください。

- Google Cloud、戦略的クラウド エンジニア Celia Ji
- Google Cloud、クラウド データ エンジニア Megha Bedi