BigQuery パーティション分割テーブル①基本

1. なぜ、パーティション分割が必要？

• BigQueryは、列指向データベース
• 通常のテーブルにWHERE句で特定のカラムについて条件を指定して、抽出結果を絞ったとしても、SELECT句で指定したカラムは全件スキャンされる
• 料金体系(※1)によって、データのスキャン量で課金されるため、スキャン量を減らすことは重要
• クエリのパフォーマンスの観点からしても、スキャン量の削減は重要
• その実現方法の1つとして、テーブルのパーティション分割がある
• 公式：「分割テーブルの概要」

2. 例

• パーティション分割無しとありの2つのテーブルを用意し、数件データを入れる
• 同一クエリでのデータスキャン量の違いを確認する

2.1. パーティション分割無し

• テーブル準備

-- partition分割無し
CREATE TABLE <dataset_name>.not_partitioned_table (
  id STRING,
  updated_at DATETIME
)
;

-- データ(適当にinsert済み)
id     | updated_at
-------+---------------------
id_01  | '2023-03-01T01:00:00'
id_02  | '2023-03-02T02:00:00'
...
id_59  | '2023-04-24T06:00:00'
id_60  | '2023-04-25T07:00:00'

-- 4月以降のデータのみをWHERE句で条件指定してSELECT
SELECT
    *
FROM <dataset_name>.not_partitioned_table
WHERE
    updated_at > '2023-04-01T00:00:00'
;

• 結果： 891B(全件スキャン)
  

2.2. パーティション分割あり

• テーブル準備

-- partition分割あり
-- updated_atで1日ごと
CREATE TABLE <dataset_name>.partitioned_table (
  id STRING,
  updated_at DATETIME
)
PARTITION BY DATETIME_TRUNC(updated_at, DAY)
;

-- データは同様

-- 4月以降のデータのみをWHERE句で条件指定してSELECT
SELECT
    *
FROM <dataset_name>.partitioned_table
WHERE
    updated_at > '2023-04-01T00:00:00'
;

• 結果： 435B(4月以降のみにデータ量が削減)
  

3. ビューからの参照

• 2.2.で作成したパーティション分割ありのテーブル全量を参照するビューを定義

• ビューに対するSELECT文のときに、WHERE句としてパーティションキーを条件とする

• ビューの定義

-- partition分割したテーブル全件を参照
CREATE VIEW <dataset_name_2>.partitioned_view AS (
SELECT
    *
FROM <dataset_name>.partitioned_table
)
;

-- ビューを参照する際、WHERE句で4月以降に制限
SELECT
  *
FROM <dataset_name_2>.mart.partitioned_view
WHERE
  updated_at > '2023-04-01T00:00:00'
;

• 結果： 435B(4月以降のみにデータ量が削減)
  

4. 補足

• パーティションキーとして使用できるデータ型は、下記

  • 　INTEGER
  • 　DATE
  • 　TIMESTAMP
  • 　DATETIME

• 1つの分割テーブルあたりのパーティション数：4,000(デフォルト)