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

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

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

## 公式の説明
[読み仮名データの促音・拗音を小書きで表記するもの - zip圧縮形式 日本郵便](http://www.post.japanpost.jp/zipcode/dl/kogaki-zip.html)

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

## 検証

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

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

```sh:console
# bundle install
$ rbenv exec bundle install --path=vendor/bundle/
```

```rb:計測処理を追加しました
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
```

結果です。

```sh: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](https://qiita.com/modeverv@github)さんのコメントの通りで、たしかにトランザクションの考慮が漏れていました。
 
```rb:トランザクション制御が効いていないソース
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
```

改修後のソース

```ruby:トランザクション制御を追加したソース
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
```

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

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

```ruby:実行時のログ
  ↳ 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をカラムから除外する方法を選択しました。

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

### 参考

[Railsのバッチ処理のコツ - tech-kazuhisa's blog](http://tech-kazuhisa.hatenablog.com/entry/2014/06/09/210942)
[delete, delete_all, destroy, destroy_allについて - Qiita](https://qiita.com/kamelo_15/items/0fa7fb15a1d2c1e44db2)
[Ruby でベンチマークを取る方法 - Qiita](https://qiita.com/scivola/items/c5b2aeaf7d67a9ef310a)
[ActiveRecordで複数レコード、BULK INSERTする方法とパフォーマンスについて - Qiita](https://qiita.com/xend/items/79184ded56158ea1b97a)