BigQuery Partition Expiration でパーティション制限を超えたい
code:limitation
パーティション分割テーブルあたりの最大パーティション数 - 4,000
1 つのジョブで変更される最大パーティション数 - 4,000 件
ジョブ オペレーション（クエリまたは読み込み）ごとに対象にできるパーティション数は最大 4,000 件です。4,000 件を超えるパーティションを対象とするクエリまたは読み込みジョブは、BigQuery で拒否されます。
取り込み時間パーティション分割テーブルあたりの最大パーティション変更数 - 5,000 件
列パーティション分割テーブルあたりの最大パーティション変更数 - 30,000 件
Date Partitioned Table について考える
4,000 = 11年足らず、都度全データを転送する場合、データの値域が11年に渡っていたら失敗することになる
そこで、パーティションの有効期限を使ったら制限を回避できないかというのが思いつき。
パーティション数上限以上の日付データがあるが、partition 保持期間を考慮すると収まる場合に受け入れてくれると助かる。
パーティション有効期限は、この期間を過ぎたパーティションは自動で削除されるようにする設定。少なくとも --time_partitioning_epixration はパーティション作成日からではなく、パーティションの日付から
テーブルのパーティションのデフォルトの存続期間（秒）です。最小値はありません。パーティションの日付にこの整数値を足した値が有効期限になります
どのタイミングで作用するか知らないが、1年のデータを転送し、残存期間を90日に設定すると、すぐそれ以前のデータは読み取れなくなりテーブルサイズも小さくなるのを確認した。
code:gen.rb
require 'date'
require 'json'
date = Date.today
(1..5000).each do |i|
puts JSON.dump({ date: date - i, num: i })
end
雑にパーティション数を超えるデータを ruby gen.rb > date.ndjson などして作る
x load ジョブで table を作りつつ expiration を指定 $ bq load --source_format=NEWLINE_DELIMITED_JSON --autodetect --time_partitioning_field=date --time_partitioning_expiration=2592000 gomibako.max_partition ./data.ndjson
Error in query string: Error processing job 'pokutuna-playground:bqjob_r3448dd143d9e6eda_00000172f66c5a2f_1': Too many partitions produced by query, allowed 4000, query produces at least 5000 partitions
x 先にテーブルと expiration を指定してからロード * いけたりしないかな? と思ってやったけどだめだった x --ignore-unknown-values と組み合わせる たぶんだめだろうなと思うけどやってみる
x 先にテーブルを作っておき streaming insert CLI からも bq insert でできる
まだ試してないけどできるんじゃないかな? NDJSON に制約されるのが難点
(例えば差分ロードに対応していない Firestore/Datastore Export Data には使えない)
* 部分的に入れること自体は可能。過去 1 年間 ~ 将来の 6 か月間のデータに限られるので 4000 パーティションを超えうる値域のデータを入れたいという目的は達成されない。長期間に渡る(4000パーティションを超えうる)データを、このさらに狭い期間に限って残して入れたい場合には使える。 bq insert dataset.table /tmp/mydata.json
echo '{"a":1, "b":2}' | bq insert dataset.table
のように、引数でも標準入力からでもどちらでも対応している
$ bq --project_id=pokutuna-playground insert gomibako.partition_limit ./data.ndjson
record 365 errors: invalid: Value 18075 for field date of the destination table pokutuna-playground:gomibako.partition_limit is outside the allowed bounds. You can only stream to date range within 365 days in the past and 183 days in the future relative to the current date.
record 366 errors: invalid: Value 18074 for field date of the destination table pokutuna-playground:gomibako.partition_limit is outside the allowed bounds. You can only stream to date range within 365 days in the past and 183 days in the future relative to the current date.
record 367 errors: invalid: Value 18073 for field date of the destination table pokutuna-playground:gomibako.partition_limit is outside the allowed bounds. You can only stream to date range within 365 days in the past and 183 days in the future relative to the current date.
...
record 0 errors: stopped:
record 1 errors: stopped:
record 2 errors: stopped:
...
という感じ、少なくともパーティションが考慮されて警告に反映されてる
record n errors: ... は n 行目のレコードによるエラー
--skip_invalid_rows オプションを使えばこのままでも有効なものが入りそう
You can only stream to date range within 365 days in the past and 183 days in the future relative to the current date.
へーこの仕様どこ、と思ったけど書いてあった
パーティション分割テーブルへのストリーミング
DATE 列または TIMESTAMP 列で分割されたテーブルに、過去 1 年間と将来の 6 か月間のデータをストリーミングできます。この範囲外のデータは拒否されます。
データがストリーミングされると、過去 7 日間と将来の 3 日間のデータがストリーミング バッファに置かれ、対応するパーティションに抽出されます。この範囲外のデータ（ただし過去 1 年、将来 6 か月の範囲内）は、ストリーミング バッファに配置され、次に UNPARTITIONED パーティションへ抽出されます。パーティショニングされていないデータが十分蓄積されると、対応するパーティションに読み込まれます。
取り込み時間パーティション分割テーブルにデータをストリーミングする場合は、insertAll リクエストの一部としてパーティション デコレータを指定することで、推定された日付をオーバーライドできます。たとえば、次のようにパーティション デコレータを使用して、mydataset.table テーブルの 2017-03-01 に対応するパーティションにストリーミングできます。
へー、以前 Stackdriver Logging から sink した際(_PARTITIONTIME でパーティションが作られる時代、2020年2月あたりまで)に、実際の取り込み時間じゃなくてログの @timestamp が採用されていたけど、パーティションデコレータ が裏で使われていたんんかな。サポートに聞いた際に、そういう挙動です、じゃなくてパーティションデコレータの存在を教えてくれりゃよかったんだけど。
ならどうする
1度パーティションのないテーブルに全転送してクエリしてパーティションテーブルに移す、費用は諦める
Dataflow で要らない期間を落とす