唯物是真 @Scaled

BigQueryのクエリでテーブル名を書くときに末尾に*を書いておくと、その部分が_TABLE_SUFFIXというカラムに入っていてWHEREの条件に書いて使うテーブルを絞り込むことができます
BigQueryではテーブル名の末尾に日付を書いておいて、_TABLE_SUFFIXで絞り込むのがよく使われています
f:id:sucrose:20180630232033p:plain
参照したバイト数によって課金されるので、できるだけ余分なテーブルを参照するのを避けるとコストを節約できます
以下の画像のように_TABLE_SUFFIXの条件を外すと処理されるバイト数が増えています
f:id:sucrose:20180630232131p:plain

BigQueryではパラメータ付きのViewやWITH句が書けないので、ビューの外側からパラメータとして_TABLE_SUFFIXの絞り込みを書くのはできないかと思っていたのですが、やる方法を教えてもらったので詳しい挙動を調べてブログに書いておきます
ビューとWITH句でだいたい同じ挙動のようなので以下ではWITH句で説明していきます

方法

必要なのは、_TABLE_SUFFIXをカラムとして外側に渡すことです
ただし_TABLE_で始まるカラムをユーザーが作るのは許されていないのでなんらかの適当な名前にしておく必要があります
f:id:sucrose:20180630231855p:plain
外側に渡した_TABLE_SUFFIXのカラムに対して条件をかけることで参照されるテーブルを絞ることができます
f:id:sucrose:20180630232643p:plain

制約

いくつか試したところ、ビューやWITH句のクエリの中にLIMITがあったり_TABLE_SUFFIXをPARTITION BYの条件に含まないウィンドウ関数を書いたりすると、外側から条件で絞っても全部使われてしまうようです(他にもダメなものはありそう)
当然かも知れませんが、ビューやWITH句のクエリの時点で全テーブルのデータが影響してくる場合はダメみたいです
f:id:sucrose:20180630232737p:plain

利用例

単純に`SELECT`してそれぞれの行のデータを取ってくる

カラムを絞ったり追加したり、値に演算をしたり、UNNEST()したりするだけの場合この記事の方法が役に立ちそうです
f:id:sucrose:20180630232643p:plain

`GROUP BY`やウィンドウ関数で集計する場合

ビューやWITH句の外側に_TABLE_SUFFIXを出さなければならない都合上、GROUP BYに_TABLE_SUFFIXのカラムを含める必要があってクエリが制限されます
f:id:sucrose:20180630234219p:plain
同様にウィンドウ関数を使う場合にはPARTITION BYに_TABLE_SUFFIXを含める必要があります
f:id:sucrose:20180630235437p:plain

テーブルを`JOIN`する場合

個人的には少し意外だったのですが、テーブルをJOINする場合でもJOINしたビューやWITH句の外側で_TABLE_SUFFIXによる絞り込みが効くようです
以下は同じ月と日のデータをJOINしているだけの特に意味のないクエリですが、_TABLE_SUFFIXによる絞り込みでバイト数が抑えられているのがわかります
f:id:sucrose:20180701001908p:plain

まとめ

試してみたところ意外といろいろなタイプのクエリでビューの外側からでも_TABLE_SUFFIXの指定ができました
試してはいませんがパーティションで分けられたテーブルの_PARTITIONTIMEでも同じようなことはできそうです

一つ注意点としては、ビューで*を使って大量のテーブルをまとめた場合_TABLE_SUFFIXを指定し忘れると意図せず大量のテーブルにアクセスしてしまうので実行前にどれぐらいのバイト数か確認したほうがよさそうです。
あらかじめ一ヶ月や一年以内のテーブルに_TABLE_SUFFIXを絞る条件を書いておいたり、クエリを実行するときの最大のバイト数に制限を書けたほうが安全かもしれません

上で出てきたクエリ例

SELECTしてくるだけ

#standardSQL
WITH data AS (
  SELECT
    *,
    _TABLE_SUFFIX AS table_suffix
  FROM `bigquery-public-data.noaa_gsod.gsod*`
)

SELECT
  *
FROM data
WHERE table_suffix = '2018'

GROUP BYで集計

#standardSQL
WITH data AS (
  SELECT
    _TABLE_SUFFIX AS table_suffix,
    mo AS month,
    COUNT(*) AS count
  FROM `bigquery-public-data.noaa_gsod.gsod*`
  GROUP BY table_suffix, month
)

SELECT
  month,
  count
FROM data
WHERE table_suffix = '2018'

ウィンドウ関数で集計

#standardSQL
WITH data AS (
  SELECT
    _TABLE_SUFFIX AS table_suffix,
    *,
    COUNT(*) OVER (PARTITION BY _TABLE_SUFFIX, mo)
  FROM `bigquery-public-data.noaa_gsod.gsod*`
)

SELECT
  *
FROM data
WHERE table_suffix = '2018'

JOINが含まれるクエリ

#standardSQL
WITH data AS (
  SELECT
    *,
    _TABLE_SUFFIX AS table_suffix
  FROM `bigquery-public-data.noaa_gsod.gsod*`
), join_test AS (
  SELECT
    data1.*,
    data2.year AS year2,
    data2.table_suffix AS table_suffix2
  FROM data AS data1
  JOIN data AS data2
  ON data1.mo = data2.mo
    AND data1.da = data2.da
)

SELECT
  *
FROM join_test
WHERE table_suffix = '2018'
AND table_suffix2 = '2017'

CREATE TEMPORARY FUNCTION unserialize(x STRING) RETURNS STRING LANGUAGE js AS """ # ここにライブラリの中身を直に書くか、もしくは外部ライブラリのインクルードをこのUDFの定義の外側に書く return JSON.stringify(unserialize(x)); # unserialize() という関数がライブラリで定義されているものとします """; SELECT unserialize('a:3:{s:1:"a";s:1:"1";s:1:"b";i:2;s:1:"c";a:3:{i:0;i:2;i:1;i:4;i:2;i:6;}}')