概要

毎日のスナップショットが見たいデータについて、日次で全件データをパーティション分割テーブルに蓄積している。
利用者が使いやすいように最新のパーティションだけのデータを取得するビューを作成しようとしたが、なぜかテーブル全件を走査してしまうビューになってしまう。
それだとクエリ料金がバカにならないので回避策を探した話。

結論

ビューで上記を実現する方法はいまのところなさそう。
近似的にやる方法はあり、WHERE句でパーティション分割列をcurrent_timestampなどの関数で指定する。
この場合、データ投入時刻のスケジュールによっては厳密には最新のパーティションでない場合が発生してしまう。

やったこと

一番最初に思いつく方法はこれだと思うが、クエリ走査量を見るとテーブル全件を取得した量になってしまう。
参考文献によると、BigQueryのエンジンがそのように処理してしまうのかもしれないとのこと。

全件走査してしまうview

SELECT * FROM [table_name]
WHERE _PARTITIONTIME = (
    SELECT MAX(_PARTITIONTIME) FROM [table_name]
)

このようにWHERE句のサブクエリでテーブルを参照しない形にすると、全件ではなく当該パーティションだけ取得した量になる。

全件走査しないview

SELECT * FROM [table_name]
WHERE _PARTITIONTIME = (
    SELECT TIMESTAMP('2022-01-01 00:00:00')
)

全件走査しないview

SELECT * FROM [table_name]
WHERE _PARTITIONTIME = (
    SELECT timestamp_sub(timestamp_trunc(current_timestamp(), day), INTERVAL 1 day)
)

DECLAREとSETで変数にタイムスタンプを格納すると全件は走査しないが、DECLAREとSETを含むSQLはビューにできない。

全件走査しないがviewにできない

DECLARE latest TIMESTAMP;
SET latest = (SELECT MAX(_PARTITIONTIME) FROM [table_name]);
SELECT * FROM [table_name]
WHERE _PARTITIONTIME = latest;

わかったこと

current_timestamp()でパーティションを指定すれば最新のパーティションを取得できそうだが、
日次のデータ投入バッチの時刻前後で、データが返ってこないパターンやデータが最新パーティションではないパターンが発生しうるので注意が必要。

参考文献