質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
CSV

CSV(Comma-Separated Values)はコンマで区切られた明白なテキスト値のリストです。もしくは、そのフォーマットでひとつ以上のリストを含むファイルを指します。

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

データベース

データベースとは、データの集合体を指します。また、そのデータの集合体の共用を可能にするシステムの意味を含めます

Q&A

3回答

8446閲覧

巨大なCSVをMySQLで読み込みたい

退会済みユーザー

退会済みユーザー

総合スコア0

CSV

CSV(Comma-Separated Values)はコンマで区切られた明白なテキスト値のリストです。もしくは、そのフォーマットでひとつ以上のリストを含むファイルを指します。

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

データベース

データベースとは、データの集合体を指します。また、そのデータの集合体の共用を可能にするシステムの意味を含めます

1グッド

3クリップ

投稿2017/12/23 08:24

編集2022/01/12 10:55

###前提・実現したいこと
1億行のCSVファイルを扱うために、MySQLを用いてデータベースで処理しようとしています。
1億行という大規模なファイルであるために、そのまま読み込もうとしても非常に時間がかかってしまい、良い方法を見つけるのに苦労しています。

###発生している問題・エラーメッセージ

読み込みスピードが遅く、現実的な時間では終わらない。

###試したこと
1億行のCSVファイル(hogehoge.csvとする)を、読み込んでテーブルに入れようとしています。
はじめは、テーブルを作成した後に以下のコードを試みました。

SQL

1LOAD DATA LOCAL INFILE "hogehoge.csv" 2INTO TABLE table FIELDS TERMINATED BY X'09'

しかし、丸一日経過すれど処理が進まなかったため、1億行のファイルを、10000行×10000ファイルに分割して順番に読み込むことを試みました。
hogehoge.csvを、splitを用いてシェル上で分解し、table.00000.csv ~ table.09999.csvという10000個のファイルに分割した後、以下のコードを実行しました。

mysqlimport -L -u root -p database table.*

しかし、これでも1つのcsvを読み込むのに45秒程かかっているため、仮にそのペースが最後まで続いても125時間はかかってしまいます。
このような巨大なCSVを高速に読み込む良い方法はないでしょうか。

LOAD DATA INFILEではなく、INSERTやMultiple INSERTと呼ばれる手法もあるようですが、1億行を処理するにあたってどちらが優れているのか判断しかねています。
Multiple INSERTを用いる場合は、MySQLとは別に、複数のレコードを同時に挿入するべく、SQLのスクリプトを作成する作業が必要だと考えているのですが、その認識は正しいでしょうか。

###参考にしたサイトなど
http://naoberry.com/tech/mysqldata/
https://qiita.com/saoyagi2/items/f37ae0d32edd06ec8c4c
http://nekopuni.holy.jp/2014/03/mysqlpython8000%E4%B8%87%E3%83%AC%E3%82%B3%E3%83%BC%E3%83%89%E3%82%92insert%E3%81%99%E3%82%8B/

umyu👍を押しています

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

hihijiji

2017/12/23 09:15

分割したファイルを、LOAD DATA INFILE で取り込んだ場合は如何でしょう?カラム数にもよりますが1万件10秒以上かかるなら何がボトルネックになっているか調査してみるのが良いかもしれません。
退会済みユーザー

退会済みユーザー

2017/12/24 20:07

ご回答ありがとうございます。カラム数は25列です。mysqlimport -L -u root -p database table.* は、LOAD DATA INFILEで分割したファイルを取り込んでいるのと同じことをしていると認識していたのですが、これは誤っているのでしょうか。マシンのスペックが十分ではない可能性も感じております。
hihijiji

2017/12/25 01:10

ほぼ同じは全く一緒とは限りません。情報は多いに越したことはありません。マシンのスペックを上げることは大概有効ですが、ネックになっている所が判らないと無駄な投資になる可能性が上がります。
guest

回答3

0

MySQLのCSV Engineを利用するのはいかがでしょうか?
その他にもCSVをインポートするときのみ、下記のようなパフォーマンスチューニングを行ってからインポートすると期待できます。もちろん終わったら元に戻しておかなければなりません。

