LoginSignup
50
71

More than 5 years have passed since last update.

@saoyagi2

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

Last updated at Posted at 2016-09-17

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を分割してから読み込むことで対応してたりします。誰か原因知ってたら教えてー。
50
71
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
50
71