投稿者:

21 7月 2020

Rails6 で csv エクスポートを実装(複数テーブル編)

この記事では、Rails6 + MySQL 環境でテーブル内容を csv としてエクスポートするメソッドを実装して、その速度比較をします。
ここでは user has_many projects の関係をもたせています。
そこで CSV には user 情報だけでなく、ueer が属している project の情報も合わせて出力することにします。(以前の記事での project の CSV 出力は project テーブルの情報だけを出力していました)

結論としては以下の様に実装方法により大きな差がでました。

処理時間(秒) (計測に時間がかりすぎる場合は ー と記した)
1,000件 10,000件 100,000件 1,000,000件 10,000,000件
実装_1(SQL) 0.14  0.3 2.4 28.6 561.5
実装_2(in_batches) 0.07 0.6 6.1 74.8
実装_3(each) 1.18  11.4 109.7

数万件程度の処理なら、どの実装も十分実用には耐えると思われます。ただし 1千万件オーダー、それ以上の件数を扱うなら、実装方法を吟味する必要があります。

この測定結果を得るコードや測定方法が妥当なのかは、以下を読んで各自で判断してみてください。

 

1. メソッドの実装

3 つの実装をしました。コードはそれぞれのリンク先を参照してください。

to_csv_by_sql()  MySQL の CSV 出力機能を利用した実装です。

2. to_csv()  in_batches() メソッドを利用して、1000 件ごとにバッチ処理する実装です。

3. to_csv_x()  単純に where 結果を each でループする実装です。

user と project の関係は schemaspy で作成した ER図も参照してください。

 

CSV は user.id, user. name, user. last_login_at, project.name の情報を出力します。
user ごとに1行の CSV を出力するのではありません。

  • user が projec に属していなければporject 情報列は 空になっている一行だけ出力します。
  • user が 1つだけの project の属していれば project 情報は所属 project の情報が埋まっている一行だけ出力します。
  • user が複数の project に属していれば、属しているproject の数文の行をそれぞれの project 情報を埋めて出力します。

つまり SQL で user に projects を left  outer join した結果を CSV 出力すると考えていただければ十分です。

CSV の行の順番は,  users.id の昇順, projects.id の昇順 とします。

to_csv_x() では、単純に User.all で ループし、各 user ごとに user.projects でループしています。

csv = CSV.new(file, **csv_options)
users.in_batches.each_record do |row|
    project_names = row.projects.order(:id).map(&:name)
    last_login_at = row.last_login_at&.strftime(CSV_DATETIME_FORMAT)
    if !project_names.empty?
        project_names.each do |p_name|
            csv << [row.id, row.name, last_login_at, p_name]
        end
    else
        csv << [row.id, row.name, last_login_at, ""]
    end
end

to_csv では、users と project を join して join 結果の行数でループしています。

users = User.where(id: users.first.id..users.last.id)
            .left_joins(:projects)
            .select(select_sql)
            .order("users.id ASC, projects.id ASC")
File.open(csv_name, 'w:UTF-8') do |file|
    file.write BOM

    csv = CSV.new(file, **csv_options)
    users.in_batches.each_record do |row|
        last_login_at = row.last_login_at&.strftime(CSV_DATETIME_FORMAT)
        csv << [row.id, row.name, last_login_at, row.project_name]
    end
end

to_csv_x() は in_batch を使ってはいますが、 N+1 問題を含んでいます。
CSV の行数だけ SQL 文が発行されてしまうのです。
to_csv() では、SQL 発行は 1 回です。

SQL の発行回数は 次のテストの章で pry を使って確認してみます。
SQLの発行回数の差が実行速度にどう影響するかはベンチマークの章で確認します。

user.last_logine_at 列は datetime です。 to_csv_by_sql() では 列の値を +9:00 時間して出力することにしました。その処理をしたので 3 つの実装すべてで同一の列情報が出力されます。

UTC_OFFSET = '+09:00'
  ...  
select_sql = <<-SQL.squish
    users.id,
    users.name,
    CASE
        WHEN users.last_login_at IS NULL THEN '' 
        ELSE convert_tz(users.last_login_at, '+00:00','#{UTC_OFFSET}')
    END AS last_login_at,
    CASE
        WHEN projects.name IS NULL THEN '' ELSE projects.name
    END AS project_name
SQL

参考情報:

2. メソッドのテスト

それぞれのメソッドについて rspec でのテストを書きました。
spec/models/user_spec.rb で参照できます。

