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データベース大量データ投入](http://h50146.www5.hpe.com/products/software/oe/linux/summary/mwtech/files/mysql-loaddata-v02.pdf)とかで検証されてます。
* メモリ設定を豪勢にするとかログ出力を極力切るとか。詳しくは奥野さんが[説明](http://nippondanji.blogspot.jp/2010/03/innodb.html)してくださっているので、そちらを読みましょう。
* LOAD DATA INFILEでCSVが巨大になるとMySQL接続が切れる場合がある。というか実体験として切れること多数。経験的に100万件を超えると切れる確率が上がる。もちろんスキーマにもよるだろうけれど。原因が分からないので、splitでCSVを分割してから読み込むことで対応してたりします。誰か原因知ってたら教えてー。