大量のデータをCSVでインポートする

Published in

FiNC Tech Blog

8 min readSep 29, 2017

法人向けサービス FiNC INSIGHT

こんにちは、技術開発部の中森です。普段は法人向けサービス「FiNC INSIGHT」の開発に携わっています。
FiNC INSIGHTとは、企業やそれに所属する従業員の体・メンタル・エンゲージメントの状態を、部署や年代など様々な切り口から可視化し、問題点を明確にして、それらに適切な改善策を実施していくサービスです。
それらの可視化には、「FiNC ウェルネスサーベイ」と呼ばれる数十問の質問を従業員に受けていただいたり、定期的に受けていただいている健診データを取り込むことにより行なっています。

大量のデータの準備

FiNC ウェルネスサーベイの受検に際し、その受検対象となる従業員情報が必要になります。
ここでの従業員情報には氏名や、あとで組織やデモグラごとの分析のために所属部署や年齢、性別を含み、大きな企業での受検となると一度に数万件のデータを取り込むことになります。

一方、健診データでは、血圧や腹囲などの数十以上に及ぶ健診項目を、数年分存在します。
データ数は従業員×受検年数になるため、こちらのデータを取り込む際にも一度に数万〜数十万件のインポートが発生します。

これらの大量のデータのインポートにはCSVが用いられます。
企業の担当者の方がフォーマットに従ってCSVを作成し、FiNC INSIGHTにアップロードすると、データの取り込みが行われるようになっています。

今回のブログでは、この大量のデータを包含したCSVを取り込む際にぶつかった問題と、それらに対して実施してきた解決策を紹介していきます。

文字コード

CSVは表形式の文字列データの集合です。
それらの編集にはほとんどのケースでExcelが用いられます。
そして、Excelで出力されるCSVの文字コードはデフォルトではShift-JISです。
一方、FiNC INSIGHTの内部ではUTF-8を使っています。
そのため、インポートにおけるファイルとDBの境界面であるアプリケーションでは、データを取り込む際にShift-JISからUTF-8への変換が必要になります。
CSV自体にUTF-8を指定することはもちろん可能なのですが、一般的な人事担当者の方に文字コードの知識を求めることは難しく、ExcelでのUTF8を指定したエクスポートは一手間かかって余計な混乱を招きかねないので、インポートファイルには高い頻度で使われるShift-JISに寄せています。

Bulk Insertによる改善

CSVファイル全体を丸飲みしてしまうと、相当なメモリ量を消費してしまいます。
それを防止するため、CSV自体は1行ずつ読み込むのが望ましいです。

ただ、実際のDBへのinsertを1行ずつ行うと、DBとWebサーバーと通信のオーバーヘッドが取り込み行数分発生してしまい、取り込み処理全体のパフォーマンスの足を大きく引っ張ることになります。
また、取り込み処理があまりにも時間がかかりすぎると、CSVのアップロードのリクエストがタイムアウトしてしまいます。
タイムアウトの時間を伸ばすということもできますが、その処理を終える間、サーバーのプロセスを占有してしまうため他のリクエストが来たときに応答できなくなってしまいます。

これには ActiveRecord::BulkInsert により複数行まとめてBulk Insertすることによりパフォーマンスを大きく向上させることができます。

しかし、通常、modelのvalidationはmodel層で記述しており、このままだと複数行を一度に取り込む場合でも1行ごとにvalidationが実行され、特に presence: true や uniqueness: true などのvalidtionでは1行ごとにDBへのSELECTが走ってしまうため、パフォーマンスの向上がそれほど見られません。
また、CSVの1行が複数のModelの定義に関わっている場合があります。
例えば、氏名やemailなどはUserモデルに持つことになりますが、部署や職種などの情報は他のモデルとのリレーションで定義します。
一方、それらをCSVをで記述する場合は、以下のように1行に定義することになります。

user.csv
姓,名,email,部署,職位
田中,太郎,taro.tanaka@example.com,営業,課長

