Embulkでjsonb型カラムを転送するときは、DB側でキャストして高速化
はじめに
YAMAP エンジニアのカレンダー | Advent Calendar 2022 - Qiita 17日目の記事です。
YAMAP の業務でも使わせて頂いているバルクデータローダー Embulk について、データベースの jsonb 型カラムを扱う際の高速化 Tips を書きます。
jsonb 型カラムを Postgres 側で text にキャストして劇的高速化
YAMAP アプリケーションのメインの DB は Postgres でデータ基盤は BigQuery で、日々多くのテーブルを Embulk で Postgres -> BigQuery へと転送しています。 その中のあるテーブルは jsonb 型のカラムを持ち、レコード数も膨大で、転送には9時間ほどかかっていました。
そんなある日、Embulk が jsonb 型カラムの値のパースに失敗したエラーを吐きました。
MessagePack cannot serialize BigInteger larger than 2^64-1
確認すると確かに1つのレコードの json の中に、64bit int を超える値が含まれていました。
どうしたもんかな... と悩みましたが、「そもそも BigQuery には string 型で格納されるわけだし、わざわざ MessagePack にシリアライズさせず、Postgres にやらせればよいのでは」と思いたち、そうしてみました。
従来のクエリ
SELECT col1, col2, col_json FROM some_table;
修正後のクエリ
SELECT col1, col2, col_json::text FROM some_table;
すると目論見通りエラーは解消できたのですが、なんと従来9時間かかっていた転送が、6時間程度で終わるようになったではありませんか!
確かに MessagePack では先のエラーが示すように細かなバリデーションの分時間がかかりそうだし、そもそも Embulk の実行環境より Postgres の環境の方がコンピューティングリソースが豊富です。 この結果は納得ができます。
手元でも検証してみた
このサンプルでは、ネットで拾ってきたサンプル json を使い、1万行の初期データを用意しています。 それを Embulk 側でシリアライズする場合と、Postgres 側でする場合(text 型へのキャストを指定)で、実行時間を比較ます。
Embulk 側でシリアライズする場合(キャスト指定なし)
docker-compose run --rm \ -e EMBULK_CONFIG=pg_to_csv \ -e PG_QUERY='SELECT id, body FROM json_samples' \ embulk real 0m3.978s user 0m9.468s sys 0m0.684s
Postgres 側でシリアライズする場合(キャスト指定あり)
docker-compose run --rm \ -e EMBULK_CONFIG=pg_to_csv \ -e PG_QUERY='SELECT id, body::text FROM json_samples' \ embulk real 0m3.582s user 0m7.907s sys 0m0.505s
数回実行してみましたが、安定して1秒程度の差がありました。 1万行のデータだけでも結構な差を確認できました。
おわりに
これまでに幾度も Embulk の高速化のために戦ってきましたが、最も費用対効果の高かったのがこれですw
jsonb 型カラムのある巨大テーブルでのみ役に立つニッチな Tips ですが、あと 2, 3 人は困ってる人いるのでは。届けこの想い 🕊️