test 環境 (レコード数が少ない状態) で 3 つのCSV 出力メソッドを呼び出して、発行される SQL を確認してみます。
user_spec.rb の to_csv_by_sql のテスト部に binding.pry を追加して ブレークポイントを設定します。そして rm log/test.log してから rspec spec/moddes/user_sper.rb:110 として to_csv_by_sql() のテストを走らせます。
プログラム実行が bindig.pry の秒で停止したら、 別端末で、 tail -f log/test.log をします。rspec 字実行端末にもどり、User.to_csv() を入力し Enter キーを入力します。
すると、tail- f の実行端末に実行された SQL が表示されます。

同様にして、 to_csv_x() を実行したときの SQL 内容、 to_csv_by_sql() を実行したときの SQL 内容を表示させます。

break point の設定

rspec を実行してbreak point で停止させる

User.to_csv() 実行時の SQL

to_csv() は 1000 件ごとに 1 つの SQL が実行されます。test DB には 数件しか user  レコードがないので、 1つのSQLだけが実行されます。次の章で大容量 DV をつくった後に、user を2000 件処理して、SQL が 複数回実行されることを確認することにします。

User.to_csv_x() 実行時の SQL

ti_csv_x() は users を select する SQL だけでなく、各 user ごとに projects を  select する SQL が実行されています。
これがいわゆる N+1 問題です。この実装では大量のSQLが発行されます。対象レコードの件数が多い場合、実用に耐えない処理時間になってしまいます。

参考情報:  https://qiita.com/muroya2355/items/d4eecbe722a8ddb2568b    SQLクエリのN+1問題

to_csv_by_sql() 実行時のSQL

 to_sql_by_sql() は、user のレコード数に関係なく常に 1 つの SQL が実行されます。

3. 大容量DBの作成

$ rails 'db:make_big_db[10000000,1000000]'

として、 Project テーブルに 1千万件,  User テーブルに 1千万件を登録します。
user と project の関係は 10 user ごとに 所属する project 数が [0, 1, 0, 0, 0, 3, 0, 0, 5, 0] となるように登録しています。(つまり 中間テーブル project_users_relation には 900万件登録)
実行には 40分かかりました。(すぐに rails db:dump して dump を取りました)

この rake task の内容は lib/tasks/make_big_db.rake で参照できます。

 

先頭の 2000 件を to_csv() で csv 出力して、そのときに実行される SQL を確認してみます。
rails c としてコンソールに入ります。そして User.to_csv(offset:0, limit:2000) を実行します。

4 つの SQL が実行されています。 user  1000 件ごとではなく、 users と projects を left outer join した結果のテーブルを 1000  県ごとに処理しているので、こ0の場合は 2 つのSQLではなく、4 つの SQL が実行されています。

ここでは、スクリーンショットは示しませんが、to_csv_by_sql(offset:0, limit:2000) や to_csv_x(offset:0, limit:200) を実行させて、どんな SQL が実行されるかを確認してみてください。(to_csv_x() は liit:2000 でなく、 limt:200 ぐらいで試してください)
to_csv_by_sql() では 1 つの SQLが実行されます。to_csv_x() では多量の SQL が実行されます。

rails c の画面に表示される SQL は log/development.log にも記録されています。 less -R log/development.log などとして閲覧してみてください。

4. ベンチマーク実施

ベンチマークのための rake task を作成しました。users テーブルのレコードの 指定番目から指定件数をcsv 出力するコマンドを実行してその処理時間と使用メモリー量を計測をします。
lib/tasks/benchmark_csv_user.rake でコードを参照できます。

計測環境:

  • PC  MacBook Pro (CPU  2.3 GHz, クアッドコア Intel Core i5,メモリー 16GB)
  • OS Catalina 10.15.5
  • ruby 2.7.1p83 (2020-03-31 revision a0c7c23c9c) [x86_64-darwin19]
  • rails 6.0.3.2
  • MySQL Ver 8.0.19 for osx10.15 on x86_64 (Homebrew)
実行速度の測定 (秒)
1,000件 10,000件 100,000件 1,000,000件 10,000,000件
実装_1(SQL) 0.14 0.3 2.4 28.6  561.5
実装_2(in_batches) 0.07 0.6 6.1 74.8
実装_3(each) 1.18 11.4 109.7

 

メモリー使用量の測定(Kb)  (計測に時間がかりすぎる場合は ー と記した)
1,000件 10,000件 100,000件 1,000,000件 10,000,000件
実装_1(SQL) 1 8  87 875
実装_2(in_batches) 5 54 546
実装_3(each) 73 730

