BigQueryでパーティション分割テーブルの作成方法を確認します。
パーティション分割できる種類
BigQueryでは、以下の条件でテーブルをパーティションできます。
- 時間単位の列
TIMESTAMP、DATE、DATETIME列に基づいて
- 取り込み時間
- 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")] )
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
検索
検索してみます。
> 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コマンド経由でないと利用できないようです。
