大きな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 がずーっと続くことになります。