本題の前に

パーティションとクラスタリングで得られるメリット

パーティションやクラスタリングを実装することで、
クエリのパフォーマンスを向上させ、クエリリソースの消費を抑えることができる。

パーティションやクラスタリングが実装されたテーブルに対してクエリを実行すると、
テーブルに対してスキャンする範囲を狭めることができるため、
そのようなメリットを得ることができる。

パーティションとは

テーブルの特定の列の値を基準にして、内部的に複数パートに分割する機能。
クエリ実行時に全体にアクセスするのではなく、WHERE句などで必要なパートに絞り込むことで、そのパートに限定してアクセスすることができる。

分割テーブルの概要（BigQuery公式ドキュメント）に記載の通り、時間単位の列、取り込み時間、整数範囲の列に応じて複数パートに、パーティションで分割することができる。

クラスタリングとは

テーブルの特定の列に基づいて、
テーブルのデータを内部的にソート＆近い位置に配置することができる機能。
パーティションと併用することができる。
（パーティション分割した後に、クラスタリングされる。）

クラスタ化列は最大4列まで指定可能で、指定する順番にも意味がある。
テーブルやテーブルパーティション全体ではなく、クラスタ化列に基づいて関連するブロックのみをスキャンするかたちになる。

BigQueryのパーティションのひとつ「シャーディングテーブル」とは

シャーディングテーブルパーティションのひとつで、
データを日付毎に別のテーブルとして作成する方法。
WHERE句で_table_suffixでフィルタリングすることで、テーブルのスキャンの範囲を指定できる。

シャーディングテーブルのメリットは、日付毎に異なるスキーマを適用することができること。
データソースでカラムが増減した場合でもそのまま取り込めるので、BigQueryをデータレイク的に使用する時に便利。

本題

業務でどのように、パーティションとクラスタリングを実装したのかについて。

パーティションとクラスタリングの実装方法

下記のクエリで、パーティショニングとクラスタリングがされたデータマートを生成した。
（ETLツールのワークフロー機能で毎日同じ時刻に定期実行する）

/* create or replace tableは、全量洗い替え更新を意味している。 */
/* or replaceで、同じ名前のテーブルが存在する場合は置き換えられるようになる。 */

create or replace table
    `生成するデータマートの名前`
partition by -- monthごとにパーティショニングされる。
    month
cluster by -- day, os, workidごとに、この優先順位でクラスタリングされる。
    day
    , os
    , work_id
as

select
    day
    , date_trunc(day, month) as month -- 日別ではなく月別でパーティショニングしたいので、この列を生成。
    , content_id
    , content_name
    , work_id
    , work_title
    , uu
    , pv
    , os
from
    `統合テーブル名`
;

補足

データポータルでday,os,work_idでフィルタリングすることが多いので、
dayとosとwork_idをクラスタ化列に設定した。
今回は10年以上のデータを取り扱う必要があるため、
dayではなくmonthでパーティショニングを行った。
理由は、BigQueryでは、1テーブルにつき、4,000パーティションまでしか保持できないため。
365日×11年=4,015日の段階ではエラーが発生してしまうため、
dayでパーティショニングするのは控えた方が良いと考えられた。

パーティション、クラスタリング実装で得られた結果

クエリリソースの消費を節約することができていた。

パーティション、クラスタリング無しのテーブルに下記のクエリを実行すると「14GB」クエリリソースが消費されるのに対し、

select
    day
    , date_trunc(day, month) as month
    , content_id
    , content_name
    , work_id
    , work_title
    , uu
    , pv
    , os
from
    `統合テーブル名`
;

パーティションとクラスタリングが実装されたテーブルに下記のクエリを実行して同じカラムを抽出しようとすると、クエリリソースの消費は「12GB」となっていた。

select
    day
    , date_trunc(day, month) as month
    , content_id
    , content_name
    , work_id
    , work_title
    , uu
    , pv
    , os
from
    `生成するデータマートの名前`
;