Ruby on Railsでバルク処理を効率的に | Geppoプロダクトブログ
はじめに
こんにちは。小山です。
昨年、印刷会社の営業からITエンジニアにキャリアチェンジし今年の6月から株式会社ヒューマンキャピタルテクノロジー(HCT)にバックエンドエンジニアとしてジョインしました。
弊社では従業員のコンディション可視化・改善ツール「Geppo」を提供しています。
今回はGeppoの開発で得たRuby on Railsにおけるバルクインサート・アップデートでの知見について書いていきます。
直面した課題
先日、設問への回答データ（複数問分）を受け取りDB内のデータ状況に応じて新規登録・更新を切り替えるAPI開発を行う機会がありました。開発の簡易的なイメージ図は以下の通りです。
このAPI開発の過程で私が直面した課題は以下の２点です。
課題１：新規・更新のデータが混在することによりロジックが複雑になり実装が冗長になる
課題２：データ量の増加に応じて処理コストが膨れる・パフォーマンスが低下する
実装方法の検討 その１
実装案のその１として私は以下の方法を検討しました。
（実装イメージ中のデータ名は例でありGeppoのものではありません）
■実装イメージ１
def create
params.each do |param|
item = Item.find_or_initialize_by(
user_id: param[:user_id],
item_no: param[:item_no]
)
item.update_attributes!(
name: param[:name]
)
end
end１、find_or_initialize_byで該当するデータが存在する場合は取得、存在しない場合は新規にインスタンスを作成する。
２、update_attributesで新規作成・更新を行う。
可読性を担保出来たことにより、「課題１：新規・更新のデータが混在することによりロジックが複雑になり実装が冗長になる」はクリア出来ましたが、「課題２：データ量の増加に応じて処理コストが膨れる・パフォーマンスが低下する」を解決していないので不採用にしました。
実装方法の検討 その２
次に検討したのがgemの使用です。（今回採用したのはこの実装方法です。）
activerecord-importというgemを使用しての実装を試みました。
ActiveRecordでUPSERTを実現するメソッドの調査も進めましたが、結果としては現行Rails5系では、find_or_create や find_or_initialize + save以外でActiveRecordで複数レコードを一括でUPSERTする機能は提供されていませんでした。※1
本gemはバルクインサートをActiveRecordを用いて実現してくれます。
・レコードごとでなく一括処理が可能なのでSQLの発行数が削減出来そう
・コードも新規・更新一括で書けるので可読性が高くなりそう
また、GitHubのREADMEの記載に
In our case, it converted an 18 hour batch process to <2 hrs.
（18時間かかっていたバッチ処理が2時間になりました。）
なんということでしょう(゜o゜)
・処理時間の短縮も見込める
以上の理由から実装に踏み切りました。
activerecord-importを使用して実装すると以下のようになります。
■実装イメージ２
def create
items = params.map do |param|
Item.new(
user_id: param[:user_id],
item_no: param[:item_no],
name: param[:name]
)
end
Item.import! items, on_duplicate_key_update:
{conflict_target: %i[user_id item_no],
columns: %i[name]}
end・import
パラメータをmapメソッドで回してレコード分のnewオブジェクトを作成します。activerecord-importではimportメソッドを使用してnewオブジェクト群をDBにimportすることが可能です。
・on_duplicate_key_update
on_duplicate_key_updateオプションを付けるとUPSERTも可能です。
conflict_targetにprimary_keyないしunique indexを指定することで既存レコード時にはcolumnsに指定したカラムのみをupdateをしてくれます。
・発行SQL
発行されるSQLは以下の形になります。
INSERT INTO `items`(
`id`,
`user_id`,
`item_no`,
`name`,
`created_at`,
`updated_at`
)
VALUES(
NULL,
'5d7f05fca4e2e301d121b97d',
'5d7f05fce5ea8c3c0e8fc419',
'foo',
'2019-09-17 14:55:45',
'2019-09-17 14:55:45'
),
(
NULL,
'5d7f05fccee0b7deb1adbbcb',
'5d80f68217fe2b60574f81ba',
'boo',
'2019-09-17 14:55:45',
'2019-09-17 14:55:45'
),
.
.
.
.
(
NULL,
'5d7f05fcf520b206928e1e7a',
'5d80f6uiy989y880574f81ba',
'bar',
'2019-09-17 14:55:45',
'2019-09-17 14:55:45'
)
ON DUPLICATE KEY UPDATE
`items`.`user_id` =
VALUES(
`user_id`
),
`items`.`item_no` =
VALUES(
`item_no`
)
`players`.`updated_at` =
VALUES(
`updated_at`
)
ON DUPLICATE KEY UPDATE構文が発行されており、UPSERTが実行されていることが確認出来ます。
注意点＆悩み
activerecord-importの注意点＆悩みとして以下のことが挙げられます。
１. 対象DBが限定されている
activerecord-importの対象DBはMySQL, SQLite 3.24.0+, Postgres 9.5+に限られます。（上記実装イメージはPostgres対応での実装です。）
実装方法も若干異なるので注意が必要です。
参考：MySQLでの書き方
Item.import! items, on_duplicate_key_update: [:user_id, :item_no, :name]２. エラーハンドリングでの悩み
activerecord-importではimportにエクスクラメーションマークを付けて例外を発生させることは可能ですが、これでは後続のインスタンス群が妥当か判断が出来ません。
エクスクラメーションマークを付けずにimportを実行すると保存に失敗したインスタンスはfailed_instancesの中に配列で返って来ます。それを利用してエラーをハンドリングするのが良いかと思います。
hoge.failed_instances.each do |f|
puts f.errors.full_messages
end
結果
ローカルに3カラム程度のテーブルを用意して。実装イメージ１（find_or_initialize_by使用）と実装イメージ２（activerecord-import使用）の処理速度を比較しました。
実行時間推移の結果は以下のようになりました。
indexは貼らずに計測しています。
【実行時間推移グラフ】
少ない10件程度でも処理時間は約240%程度速くなりました。
グラフが示すように件数が多いほどactiverecord-importの有用性が発揮されています。10,000件ではなんと約6740%の速度改善が見られました。
Githubに記載されていた
In our case, it converted an 18 hour batch process to <2 hrs.
は伊達ではないようです。
また、SQLの発行数も件数分から1回に削減することが出来ました。
おわりに
今回はRuby on Railsでのバルクインサート・アップデートを効率的に実装する課題に取り組み、その知見を書かせていただきました。
個人的にはfind_or_create や find_or_initialize + save以外でRails 5系に標準でActiveRecordに複数レコードを一括でUPSERTする機能が提供されていないことはかなり意外な発見でした。
activerecord-importを使用した結果として可読性高くUPSERTを書くことができ、処理速度を大幅に改善出来ることが出来ました。
バッチ処理などの大量データを処理する際に有効的ではないかと思うので今後も積極的に取り入れていきたいと思います。
HCTでは活発に技術や知識を共有出来る文化があります。
今後も恐れず積極的に知識をアウトプットしてより良いプロダクト開発に貢献していきたいと思います！！
※1 Rails 6.0ではupsert_allメソッドが使用可能になりました。
6系からはactiverecord-importに頼らずとも実装が可能になります。
余談
activerecord-importではSQL発行数が削減されますがその分一度に処理されるSQLバイト数が件数に応じて大きくなります。
クエリの最大はどこなのか気になったので調査してみました。
MySQLでの最大長の設定は以下で確認出来ます。
show variables like 'max_allowed_packet';必要に応じて適宜設定を変更する事ができます。
デフォルトは16MBで1GBまで設定可能なようです。
https://dev.mysql.com/doc/refman/5.6/ja/packet-too-large.html
Postgresは型の最大長の記載はありましたが明確なクエリの最大長の記載は見つかりませんでした、、、
まだまだ勉強が足りませぬ。精進します(´・ω・｀)