SQL での実装でも 1千万件での処理時間は厳しいです。非同期処理にするといった工夫が必要と思われます。
ActiveRecord の each 実装は N+1問題のために in_batches での実装より 20 倍程度遅くなっています。

5. まとめ

 

  1. N+1 問題を避けることは必須。
  2. SQL での csv 出力実装でも1千万件は分単位の時間がかかる。
  3. SQL の CONVERT_TZ関数を利用して、datetime を timezone に合わせた値に変換できる。

 

つぎは、user – project の情報の CSVインポートを複数の方法で実装し、処理速度・メモリー使用量を比較していきます。

 

20 7月 2020

Rails6 で csv インポートを実装

この記事では、Rails6 + MySQL 環境で CSV ファイルをインポートしてテーブルにレコード登録するメソッドを実装して、その速度比較をします。

結論としては以下の様に実装方法により大きな差がでました。

処理時間(秒)
1,000件 10,000件 100,000件 1,000,000件 10,000,000件
実装_1(SQL) 0.04 0.09 0.5 7.3 129.1
実装_2(insert_all) 0.21 1.79 17.2 183. 8
実装_3(each) 5,41  49.98

数万件程度の処理なら、どの実装も十分実用には耐えると思われます。ただし 1千万件オーダー、それ以上の件数を扱うなら、実装方法を吟味する必要があります。

この測定結果を得るコードや測定方法が妥当なのかは、以下を読んで各自で判断してみてください。

1. メソッドの実装

インポートについても3つの方法で実装をしました。コードはリンク先で参照できます。

import_by_sql     SQL で CSV ファイルをインポートとする実装です。
imorrt                    inert_all を使って 1000 行ごとに  insert 処理する実装です。
import_x              1 行ごとに insert をする実装です。

2. メソッドのテスト

3 つのインポート実装それぞれに対して、 spec/fixtures/export_projects.csv を import した後の DB 内容を比較するテストをしています。
それぞれのテストは以下のリンクから参照してください。

  1. import_by_sql     SQL文で CSV を import する実装です。
  2. import  1000 行ごとに insert_all する実装です。
  3. import_x     CSV 一行ごとに find_or_create する実装です。

本来なら、DB内容はすべて同じになるべきですが、現状実装ではそうなっていません。
SQL での import と ActiveRecord での import で、 datetime の列の値が 9時間ずれるのです。。
その原因は、Rails での timezone は Asia/Tokyo 、MysQL の timezone は UTC としていることに起因しています。

CSV ファイルの “202-01-01 01:02:03” は ActiveRecord 経由では  Time.zon.parse(“2020-01-01 01:02:03”) で扱っています。(CSV ファイルを Excel  などで閲覧したとき、JST のほうがユーザーにとっては自然です)

SQL で読み込む時は “2020-01-01 01:02:03″ が直接 DB に UTC として取り込まれます。それを ActiveRecord 経由で読み込むと、+9:00 された 2020-01-01 10:02:03” となってしまいます。

このへんのことは、 Rails, DB, csv での timezone をどうするかの仕様をはっきりさせて、それに沿った実装することでTimezone の解釈間違いを無くようにすることが必要になります。
SQL での import 時に datetime の offset 操作を組み込めれば rspec で 同じ DB 結果になるというテストが書けたはずなのですが、その方法がわかりませんでした。ここでの目的は CSV インポートの速度比較なので timezone の扱いは手を抜いています。

3. 大容量 CSV の作成

大容量の project レコードの CSV を作る rake task をつくりました。
lib/tasks/make_big_csv.rake で参照できます。

実際に CSV ファイルを作る時は rails ‘db:make_big_csv[1000]’ > csvs/1000.csv のようにします。
1千万行の CSV は 1GB程度になります。普段は gzip しておいて、使うときだけ gunizp するとよいです。unzip すると 80MB 程度になります。

4. ベンチマーク実施

3 つの実装に対して指定ファイルを import して。その実行速度、使用メモリー量を計測する rake task をつくりました。
lib/tasks/benchmark_csv_project.rake で参照できます。


次のコマンドを実行して計測をしていきます。

$ rails ‘benchmark:import:project[csvs/1000.csv]’
$ rails ‘benchmark:import:project[csvs/10000.csv]’
$ rails ‘benchmark:import:project[csvs/100000.csv]’
$ rails ‘benchmark:import:project[csvs/1000000.csv]’
$ rails ‘benchmark:import:project[csvs/10000000.csv]’

1万行で import_x は メモリー使用量測定がなかなか終了しなくなったので、 それ以降の測定では import_x メソッドは測定から外しました。(コメントアウトしました)
10万行で import のメモリー使用量測定は数分かかったので、それ以降は ctrl-c で中断をしました。

