BigQueryのパーティショニング

gcp
https://cloud.google.com/bigquery/docs/creating-partitioned-tables?hl=ja

Contents

1 パーティショニングの検証用環境。文字列をパーティショニングに指定
2 パーティショニング対象をtimestampにしてみる
- 2.1 ① 自分でパーティションフィールドを指定する。
- 2.2 ② 自分で指定ではなく、自動的にパーティションを作成する。
3 検証
4 ちなみに・・・
5 パーティション
6 _PARTITIONDATE,_PARTITIONTIME
7 パーティション、シャード化
8 シャーディング

パーティショニングの検証用環境。文字列をパーティショニングに指定

パーティショニングとして、スケジュールで
以下のようにevent_nameを指定してパーティショニングをしてみます。

SELECT
  event_name,
  event.key,
  event.value.string_value,
  user_pseudo_id
  FROM `yoshida-labo.analytics_257508939.events_*`,
  UNNEST(event_params) AS event
  WHERE _TABLE_SUFFIX = FORMAT_TIMESTAMP("%Y%m%d", TIMESTAMP_SUB(@run_date, INTERVAL 1 day),"Asia/Tokyo")

パーティションは、日付しかできないので、実際に作成されたテーブルを見ると、
パーティションにはスキーマが指定されていません。
つまり、パーティションはSTRING型は指定できないということです。さらに言えば、日付しか指定できません。

実際にスケジュールクエリを実行してみると、以下のようにTIMESTAMPでないとダメよとエラーになりました。

パーティショニング対象をtimestampにしてみる

パーティショニングスキーマをtimestamp型にすると、
以下の2つでパーティショニングをすることができます。
_PARTITIONTIME：timestamp型で指定
_PARTITIONDATE：datetime型で指定

ここでなぜ、timestamp型でパーティショニングをするかというと、大は小を兼ねると同じように、
timestampにはdatetimeが含まれるので、
timestamp型(秒数まで)でパーティショニングをしておけば、datetime(日付)でのパーティショニングも可能になる。

SELECT
  CURRENT_TIMESTAMP() as created_at,  -- 実行日付
  event_name,
  event.key,
  event.value.string_value,
  user_pseudo_id
  FROM `yoshida-labo.analytics_257508939.events_*`,
  UNNEST(event_params) AS event
  WHERE _TABLE_SUFFIX = FORMAT_TIMESTAMP("%Y%m%d", TIMESTAMP_SUB(@run_date, INTERVAL 1 day),"Asia/Tokyo")

@run_dateには、実行日時が入るので、GA4のローデータが入るのが、1日前の日付になるので、1日前の日付にしてTABLE_SUFFIXに入れている。

BigQueryのtimezoneのデフォルトはUTC

これでスキャンしようとすると、このクエリを実行すると、925.01 KB が処理されます。とのこと
SELECT
*
FROM `yoshida-labo.test_partition.test_partition_timestamp_20230304`
WHERE created_at = "2023-03-05 11:17:03.170191 UTC";

パーティションの作成方法は以下2通りで、

① 自分でパーティションフィールドを指定する。
② 自動的にパーティションを作成する。

これらについて説明していきます。

まず大前提として日付によるパーティションです。

① 自分でパーティションフィールドを指定する。

この時は、自分でスキーマを指定したので、そのスキーマをWHEREでフィルタすることでパーティションができます。
スケジュールクエリで、パーティションするフィールドを指定することができます。
それか、CREATE TABLEでOPTIONでPARTITION_FIELD指定で作成することが可能になります。

CREATE TABLE `yoshida-labo.test.test_partition`  
 PARTITION BY created_at
 AS
 SELECT
    CURRENT_TIMESTAMP() as created_at,
    event_name,
    count
FROM `yoshida-labo.test.test`;

もちろん指定できるフィールドは、DATETIME型、TIMESTAMP型のフィールドになります。

② 自分で指定ではなく、自動的にパーティションを作成する。

自動的に作成するというのは①のように指定せず作成します。
そして作成は、テーブルにデータが取り込まれた時間でパーティションを切るようになり、自動的に擬似列と呼ばれる_PARTITIONDATE、_PARTITIONTIMEが作成されます。
これを実際にフィルタで指定することで、絞り込みを行い、スキャン量を減らすことができます。

