goodbyegangsterのブログ

備忘録的な

BigQueryでパーティション分割テーブルを作成する

BigQueryでパーティション分割テーブルの作成方法を確認します。

パーティション分割できる種類

BigQueryでは、以下の条件でテーブルをパーティションできます。

  • 時間単位の列
    • TIMESTAMPDATEDATETIME列に基づいて
  • 取り込み時間
    • BigQueryにデータを取り込んだタイムスタンプに基づいて
  • 整数範囲
    • テーブル内の整数列に基づいて

パーティション分割テーブルの概要

今回は「時間単位の列」の条件を利用して、パーティションを作成します。

今回扱うデータ

2019年1月頭から2021年7月末までの、日経225のデータを利用します。データの中身はこんな感じです。

trading_day,closing_quotation,opening_quotation,high,low
"2019-01-04","19561.96","19655.13","19692.58","19241.37"
"2019-01-07","20038.97","19944.61","20266.22","19920.80"
"2019-01-08","20204.04","20224.67","20347.92","20106.36"

パーティション分割されたテーブルの作成

パーティション分割したテーブルを作成します。PARTITION BY句にパーティションの条件として利用したい列を指定することで、パーティション分割されたテーブルを作成してくれます。また、DATE_TRUNC関数を併用することで、任意の期間(DAY, WEEK, MONTH, QUARTER, YEAR)で分割してくれます。明示的に「2021年7月用のパーティションを作成する」みたいな宣言をせずとも、自動的にパーティションを作成してくれます。

CREATE TABLE sample.n225 ( 
  trading_day DATE NOT NULL OPTIONS(description="取引日"),
  closing_quotation NUMERIC NOT NULL OPTIONS(description="終値"),
  opening_quotation NUMERIC NOT NULL OPTIONS(description="始値"),
  high NUMERIC NOT NULL OPTIONS(description="高値"),
  low NUMERIC NOT NULL OPTIONS(description="低値")
) 
PARTITION BY
  DATE_TRUNC(trading_day, MONTH)
OPTIONS (
  partition_expiration_days=1825,
  require_partition_filter=true,
  friendly_name="日経225時系列データ",
  description="月別パーティションされた、201901から202107までの日経225時系列データ",
  labels=[("environ", "dev")]
)

CREATE TABLE ステートメント

DATE_TRUNC 関数

partition_expiration_daysオプションは、パーティションの有効期限(BigQuery が各パーティションにデータを保持する期間)を指定できます。

A partition's expiration time is calculated from the partition boundary in UTC. For example, with daily partitioning, the partition boundary is at midnight (00:00:00 UTC). If the table's partition expiration is 6 hours, then each partition expires at 06:00:00 UTC the following day. When a partition expires, BigQuery deletes the data in that partition.

partition boundary(最新のパーティションと理解)より指定期間以上に古い場合、パーティションを(つまりデータを)削除してくれます。

パーティションの有効期限を設定する

require_partition_filterを有効とすると、パーティション分割されたテーブルへのクエリには、対象パーティションを限定する条件をつける必要があります。

パーティション分割テーブルに [パーティション フィルタを要求] 設定がある場合は、そのテーブルのすべてのクエリに、パーティショニングする列のみを参照する少なくとも 1 つの述語を含める必要があります。

パーティション フィルタ要件を設定する

また、ロードされるデータに応じて、以下2つの特殊パーティションが作成されます。

__NULL__: パーティショニングする列で NULL 値を持つ行が含まれます。
__UNPARTITIONED__: パーティショニングする列の値が 1960-01-01 より前、または 2159-12-31 より後の行を含みます。

時間単位列パーティショニング

データのロード

Cloud Storage上にアップロードしたデータを、作成したテーブルにロードしてみます。

$ bq load \
--field_delimiter=',' \
--quote='"' \
--skip_leading_rows=1 \
--source_format=CSV \
samle.n225 gs://shinemore/n225/n225_201901_202107.csv

bq load

検索

検索してみます。

> SELECT * FROM `sample.n225` WHERE trading_day BETWEEN '2020-01-01' AND '2020-01-31' LIMIT 3
---
trading_day,closing_quotation,opening_quotation,high,low
2020-01-06,23204.86,23319.76,23365.36,23148.53
2020-01-07,23575.72,23320.12,23577.44,23299.92
2020-01-08,23204.76,23217.49,23303.21,22951.18

なお、require_partition_filterを有効としたため、WHERE句でtrading_day列を条件指定しない場合、クエリはエラーとなります。

INFORMATION_SCHEMAビューでの確認

作成されているパーティション情報は、管理ビューのINFORMATION_SCHEMA.PARTITIONSで確認できます。

> SELECT table_schema, table_name, partition_id, total_rows FROM `sample.INFORMATION_SCHEMA.PARTITIONS` 
WHERE table_schema="sample" AND table_name="n225" 
ORDER BY partition_id
---
table_schema,table_name,partition_id,total_rows
sample,n225,201901,19
sample,n225,201902,19
sample,n225,201903,20

INFORMATION_SCHEMA.PARTITIONS ビュー

パーティション・デコレータを利用した検索

パーティション・デコレータを利用して、テーブル内のパーティションを直接指定してクエリを実行できます。デコレータ形式はパーティショニングした条件により異なります。以下を参照。

パーティション デコレータ

月別でパーティションを作成した場合、YYYYMMという形式となり、テーブル名のsuffixに$YYYYMMと年月を指定することで、パーティションを指定することができます。例えば、n225$2020101と指定すると、2020年1月のパーティションを指定することになります。

$ bq query 'select * from sample.n225$202001 limit 3'
Waiting on bqjob_r3964f2f0de3a2dc1_0000017b0232d00c_1 ... (0s) Current status: DONE
+-------------+-------------------+-------------------+----------+----------+
| trading_day | closing_quotation | opening_quotation |   high   |   low    |
+-------------+-------------------+-------------------+----------+----------+
|  2020-01-06 |          23204.86 |          23319.76 | 23365.36 | 23148.53 |
|  2020-01-07 |          23575.72 |          23320.12 | 23577.44 | 23299.92 |
|  2020-01-08 |          23204.76 |          23217.49 | 23303.21 | 22951.18 |
+-------------+-------------------+-------------------+----------+----------+

パーティション・デコレータは、bqコマンド経由でないと利用できないようです。