💡

LOAD DATA INFILE 構文 のLOCALを試してみた

y_murofushi2022/11/01に公開

LOAD DATA INFILE について

CSVファイルのデータをMySQLデータベースにインポートする方法です。
通常のインポートより高速にインポートが可能です。

LOAD DATA INFILEについて

使用してみようと思った経緯

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 のセキュリティーの問題

sh
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を実行します。

構文の詳細は公式を参考にするといいと思います。
https://dev.mysql.com/doc/refman/5.6/ja/load-data.html

MySQL
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に設定する必要があること
    • 今回試したバージョンだと、サーバー側の設定はデフォルトで1だったので、クライアントのみ、接続するときに変更した。
  • MySQLのバージョンが新しいと、サーバー側のデフォルトも変えないといけないらしい。
  • バルクインサートで1回あたりの件数を変更したときのパフォーマンスの違いが気になる
      - 10件毎の場合は5時間超
      - 1000件毎の場合はレスポンスが返ってこなかった

他の手法との比較

実際に他の手法と比較したときに、どの程度結果が変わるかも比較してみました。

1件毎、100件毎、10000件毎、テスト対象テーブルの全件(492,696件)のインサートが記述してあるSQLファイルと、LOAD DATALOCAL IN FILE用のcsvのファイルを用意し、それぞれ動かしてみました。
(記事の上記で紹介したテーブルだと、件数が多すぎてLOAD DATALOCAL IN FILE以外だと、レスポンスが返ってこないことが多かったため、比較の時は件数が少ないテーブルを使用しています)

csv
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), ・・・
sh
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レコードづつインサート 1時間35分34.47秒
バルクインサート(100件毎) 34分30.38秒
バルクインサート(10000件毎) 49分20.03秒
バルクインサート(全件) -
LOAD DATALOCAL IN FILE 4.187秒

バルクインサート(全件)は、6時間ほど待ってもレスポンスが返ってきませんでしたので、中断しました。
驚くほど、LOAD DATALOCAL IN FILEが早いことがわかりました。

まとめ

ものすごく爆速で終わったので非常に良かったです。
ただ、どうしてもテーブル毎にやらないといけないので、それが面倒だったりも。。。て気がします。
MySQLでは、特定のテーブルを含まないでダンプを取れるので、肥大しているテーブル以外をまとめてダンプを取り、肥大しているテーブルはLOAD DATA LOCAL INFILEを使うのが、比較的効率よくインポートできるのかもしれないです。(ネットワークの影響が及ばないよう、同じ場所にダンプを置けば、そもそも問題ないかもしれないですが)

レスキューナウテックブログ

日本で唯一の危機管理情報を専門に取り扱う防災Techのスタートアップ、(株)レスキューナウです。当社で活躍するエンジニアの技術ブログを中心に公開していきます。

Discussion

ログインするとコメントできます