BigQueryでクエリを書く時に、クエリの書き方によって実行時間を高速化できたり処理するバイト数を節約したりできます
Googleが公式でBigQueryのベストプラクティス集(今はまだ未翻訳)を公開してくれているので、そのうちのクエリを書く時周りのノウハウを簡単にまとめておきます。別々のページの内容なので重複があったら端折ったりしています
誤訳や解釈の誤りがあったらコメントなどで教えてください
BigQueryのベストプラクティス(クエリ編)
入力されるデータの量を減らす
Managing Input Data and Data Sources | BigQuery | Google Cloud Platform
SELECT *
を避ける
SELECT *
はテーブル全体を読み込んでしまうのでよくない。できるだけ使うカラムを減らしましょう
Standard SQLを使っているならSELECT * EXCEPT (カラム名1, ...)
と書くことで指定したカラム以外のカラムを取ってくることで少し節約ができる。
LIMIT
を付けても読み込まれるデータ量は変わらないので、テーブル全体を読み込んだのと同じバイト量で課金されてしまいます
もしもすべてのカラムにアクセスする必要があるのなら、あらかじめテーブルを日付でパーティショニングしておいたりして小さくしておくとよい
日付でパーティショニングされたテーブルの場合必要なパーティションだけを指定する
日付でパーティショニングされたテーブルにクエリを書く場合、必要な日付を指定することで計算に不必要な日付のデータを取ってくることがなくなります
WHERE _PARTITIONTIME BETWEEN TIMESTAMP("2017-10-01") AND TIMESTAMP("2017-10-07")
可能な限り非正規化されたデータで扱う
非正規化されたデータはJOINが必要ないので効率的に並列にクエリを実行することができる
1対多の関係性をflattenされた形のデータで持つよりは(構造体の)配列のフィールドで持つべき。
(構造体の)配列のフィールドを使わずにflattenされたデータを扱う場合、GROUP BY
が必要になってネットワーク通信(シャッフル)のせいでパフォーマンスが落ちることがある
外部リソースを入力にするのは高速ではない
BigQueryではGoogle Cloud StorageやGoogle DriveやGoogle Cloud Bigtableを入力にすることができるが、BigQueryのテーブルを参照するほうが基本的に高速なのでクエリのパフォーマンスが重要なときは使うべきではない
テーブル名をワイルドカードで指定するときは必要なテーブルだけを指定する
BigQueryではFROM
で指定するテーブル名の末尾に*
を指定することでプレフィックスにマッチした複数のテーブルからデータを取ってくることができる
パーティションの部分でも同じような話をしていたが、必要ないテーブルは取ってくる必要が無いので必要十分なできるだけ長いプレフィックスを指定するとよい
この記事の原文には書かれていないがWHERE _TABLE_SUFFIX BETWEEN '20171001'AND '20171007'
のようにワイルドカード部分に条件を書いて絞り込むこともできる
通信の最適化
Optimizing Communication Between Slots | BigQuery | Google Cloud Platform
JOINする前にデータの量を減らす
JOIN
した後にデータをWHERE
の条件などでフィルタリングするのと、JOIN
する前にフィルタリングするのではパフォーマンスが大きく変わることがある
JOIN
する前にデータ量を減らすことが可能ならできるだけやるべき
WITH句を使ってもクエリは効率的にならない
WITH
句は主に可読性やコードの書きやすさのためのもので、複数のWITH
句中に共通のクエリが含まれていてもそれぞれ実行される
日付ごとにテーブルを作るのを避ける
それぞれのテーブルごとにスキーマやメタデータを持ったり、パーミッションの確認をしないといけないので効率的ではない
もし日付ごとに分割しないならパーティションの機能を使うべき
計算の最適化
Optimizing Query Computation | BigQuery | Google Cloud Platform
同じ変換をSQLで何度もするのを避ける
同じデータの変換結果を何度も使うときは、途中結果を別のテーブルに保存すべき
JavaScript のユーザー定義関数(UDF)を避ける
JavaScriptのUDFを呼ぶとJava(原文ママ)のサブプロセスが実行されるので遅くなる。可能ならSQLのUDFを使うべき
クエリの順番に気をつける
データをORDER BY
でソートしてからフィルタリングするのと、フィルタリングしてからソートするのでは後者の方が圧倒的に速い。
ソートや正規表現などによる複雑な処理はできるだけ後の方で行ったほうがデータ量が減っているので効率的
JOINの順番に気をつける
ある程度はオプティマイザが配慮してくれるが、大きなテーブルに対して小さなテーブルをJOIN
するようにしていくと効率的になる
日付でパーティショニングされたテーブルの場合必要なパーティションだけを指定する
上の方で書いたのと同じ。処理するデータ量が減るので効率的になる
出力周りの最適化
Managing Query Outputs | BigQuery | Google Cloud Platform
繰り返しJOINやサブクエリをするのを避ける
何度も同じテーブルをJOIN
したり何度も同じサブクエリを投げる場合は、そういったテーブルを作ってしまったほうが効率的になる
出力が大きい場合にはテーブルに保存することを考える
クエリの結果が大きすぎるとResponse too large
というエラーになる
この場合出力をフィルタリングしたりLIMIT
を指定してデータ量を少なくするか、テーブルに保存すればよい
ただしテーブルに保存する場合にはストレージ代がかかる
大きなデータをソートするときはLIMITを指定する
大量のデータに対してORDER BY
を指定するとResources exceeded
のエラーが出てしまう
この場合LIMIT
を指定するとエラーがでなくなることがある
アンチパターンを避ける
Avoiding SQL Anti-Patterns | BigQuery | Google Cloud Platform
self joinを避ける
同じテーブル同士をJOIN
するself joinはデータの行数が大きく増えることが多い
できるだけself joinの代わりにウィンドウ関数を使うべき
データの偏り
GROUP BY
やJOIN
などをする時にキーの値に偏りがあるとパフォーマンスに悪影響がある
例えばユーザーIDでGROUP BY
して集計した時に、ほとんどのレコードのユーザーIDがnull
で著しい偏りになったりする
偏りが激しいと、その値が割り当たったスロットのリソースを使い切ってresources exceeded
のエラーが出る
対策としては以下の2つがある
- 近似的な集計関数を使う
- できるだけあらかじめデータ量を減らしておく
同様にJOINのときも以下のような対策が考えられる
- できるだけあらかじめデータ量を減らしておく
- 可能なら2つのクエリに分ける
以下の記事の説明もわかりやすい
Query Plan Explanation | BigQuery | Google Cloud Platform
CROSS JOIN(デカルト積)
テーブルの行のすべての組み合わせでJOINするCROSS JOIN
をするとデータ量が非常に多くなって最悪クエリが終わらなくなる
できるだけCROSS JOINを使わずにウィンドウ関数を使ったり、もし可能なら集計してからCROSS JOINするとよい(この部分は翻訳が怪しい。原文→Use a GROUP BY clause to pre-aggregate the data.
)
UPDATEやINSERTを1行ずつやらない
UPDATE
やINSERT
は1行ずつやらずに複数行でまとめて行うのがよい
個人的な感想
非正規化されたデータの方が効率的という話とWITH
句を使ってもクエリは効率的にならないという話が意外だった
日付ごとにテーブルを使うのではなくパーティション機能を使うのが強く推奨されていたが、テーブルを分けない場合パーティション指定し忘れるとテーブルのフルスキャンが動いて課金的に死ぬ可能性が怖くてなかなかパーティションに移行できていない
パーティションを指定しないクエリは投げられないようにする設定みたいなのができないのだろうか……
ライセンス周り
この記事は以下のURLのドキュメントに書かれている内容の翻訳を多く含みます
- BigQuery Best Practices: Optimizing Query Performance | BigQuery | Google Cloud Platform
- Managing Input Data and Data Sources | BigQuery | Google Cloud Platform
- Optimizing Communication Between Slots | BigQuery | Google Cloud Platform
- Optimizing Query Computation | BigQuery | Google Cloud Platform
- Managing Query Outputs | BigQuery | Google Cloud Platform
- Avoiding SQL Anti-Patterns | BigQuery | Google Cloud Platform
元のドキュメントはCreative Commons 3.0 Attribution Licenseで公開されているのでこの記事についても同様です
念のためSite Policiesのページにあった以下の表示も載せておきます
Site Policies | Google Developers
Portions of this page are modifications based on work created and shared by Google and used according to terms described in the Creative Commons 3.0 Attribution License.