Edited at

[MySQL]巨大なCSVをインポートする

More than 1 year has passed since last update.

MySQLで巨大なCSVをインポートする方法をいくつか試してみました。

MySQLのバージョンは5.6.30でテストしています。

mysql> select version();

+-----------+
| version() |
+-----------+
| 5.6.30 |
+-----------+
1 row in set (0.00 sec)


元データ

インポートするCSVはこういう時の定番である郵便番号データです。公開してくださっている日本郵便株式会社さん、ありがとうございます。

以下の手順でダウンロードして展開します。

$ wget http://www.post.japanpost.jp/zipcode/dl/kogaki/zip/ken_all.zip

--2016-09-17 14:40:05-- http://www.post.japanpost.jp/zipcode/dl/kogaki/zip/ken_all.zip
www.post.japanpost.jp (www.post.japanpost.jp) をDNSに問いあわせています... 43.253.37.203
www.post.japanpost.jp (www.post.japanpost.jp)|43.253.37.203|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 1684704 (1.6M) [application/zip]
`ken_all.zip' に保存中

ken_all.zip 100%[===================>] 1.61M 3.84MB/s in 0.4s

2016-09-17 14:40:06 (3.84 MB/s) - `ken_all.zip' へ保存完了 [1684704/1684704]

$ unzip ken_all.zip
Archive: ken_all.zip
extracting: KEN_ALL.CSV
$ wc -l KEN_ALL.CSV
123948 KEN_ALL.CSV

12万行ですから巨大とまでは言えないかもしれませんが、まあ傾向くらいはつかめるかなと。


インポート用テーブル

インポートするテーブルは以下のように定義しました。

mysql> CREATE TABLE zipcode (

-> jiscode CHAR(5),
-> zipcode_old CHAR(5),
-> zipcode CHAR(7),
-> pref_kana VARCHAR(255),
-> city_kana VARCHAR(255),
-> street_kana VARCHAR(255),
-> pref VARCHAR(255),
-> city VARCHAR(255),
-> street VARCHAR(255),
-> flag1 TINYINT,
-> flag2 TINYINT,
-> flag3 TINYINT,
-> flag4 TINYINT,
-> flag5 TINYINT,
-> flag6 TINYINT
-> ) DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

以下のテストごとにテーブルは作り直しています。


1行ごとにINSERT

まずは1行ごとにINSERTしてみます。

以下のようなインチキスクリプトでINSERT用のSQLを作ります。

$ cat conv1.pl

use strict;
use warnings;

binmode STDIN, ":encoding(sjis)";
binmode STDOUT, ":encoding(utf8)";

