BQ で最新日付のパーティションを select したい

BigQuery
  • データベースのスナップショットを日次バッチでBQに load している。
  • x日の朝に、その時点のスナップショットをx-1日のパーティションに入れている。

というケースで、その時点でBQに入っている最新のスナップショットを select したい。
そして、最新のスナップショットを select するクエリを view として提供したい。

クエリ案1: 素直にサブクエリ書く
SELECT
  *
FROM
  `some_dataset.partitioned_table`
WHERE
  _PARTITIONDATE = (SELECT MAX(_PARTITIONDATE) FROM `some_dataset.partitioned_table`);

パーティションの絞り込みが行われず、フルスキャンになってしまう...

クエリ案2: scripting 使う

ritchiekotzen.hatenablog.com

パーティションを絞り込みつつ select できる。ただし、view にすることはできない。

クエリ案3: スナップショットが load される時間を見計らって参照先切り替え

朝6時までにはスナップショットの load が済んでいるとする。
→ x日朝6時以降は x-1日のパーティションを参照する。
→ 24 + 6 = 30。現在時刻の30時間前の日付のパーティションを参照する。

SELECT
  *
FROM
  `some_dataset.partitioned_table`
WHERE
  _PARTITIONDATE = ( DATE(TIMESTAMP_SUB(CURRENT_TIMESTAMP() , INTERVAL 30 HOUR), "Asia/Tokyo"));

ちゃんとパーティションの絞り込みしてくれる。ただし、load が6時より遅れた場合、クエリの結果がゼロ件になる。

クエリ案4: 案１を改良し2段階で絞り込み
WITH
  src AS (
  SELECT
    *,
    _PARTITIONDATE AS pdate,
  FROM
    `some_dataset.partitioned_table`
  WHERE
    _PARTITIONDATE >= DATE_SUB( CURRENT_DATE("Asia/Tokyo"), INTERVAL 2 DAY))

SELECT
  * EXCEPT(pdate)
FROM
  src
WHERE
  src.pdate = (
  SELECT
    MAX(pdate)
  FROM
    src)

最大2日分パーティションを読むことになるがフルスキャンにはならない。
load が1日以上遅れるとクエリの結果がゼロ件になる。