下記の他にも、ファイルI/Oを軽減するために、ファイルシステムをXFSパーティションにしたり、アトミック書き込みを利用したりする方法があります。

conf

1# 2# CSV大規模用 3# 4[mysqld] 5#↓↓コメントアウト↓↓ 6#log-bin = mysql-bin 7 8innodb_log_file_size = 512M 9innodb_log_buffer_size = 32M 10innodb_autoextend_increment = 64 11innodb_flush_log_at_trx_commit = 2 12innodb_data_file_path = ibdata1:10G:autoextend:max:100G 13skip_innodb_doublewrite

上記設定変更と、MySQLの再起動が完了したら、いちおう設定を確認しておきます。

sql

1SELECT 2 @@GLOBAL.log_bin, 3 @@GLOBAL.innodb_log_file_size, 4 @@GLOBAL.innodb_log_buffer_size, 5 @@GLOBAL.innodb_autoextend_increment, 6 @@GLOBAL.innodb_flush_log_at_trx_commit, 7 @@GLOBAL.innodb_data_file_path, 8 @@GLOBAL.innodb_doublewrite \G

そして、CSV Engineを使ってテーブルを作成します。プライマリーキーや、インデックスなどはALTER TABLEで後から作成します。

sql

1CREATE TABLE テーブル名 (テーブル定義) ENGINE CSV;

上記SQLコマンドを実行すると、/var/lib/mysql/データーベース名/テーブル名.CSVというファイルが出来上がるので、これをインポートしたいCSVで上書きします。

bash

1sudo cp -f CSVファイル.csv /var/lib/mysql/データーベース名/テーブル名.CSV

完了したら、下記ALTER TABLEコマンドでプライマリーキーとインデックスなどを作成します。

sql

1ALTER TABLE テーブル名 ADD PRIMARY KEY(カラム), ADD INDEX(カラム),,, ENGINE INNODB;

投稿2017/12/23 10:54

Tomak

総合スコア1652

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

こんな記事があります。
3. 適切なサイズでCOMMITする。

Tomakさんの紹介されている方法と同じで、なぜ高速なのかというと
「ALTER TABLE時には1万行ごとにCOMMITしてくれるので、Undoログを大量に作成せず、I/O性能が低下しない」ということです。

既にファイルを分割して試されているので、結果COMMIT分割していることになり、ファイルを分割している時間が大きいなら効果は得られますが、そうでないならあまり変わらない結果になると思われます。
※先ずは、プライマリーを含めインデックスをDROPしてからのLOAD DATAによる処理の時間計測を確認された方が良いと思いますけれど。

で、試してはいないのですが、現状の処理実行時にCPU使用率が80%以下であれば、ファイルを分割すると共にLOAD DATAによる処理を並列実行させてはどうでしょうか。
LOAD DATAもUndoログを消費するということはトランザクション処理を行っているということなので、
並列実行も可能じゃないかと思います。

幾つに分割するかはCPU使用率が90%位になるまでで、分割すれば良いかと思います。
※試算の125時間は、8分割出来れば15時間程度には短縮できることになります。

因みに、1万行で45秒ということなので、ハードスペックはそんなに良くないのでしょうね。

ハードは処理時間に大いに影響しますので、変更可能なら検討されてみてはどうですか。
1番は高速なストレージ、2番はCPU、3番目はメモリ(実行時に空きが少ないならですが)
というところでしょうか。

変更できないということであれば、リスクは高くなりますがOC(オーバークロック)してみるとか。

投稿2017/12/23 17:29

編集2017/12/23 18:04
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

具体的なデータがどのようなものかわかりませんが
データ投入は整合性チェックなどを考えればそれなりに時間がかかるものです
適当な単位(例えば1億行のデータであれば100~200万行単位くらい?)で
グルーピングができるならパーティショニングすれば
全体テーブルの整合性チェックから開放されるかもしれません。
いずれにしても1億行となるとRDBで普通にデータ管理するには負荷が
かかりすぎて現実的ではないかもしれません。
ビッグデータに強いと言われるNoSQLなど別のアプローチを検討したほうが
よいかもしれません

投稿2017/12/23 09:26

yambejp

総合スコア114583

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

まだベストアンサーが選ばれていません

会員登録して回答してみよう

アカウントをお持ちの方は

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問