while(<>){
s/[\r\n"]//g;
my @data = split(/,/);
print "INSERT INTO zipcode ( `jiscode`, `zipcode_old`, `zipcode`, `pref_kana`, `city_kana`, `street_kana`, `pref`, `city`, `street`, `flag1`, `flag2`, `flag3`, `flag4`, `flag5`, `flag6` ) VALUES ( '${data[0]}', '${data[1]}', '${data[2]}', '${data[3]}', '${data[4]}', '${data[5]}', '${data[6]}', '${data[7]}', '${data[8]}', '${data[9]}', '${data[10]}', '${data[11]}', '${data[12]}', '${data[13]}', '${data[14]}' );\n";
}

$ perl conv1.pl < KEN_ALL.CSV > import1.sql
$ head -3 import1.sql
INSERT INTO zipcode ( `jiscode`, `zipcode_old`, `zipcode`, `pref_kana`, `city_kana`, `street_kana`, `pref`, `city`, `street`, `flag1`, `flag2`, `flag3`, `flag4`, `flag5`, `flag6` ) VALUES ( '01101', '060 ', '0600000', 'ホッカイドウ', 'サッポロシチュウオウク', 'イカニケイサイガナイバアイ', '北海道', '札幌市中央区', '以下に掲載がない場合', '0', '0', '0', '0', '0', '0' );
INSERT INTO zipcode ( `jiscode`, `zipcode_old`, `zipcode`, `pref_kana`, `city_kana`, `street_kana`, `pref`, `city`, `street`, `flag1`, `flag2`, `flag3`, `flag4`, `flag5`, `flag6` ) VALUES ( '01101', '064 ', '0640941', 'ホッカイドウ', 'サッポロシチュウオウク', 'アサヒガオカ', '北海道', '札幌市中央区', '旭ケ丘', '0', '0', '1', '0', '0', '0' );
INSERT INTO zipcode ( `jiscode`, `zipcode_old`, `zipcode`, `pref_kana`, `city_kana`, `street_kana`, `pref`, `city`, `street`, `flag1`, `flag2`, `flag3`, `flag4`, `flag5`, `flag6` ) VALUES ( '01101', '060 ', '0600041', 'ホッカイドウ', 'サッポロシチュウオウク', 'オオドオリヒガシ', '北海道', '札幌市中央区', '大通東', '0', '0', '1', '0', '0', '0' );

MySQLで実行します。

mysql> source import1.sql

(途中盛大に省略)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

12万行分も上記のように出力されてしまいますので、仕方なくストップウォッチで計測しました。コンソール出力のオーバーヘッドも含みますので不正確ではありますが、2分32秒53という結果でした。

mysql> select count(*) from zipcode;

+----------+
| count(*) |
+----------+
| 123948 |
+----------+
1 row in set (0.09 sec)

取り込み自体はちゃんと行われています。

(2016/12/24追記)

流石にコンソールに出しっぱなしはひどいかなと思って、リダイレクトで流し込んでみました。実施時期が違うので、少し環境とデータが変わってますが、大きな違いはないと思います。

> /usr/bin/time mysql test < import1.sql

166.14 real 4.60 user 2.55 sys

意外とタイムに変わりはなかったです。あらら。コンソールのスクロールって大きな負荷だと思ってたんですが、今どきはそれほどでもないってことなんでしょうか。

mysql> select version();

+-----------+
| version() |
+-----------+
| 5.6.34 |
+-----------+
1 row in set (0.00 sec)

mysql> select count(*) from zipcode;
+----------+
| count(*) |
+----------+
| 124056 |
+----------+
1 row in set (0.24 sec)

(2016/12/24追記終わり)


Multiple INSERT

INSERT文では複数のVALUESをくっつけるMultiple INSERTという方法も使えます。ということでインチキスクリプトその2です。

$ cat conv2.pl

use strict;
use warnings;

binmode STDIN, ":encoding(sjis)";
binmode STDOUT, ":encoding(utf8)";

print "INSERT INTO zipcode ( `jiscode`, `zipcode_old`, `zipcode`, `pref_kana`, `city_kana`, `street_kana`, `pref`, `city`, `street`, `flag1`, `flag2`, `flag3`, `flag4`, `flag5`, `flag6` ) VALUES\n";

my $first = 1;
while(<>){
s/[\r\n"]//g;
my @data = split(/,/);
print "," if( !$first );
print "( '${data[0]}', '${data[1]}', '${data[2]}', '${data[3]}', '${data[4]}', '${data[5]}', '${data[6]}', '${data[7]}', '${data[8]}', '${data[9]}', '${data[10]}', '${data[11]}', '${data[12]}', '${data[13]}', '${data[14]}' )\n";
$first = 0;
}
print ";\n";
$ perl conv2.pl < KEN_ALL.CSV > import2.sql
$ head -3 import2.sql
INSERT INTO zipcode ( `jiscode`, `zipcode_old`, `zipcode`, `pref_kana`, `city_kana`, `street_kana`, `pref`, `city`, `street`, `flag1`, `flag2`, `flag3`, `flag4`, `flag5`, `flag6` ) VALUES
( '01101', '060 ', '0600000', 'ホッカイドウ', 'サッポロシチュウオウク', 'イカニケイサイガナイバアイ', '北海道', '札幌市中央区', '以下に掲載がない場合', '0', '0', '0', '0', '0', '0' )
,( '01101', '064 ', '0640941', 'ホッカイドウ', 'サッポロシチュウオウク', 'アサヒガオカ', '北海道', '札幌市中央区', '旭ケ丘', '0', '0', '1', '0', '0', '0' )

MySQLで実行します。

mysql> source import2.sql

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 2
Current database: test

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 3
Current database: test

ERROR 2006 (HY000): MySQL server has gone away

SQL文が大きすぎるとこのようなエラーになるそうです。ということでmy.cnfのmax_allowed_packetを32MBほどに設定して再挑戦。

mysql> source import2.sql

Query OK, 123948 rows affected (4.32 sec)
Records: 123948 Duplicates: 0 Warnings: 0

無事に取り込めました。4秒32。当然ですが、めっちゃ高速化してます。実用的にはVALUESを12万まとめてなんて無茶せずに1000とかくらいで区切った方がいいようには思います。


LOAD DATA INFILE

CSV(というか定型テキスト)を直接読み込む構文というのもあります。それがLOAD DATA INFILEです。

mysql> set character_set_database=sjis;

Query OK, 0 rows affected (0.01 sec)

mysql> LOAD DATA LOCAL INFILE './KEN_ALL.CSV' INTO TABLE zipcode FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
ERROR 1148 (42000): The used command is not allowed with this MySQL version

が、怒られてしまいました。詳しくは6.1.6 LOAD DATA LOCAL のセキュリティーの問題を見ていただくとして、要するにセキュリティのために禁止されている操作なわけですね。回避方法としては、mysqlクライアントを以下のように起動します。

$ mysql --local-infile=1

mysql> set character_set_database=sjis;

Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA LOCAL INFILE './KEN_ALL.CSV' INTO TABLE zipcode FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
Query OK, 123948 rows affected (1.97 sec)
Records: 123948 Deleted: 0 Skipped: 0 Warnings: 0

無事に取り込めました。1秒97とこれまでで最速です。


CSVエンジン利用

漢(オトコ)のコンピュータ道: たった3秒でInnoDBのデータローディングが快適になるライフハックで紹介されているのが、CSVストレージエンジンでテーブルを作ってファイルを差し替え、その後エンジンタイプを変換するという方法です。エンジンタイプを複数持てるMySQLならではの方法で、正直この記事を見た時は頭を殴られたような衝撃を受けました。そんな方法があったとは!

ということでやってみるわけです。まずはテーブル作成。

mysql> CREATE TABLE zipcode (

-> jiscode CHAR(5),
-> zipcode_old CHAR(5),
-> zipcode CHAR(7),
-> pref_kana VARCHAR(255),
-> city_kana VARCHAR(255),
-> street_kana VARCHAR(255),
-> pref VARCHAR(255),
-> city VARCHAR(255),
-> street VARCHAR(255),
-> flag1 TINYINT,
-> flag2 TINYINT,
-> flag3 TINYINT,
-> flag4 TINYINT,
-> flag5 TINYINT,
-> flag6 TINYINT
-> ) ENGINE CSV, DEFAULT CHARSET=sjis;
ERROR 1178 (42000): The storage engine for the table doesn't support nullable columns

怒られてしまいました。NULLカラムは使えないっぽいのでNOT NULLを付けてみました。

mysql> CREATE TABLE zipcode (

-> jiscode CHAR(5) NOT NULL,
-> zipcode_old CHAR(5) NOT NULL,
-> zipcode CHAR(7) NOT NULL,
-> pref_kana VARCHAR(255) NOT NULL,
-> city_kana VARCHAR(255) NOT NULL,
-> street_kana VARCHAR(255) NOT NULL,
-> pref VARCHAR(255) NOT NULL,
-> city VARCHAR(255) NOT NULL,
-> street VARCHAR(255) NOT NULL,
-> flag1 TINYINT NOT NULL,
-> flag2 TINYINT NOT NULL,
-> flag3 TINYINT NOT NULL,
-> flag4 TINYINT NOT NULL,
-> flag5 TINYINT NOT NULL,
-> flag6 TINYINT NOT NULL
-> ) ENGINE CSV, DEFAULT CHARSET=sjis;
Query OK, 0 rows affected (0.01 sec)

今度は無事に作れましたね。

この状態で一旦MySQLを落とします。落とさなくても大丈夫なような気もするのですが念のため。

$ sudo /usr/local/etc/rc.d/mysql-server stop

パスワード:
Stopping mysql.
Waiting for PIDS: 72133.

データディレクトリを見に行ったら、ありましたありました。CSVファイルが出来てます。

$ sudo ls -l /var/db/mysql/test

total 32
-rw-rw---- 1 mysql mysql 35 9月 17 16:05 zipcode.CSM
-rw-rw---- 1 mysql mysql 0 9月 17 16:05 zipcode.CSV
-rw-rw---- 1 mysql mysql 9056 9月 17 16:05 zipcode.frm

CSVファイルを差し替えます。

$ sudo cp KEN_ALL.CSV /var/db/mysql/test/zipcode.CSV

$ sudo ls -l /var/db/mysql/test
total 24096
-rw-rw---- 1 mysql mysql 35 9月 17 16:05 zipcode.CSM
-rw-rw---- 1 mysql mysql 12260865 9月 17 16:07 zipcode.CSV
-rw-rw---- 1 mysql mysql 9056 9月 17 16:05 zipcode.frm

MySQLを再起動。

$ sudo /usr/local/etc/rc.d/mysql-server start

Starting mysql.

さーてデータは入ってるかなーと思ったら、、

mysql> select count(*) from zipcode;

ERROR 1194 (HY000): Table 'zipcode' is marked as crashed and should be repaired

なんでやねん。とりあえずリペアしてみる。

mysql> repair table zipcode;

+--------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+--------+----------+----------+
| test.zipcode | repair | status | OK |
+--------------+--------+----------+----------+
1 row in set (0.03 sec)

改めてカウント取ってみると、

mysql> select count(*) from zipcode;

+----------+
| count(*) |
+----------+
| 4326 |
+----------+
1 row in set (0.02 sec)

全然件数がちゃいますな。うーん、なんででしょ。わからないので、今回はとりあえずここまで。


ポエム

ポエムというか、ちゃんと調べられてないこととか思い付きとかを適当に書き連ね。


  • インデックスとか制約とかを一時的に落とすと速くなる。と思う。多分。

  • CSVを事前にプライマリキーでソートしておくとLOAD DATAが速くなるそうです。MySQL Server 5.0 InnoDBデータベース大量データ投入とかで検証されてます。

  • メモリ設定を豪勢にするとかログ出力を極力切るとか。詳しくは奥野さんが説明してくださっているので、そちらを読みましょう。

  • LOAD DATA INFILEでCSVが巨大になるとMySQL接続が切れる場合がある。というか実体験として切れること多数。経験的に100万件を超えると切れる確率が上がる。もちろんスキーマにもよるだろうけれど。原因が分からないので、splitでCSVを分割してから読み込むことで対応してたりします。誰か原因知ってたら教えてー。