膨大なサイズのCSVファイルから処理短でMySQLへインポート

2019-09-23

CSVファイルからDB(MySQL)にインポートする機能はよくあるし、情報もゴロゴロとある。前回の記事では大量のデータをCSVファイルとして素早く書き込む方法を書いたが、今回はその逆と言っても良い。

ただ、CSV→DBは色々な制約を受けるので最も早く確実なただ一つの答えがあるわけではなさそう。今回紹介するのはサーバーのupload_max_filesizeや利用可能メモリについては度外視している。

CSVインポートの手段

先に方法を列挙すると以下のようになる。

  1. 1レコードずつインサート（激遅い）
  2. バルクインサート（速い）
  3. バッチでインサート（ちょっと遅い）
  4. LOAD DATA LOCAL INFILEインサート（最速）

全てのやり方ができる環境かどうかも考慮に入れる必要がある。

また今回はフレームワークで使用されることを前提としてPHP(Laravel)で検証しているが、Insertの処理速度という意味ではDBの問題なのでどのフレームワークであろうが、どの言語であろうが同じである。

遅すぎ。1レコードごとにINSERT

この方法は当然一番遅い方法。CSVのデータを取得して一行ごとにINSERT文を発行しているのでとてつもなく時間がかかる。

$file = new \SplFileObject($path);
$file->setFlags(\SplFileObject::READ_CSV);

foreach ($file as $row => $line) {
	Hoge::create([
           'huga'=> $line[0],
            // 省略
        ]);
}

laravelのModel::createでやっている分、普通のInsertより処理が思いのだろうが、そもそもこのような1行ずつInsertは問題がある。

今回検証したデータ（30万行のCSVファイル）をこの方法でimportしようとしたが5分以上かかってリクエストタイムアウトになった。

BULK INSERT

BULK INSERTは１度のクエリで複数のデータをInsertする方法だ。

INSERT INTO hoge values('test1'), ('test2'), ('test3');

これは当然速い。また、１クエリの処理なのでトランザクションを貼る必要もない。

$csvData = [
['hoge','foo',...]
...
]
DB::table('hoge').insert($csvData);

これで処理すればいいと思われたのだが、Bulkにも問題は存在する。

MySQLの場合、「max_allowed_packet」というクエリのデータサイズの上限が存在する。なんでも16MBが最大値なのだとか。

https://qiita.com/_am_/items/91824da643256d46b847

当然、私が検証した30万行のCSVファイルを１クエリでINSERTしようとした場合、SQL文のデータサイズは16MBなど軽く超えてしまう。

膨大なデータをINSERTする場合で、そのデータ量が不定の場合は次のバッチ処理が一般的で王道だろう。

バッチINSERT処理

1件ずつINSERTは遅すぎるし、1度のクエリでINSERTはデータサイズが問題になる。一般的にはバッチで処理するだろう。（2000件に1回INSERTなど）

$file = new \SplFileObject($path);
$file->setFlags(\SplFileObject::READ_CSV);
$cnt = 0;
$insertDate = [];
foreach ($file as $row => $line) {
	$cnt++;
	$insertDate[] = [
       'huga'=> $line[0],
        // 省略
    ];
	if ($cnt >= 2000) {
		DB::table('hoge').insert($insertDate);
		$cnt = 0;
		$insertDate = [];
	}
}

これならどれだけデカイサイズのCSVでもほとんどの場合正常にINSERTできるし、現実的な処理時間を達成できるので、多くのケースではバッチ処理で行われる。

ただし、今回の検証データ（30万行データ）では全てのクエリの処理完了まで2分程度かかった。

LOAD DATA LOCAL INFILEでCSVファイルごと実行

バッチ処理の実行時間では納得できない人は、CSVファイルからINSERT実行を検討してみると良い。

これはバルクインサートよりも速く、max_allowed_packetの制限もない、最速のINSERT方法と思われる。

$path = "test.csv";
$sql = <<< EOM
LOAD DATA LOCAL INFILE '$path' REPLACE INTO TABLE hoge 
CHARACTER SET utf8 FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
IGNORE 1 LINES;
EOM;
DB::insert($sql, []);

UTF-8でのファイルで、csvの1行目は無視している。

これだとなんと2秒台で30万行のデータをINSERTできた。とてつもなく早い！ファイルパスを指定していることからファイルデータそのものを送ってバルクINSERTのごとく処理している。

当然100GBのCSVファイルをこの方法でINSERTできるかと言ったら不可能だろう。DBサーバーのメモリ量を超えると落ちる。

なので巨大なファイルをインポートする必要があり、そのサイズが不定の場合はやはりバッチ処理でInsertするのが無難でしょう。それでも処理速度を追い求めたい場合はLOAD DATA LOCAL INFILEでInsertするのが良い。

MySQL, phpcsv, mysql, php