Google BigQuery の View でうまく Partition を指定する方法

TL; DR

• BigQuery の View を作成する際、Partition の情報を SELECT 句で引っ張ってきて外部参照できるようにした。
• Partition を外部参照できるようにすることで、View を呼ぶときに欲しい部分だけの Partition を切ることで、取得するデータの Partition (≒日付情報) を柔軟に設定できるようになった。
• ただし BigQuery の処理上、View の中で集計関数を用いている場合はこの方法が取れず、他の手段で解決する必要がありそう。  

Bigquery で View の Partition を切るモチベーション

業務上 BigQuery を使ってデータの集計や加工を行うことが多いのですが、その際、以下の 3つの課題を同時に解決するために View 作成時に少し工夫をしたことを紹介させてください。

• View を使って利用者が書くクエリを可能な限りシンプルにする (データマートを整備しないと分析用のクエリは長大化 / 複雑化しミスも増える)
• BI ツールで View からデータを取ってくるときに、フィルタ設定で任意の Partition からデータを持ってこられるようにする (あるある)
• 一方で必要以上の Partition を取ってこないようにしてコストを最適化する (なので BI ツール上での日付指定に柔軟に対応できる View を作る必要がある)

View での Partition の切り方

BigQuery で View を作成する際、SELECT 句中で Partition の情報を取ってくることで、Partition の情報を View の外側から指定することができるようになります。 例えば、下記のクエリで mydataset.view1 という View を作ったとして、

SELECT
    _PARTITIONDATE AS pd,
    field1,
    field2
FROM
    mydataset.table1

これを

SELECT
    field1,
    field2
FROM
    mydataset.view1
WHERE
    pd = DATE('2019-07-18')

のように View からクエリを作る際に WHERE 句で Partition を絞ることで、ちゃんともとの View で参照している mydataset.table1 の Partition も絞られて、任意の期間のデータを最小限のコストで取ってくることが可能になります。

もちろん、以下のように View の方で複数テーブルを結合している場合でも、この View を mydataset.view2 として、

SELECT
    a.pd1,
    b.pd2,
    a.field1,
    b.field2
FROM
    (SELECT _PARTITIONDATE AS pd1, field1 FROM mydataset.table1) a
    LEFT JOIN
    (SELECT _PARTITIONDATE AS pd2, field2 FROM mydataset.table2) b
    ON
        a.pd1 = b.pd2

View からクエリを作る際にそれぞれの Partition を指定してあげることでちゃんと取ってくる Partition を絞ることができます。

SELECT
    field1,
    field2
FROM
    mydataset.view2
WHERE
    pd1 = DATE('2019-07-18')
    AND pd2 = DATE('2019-07-18')

ただし、BigQuery の仕様上、View 中で GROUP BY を使った集計を行ってしまうと、この方法は使えなくなります。

Google のドキュメントを参照したところ、どうやら処理の順番として集計関数の処理が走る前に取得する Partition の決定がなされるらしく、View 中に集計の処理が入っている場合、その処理がなされる時点で取得する Partition の決定がなされる必要があるため、上記のように外部から Partition を絞ることができなくなるみたいです。