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

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

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

```
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.30    |
+-----------+
1 row in set (0.00 sec)
```

# 元データ

インポートするCSVはこういう時の定番である[郵便番号データ](http://www.post.japanpost.jp/zipcode/download.html)です。公開してくださっている日本郵便株式会社さん、ありがとうございます。

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

```
$ 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 のセキュリティーの問題](http://dev.mysql.com/doc/refman/5.6/ja/load-data-local.html)を見ていただくとして、要するにセキュリティのために禁止されている操作なわけですね。回避方法としては、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のデータローディングが快適になるライフハック](http://nippondanji.blogspot.jp/2010/03/innodb.html)で紹介されているのが、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を分割してから読み込むことで対応してたりします。誰か原因知ってたら教えてー。