Trong quá trình phát triển phần mềm, chúng ta chắc hẳn đã từng rất nhiều lần thêm mới hay cập nhập lại dữ liệu nhưng hầu như chỉ với một lượng tương đối ít dữ liệu thay đổi, quá trình này chả mất là bao thời gian.
Mình đã thật sự sai lầm khi cho rằng như vậy, hãy để ý một chút, thông thường trong rails đang insert dữ liệu kiểu row by row, nghĩa là mỗi một lần tạo mới hoặc thay đổi dữ liệu 1 bản ghi thì sẽ tạo ra 1 câu truy vấn tương ứng. Thử tưởng tượng bạn muốn import khoảng vài triệu bản thì số câu query và thời gian sẽ như thế nào.
Để giải quyết vấn đề đó trong ruby thì lại không khó, trong quá trình tìm hiểu, mình được biết đến gem activerecord-import. Khoan đã, trước đó thì bạn đã từng biết những các nào nào để import dữ liệu chưa.
1. Tạo bản ghi theo cách tạo từng cái một (row by row )
1 2 3 4 5 6 | users = [] 10.times do |i| users << User.new(name: "user #{i}") end User.import users |
Ví dụ trên chị hiệu quả với lượng ít dữ liệu thôi, như đã nói ở trên, vấn đề cần giải quyết ở đây là hãy import khoảng vài triệu bản ghi vào database của mình. Theo cách thông thường thì bạn nhập từng hàng một trong file csv và sao đó chèn chúng vào trong database của mình thông qua file seed.rb
1 2 3 4 5 | <span class="token comment"># seeds.rb</span> <span class="token constant">CSV</span><span class="token punctuation">.</span><span class="token function">foreach</span><span class="token punctuation">(</span><span class="token string">'products.csv'</span><span class="token punctuation">,</span> headers<span class="token punctuation">:</span> <span class="token keyword">true</span><span class="token punctuation">)</span> <span class="token keyword">do</span> <span class="token operator">|</span>row<span class="token operator">|</span> <span class="token constant">Product</span><span class="token punctuation">.</span><span class="token function">create</span><span class="token punctuation">(</span>product_name<span class="token punctuation">:</span> row<span class="token punctuation">[</span><span class="token string">'Product Name'</span><span class="token punctuation">]</span><span class="token punctuation">)</span> <span class="token keyword">end</span> |
Có vẻ cách trên đúng nhưng lại hiệu suất import lại quá chậm, bạn không thể ngồi cả ngày chỉ để import vài triệu dữ liệu thôi đấy chứ.
2. Sử dụng câu lệnh SQL INSERT(code khó đọc và không an toàn)
1 2 3 4 5 6 7 8 9 10 11 12 13 | <span class="token comment"># Gán giá trị cho users bằng một mảng gồm các user hash</span> <span class="token comment"># like [{ name: "Sam" }, { name: "Charls" }]</span> sql <span class="token operator">=</span> <span class="token string">"INSERT INTO users VALUES "</span> sql_values <span class="token operator">=</span> <span class="token punctuation">[</span><span class="token punctuation">]</span> users<span class="token punctuation">.</span><span class="token keyword">each</span> <span class="token keyword">do</span> <span class="token operator">|</span>user<span class="token operator">|</span> sql_values <span class="token operator"><</span><span class="token operator"><</span> <span class="token string">"(<span class="token interpolation"><span class="token delimiter tag">#{</span>user<span class="token punctuation">.</span>values<span class="token punctuation">.</span><span class="token function">join</span><span class="token punctuation">(</span><span class="token string">", "</span><span class="token punctuation">)</span><span class="token delimiter tag">}</span></span>)"</span> <span class="token keyword">end</span> sql <span class="token operator">+</span><span class="token operator">=</span> sql_values<span class="token punctuation">.</span><span class="token function">join</span><span class="token punctuation">(</span><span class="token string">", "</span><span class="token punctuation">)</span> <span class="token constant">ActiveRecord</span><span class="token punctuation">:</span><span class="token punctuation">:</span><span class="token constant">Base</span><span class="token punctuation">.</span>connection<span class="token punctuation">.</span><span class="token function">insert_sql</span><span class="token punctuation">(</span>sql<span class="token punctuation">)</span> |
3. Sử dụng activerecord-import gem(nhanh hơn)
1 2 3 4 5 6 | users <span class="token operator">=</span> <span class="token punctuation">[</span><span class="token punctuation">]</span> <span class="token number">10.</span>times <span class="token keyword">do</span> <span class="token operator">|</span>i<span class="token operator">|</span> users <span class="token operator"><</span><span class="token operator"><</span> <span class="token constant">User</span><span class="token punctuation">.</span><span class="token keyword">new</span><span class="token punctuation">(</span>name<span class="token punctuation">:</span> <span class="token string">"user <span class="token interpolation"><span class="token delimiter tag">#{</span>i<span class="token delimiter tag">}</span></span>"</span><span class="token punctuation">)</span> <span class="token keyword">end</span> <span class="token constant">User</span><span class="token punctuation">.</span>import users |
ActiveRecord-import là một gem của ruby được viết bởi ông Zach Dennis. Nó thì nhanh hơn nhiều so với cách insert row by row thông thường và cũng rất dễ thực hiện.
Để có thể sử dụng nó bạn cần import gem "activerecord-import"
vào trong Gemfile
và nhớ gõ bundle install
nhé.
Cơ chế hoạt động của gem này là giảm số lượng lớn câu query của bạn thành duy nhất một công query. Thay vì ra tận vào triệu câu query thì giờ đây chỉ có duy nhất 1 câu query.
Ngoài ra bạn cũng có thế áp dụng để chèn mới một cột, thêm mới toàn bộ dữ liệu trong bảng, cập nhật loại toàn bộ dữ liệu, …
Dưới đây là đoạn code ví dụ về update dữ liệu lớn từ file csv:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | <span class="token keyword">def</span> update <span class="token keyword">return</span> <span class="token keyword">if</span> is_empty_db<span class="token operator">?</span> file_path <span class="token operator">=</span> <span class="token string">"product.csv"</span> instances <span class="token operator">=</span> <span class="token punctuation">[</span><span class="token punctuation">]</span> update_keys <span class="token operator">=</span> <span class="token punctuation">[</span><span class="token punctuation">]</span> <span class="token constant">CSV</span><span class="token punctuation">.</span><span class="token function">foreach</span><span class="token punctuation">(</span>file_path<span class="token punctuation">,</span> headers<span class="token punctuation">:</span> <span class="token keyword">true</span><span class="token punctuation">)</span> <span class="token keyword">do</span> <span class="token operator">|</span>row<span class="token operator">|</span> object <span class="token operator">=</span> <span class="token constant">Product</span><span class="token punctuation">.</span>find_by id<span class="token punctuation">:</span> row<span class="token punctuation">[</span><span class="token string">"id"</span><span class="token punctuation">]</span> update_keys <span class="token operator">=</span> row<span class="token punctuation">.</span>to_h<span class="token punctuation">.</span><span class="token function">except</span><span class="token punctuation">(</span><span class="token string">"id"</span><span class="token punctuation">)</span><span class="token punctuation">.</span>keys <span class="token keyword">if</span> update_keys<span class="token punctuation">.</span>blank<span class="token operator">?</span> <span class="token keyword">next</span> <span class="token keyword">unless</span> object object<span class="token punctuation">.</span>assign_attributes row<span class="token punctuation">.</span>to_h instances <span class="token operator"><</span><span class="token operator"><</span> object <span class="token keyword">if</span> object <span class="token keyword">end</span> <span class="token constant">Product</span><span class="token punctuation">.</span>import instances<span class="token punctuation">,</span> on_duplicate_key_update<span class="token punctuation">:</span> update_keys<span class="token punctuation">,</span> validate<span class="token punctuation">:</span> <span class="token keyword">false</span> <span class="token keyword">end</span> |
Bằng cách sử dụng activerecord-import, khi import khoảng 500.000 bản ghi từ hơn 1 giờ xuống còn dưới 3 phút, quá nhanh đúng không.
Hiểu hơn về việc thao tác với cơ sở dữ liệu
Tại thời điểm này thì mình khá là hài lòng khi import với dữ liệu lớn, tuy nhiên bạn có hiểu được tại sao nhiều câu query nhỏ lại chậm hơn rất nhiều so với 1 câu query lớn không.
Mình chỉ hiểu đơn giản là với một câu query thì khoảng thời thời gian để gọi đến nó khá là mất thời gian, còn việc thực hiện thì tương đối nhanh.
Đúng vậy, khi ActiveRecord thực hiện thao tác với insert row by row, nó sẽ truy cập vào cơ sở dữ liệu bằng một câu lệnh chèn, và dĩ nhiên việc chạy sql cho một lần chèn là không mất nhiều thời gian nhưng số lần truy nhập vào csdl để mở 1 transaction, sau đó hoàn thành nó thì lại mất tương đối nhiều thời gian. Dẫn đến việc thường xuyên phải hits vào database mà không đem lại tác dụng gì còn khiến giảm perfomance.
4. Bulk insert với rails 6
Nếu bạn đang dùng rails 6 thì xin chúc mừng nhé, vấn đề insert lượng lớn dữ liệu đã được hỗ trợ tận răng rồi nhé.
Bắt đầu từ rails 6 thì đã bổ sung thêm insert_all
, insert_all!
và upsert_all
vào ActiveRecord::Persistence
insert_all
Sử dụng insert_all giúp chúng ta có thể thực hiện chèn số lượng lớn như ví dụ dưới đây
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | result <span class="token operator">=</span> <span class="token constant">User</span><span class="token punctuation">.</span><span class="token function">insert_all</span><span class="token punctuation">(</span> <span class="token punctuation">[</span> <span class="token punctuation">{</span> name<span class="token punctuation">:</span> <span class="token string">"Sam"</span><span class="token punctuation">,</span> email<span class="token punctuation">:</span> <span class="token string">"<a href="/cdn-cgi/l/email-protection" class="__cf_email__">[email protected]</a>"</span> <span class="token punctuation">}</span><span class="token punctuation">,</span> <span class="token punctuation">{</span> name<span class="token punctuation">:</span> <span class="token string">"Sum"</span><span class="token punctuation">,</span> email<span class="token punctuation">:</span> <span class="token string">"<a href="/cdn-cgi/l/email-protection" class="__cf_email__">[email protected]</a>"</span> <span class="token punctuation">}</span> <span class="token punctuation">]</span> <span class="token punctuation">)</span> <span class="token comment"># Bulk Insert (2.3ms) INSERT INTO "users"("name","email")</span> <span class="token comment"># VALUES("Sam", "<a href="/cdn-cgi/l/email-protection" class="__cf_email__">[email protected]</a>"...)</span> <span class="token comment"># ON CONFLICT DO NOTHING RETURNING "id"</span> puts result<span class="token punctuation">.</span>inspect <span class="token comment">#<ActiveRecord::Result:0x00007fb6612a1ad8 @columns=["id"], @rows=[[1], [2]],</span> <span class="token variable">@hash_rows</span><span class="token operator">=</span><span class="token keyword">nil</span><span class="token punctuation">,</span> <span class="token variable">@column_types</span><span class="token operator">=</span> <span class="token punctuation">{</span><span class="token string">"id"</span><span class="token operator">=</span><span class="token operator">></span><span class="token comment">#<ActiveModel::Type::Integer:0x00007fb65f420078 ....></span> puts <span class="token constant">User</span><span class="token punctuation">.</span>count <span class="token operator">=</span><span class="token operator">></span> <span class="token number">2</span> |
Như đã đề cập ở trên,hãy để ý ON CONFLICT DO NOTHING RETURNING "id"
trong truy vấn. Điều này được hỗ trợ bởi cơ sở dữ liệu SQLite và PostgreQuery. Nếu có xung đột hoặc vi phạm khóa duy nhất trong quá trình chèn số lượng lớn, nó sẽ bỏ qua bản ghi xung đột và tiến hành chèn bản ghi tiếp theo.
insert_all!
Nếu cần đảm bảo tất cả các hàng được chèn, chúng ta có thể sử dụng insert_all!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | result <span class="token operator">=</span> <span class="token constant">User</span><span class="token punctuation">.</span><span class="token function">insert_all</span><span class="token punctuation">(</span> <span class="token punctuation">[</span> <span class="token punctuation">{</span> name<span class="token punctuation">:</span> <span class="token string">"Sam"</span><span class="token punctuation">,</span> email<span class="token punctuation">:</span> <span class="token string">"<a href="/cdn-cgi/l/email-protection" class="__cf_email__">[email protected]</a>"</span> <span class="token punctuation">}</span><span class="token punctuation">,</span> <span class="token punctuation">{</span> name<span class="token punctuation">:</span> <span class="token string">"Sum"</span><span class="token punctuation">,</span> email<span class="token punctuation">:</span> <span class="token string">"<a href="/cdn-cgi/l/email-protection" class="__cf_email__">[email protected]</a>"</span> <span class="token punctuation">}</span> <span class="token punctuation">]</span><span class="token punctuation">,</span> returning<span class="token punctuation">:</span> <span class="token string">%w[id name]</span> <span class="token punctuation">)</span> <span class="token comment"># Bulk Insert (2.3ms) INSERT INTO "users"("name","email")</span> <span class="token comment"># VALUES("Sam", "<a href="/cdn-cgi/l/email-protection" class="__cf_email__">[email protected]</a>"...)</span> <span class="token comment"># ON CONFLICT DO NOTHING RETURNING "id", "name"</span> puts result<span class="token punctuation">.</span>inspect <span class="token comment">#<ActiveRecord::Result:0x00007fb6612a1ad8 @columns=["id", "name"],</span> <span class="token variable">@rows</span><span class="token operator">=</span><span class="token punctuation">[</span><span class="token punctuation">[</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">"Sam"</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token string">"Sum"</span><span class="token punctuation">]</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token variable">@hash_rows</span><span class="token operator">=</span><span class="token keyword">nil</span><span class="token punctuation">,</span> <span class="token variable">@column_types</span><span class="token operator">=</span> <span class="token punctuation">{</span><span class="token string">"id"</span><span class="token operator">=</span><span class="token operator">></span><span class="token comment">#<ActiveModel::Type::Integer:0x00007fb65f420078 ....></span> |
upsert_all
Nếu một bản ghi tồn tại, và ta muốn cập nhật nó hoặc nếu không thì tạo một bản ghi mới thì việc này được gọi là upert.
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 | result <span class="token operator">=</span> <span class="token constant">User</span><span class="token punctuation">.</span><span class="token function">upsert_all</span><span class="token punctuation">(</span> <span class="token punctuation">[</span> <span class="token punctuation">{</span> id<span class="token punctuation">:</span> <span class="token number">1</span><span class="token punctuation">,</span> name<span class="token punctuation">:</span> <span class="token string">"Sam new"</span><span class="token punctuation">,</span> email<span class="token punctuation">:</span> <span class="token string">"<a href="/cdn-cgi/l/email-protection" class="__cf_email__">[email protected]</a>"</span> <span class="token punctuation">}</span><span class="token punctuation">,</span> <span class="token punctuation">{</span> id<span class="token punctuation">:</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token comment"># trùng id</span> name<span class="token punctuation">:</span> <span class="token string">"Sam's new"</span><span class="token punctuation">,</span> email<span class="token punctuation">:</span> <span class="token string">"<a href="/cdn-cgi/l/email-protection" class="__cf_email__">[email protected]</a>"</span> <span class="token punctuation">}</span><span class="token punctuation">,</span> <span class="token punctuation">{</span> id<span class="token punctuation">:</span> <span class="token number">2</span><span class="token punctuation">,</span> name<span class="token punctuation">:</span> <span class="token string">"Charles"</span><span class="token punctuation">,</span> <span class="token comment"># cập nhật tên</span> email<span class="token punctuation">:</span> <span class="token string">"<a href="/cdn-cgi/l/email-protection" class="__cf_email__">[email protected]</a>"</span> <span class="token punctuation">}</span><span class="token punctuation">,</span> <span class="token punctuation">{</span> id<span class="token punctuation">:</span> <span class="token number">3</span><span class="token punctuation">,</span> <span class="token comment"># tạo mới một bản ghi chưa có</span> name<span class="token punctuation">:</span> <span class="token string">"David"</span><span class="token punctuation">,</span> email<span class="token punctuation">:</span> <span class="token string">"<a href="/cdn-cgi/l/email-protection" class="__cf_email__">[email protected]</a>"</span> <span class="token punctuation">}</span> <span class="token punctuation">]</span> <span class="token punctuation">)</span> <span class="token comment"># Bulk Insert (26.3ms) INSERT INTO `users`(`id`,`name`,`email`)</span> <span class="token comment"># VALUES (1, 'Sam new', '<a href="/cdn-cgi/l/email-protection" class="__cf_email__">[email protected]</a>')...</span> <span class="token comment"># ON DUPLICATE KEY UPDATE `name`=VALUES(`name`)</span> puts <span class="token constant">User</span><span class="token punctuation">.</span>count <span class="token operator">=</span><span class="token operator">></span> <span class="token number">3</span> |
Hàng thứ hai trong mảng đầu vào trùng lặp id = 1 nên do đó tên của người dùng sẽ là Sam's new
thay vì Sam new
.
Hàng thứ ba trong mảng đầu vào không bị trùng lặp nên nó chỉ thực hiện việc cập nhật.
Hàng thứ tư với id = 3 không có trong csdl nên do đó sẽ tạo mới ở đây.
Nguồn
https://medium.com/@eric_lum/importing-large-datasets-in-ror-why-you-should-use-activerecord-import-26fc915e6fd0
https://blog.saeloun.com/2019/11/26/rails-6-insert-all.html