BigQueryのパーティション分割テーブルでTIMESTAMPでエラーが出るときの理由
what
- BigQueryでパーティション分割テーブルを作成する際、パーティショニングを設定することができるが、TIMESTAMPを利用しようとするとエラーが出る場合がある
- 「公式ドキュメントでは設定できると記載があるが、エラーが出るのはなぜなのか?」 これについて調べてみたログ
BigQueryのパーティショニングについて
BQでパーティションテーブルを作る際に、パーティショニングを設定する。
これは、公式ドキュメントでは次の型から設定することができる。
- 整数範囲パーティシニング
- INTEGER
- 時間単位列パーティシニング
- DATETIME
- DATE
- TIMESTAMP
しかし、TIMESTAMPに関しては設定をしてもエラーが出てくる場合がある。
PARTITION BY expression must be DATE(<timestamp_column>), DATE(<datetime_column>), DATETIME_TRUNC(<datetime_column>, DAY/HOUR/MONTH/YEAR), a DATE column, TIMESTAMP_TRUNC(<timestamp_column>, DAY/HOUR/MONTH/YEAR), DATE_TRUNC(<date_column>, MONTH/YEAR), or RANGE_BUCKET(<int64_column>, GENERATE_ARRAY(<int64_value>, <int64_value>[, <int64_value>]))
エラーが出る理由
これは、TIMESTAMPの仕様とパーティション管理に関連している。
TIMESTAMP 列と DATETIME 列では、パーティションを時間単位、日単位、月単位、年単位のいずれで作成できます。
https://cloud.google.com/bigquery/docs/partitioned-tables?hl=ja#date_timestamp_partitioned_tables
TIMESTAMPは秒やマイクロ秒の精度を持つため、そのままパーティションキーとして指定すると、非常に多くのパーティションが作成されてしまい、管理が困難になったり、パフォーマンス上の問題が発生したりする可能性がある。
BQではこれを回避するために、TIMESTAMPカラムを利用する場合は特定の粒度に切り詰める関数（TIMESTAMP_TRUNC, DATETIME_TRUNC, DATE）で変換したもののみをパーティションカラムとして指定するように内部で制限をかけている。
そのため、これらの関数を利用せずにTIMESTAMPカラムを指定した場合、「特定の単位に切り詰めてくれ」とエラーが出てくる。
_PARTITIONTIMEについて
BQでパーティションを設定する際の疑似列として _PARTITIONTIME が存在する。
これはTIMESTAMP型なのだが、あらかじめ特定単位で切り詰めるようにできている。
取り込み時間パーティション分割テーブルには、_PARTITIONTIME という疑似列（パーティショニング列）があります。この列の値は、各行の取り込み時間（UTC）であり、TIMESTAMP 値としてパーティションの範囲（1 時間ごとや 1 日ごとなど）に切り詰められます。
https://cloud.google.com/bigquery/docs/querying-partitioned-tables?hl=ja#query_an_ingestion-time_partitioned_table
まとめ
TIMESTAMPをパーティションとして設定しようとしてエラーで引っかかった事があったので改めて調べてみた。
管理の面で複雑になる点や、それを避けるためにBQ側でエラーとして出しているというのが初めてわかった。
Discussion