このように実際のモデルと行が対応しない場合、別途CSVの各行をvalidationするためのクラスを作成し、カラムの型や利用可能な文字列とそのフォーマットなど、DBに依存しないようなvalidationだけ行います。
そして、validationを通ったものだけまとめてvalidatoinをskipしてBulk Insertします。
ただし、これだけでは複数プロセスが同時に走った場合に、本来ユニークであるべきものが重複して取り込まれてしまう恐れがあるので、最低限、ユニーク制約などDBでしかチェックできないものは、テーブル定義時にちゃんと制約をかけておきます。

実際のコードは下記のようになります。

users = []
CSV.each_line do |row|
  next unless UserRow.new(row).valid?
  users << User.new(row)
end
User.import users, on_duplicate_key_update: [:first_name, :last_name, :email], validate: false

Delayed Jobによる非同期化

FiNC INSIGHTではバックエンドのJob/WorkerとしてDelayed::Jobを利用しています。
Delayed Jobを用いて、CSVのアップロードリクエストだけ受け付けて、実際の取り込み処理を後回しにすることも可能です。

ただし、このような遅延実行を行う場合、次の2つの問題に気をつける必要があります。

まず1つ目は、非同期処理になるので、CSVファイルに問題があった場合に、その問題箇所を同期的にユーザーに通知することができなくなります。
そのため、CSVファイルの取り込みで失敗した際は、メールやChatなどブラウザ以外での通知手段を考える必要が出てきます。

2つ目は、インポートするデータのサイズです。
通常、アプリケーションとDelayed JobのWorkerは別々のサーバーで動いているケースが大半です。
そのため、両方で同一のデータを参照するためには、一度アプリケーションサーバーでCSVのパースをし、Delayed JobのhandlerカラムにシリアライズされたRuby Objectを入れてWorker側で処理することになります。
このとき、あまりにデータサイズが大きいとhandlerカラムのカラム長をオーバーしてDelayed JobへJobを入れるときに失敗してしまいます。

これを回避するには、データサイズをカラム長をオーバーしないところで切って複数のJobに分割して入れることで対処できます。

しかし、Workerが複数動作している環境では、1つのCSVファイルに対して並列に処理をすることになります。
そのため、複数のCSVの行で同一のレコードを処理する記述があった際に、トランザクションを考慮していないとデータを上書きしてしまったり、トランザクションを考慮しても、一方のWorkerが長時間ロックを獲得していると、もう一方のWorkerでロックが獲得できずに結局Lock wait timeoutが発生しまうという問題があります。
また、本来はファイルの先頭から末尾まで順に処理することを期待していたのものが、実際には処理を追い越してしまう場合が発生してしまいます。
このように、CSVを分割して処理するケースでは、同一のレコード群に処理を行わない様にCSVデータを分割するか、Worker間での競合状態を考慮して適切に排他制御を盛り込む必要があり、複雑性が上昇します。

S3による非同期化

CSVのアップロード時にファイルを一時的に別のところに退避して、後からバッチなどで取り込み処理を行うことも可能です。
具体的にはアプリケーションサーバでは受け取ったファイルを別のサーバーにアップロードし、バッチサーバーでは先ほどのファイルをダウンロードして実際の取り込み処理を行います。
これはAWS環境ではS3を使うことで簡単に実現できます。

この方法では処理時間に対する制約がないのであれば、一度に処理するデータサイズが大きくても1プロセスでシーケンシャルにファイルを処理できるので、Delayed Jobのような並列データを考慮する必要がなくなります。
ただ、どうしてもファイルアップロードを挟む関係上、システムの複雑性が増してしまいます。

まとめ

本エントリでは大量データをCSVでインポートする際に、FiNCで遭遇した問題と対処方法について紹介しました。
このような非エンジニアでも大量のデータをインポートする仕組みを作っておくと、機能提供の対象となるユーザーだけではなくQAを行う際にも役立ちます。