iT邦幫忙

2021 iThome 鐵人賽

DAY 4
0
自我挑戰組

Ruby on Rails 與它們相關的東西 II系列 第 4

Day04 - Gem-activerecord-import 批次建立介紹與應用

前言

當需要大量建立資料時,可以選擇逐筆建立,但會有 N+1 insert problem

透過 activerecord-import Gem 只要幾條 SQL 便解決

如何安裝

放在 Gemfile 檔案中,可參考此 commit

推薦至 GitHub 看文件,寫得很清楚,且有提供範例

使用方法

rails console --sandbox 中演練示範

# rails console --sandbox 可縮寫成 rails c -s
# 不建議在 production 使用 sandbox , 可能會造成 DB Lock,詳情自行上網查

$ rails c -s

[1] pry(main)> Shop.count
  TRANSACTION (0.2ms)  BEGIN
   (11.7ms)  SELECT COUNT(*) FROM "shops"
0
[2] pry(main)> shops = []
[]
[3] pry(main)> 10.times { |i| shops << { name: "test_#{i}", email: 'test', note: nil } }
10
[4] pry(main)> Shop.import(shops)
  Shop Create Many (1.0ms)  INSERT INTO "shops" ("name","email","note","created_at","updated_at") VALUES ('test_0','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_1','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_2','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_3','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_4','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_5','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_6','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_7','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_8','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_9','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104') RETURNING "id"
#<Struct:ActiveRecord::Import::Result:0x00008098
    failed_instances = [],
    ids = [
        [0] 1,
        [1] 2,
        [2] 3,
        [3] 4,
        [4] 5,
        [5] 6,
        [6] 7,
        [7] 8,
        [8] 9,
        [9] 10
    ],
    num_inserts = 1,
    results = []
>
[5] pry(main)> Shop.count
   (0.4ms)  SELECT COUNT(*) FROM "shops"
10

注意

在使用 import 時,須留意不會檢查 ActiveRecordvalidates 與觸發 callback,可參考這篇 GitHub Issue

以下為還原情境

# app/models/shop.rb
class Shop < ApplicationRecord
  strip_attributes
  validates :name, uniqueness: true
  after_commit :say_hello

  private

  def say_hello
    puts "hello"
  end
end

---

$ rails c -s

[1] pry(main)> Shop.count
  TRANSACTION (0.2ms)  BEGIN
   (1.1ms)  SELECT COUNT(*) FROM "shops"
0
[2] pry(main)> shops = []
[]
[3] pry(main)> 10.times { |i| shops << { name: "test", email: "test", note: nil } }
10
[4] pry(main)> Shop.import(shops)
  Shop Create Many (1.8ms)  INSERT INTO "shops" ("name","email","note","created_at","updated_at") VALUES ('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983') RETURNING "id"
#<Struct:ActiveRecord::Import::Result:0x000083b8
    failed_instances = [],
    ids = [
        [0] 1,
        [1] 2,
        [2] 3,
        [3] 4,
        [4] 5,
        [5] 6,
        [6] 7,
        [7] 8,
        [8] 9,
        [9] 10
    ],
    num_inserts = 1,
    results = []
>
[5] pry(main)> Shop.count
   (0.5ms)  SELECT COUNT(*) FROM "shops"
10
[6] pry(main)> Shop.pluck(:name)
   (0.3ms)  SELECT "shops"."name" FROM "shops"
[
    [0] "test",
    [1] "test",
    [2] "test",
    [3] "test",
    [4] "test",
    [5] "test",
    [6] "test",
    [7] "test",
    [8] "test",
    [9] "test"
]

避免踩到 unique 解法

Database 增加 unique ,便可避免建立重複的資料,可參考此 commit

add_index :shops, :name, unique: true

小結

大量資料存入 Database 時,import 是很好用的工具,同時也須留意眉角,避免踩雷。

note: Callbacks 解法可參考 GitHub README

參考資料

  1. activerecord-import GitHub
  2. 比較各種方式在 Rails DB 中寫入大筆資料的速度

鐵人賽文章連結:https://ithelp.ithome.com.tw/articles/10264572
medium 文章連結:https://link.medium.com/GX2nvti2Mjb
本文同步發布於 小菜的 Blog https://riverye.com/

備註:之後文章修改更新,以個人部落格為主


上一篇
Day03 - Gem-strip_attributes 介紹與應用
下一篇
Day05 - Gem-paranoia 軟刪除介紹與應用
系列文
Ruby on Rails 與它們相關的東西 II30

尚未有邦友留言

立即登入留言