管理画面でのテーブル作成から、自動的に日付で取り込みを選択することで、自動的にパーティションを作成します。

検証

上では2つのパーティショニング方法を説明しましたが、ここで1つ疑問が生じます。
まず普通に空のテーブルを作成しておいて、その上でスケジュールクエリで①を指定して取り込んだ場合どうなるのか？
要は上の2つの方法両方を兼ね備えたパーティションテーブル作成ができるのか。です。

【自分の予想】
②の効果で、スケジュールクエリによって取り込まれたデータが入るので、その取り込み時間でパーティション作成されて、_PARTITIONDATEなどが作成される。
①の効果で、指定するので、そのカラムでもパーティションが切れるようになる。
なので、2つのパーティションが出来上がるようになるのでは？
でも、セグメントがパーティションごとにできるということは、縦横ができてしまうので、難しそうな予感もします。

まず空のテーブルを事前に作成しておきます。
そこで作成するときに、パーティションについて「取り込み時間により分割」にします。

次にスケジュールクエリの方で、以下のように、created_atでパーティショニングフィールドに指定します。

スケジュールクエリを実行してみると、以下のようなエラーが出てしまいました。
Incompatible table partitioning specification. Destination table exists with partitioning specification interval(type:DAY,field:), but transfer target partitioning specification is interval(type:DAY,field:created_at). Please retry after updating either the destination table or the transfer partitioning specification.

宛先テーブルはパーティションテーブルであり、互換性がないと言われてますね、、
できなそうです。。

ちなみに・・・

パーティションテーブルはフィルタをかけることで、スキャン量を減らします。
なのでパーティションテーブルのデータを取りたい場合は、全件取得はさせないようにしたいですよね！
そこで、必ずパーティションのフィルタをかけるように制限をかけることができます。

以下のテーブルでは省略可となっており、制限はかけてないのでエラーにはなりません。

テーブルの作成をする際に、フィルタをかけるオプションを選択しておくと、以下のようになり、ユーザーはクエリ実行する際にこのパーティションテーブルを使う時は、必須でパーティションフィルタを指定しなければなりません。
これにより、無駄なコストは避けれそうですね！
スケジュールクエリではパーティションフィルタの設定はできなそうでしたが、通常のBQ上でのテーブル作成では可能です！

スケジュールクエリを実行するとき、特にテーブルがなければ自動的に勝手にテーブルが作成され、そしてカラムの型も決まります。
しかし、
普通にテーブルを先に空のテーブルを作成しておくときに、フィルタなどを設定しておき、そこで作成しておけばフィルタができるようになるのかもしれません。

▼ 以下のようなエラーが出るようになります。
Cannot query over table 'yoshida-labo.test_partition.t' without a filter over column(s) '_PARTITION_LOAD_TIME', '_PARTITIONDATE', '_PARTITIONTIME' that can be used for partition elimination

パーティション

先程のシャーディングはテーブル名に対して設定するもので、
このパーティションはテーブルの中のカラムに対して指定します。

パーティションを設定したいカラムを指定します。
パーティションを設定したいカラムはテーブルを作成時に指定します。
そうすることで、

そして日付系のカラムの場合は、以下のような特別なカラムを使用して参照することができます。

カラム	説明
_PARTITIONDATE	日付粒度でパーティションを行う
_PARTITIONTIME	時間粒度でパーティションを行う

created_atというカラムはTIMESTAMP型で、
その中には日付のDateと、時間のDATETIMEが情報として入っています。

ちなみにcreated_atは、unixtimeなので日本時間では15時が日付の変わる時間となります。

_PARTITIONDATE,_PARTITIONTIME

パーティション、シャード化

シャード化とは、特定のカラムの値でパーティションを切るというもの。
シャード化をテーブルに適用するには、
作成する段階でシャード化するカラム(スキーム)を指定する必要がある。

そうすることで、_PARTITIONで値を切ることができる。

シャーディング

以下のように関東と関西の都道府県