🕙

【BigQuery】時間単位のパーティショニングの動き

2021/04/12に公開

Google Cloud

はじめに

少し前のこと^[1]ですが、BigQueryで時間単位のパーティショニングがGAしました。
(合わせて、月単位や年単位のパーティショニングも機能追加されています)

やってみたこと

これまでは「日単位のパーティショニング」しかできませんでした。

時間単位でパーティショニングができるのなら、わざわざ日単位を利用する必要がないのでは？
と思ったので、動作検証をしてみました。本投稿はその備忘録になります^^

【検証目的】

「時間単位のパーティショニング」のクエリ記述方法
日付で検索した場合に「日単位のパーティショニング」とスキャン対象容量は同じになるか

時間単位のパーティショニングの設定

下記のようなCREATE TABLEのSQL文で作成します。

時間単位のパーティショニングを設定したテーブルを作成するSQL文

CREATE TABLE dataset.table01 (
   field_name01 string options(description='フィールド名01'),
   field_name02 string options(description='フィールド名02'),
   time_stamp timestamp options(description='日時')
)
+ PARTITION BY TIMESTAMP_TRUNC(time_stamp, HOUR)

パーティショニングには、PARTITION BY句^[2]を利用します。
TIMESTAMP_TRUNC関数^[3]でHOUR指定して、time_stampフィールドの時間以下を切り捨てます。

実行環境

Product	version
BigQuery	2021年4月2日時点
Region	asia-northeast1

【前提条件】
・「時間単位のパーティショニング」が設定されたテーブルを作成済みです。
・ 2019年10月20日〜22日のデータを格納しています。

【テーブルに関する情報】

日単位のレコード数と容量

日付	レコード数	容量
2019-10-20	130,115	29.1MiB
2019-10-21	4,205,109	897.9MiB
2019-10-22	272,850	57.6MiB
合計	4,608,074	984.6MiB

2019年10月21日の時間単位のレコード数

時間	レコード数
0:00:00	397,675
1:00:00	458,020
2:00:00	475,818
3:00:00	362,889
4:00:00	363,604
5:00:00	431,073
6:00:00	432,320
7:00:00	394,299
8:00:00	354,093
9:00:00	269,565
10:00:00	122,543
11:00:00	57,313
12:00:00	27,992
13:00:00	12,225
14:00:00	6,676
15:00:00	5,868
16:00:00	4,442
17:00:00	4,269
18:00:00	3,767
19:00:00	3,732
20:00:00	3,936
21:00:00	3,867
22:00:00	4,271
23:00:00	4,852
合計	4,205,109

検証シナリオ

日付のみ指定
日付+時間指定
日付+時間の範囲指定
日付+EXTRACTによる時間指定

1. 日付のみ指定

下記クエリで日付のみの指定で時間単位のパーティショニングが効くか確認しました。

1-1.スキャン対象容量確認

SELECT
 *
FROM
 `<プロジェクト名.データセット名.テーブル名>`
WHERE
 DATE(time_stamp) = "2019-10-21"

【結果】
1-1. 「日単位のパーティショニング」と同サイズで897.9MiBでした。

2. 日付+時間指定

2019年10月21日の10:00:00をポンポイントでクエリしました。
スキャン対象容量とレコード数の2つのポイントで確認しました。

2-1.スキャン対象容量確認

SELECT
 *
FROM
 `<プロジェクト名.データセット名.テーブル名>`
WHERE
 TIMESTAMP_TRUNC(time_stamp, HOUR) = "2019-10-21 10:00:00"

2-2.レコード数確認

SELECT
 TIMESTAMP_TRUNC(time_stamp, HOUR) AS partition_timestamp,
 COUNT(0) AS cnt
FROM
 `<プロジェクト名.データセット名.テーブル名>`
WHERE
 TIMESTAMP_TRUNC(time_stamp, HOUR) = "2019-10-21 10:00:00"
GROUP BY 1
ORDER BY 1

【結果】
2-1. 日単位の897.9MiBよりも小さい25.1MiBとなりました。
2-2. cntフィールドは122,543となりました。(10時台のみヒット)

3. 日付+時間の範囲指定

2019年10月21日の10:00:00〜10:59:59を範囲指定でクエリしました。
スキャン対象容量とレコード数の2つのポイントで確認しました。

3-1.スキャン対象容量確認

SELECT
 *
FROM
 `<プロジェクト名.データセット名.テーブル名>`
WHERE
 time_stamp BETWEEN "2019-10-21 10:00:00" AND "2019-10-21 10:59:59"

3-2.レコード数確認

SELECT
 TIMESTAMP_TRUNC(time_stamp, HOUR) AS partition_timestamp,
 COUNT(0) AS cnt
FROM
 `<プロジェクト名.データセット名.テーブル名>`
WHERE
 time_stamp BETWEEN "2019-10-21 10:00:00" AND "2019-10-21 10:59:59"
GROUP BY 1
ORDER BY 1

【結果】
3-1. 日単位の897.9MiBよりも小さい25.1MiBとなりました。
3-2. cntフィールドは122,543となりました。(10時台のみヒット)

4. 日付+EXTRACT関数による時間指定

2019年10月21日の時間を10と、EXTRACT関数^[4]による時間指定でクエリしました。
スキャン対象容量とレコード数の2つのポイントで確認しました。

4-1.スキャン対象容量確認

SELECT
 *
FROM
 `<プロジェクト名.データセット名.テーブル名>`
WHERE
 DATE(time_stamp) = "2019-10-21"
 AND EXTRACT(HOUR FROM time_stamp) = 10

4-2.レコード数確認

SELECT
 TIMESTAMP_TRUNC(time_stamp, HOUR) AS partition_timestamp,
 COUNT(0) AS cnt
FROM
 `<プロジェクト名.データセット名.テーブル名>`
WHERE
 DATE(time_stamp) = "2019-10-21"
 AND EXTRACT(HOUR FROM time_stamp) = 10
GROUP BY 1
ORDER BY 1

【結果】
4-1. 「日単位のパーティショニング」と同サイズで897.9MiBでした。
4-2. cntフィールドは122,543となりました。(10時台のみヒット)

まとめ

1のケースは時間指定していませんが、時間単位のパーティションを24時間分読みました。
2と3のケースは「時間単位のパーティショニング」が効きました。
4のケースは時間単位は効かず、1と同じく時間単位のパーティションを24時間分読みました。

検証結果では、「日単位のパーティショニング」は必要ないように思ってしまいましたが
本当に制約がないか調査してみたところ、以下のような上限値が設けられていました。^[5]

パーティション分割テーブルごとのパーティション最大数 - 4,000
ジョブオペレーション（クエリまたは読み込み）ごとに対象にできるパーティション数は最大 4,000 件です。4,000 件を超えるパーティションを対象とするクエリまたは読み込みジョブは、BigQuery で拒否されます。

日単位だと11年と数ヶ月、時間単位だと5ヶ月と10日ほどが上限になります。
年単位の長期間におよぶクエリが使えないというデメリットがありました。

要件に合えば、「時間単位のパーティショニング」に大きなメリットがありそうですね！

脚注