Posted at

大きなCSVをMySQLからActiveRecord経由で出力する方法

RubyからMySQLへの接続は、多くの場合はActiveRecordを使ってるかと思います。

たくさんのレコードを出力際にはfind_eachを使うケースが多いですが、これは並び順を指定できません。最後のidから次の100件みたいに小分けにSQLが流れています。

それはそれで素晴らしいのですが、大きなCSVを出したい時って、SQLでJOINした結果を出したいのではないでしょうか。

そうなるとActiveRecordのリレーションを使ったfind_eachではなく、生のSQLを流したい。しかし自力でSQLを作ってしまうとメモリがあふれる。さぁどうしようというわけです。

そして答えはこちら。


  • mysql2resultsetを戻り値でもらうようにする

  • mysqlのコネクションに、以下のオプションを追加する


    • stream:true

    • cache_rows: false



これだけです。

逆にこれがないとメモリ不足で処理が落ちます。

負荷テストをしてみると、7,000,000万レコードもほとんどメモリを使用せずに出力できました。3.4Gのテキストファイルをメモリ1Gのマシンで出力できています。

以下サンプルコード

require 'active_record'

ActiveRecord::Base.establish_connection(
adapter: 'mysql2',
charset: 'utf8mb4',
encoding: 'utf8mb4',
collation: 'utf8mb4_general_ci',
database: 'sample',
host: 'samplehost',
username: 'sample',
password: 'sample!',
stream: true, # これが徐々に結果を書き出すパラメータ
cache_rows: false # stream が trueだと cache_rows は強制的にfalseになる
)

# ここに巨大な結果を返すSQLを記述。CSVのカラム名を指定したかったら、as で別名を指定
sql = <<'EOS'
select * from samples limit 1000000
EOS

# executeを使って結果をmysql2 resultset で結果を受け取る
results = ActiveRecord::Base.connection.execute(sql)

# CSV出力。BOMをつけることでエクセルで開きやすくしている。
open("csv/sample.csv","w") do |f|
f.print("\uFEFF" + results.fields.join(',') + "\r\n")
results.each do |fields|
fields.map! { |field| '"' + field.to_s.gsub('"', '""') + '"' }
f.print(fields.join(',') + "\r\n")
end
end

ちなみに、show processlist のSQLを流すと、この時間のかかるSQLのステータスは Writing to net がずーっと続くことになります。