BigQueryのシャーディングテーブル(日付別テーブル)でフルスキャンを避ける方法 with BigQuery Scripting & dbt

SQLをレビューしていて、シャーディングテーブル(日付別テーブル)をサブクエリを使ってフィルタしているものがあった。BigQureyのシャーディングテーブルはWHERE句で日付の条件を書いてやるとスキャン範囲を限定することができるので便利ではあるが、サブクエリを使うなど定数でないものが入るとフルスキャンが走ってしまう。

このように書くことによって、スキャン量は from_date ~ to_date までのテーブルしかスキャンしないので全tableのスキャンすることを防ぐことができます。しかし、このワイルドカードテーブルへのクエリにサブクエリなどの定数式でない条件を使ってしまうと、途端にフルスキャンを行ってしまいます.

このこと自体は覚えていたものの「じゃあ、具体的にどうやって回避すればいいの?」というのをぱっと言語化できなかったので、ちょっとまとめておく。

ちなみに、サブクエリを使ってスキャン範囲を限定するのはまあまあ便利で「毎日届くとは限らないデータソースに対して、最新の3日分に対して処理したい」といった場合に便利に使えます。便利なので使いたい気持ちは分かるので、安全に使う方法をまとめる形になります。

ベースになるクエリ

最初にダメな例です。これだとシャーディングテーブルに対してフルスキャンが走ってしまい、スキャン量の料金やスロット使用量などからも大きな無駄が発生してしまいます。

SELECT
  *
FROM
  `my-project.my_dataset.my_table_*`
WHERE
  _table_suffix = (SELECT MAX(_table_suffix) FROM `my-project.my_dataset.my_table_*`)

BigQuery Scriptingを使ってフルスキャンを回避する

割と広く勧められる方法としてはBigQuery Scriptingを使う方法でしょう。変数に一度セットしてから、それを参照する形です。これだとフルスキャンを避けられます。

DECLARE max_table_suffix STRING;
SET max_table_suffix = (SELECT MAX(_table_suffix) FROM `my-project.my_dataset.my_table_*`);

SELECT
  *
FROM
  `my-project.my_dataset.my_table_*`
WHERE
  _table_suffix = max_table_suffix

dbtを使う場合

dbtを使う場合、残念ながらBigQuery Scriptingとの同居ができません(multi-statement queriesだとDDLがうまく動かないため)。しかし、Jinjaのテンプレートを使うと、ほぼ同様のことができるので、これでしのぐのがよいと思います。この方法でも同様にフルスキャンを避けられます。BigQuery Scriptingと比べると、記述量はちょっと多くなるのでダルくはありますが、まあフルスキャンがかかるより全然マシなケースが多いと思います。

{% set results = run_query('SELECT MAX(_table_suffix) FROM `my-project.my_dataset.my_table_*`') %}

{% if execute %}
{% set max_table_suffix = results.columns[0].values()[0] %}
{% else %}
{% set max_table_suffix = none %}
{% endif %}

SELECT
  *
FROM
  `my-project.my_dataset.my_table_*`
WHERE
  _table_suffix = "{{ max_table_suffix }}"