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

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

ただいまの
回答率

91.25%

  • MySQL

    4572questions

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

  • SQL

    1757questions

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

  • データベース

    564questions

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

  • CSV

    429questions

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

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

受付中

回答 3

投稿 編集

  • 評価
  • クリップ 3
  • VIEW 119

hono_nico

score 2

前提・実現したいこと

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

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

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

試したこと

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

LOAD DATA LOCAL INFILE "hogehoge.csv"
INTO 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/

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • hihijiji

    2017/12/23 18:15

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

    キャンセル

  • hono_nico

    2017/12/25 05:07

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

    キャンセル

  • hihijiji

    2017/12/25 10:10

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

    キャンセル

回答 3

+1

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

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

#
# CSV大規模用
#
[mysqld]
#↓↓コメントアウト↓↓
#log-bin = mysql-bin

innodb_log_file_size           = 512M
innodb_log_buffer_size         = 32M
innodb_autoextend_increment    = 64
innodb_flush_log_at_trx_commit = 2
innodb_data_file_path          = ibdata1:10G:autoextend:max:100G
skip_innodb_doublewrite

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

SELECT
  @@GLOBAL.log_bin,
  @@GLOBAL.innodb_log_file_size,
  @@GLOBAL.innodb_log_buffer_size,
  @@GLOBAL.innodb_autoextend_increment,
  @@GLOBAL.innodb_flush_log_at_trx_commit,
  @@GLOBAL.innodb_data_file_path,
  @@GLOBAL.innodb_doublewrite \G

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

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

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

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

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

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(オーバークロック)してみるとか。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

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

ただいまの回答率

91.25%

関連した質問

同じタグがついた質問を見る

  • MySQL

    4572questions

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

  • SQL

    1757questions

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

  • データベース

    564questions

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

  • CSV

    429questions

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