LOAD DATA INFILE について
CSVファイルのデータをMySQLデータベースにインポートする方法です。
通常のインポートより高速にインポートが可能です。
使用してみようと思った経緯
GCPのCloudSQL（MySQL）へ、全テーブルのダンプ（50GB）をインポートしようとしたところ、時間がかかりすぎてネットワークが切れて、途中でエラーになってしまった。
何度か試してもうまくいかなったので、テーブル毎にダンプを用意し、インポート（バルクインサート方式）を試みたが、数GBを超えるテーブルだと、そちらでもネットワークが切れてエラーになるか、あるいは5時間以上の時間がかかってしまった。
別の手段で何とか爆速でインポートできないかを調べてみました。
使用したツール、環境など
Mysql 5.6
test_table.csv （5.16GBのインポート用のCSV）
mycli （MySQLクライアント csvのインポートで活用）
LOAD DATA INFILEの実行
ログイン
ログインします。
注意点として、--local-infileを有効にしないと、LOAD DATA LOCAL実行時にエラーとなります。
詳しくは下記のURLを参照ですが、セキュリティがらみの問題で、デフォルトではできないようになっているようです。
LOAD DATA LOCAL のセキュリティーの問題
mycli -u test_user -D testdb --local-infile=1
| オプション | 意味 |
|---|---|
| -h | ホスト名（省略時はlocalhost） |
| -P | ポート番号（省略時は3306） |
| -u | ユーザー名 |
| -D | データベース名 |
| --local-infile | オン（1）になっていないと、LOAD DATA LOCAL INFILEが使用できない |
実施
LOAD DATA LOCAL INFILEを実行します。
構文の詳細は公式を参考にするといいと思います。
LOAD DATA LOCAL INFILE '/Users/xxxxx/export_csv/test_table.csv' IGNORE INTO TABLE TEST_TABLE FIELDS TERMINATED BY ',' ENCLOSED BY '"'
Query OK, 17323928 rows affected
Time: 378.725s
select count(*) from `TEST_TABLE`;
+----------+
| count(*) |
+----------+
| 17323928 |
+----------+
1 row in set
Time: 2.234s
結果、378.725sで、ものすごく爆速で終わりました！
つまづいたこと、気になったこと
- サーバー・クライアント両方の
local_infileオプションを1に設定する必要があること- 今回試したバージョンだと、サーバー側の設定はデフォルトで１だったので、クライアントのみ、接続するときに変更した。
- MySQLのバージョンが新しいと、サーバー側のデフォルトも変えないといけないらしい。
- バルクインサートで1回あたりの件数を変更したときのパフォーマンスの違いが気になる
- 10件毎の場合は5時間超
- 1000件毎の場合はレスポンスが返ってこなかった
他の手法との比較
実際に他の手法と比較したときに、どの程度結果が変わるかも比較してみました。
１件毎、100件毎、10000件毎、テスト対象テーブルの全件（492,696件）のインサートが記述してあるSQLファイルと、LOAD DATALOCAL IN FILE用のcsvのファイルを用意し、それぞれ動かしてみました。
（記事の上記で紹介したテーブルだと、件数が多すぎてLOAD DATALOCAL IN FILE以外だと、レスポンスが返ってこないことが多かったため、比較の時は件数が少ないテーブルを使用しています）
INSERT INTO TEST_TABLE2 (columnA,columnB,columnC,columnD,columnE) VALUES
(1902015,1,30038,30038,0,0),(1902016,1,30039,30038,30039),(1902017,1,30039,30038,30039),(1902018,1,30040,30038,30040,0), ・・・
time (mycli -u test_user -ptest testdb < TEST_TABLE2_XXX.sql)
( mycli -u test_user -ptest testdb < TEST_TABLE2_XXX.sql; ) 2045.77s user 5.47s system 99% cpu 34:30.38 total
結果（492,696件の登録にかかった時間）
| オプション | 意味 |
|---|---|
| １レコードづつインサート | 1時間35分34.47秒 |
| バルクインサート（100件毎） | 34分30.38秒 |
| バルクインサート（10000件毎） | 49分20.03秒 |
| バルクインサート（全件） | - |
| LOAD DATALOCAL IN FILE | 4.187秒 |
バルクインサート（全件）は、６時間ほど待ってもレスポンスが返ってきませんでしたので、中断しました。
驚くほど、LOAD DATALOCAL IN FILEが早いことがわかりました。
まとめ
ものすごく爆速で終わったので非常に良かったです。
ただ、どうしてもテーブル毎にやらないといけないので、それが面倒だったりも。。。て気がします。
MySQLでは、特定のテーブルを含まないでダンプを取れるので、肥大しているテーブル以外をまとめてダンプを取り、肥大しているテーブルはLOAD DATA LOCAL INFILEを使うのが、比較的効率よくインポートできるのかもしれないです。（ネットワークの影響が及ばないよう、同じ場所にダンプを置けば、そもそも問題ないかもしれないですが）
Discussion