BigQuery公式ドキュメントのクエリ計算の最適化を理解するために、
ドキュメントの内容を自分の言葉でまとめてみた。
自分が業務で作成したクエリと照らし合わせてみたいと思う。
SQL クエリ経由のデータの繰り返し変換を避ける。
おすすめの方法: SQL を使用して ETL オペレーションを実行する場合は、同じデータを繰り返し変換する状況を避けてください。
たとえば、SQL を使用して文字列をトリミングしたり、正規表現を使用してデータを抽出したりする場合は、宛先テーブルで変換結果を実体化する方がパフォーマンスが向上します。正規表現のような関数には追加のコンピューティングが必要です。追加される変換オーバーヘッドなしに宛先テーブルを照会する方がはるかに効率的です。
同じデータを同じクエリの中で何度も変換することは控えた方が良いとのこと。
例えば、tableに対してクエリを実行して、
nameカラムの小文字への変換＆空白を除去が実施されたデータが格納されたテーブルを新しく作成するための下記のクエリがあったとする。
select
regexp_replace(lower(name), r'^\s+', '') --lowerでnameを小文字に変換したうえで、正規表現で空白を除去
from
`table`
このクエリよりもパフォーマンスを上げたい場合は、nameカラムを小文字に変換したテーブルを新しく作成し、
またそのテーブルに対してregexp_replaceで空白を除去するクエリを実行してテーブルを作ると良さそう。
※引用：SQL クエリ経由のデータの繰り返し変換を避ける。
結合パターンを最適化する
おすすめの方法: 複数のテーブルのデータを結合するクエリの場合は、結合パターンを最適化します。最大のテーブルから始めます。
JOIN を使用してクエリを作成する場合は、データを結合する順序を考慮してください。Google 標準 SQL クエリ オプティマイザを使用すれば、結合のどちら側にどのテーブルを配置するかを決定できる。また、結合対象のテーブルを適切に並べ替えておくとよい。ベスト プラクティスは、最初に最も行数の多いテーブルを配置し、その後に行数の少ないテーブルを順に配置することです。
大きいテーブルを JOIN の左側に配置し、小さいテーブルを JOIN の右側に配置した場合は、ブロードキャスト結合が形成されます。ブロードキャスト結合は、小さいテーブル内のすべてのデータを大きいテーブルを処理する各スロットに送信します。ブロードキャスト結合を最初に実行することをおすすめします。
JOIN 内のテーブルのサイズを表示するには、テーブルに関する情報の取得をご覧ください。
テーブルを結合する際は、大きいテーブルからテーブルを並べて結合していくとクエリパフォーマンスを最適化できる。
下記のようなイメージ。
select
*
from
`大きいテーブル` as l
left join `中くらいのテーブル` as m
on l.id = m.id
left join `小さいテーブル` as s
on m.id = s.id
※引用：結合パターンを最適化する
結合でINT64データ型を使用して費用を削減し、比較パフォーマンスを向上させる。
おすすめの方法: ユースケースでサポートされている場合、STRING データ型ではなく、INT64 データ型を結合で使用します。
BigQuery では、従来のデータベースのように主キーがインデックス化されないため、結合列が広いほど比較に時間がかかります。したがって、結合で INT64 データ型を使用するほうが、STRING データ型を使用するよりも低コストで効率的です。
可能であれば、結合の際、STRING型を使用するのではなく、INT64型を使用した方が良い。
※引用：結合でINT64データ型を使用して費用を削減し、比較パフォーマンスを向上させる。
分割されたクエリを取り除く
ベスト プラクティス: 手続き型言語、変数、一時テーブル、自動的に期限切れになるテーブルを使用して、計算を維持し、後でクエリで使用します。
クエリ内の複数の場所で使用される共通テーブル式がクエリに含まれている場合、それらは参照されるたびに評価されます。これにより、内部クエリの複雑さとリソースの消費量が増加する可能性があります。
CTE の結果を、CTE から返されるデータに応じてスカラー変数または一時テーブルに格納できます。一時テーブルのストレージには課金されません。
CTEとは、クエリ内のWITH句のこと。
クエリの中の複数の場所で使用されるWITH句の式がクエリに含まれていると、参照される度に評価されてクエリリソースの消費量が増えてしまう。
そのため、クエリ内の複数個所で使用されるクエリは、
WITH句を使用するのではなく、create tempで一時テーブルを作成してそれを複数個所で使用した方が、クエリリソースの消費を抑えることができる。
※引用：分割されたクエリを取り除く