処理時間(秒) (測定中止したものはーと記載)
1,000件 10,000件 100,000件 1,000,000件 10,000,000件
実装_1(SQL) 0.04 0.09 0.5 7.3 129.1
実装_2(inert_all) 0.21 1.79 17.2 183. 8
実装_3(each) 5,41  49.98

 

メモリー使用量(MB) (測定中止したものは ー と記載)
1,000件 10,000件 100,000件 1,000,000件 10,000,000件
実装_1(SQL) 0.07  0.07 0.07 0.07  ー
実装_2(insert_all) 12.1 122.1
実装_3(each) 396.7

 

SQL をつかった実装でも 1千万件までぐらいまでが限界です。
insert_all をつかった実装では 100万件ぐらいまでが限界です。
(本番サーバーの能力が PCの 10倍, 100 倍あれば、1千万件以上も可能とおもわれます)

CSV インポートは CSVインポートよりは重い処理になります。DB の index 更新処理が必要なのもその一因です。

5. まとめ

SQL の CSV インポート機能を使う実装でも 1千万件程度が限界です。
ActiveRecord の insert_all で 1000件ごとに SQL 発行するようにしても SQL 実装より 100 倍ほど遅いです。
インポート実装では, timezone の扱いに注意が必要です。

次の記事では、 user has_many projects の関係がある場合に user + project の情報を CSV エクスポートする実装の速度・メモリー使用量を比べていきます。

15 7月 2020

Rails6の学習環境を構築

