你必須很努力

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

2021/09/17
字數統計: 933閱讀時間: 5 min

前言

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

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

如何安裝

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

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

使用方法

rails console --sandbox 中演練示範

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
# 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

以下為還原情境

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
# 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

1
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/

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

原文連結:https://riverye.com/2021/09/17/Day04-Gem-activerecord-import-批次建立介紹與應用/

發表日期:2021-09-17

更新日期:2021-09-24

CATALOG
  1. 1. 前言
  2. 2. 如何安裝
  3. 3. 使用方法
  4. 4. 注意
  5. 5. 避免踩到 unique 解法
  6. 6. 小結
  7. 7. 參考資料