BigQueryベストプラクティス (パフォーマンス:2)

公式ドキュメントに記載のある「BigQuery best practices」をまとめてみるシリーズ。

今回はパフォーマンスのプラクティス後半をまとめます。

イタリック体はドキュメントに記載のない追加コメント

クエリの計算量に関するベストプラクティス

[原文](https://cloud.google.com/bigquery/docs/best-practices-performance-compute

If you are using SQL to perform ETL operations, avoid situations where you are repeatedly transforming the same data.

何度も同じ変換処理を繰り返す場合は、マテリアライズなどで効率化する。

Avoid using JavaScript user-defined functions. Use native UDFs instead

JavaScriptユーザー定義関数は避け、ネイティブのユーザー定義関数を使う。

• JavaScriptユーザー定義関数はサブプロセスを起動するため、パフォーマンスインパクトがある。

If your use case supports it, use an approximate aggregation function

可能であれば近似集計関数を利用する

• 正確な値が不要ならば、APPROX_COUNT_DISTINCTやHyperLogLog（HLL_COUNT）のような近似集計関数を利用することでパフォーマンスを向上させられる。

Use ORDER BY only in the outermost query or within window clauses (analytic functions). Push complex operations to the end of the query

ORDER BYは、極力データを絞ったあとに利用しよう。

• ソートはパフォーマンスに大きな影響を与えるが、クエリの最後や、window関数内にORDER BYを配置すれば効率化可能。
• 正規表現などの関数についても、データを絞った後に実施すればパフォーマンス向上が狙える。

For queries that join data from multiple tables, optimize your join patterns. Start with the largest table.

JOINをするときは、サイズの大きなテーブルを先に記述しよう

• 小さなテーブルが後にくると、効率の良いブロードキャストジョインが行われる。（小さなテーブルの全データを各スロットにばらまくためシンプル）
• これに関してはオプティマイザーが効くこともあるが、今のところはユーザーも気をつけた方が良い。

When querying a partitioned table, use the _PARTITIONTIME pseudo column to filter the partitions

パーティション分割テーブルにクエリをするときは、_PARTITIONTIMEカラムでフィルタをする

クエリのアウトプットに関するベストプラクティス

[原文」(https://cloud.google.com/bigquery/docs/best-practices-performance-output)

Avoid repeatedly joining the same tables and using the same subqueries

繰り返しのJOINやサブクエリは避ける

• JOINが繰り返し必要ならば、ネストされたフィールドを利用することで非正規化をすることでパフォーマンスが向上できる
• 同じサブクエリが複数回登場する場合、マテリアライズすることでパフォーマンスが改善できる。ストレージコストはかかるが、多くの場合はパフォーマンス改善効果がコストを上回るはず。

Carefully consider materializing large result sets to a destination table. Writing large result sets has performance and cost impacts

大きなサイズの結果セットを作る場合は、コストとパフォーマンスに注意しよう。

• BigQueryはだいたい10GBの結果キャッシュを持っているが、大きな結果セットを生み出してしまうと容易にその制限を超えてしまう。
• 大きすぎると Response too large というエラーにより失敗してしまう。
• 回避するプラクティス
  • WHEREなどにより、結果セットを適切に小さくする
  • LIMITを利用して結果セットを小さくする。ORDER BYを利用するときにも効果がある
  • 結果セットをテーブルに書き込む
• 大きすぎる結果セットをテーブルに書き込む場合、パフォーマンスやコストへのインパクトがある。有効期限を設定することで、大きすぎるテーブルが残らないようにするのも良い。

If you are sorting a very large number of values, use a LIMIT clause.

大きな結果セットをソートする場合、LIMITを利用する。

• ORDER BYはResource exceededエラーを発生させることがある。これは、ORDER BYは最終的には1スロットでソート処理を実施しなければならないが、結果セットサイズが1スロットへの割り当て上限をオーバーしてしまうため。
• 上記は、LIMITを利用することで回避可能

SQLアンチパターン

原文

以下のSQLアンチパターンを避けることで、パフォーマンス向上が狙える。

Avoid self-joins. Use a window function instead.

セルフジョインは避け、可能な限りwindow関数を利用する。

• セルフジョインはレコード数が２倍に膨れ上がり、パフォーマンス劣化につながる

If your query processes keys that are heavily skewed to a few values, filter your data as early as possible

特定のキーにデータが偏っている場合、できるだけ早くデータをフィルタリングする

• スロット間でパーティションを共有することはできないため、特定のパーティションサイズが大きくなると、それが原因で遅くなったり、resource exceeded エラーになってしまう。
• 例えばユーザーテーブルで、匿名ユーザーを同じIDで登録している場合、匿名ユーザーIDのレコードが圧倒的に大きくなる
• query planをみた時に、avgとmaxが大きく乖離している場合はこの事象が発生している可能性がある。
• APPROX_TOP_COUNT を利用することでこの事象が起きているか確認できる。
• JOINした結果、各キーのデータの偏りが出ることもあるため、事前にフィルタリングするなどをした方が良い。

Avoid joins that generate more outputs than inputs. When a CROSS JOIN is required, pre-aggregate your data.

インプットよりも大きなアウトプットを生み出すクエリを避ける。

• CROSS JOINをする場合は事前に集計をすることでデータ量を減らす。
• GROUP BY やWindow関数を利用してデータ量を減らす

Avoid point-specific DML statements (updating or inserting 1 row at a time). Batch your updates and inserts

DMPクエリを実行する場合は、１行ずつではなくまとめて行う。

• １行ずつの更新が必要な場合、BigQueryは用途にあっていないのでCloudSQLなどOLTPに向いたものを利用する。
• もしくは、ストリーミングインサートを利用する。
• ステートメントのサイズにも制限があるので、大量のUPDATEがある場合は、１行ずつクエリを並べるのではなくまとめて更新されるようなクエリを書く。