Rails6 の小さなプロジェクトをつくり、いろいろ試していくことにします。
まず Rails6 のプロジェクトを rails new で作成します。そのプロジェクトで、DB内容の csv エクスポート・インポートの処理をいくつか実装して処理速度を比較していきます。
(実際のプロジェクトコードは https://github.com/katoy/rail6-with_rspec  で参照できます)

この記事では作業前の環境準備について述べます。

 

1. Rspec の導入

rails6では、minitestというテストフレームワークが標準です。(Railsチュートリアルでも mnitest でテストをしています)
しかし、このプロジェクトでは rspec というフレームワークを使うようにします。
理由は私がRails のテストについては書籍 “Everyday Rails – RSpecによるRailsテスト入門” で勉強したことがあり、rspec に慣れているからです。

ここでは、設定のこまかな手順は省略します。
【動画付き】Everyday RailsのサンプルアプリをRails 6で動かす際に必要なテストコードの変更点
など、web 上で Rails6 環境のテストを rspec で行うための設定方法がいろいろ見つかります。
ご自分で rails new からプロジェクトを作っていく際は、これらを参照しながら作業することをお勧めします。

githbu においたプロジェクトコード https://github.com/katoy/rail6-with_rspec は、rails6 で rails new してから rspec を導入した環境になっています。github から clone すれば、rspec が PASS する状態になっているはずです。

なお、このプロジェクトでは、 DB には MySQLを使っています。mysql サーバーをインストール/稼働させた状態で DB 構築をしてから、rspec を走らせる必要があります。

DB構築と rspec の実行

2. DBのGUIクライアントの導入

DB の内容を確認したり、SQL文を実行して結果を確認するといった操作をするために、GUI クライアントツールがあると便利です。
私は DBevar というツールをつかっています。主なDB (MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, etc) に対応しています。Mac の場合は brew cask install dbeaver-community でインストールできます。
(DBeaver のホームページから 各種 OS 甩のインストーラを取得することもできます)

下に このプロジェクトの DB (MySQL) についてテーブルの内容表示、ER 図表示、SQL実行の様子を示します。

テーブル内容の表示

簡易 ER 図の表示

SQL文の実行

3. ER図の自動作成

ER図の表示が DBeaver である程度できます。もっと複雑。大量のテーブルのER図を生成するなら、専用の別ツールがあります。
私は ER図の作成には、schemaspy を使っています。このプロジェクトでは、schempy で ER図を作る設定をしてあります。

$ cd schemaspy
$ ,.run/sh

を実行すると ./html 以下に DB 全体の ER 図が生成されます。 html/index.html をブラウザで開くと閲覧できます。

 

この図は  svg 形式で生成するように設定してあります。svg 形式なので、ブラウザで拡大・縮小表示をしていっても字や線が潰れたり粗いドット表示になることはありません。もっと複雑で、テーブル数・カラム数が多くて拡大・縮小表示する必要がある場合でも綺麗に表示されます。

4. DB の store/restore

100万件。1000 万件のデータを保持させたDBをつくるのはそれなりに時間がかかります。一度つくったDB は dumo ファイルを store してき、必要に応じて dump ファイルを restore するようにすると便利です。store / restore の操作はコマンドライン操作で可能ですが、タイプミスを防ぐために、rails の rake task をつくりました。


$ rails db:dump を実行すると development 環境の DB の dump が ./tmp/rail6-with_rspec_development.dump に保存されます。

$ rails db:restore を実行すると、 ./tmp/rail6-with_rspec_development.dump が develop 環境の DB に restore されます。

5. ベンチマーク方法

エクスポートでは、DBに1千万件のデータを作っておきます。その状態で  1万件。10万件, …を csv としてエクスポートするメソッドを呼び出して実行時間とメモリー使用量を測定します。
インポートでは、DBに1千万件のデータを作っておきます。さらに 1万件、10万件,  … のデータ行を持つ csv ファイルを作成しておきます。その状態で インポートするメソッドを 読み込む csv ファイルを指定して実行時間とメモリー使用量を測定します。

これらの操作についても rake task を作りました。

 

1000 件レベルの DB 作成 (アニメーションgif)

10000件ていどなら、処理時間は数銃秒ですが、100万件、1千万件の場合は数分かかります。作業の進行状態がわかりようにプログレスバー表示する工夫をしています。

次回は、実際に csv エクスポートの処理を複数実装してから、それらの処理時間・使用メモリー量を比較していきます。

 

15 7月 2020

Rails6 の学習

RubyOnRails はいまでも広く使われています。Rails 6.0が 2019年8月にリリースされ、マイナーバージョンアップも続いています。

Ruby on Railsバージョンアップ情報

まだ実際の現場では Rail6 ではなく Rails4 や Rails5 を利用している場合もあるかもしれません。でも、エンジニアとしては、現場での環境にかかわらず新しいバージョンについて勉強をしておくことが必要です。

ここでは。個人的なRails6の勉強として使用しているテキストと、実際のコーディング練習について述べます。

Rails を勉強しようとしている方の参考になれば幸いです。

 

使用しているテキスト

Ruby on Rails チュートリアル の rails 6.0 版対応をテキストとして使っています。
(このコンテンツは有料。Webテキスト (第6版) 908 円, Rails5版は無料)

https://github.com/yasslab/sample_apps
には、Railsチュートリアルの各章が終わった状態を集めたリポジトリがあります。(yasslabは 日本語版チュートリアルをリリースしている会社です。)

うまくプログラムが動作しない、テストがPASSしないなど困ったときに、どこが間違っているかをチェックするのに利用するとよいかもしれません。(私はいまのところ,これを参照したことはないですが)

yasslabからは、さらに  https://github.com/yasslab/railstutorial.jp_starter_kit
として、仮想環境 (VirtualBox + Vagrant) を使ってRailsチュートリアルの環境構築の説明が公開されています。初心者は rails 環境でつまずくことが多いです。困ったときはこれを参照するとよいかもしれません

私は 2020-06-30 時点で、8章と9章を終了しました。このテキストでは、章の終わりにその章を終了したことを tweet できるようになっています。これを使って tweet すると、最低でも 1つは いいね!が付きます。それは yasslab CEO からのものです。

第6版 #Railsチュートリアル の第8章を走破しました!

第6版 #Railsチュートリアル の第9章を走破しました!

テキストでの指示に従って、章の終了タイミングでプログラムコードを heroku にデプロイしています。
https://shrouded-refuge-10739.herokuapp.com/でアプリケーションを試すことができます。

このテキストの良いところは、常に 現時点は テストがPASSする状態かどうかを意識して説明が進んでいくことです。説明途中のコードがテスト失敗する状態である状態にある場合でもその理由が述べられている場合が多いです。最終的には必ずテストがPASSする状態になるように説明が進んでいきます。

このテキストの短所は、ボリュームが大きい事です。でも”学問に王道無し” です。それなりの機能を学ぶにはそれなりの時間をかけることは避けられません。私の場合、10章には2日かかりました。その後 11章も走破し、現在は 12章の履修中です。

実際のコーディング練習

Rails6 で追加された API をつかって古い版では実装が面倒だった機能などについて練習をしています。

https://github.com/katoy/rail6-with_rspec

ここでは  DB 内容の csv でのエクスポート/インポートについて、実装方法によって速度差がどれだけ出るかについて試行しています。(レコード数 100万、1000万レベルを処理できるコードを書いていっています)

次回は、これについて述べていきます。