RailsでKEN_ALL.CSVファイルを取り込む時にバルクインサートを利用して処理時間を改善する。

06. KEN_ALL.CSVファイルを取り込む時にバルクインサートを利用して処理時間を改善する。

この記事の続きになります。(01〜05はこちら)
とりあえず住所検索APIサーバーをRailsで構築してみました。（住所の精度は無視しています。） - Qiita

公式の説明

読み仮名データの促音・拗音を小書きで表記するもの - zip圧縮形式 日本郵便

公式にもありますが、「全国一括のデータは12万件」と記述があります。このデータを一件ずつ取り込んでいると取り込みに時間が掛かります。そこでBULK INSERTでデータを取り込み時間短縮を図ります。

検証

普通のインサートとバルクインサートを検証します。

Gemfile

# バルクインサートするためのgem
gem 'activerecord-import', '~> 0.23.0'

console

# bundle install
$ rbenv exec bundle install --path=vendor/bundle/

計測処理を追加しました

def update_zipcodes
  Benchmark.bm 20 do |r|
    r.report "INSERT" do
      csv_file_read("public/", "KEN_ALL.CSV").each do |csv|
        Yubin.create!({
            local_governments_cd: csv[0],
            past_zipcode: csv[1],
            zipcode: csv[2],
            region_kana: csv[3],
            locality_kana: csv[4],
            street_address_kana: csv[5],
            region: csv[6],
            locality: csv[7],
            street_address: csv[8],
            flag_1: csv[9],
            flag_2: csv[10],
            flag_3: csv[11],
            flag_4: csv[12],
            view_update: csv[13],
            reason: csv[14]
          })
      end
    end
    r.report "BULK INSERT" do
      yubins = []
      csv_file_read("public/", "KEN_ALL.CSV").each do |csv|
        yubins << Yubin.new({
            local_governments_cd: csv[0],
            past_zipcode: csv[1],
            zipcode: csv[2],
            region_kana: csv[3],
            locality_kana: csv[4],
            street_address_kana: csv[5],
            region: csv[6],
            locality: csv[7],
            street_address: csv[8],
            flag_1: csv[9],
            flag_2: csv[10],
            flag_3: csv[11],
            flag_4: csv[12],
            view_update: csv[13],
            reason: csv[14]
          })
      end
      Yubin.import yubins
    end
  end
  p "#{Yubin.all.length}件を登録しました。"
end

結果です。

console

$ sh ./scripts/insert_and_update_zipcodes.sh                                                                    user     system      total        real
INSERT               184.560944  87.828741 272.389685 (488.083898)
BULK INSERT           39.115773  27.896120  67.011893 ( 69.278273)

バルクインサートの圧勝ですかね。バルクインサートなら体感 5分が1分ちょいぐらいになりました。
DB取り込み処理をバルクインサートに変更します。
処理速度を短縮できたので、一旦データを全削除してから取り込みます。

modeverv@githubさんのコメントの通りで、たしかにトランザクションの考慮が漏れていました。

トランザクション制御が効いていないソース

def update_zipcodes
  yubins = []
  time = Benchmark.realtime do
    Yubin.delete_all # ActiveRecordを返さずに削除するので効率がいい。
    ActiveRecord::Base.connection.execute('ALTER TABLE yubins AUTO_INCREMENT = 1')
    csv_file_read("public/", "KEN_ALL.CSV").each do |csv|
      yubins << Yubin.new({
          local_governments_cd: csv[0],
          past_zipcode: csv[1],
          zipcode: csv[2],
          region_kana: csv[3],
          locality_kana: csv[4],
          street_address_kana: csv[5],
          region: csv[6],
          locality: csv[7],
          street_address: csv[8],
          flag_1: csv[9],
          flag_2: csv[10],
          flag_3: csv[11],
          flag_4: csv[12],
          view_update: csv[13],
          reason: csv[14]
        })
    end
    Yubin.import yubins
  end
  p "#{Yubin.all.length}を登録しました。#{time}"
end

改修後のソース

トランザクション制御を追加したソース

def update_zipcodes
  yubins = []
  time = Benchmark.realtime do
    create_yubin_date yubins
    ActiveRecord::Base.transaction do
      Yubin.delete_all # ActiveRecordを返さずに削除するので効率がいい。
      Yubin.import yubins
    end
  end
  p "#{Yubin.all.length}件を登録しました。#{time}"
end

private

  def create_yubin_date(yubins)
    csv_file_read("public/", "KEN_ALL.CSV").each do |csv|
      add_yubin(yubins, csv)
    end
  end

  def add_yubin(yubins, data)
    yubins <<Yubin.new({
        local_governments_cd: data[0],
        past_zipcode: data[1],
        zipcode: data[2],
        region_kana: data[3],
        locality_kana: data[4],
        street_address_kana: data[5],
        region: data[6],
        locality: data[7],
        street_address: data[8],
        flag_1: data[9],
        flag_2: data[10],
        flag_3: data[11],
        flag_4: data[12],
        view_update: data[13],
        reason: data[14]
      })
  end

console

sh ./scripts/insert_and_update_zipcodes.sh
"124184件を登録しました。69.21460400009528"

上記を実行した時のログです。

実行時のログ

  ↳ lib/tasks/japan_post.rb:35
   (0.9ms)  BEGIN
  ↳ lib/tasks/japan_post.rb:18
  Yubin Destroy (451.1ms)  DELETE FROM `yubins`
  ↳ lib/tasks/japan_post.rb:19
  （インサート文が大量にあり..省略）
  ↳ lib/tasks/japan_post.rb:20
   (0.9ms)  RELEASE SAVEPOINT active_record_1
  ↳ lib/tasks/japan_post.rb:20
   (11.6ms)  COMMIT
  ↳ lib/tasks/japan_post.rb:18

delete_allもYubin.importもそれぞれトランザクション制御されていますが、２つを制御するには明示的な指定が必要でした。
また、ActiveRecord::Base.connection.execute('ALTER TABLE yubins AUTO_INCREMENT = 1')この記載があると以下のERRORになり失敗してしまいます。原因特定まではできず、IDをカラムから除外する方法を選択しました。

Mysql2::Error: SAVEPOINT active_record_1 does not exist: ROLLBACK TO SAVEPOINT active_record_1 (ActiveRecord::StatementInvalid)

参考

Railsのバッチ処理のコツ - tech-kazuhisa's blog
delete, delete_all, destroy, destroy_allについて - Qiita
Ruby でベンチマークを取る方法 - Qiita
ActiveRecordで複数レコード、BULK INSERTする方法とパフォーマンスについて - Qiita