ちょっとマニアックな BigQuery の話

2023/11/18 22:25:43

はじめに

Google Cloud の有力サービスのひとつである BigQuery は、サーバレスかつ分散処理を行ってくれるので、テラバイト級のデータを処理するクエリでも秒単位で結果が返ってきます。優れた分散処理のため、使い方によってはクエリの処理効率を特に意識しなくても利用できます。一方で、同じ結果が得られるクエリでも、どのように書くかによって、処理時間が大きく変わってきます。今回は、BigQuery のクエリを書くときに気をつけると良いポイントのうち、あまり知られていない「WHERE 句の条件の順番」について、実例・数値とともにご紹介します。

WHERE 句の条件の順番

BigQuery 管理者リファレンス ガイド: クエリの最適化 （英語版はBigQuery Admin reference guide: Query optimization）で触れられているものですが、画像が荒く、動画内にしか実際のクエリがない（動画はきれいです）ため、コピー可能なコードと共にお見せしましょう。

当該記事には:

式の順番が重要: BigQuery は WHERE 句で指定されている式の順序が最適だと想定し、式の順序を変更することはありません。WHERE 句には、最もデータを絞り込むことのできる式を最初に並べる必要があります。

と書かれています。
動画Strategies for optimizing your BigQuery queries 5:48 が、該当箇所です。例では、bigquery-public-data という、（クエリ料金だけで）無料で利用できる一般公開データセットにクエリを行っています。bigquery-public-data.stackoverflow.comments テーブルから、user_display_name が anon で、かつ text に java が含まれるコメントを抽出するクエリを、以下のクエリ1のように書くことを推奨しています。

クエリ1:

SELECT text
FROM `bigquery-public-data.stackoverflow.comments`
WHERE
  user_display_name = 'anon'
  AND text LIKE '%java%';

これは、次のクエリ2より高速に処理されます。

クエリ2:

SELECT text
FROM `bigquery-public-data.stackoverflow.comments`
WHERE
  text LIKE '%java%'
  AND user_display_name = 'anon';

理由を簡単に述べるとするならば

1. 一致で判定する方が、部分一致で判定するよりも高速
2. 恐らく、user_display_name が anon であるレコードは、text に java が含まれるレコードよりも少ない
3. user_display_name が anon であるレコードを先に絞り込むと、text に java が含まれるかどうかを判定するレコード数が少なくなる

と考えられるでしょう。

クエリ1の結果は次です。
処理されたバイト数、課金されるバイト数 はともに 12.81 GB であり、スロット（ミリ秒） は 10951 でした。以下のような「実行の詳細」と「実行グラフ」が得られました。「実行の詳細」より経過時間が 814 ミリ秒で、下のステージ S00 のコンピューティングが 35 ミリ秒であることに注目しておいてください。

詳細:

グラフ:

クエリ2の結果は次です。
処理されたバイト数、課金されるバイト数 はともに 12.81 GB であり、クエリ1と同じでした。つまり、オンデマンド料金の場合では発生する料金は同じです。一方で、スロット（ミリ秒） は 128471 となり、約12倍でした。以下のような「実行の詳細」と「実行グラフ」が得られました。「実行の詳細」によると、経過時間が 2 秒で、下のステージ S00 のコンピューティングが 1 秒です。

詳細:

グラフ:

両方とも結果が27行であることは変わりませんが、クエリ1の方が処理時間が短く、スロット数も少ないことがわかります。

追加実験

user_display_name = 'anon' の部分を user_display_name LIKE '%anon%' に変更し、結果がどう変化するのか見てみましょう。どちらが先にある方が高速に処理できるのか、予想してみてください。

同じように、クエリ3とクエリ4を用意して、実行しました。

クエリ3:

SELECT text
FROM `bigquery-public-data.stackoverflow.comments`
WHERE
  user_display_name LIKE '%anon%'
  AND text LIKE '%java%';

クエリ4:

SELECT text
FROM `bigquery-public-data.stackoverflow.comments`
WHERE
  text LIKE '%java%'
  AND user_display_name LIKE '%anon%';

クエリ3は、スロット（ミリ秒）が 5908 でした。「実行の詳細」によると、全体で 903 ミリ秒でした。結果セットは 39 行で、少し増えています。

クエリ4は、スロット（ミリ秒）が 135165 でした。「実行の詳細」によると、全体で 2 秒でした。結果セットは 39 行で、クエリ3と同じです。

クエリ3とクエリ4の結果の順は予想通りでしたか？クエリ1と2の関係と大体同じだと言って良いでしょう。

「より複雑」なクエリ3の方が、クエリ2よりも高速であったことにも注目してください。「WHERE 句の条件の順序」だけでも速度・効率が変わるので、意識すると早くなることがあります。

補足

BigQuery には「パーティショニング」や「クラスタリング」という機能がありますので、そちらが利用できるときは利用すると、さらに効率良く結果を得ることができます。

参考

bigquery-public-data.stackoverflow.comments テーブルのスキーマと、SELECT id FROM ... としたときに「このクエリを実行すると、12.8 GB が処理されます。」のように表示される部分のデータサイズは以下のようになっています。

行数 86,754,111、合計論理バイト数 16.03 GB、合計物理バイト数 6.63 GB でした。

大体の長さの分布を見てみましょう。

SELECT
  APPROX_QUANTILES(LENGTH(user_display_name), 10),
  APPROX_QUANTILES(LENGTH(COALESCE(user_display_name, '')), 10),
  APPROX_QUANTILES(LENGTH(text), 10),
  APPROX_QUANTILES(LENGTH(COALESCE(text, '')), 10)
FROM `bigquery-public-data.stackoverflow.comments`;

の結果を横に並べると以下のようになっていました。

user_display_name はそもそも値が入っていないことも多く、それも含めてデータ量が少ない状態でした。こちらを先に考慮した方が断然早かったという訳ですね。

References

• BigQuery 管理者リファレンス ガイド: クエリの最適化
• BigQuery Admin reference guide: Query optimization
• Strategies for optimizing your BigQuery queries 5:48