始めに
今回業務でlaravelでフォームからのCSVインポート作業の実装を行いました。
そこまで容量がないCSVなら、時間がかからず実装できると思うのですが、
今回は最大で一万行あるCSVのデータをタイムアウトしないでインポートする必要があった為、
実装に一ヶ月程かかってしまいました。。
今後も同じような実装をするかもしれない為、記事にまとめてみました。
誰かの参考になれば幸いです。
目次
| 章 | タイトル | 備考 |
|---|---|---|
| 始めに | ||
| 1 | 設計 | インポートするCSVの情報と設計の話 |
| 2 | 作成 | 一時テーブルを作成し、CSVデータを読み込む方法 |
| 3 | バリデーション処理 | 実際のバリデーション処理の話 |
| 4 | 保存する | 一時テーブルを利用してDBに保存する方法 |
| 5 | 終わりに | |
| 6 | 参考文献 | 今回参考にさせていただいた文献まとめです |
1設計
今回使用している技術は以下になります。(一部)
laravel 9.44.0
PHP 8.1.8
Composer 2.4.4
MySQL 8.0.28
今回は例として、とある図書館利用者とその利用者が使用している図書館カードの情報を、CSVから一万件登録すると仮定します。
登録できるのは最大一万件であり、一万件より多い場合はエラーが出るようにします。
テーブル設計は下記の通りになっています。
CSVデータはこうなっていると仮定します。
※実際にはこんな情報を登録することはないと思うのですが、
あくまでCSVのデータを入れる例として考えてください
この複数テーブルで一万件登録する際に面倒なことがいくつかあります。
1. emailはuniqueでないといけない為、バリデーションチェックに時間がかかる
2. library_numはuniqueでないといけない為、バリデーションチェックに時間がかかる
3. library_status_list_idは存在するidでないと登録できない為、バリデーションチェックに時間がかかる
もし十件ぐらいしかデータがないのであれば、バリデーションチェックにそこまで時間はかからないでしょう。
しかしそれが一万件あった場合、
一万回バリデーションチェックを行うことで、一万件のSQLが発行されてしまい、
膨大な時間がかかってしまいます。
非同期処理を行うにしても、できるだけ時間がかからない方が良いでしょう。
またCSVデータの中身をFormRequest内で行うことは、
バリデーション処理のみで最大一万ループを行ってしまうので、アンチパターンになります。。
FormRequestで行うバリデーション処理は、
比較的軽量なCSV自体のバリデーションのみとかにした方が良いです。
ではこのように時間がかかるバリデーションチェックはどのように行ったのか。
今回は一時テーブルを作成しその中にCSVのデータをloadした後、すぐにバリデーションチェックを行いました。
この作業によって一回のSQLで終わらせることができ、ループ処理の中で行う必要がなくなります！
2作成
一時テーブルとは、セッション単位で利用できるテーブルです。
セッション単位の為、セッションが切れてしまうとその中に格納されている情報も削除されてしまいます。
まあ名前の通りですね。
MySQLでは一時テーブルを利用するのに便利なCREATE TEMPORARY TABLE構文があります。
普通のテーブルと同じようにカラム名とカラム型を指定することで作成できます。
CREATE TEMPORARY TABLE テーブル名 (
-> id INT,
-> name varchar(256)
参照サイト
laravelで一時テーブルを作成する為には、DB::statementを使用して直接SQL文を実行する必要があります。
またDB::rawで生のSQL式を埋め込みます。
参照
それではバリデーションチェックに使用する情報を入れる為の一時テーブルを作成してみましょう。
// 一時テーブルの名前を作成
$tableName = 'tmp_' . time();
// 一時テーブルを作成
DB::statement(DB::raw("CREATE TEMPORARY TABLE $tableName(
email varchar(255),
library_num varchar(255),
library_status_list_id INT)
// MySQLでのテーブル作成オプション
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;"));
これだけで一時テーブルが作成できます！
それでは次にCSV内のデータを一時テーブルに保存してみましょう。
LOAD DATA LOCAL INFILEを使用して一時テーブルにデータをインポートします。
これは通常のインポートより高速にインポートが可能になります。
LOCALはFILE権限なしで、ローカルファイルの読み込みが可能になります。
MySQL8.0でLOAD DATA INFILE LOCAL を利用するには、
サーバとクライアント両方で local-infile パラメータを ON にする必要があります！
セキュリティ強化のため、8.0から LOAD DATA INFILE LOCAL はデフォルトで無効にされているようです。
業務上で使用できるかどうかは確認してから使用するようにしてください。
(SQLインジェクションについては、この機能だけでなく全ての機能において危険性は常にあるため今回に限った話しではない、またcsvのファイル名や拡張子などを適切にバリデーションしていれば、比較的安心してload data infileを使用しても大丈夫かなと考えるとのことから、load data infileを使用しています)
参照サイト
DB::statement(DB::raw("LOAD DATA LOCAL
// ファイルの実際の物理パスを入れる
INFILE '{file_path}'
INTO TABLE $tableName
// 区切り文字を指定する。今回はカンマで区切る。
FIELDS TERMINATED BY ','
// データファイル内の行の終了文字を指定する。今回は各行が改行文字 (\n) で終了していると認識される。
LINES TERMINATED BY '\n'
// ヘッダー情報を無視するために一行目を読み飛ばす
IGNORE 1 LINES
//一時テーブルに保存するためには、すべてのCSVの列数が必要
(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13)
// そのうち必要な番号だけをカラムに読み込むよう指定する
SET email=@6,
// library_idの後ハイフンをつけ、７桁の数字が生成されるようにする 例：1-0000001
library_num=CONCAT('{$libraryId}', '-', LPAD(@10,'7', '0')),
library_status_list_id=@13;
"));
これで一時テーブルを作成し、CSVのデータを一時テーブルにインポートすることができました！
3バリデーション処理
それでは次に一時テーブルを利用してCSV内のデータのバリデーション処理を行っていきましょう。
今回行いたいバリデーション処理は４つです。
- CSVの行数(一万行以内か)の確認
- emailのunique確認
- library_numのunique確認
- library_status_list_idが存在するidかの確認
実際のコードを見ていきましょう。
// 最大行数の値を指定
private static $MAX_COUNT = 10000;
// 一時テーブルの行数をカウントし、変数に格納
$queryResultCount = DB::table($tableName)->count
// 変数が最大値を超える場合は例外をスローする
if ($queryResultCount > self::$MAX_COUNT) {
return new \Exception("CSVファイルの行数が最大値(10000行)を超えています", 422);
}
// 一時テーブルの'email'カラムと`library_users`テーブルの'email'カラムが一致する場合は結合し、その後結合された結果セットの行数をカウントして、変数に格納
$emailDuplicationCount = DB::table($tableName)
->join('library_users', "$tableName.email", '=', 'library_users.email')
->count();
// 結合された結果セットの行数が０より大きい場合は例外をスローする
if ($emailDuplicationCount > 0) {
return new \Exception("このメールアドレスはすでに登録されています", 422);
}
// 一時テーブルの'library_num'カラムと`library_users`テーブルの'library_num'カラムが一致する場合は結合し、その後結合された結果セットの行数をカウントして、変数に格納
$libraryNumDuplicationCount = DB::table($tableName)
->join('library_users', "$tableName.library_num", '=','library_users.library_num')
->count();
// 結合された結果セットの行数が０より大きい場合は例外をスローする
if ($libraryNumDuplicationCount > 0) {
return new \Exception("この図書館番号はすでに登録されています", 422);
}
// 一時テーブルの'library_status_list_id'カラムと`library_cards`テーブルの'library_status_list_id'カラムが一致していれば左外部結合し、その後、library_cards テーブルの library_status_list_id カラムが NULL である行を抽出し、その数をカウントして、変数に格納
$libraryStatusExistenceCount = DB::table($tableName)
->leftJoin('library_cards', 'library_cards.library_status_list_id', '=', "$tableName.library_status_list_id")
->whereNull("library_cards.library_status_list_id")
->count();
if ($libraryStatusExistenceCount > 0) {
return new \Exception("図書館カードのステータスが存在しません", 422);
}
これまでのバリデーション処理だと一万件あったら、DBの情報を最大一万回確認をしないといけませんでしたが、
この方法だと1回ずつで処理が完了し、高速化できます！
ちなみに一時テーブルに入れていないデータは、連想配列にした後valdationRulesを作成して、Validatorによるバリデーションを行なっています。
参照
4保存する
今回のCSV処理では、library_usersとlibrary_cardsを同時に作成する必要があり、
library_cardsでは外部キーとしてlibrary_users_idを持たせる必要があります。
このidを付与する為に、foreachでidを取得した後、付与していく作業を行なっていたのですが、
それだとバリデーション処理をいくら短縮化しても、その付与作業で一万回のループが発生してしまい、
意味がなくなっていました。
そのためそのループ処理をなくす為に、一時テーブルにCSVのデータを保存し、
DBのテーブルに直接インサートしました。
そのため一時テーブルでデータを入れる処理を少し変更します。
バリデーションしたい内容＋図書館カードのデータを一時テーブルに入れます。
$tableName = 'tmp_' . time();
DB::statement(DB::raw("CREATE TEMPORARY TABLE $tableName(
email varchar(255),
library_num varchar(255),
last_confirmed_date,
expired_date date,
library_status_list_id INT)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;"));
DB::statement(DB::raw("LOAD DATA LOCAL
INFILE '{file_path}'
INTO TABLE $tableName
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13)
SET email=@6,
library_num=CONCAT('{$libraryId}', '-', LPAD(@10,'7', '0')),
last_confirmed_date=@11,
expired_date date=@12,
library_status_list_id=@13;
"));
DB::statement(
"INSERT INTO library_cards
(library_num,
last_confirmed_date,
expired_date,
library_status_list_id;
library_users_id,
created_at,
updated_at)
// テーブルを結合し、特定の列を選択する
SELECT
{$tableName}.library_num,
{$tableName}.last_confirmed_date,
{$tableName}.expired_date,
{$tableName}.library_status_list_id,
// 'library_users'テーブルから'id'列を選択し、library_user_idという別名をつける
library_users.id as library_user_id,
now() as created_at,
now() as updated_at
FROM {$tableName}
INNER JOIN patients ON {$tableName}.email = library_users.email");
一時テーブルからデータを直接insertするだけで、一々library_userのidを取得して付与する必要がなくなります！
これで処理が大幅に早くなりました。
今回の処理は一時テーブルを扱う用のRepository層に記載し、Service層からメソッドを呼び出すようにしています。
ただ会社のコード規約などで書き方が決まっていると思われますので、
あくまで参考程度にしてください。
library_usersテーブルでは都道府県を文字列→数字にする処理をLaravelのプログラムを使用していました。
SQLではLaravelのプログラムを呼び出すことができずエラーになってしまう為、
library_cardsテーブルのみ一時テーブルを使用してinsert処理を行なっています。
どちらも一時テーブルに入れて、insert処理ができればもっと処理が早く行えたのですか……。
今回の場合は仕方ないですね。
5終わりに
入社して3,4ヶ月目の人がやるタスクではなかったらしく、
本当に実装に時間がかかってしまったのですが、
このタスクを行うことでとても勉強になりましたので、実装させて貰えて本当に良かったです。
長期間教えて頂いたつよつよの先輩に頭が上がりません、本当にありがとうございました。
今後量が多いデータを扱う際には、この方法を思い出して実装していきたいと思います。
ここまでお読み下さりありがとうございました。
誰かの知見になれば幸いです。
6参考文献
この記事は以下の記事を参考にして執筆させて頂きました。
第107回 CREATE TEMPORARY TABLEによる一時テーブルの利用
Laravel 8.x データベース：準備
【MySQL】csvファイルをDBにインポートする方法
13.2.7 LOAD DATA ステートメント
MySQL 8.0 の LOAD DATA で The used command is not allowed with this MySQL version エラー
【laravel】Validatorによるバリデーション