如何使用 Sequelize 和 node.js 进行批量插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29461908/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to do Bulk insert using Sequelize and node.js
提问by Uma Maheshwaraa
js + sequelize to insert 280K rows of data using JSON. The JSON is an array of 280K. Is there a way to do bulk insert in chunks. I am seeing that it takes a lot of time to update the data. When i tried to cut down the data to 40K rows it works quick. Am i taking the right approach. Please advice. I am using postgresql as backend.
js + sequelize 使用 JSON 插入 280K 行数据。JSON 是一个 280K 的数组。有没有办法在块中进行批量插入。我看到更新数据需要很多时间。当我试图将数据减少到 40K 行时，它工作得很快。我是否采取了正确的方法。请指教。我使用 postgresql 作为后端。
PNs.bulkCreate(JSON_Small)
.catch(function(err) {
console.log('Error ' + err);
})
.finally(function(err) {
console.log('FINISHED + ' \n +++++++ \n');
});
采纳答案by Evan Siroky
I utilized the cargoutility of the async library to load in up to 1000 rows at a time. See the following code for loading a csv into a database:
我利用cargo异步库的实用程序一次加载多达 1000 行。请参阅以下代码以将 csv 加载到数据库中：
var fs = require('fs'),
async = require('async'),
csv = require('csv');
var input = fs.createReadStream(filename);
var parser = csv.parse({
columns: true,
relax: true
});
var inserter = async.cargo(function(tasks, inserterCallback) {
model.bulkCreate(tasks).then(function() {
inserterCallback();
}
);
},
1000
);
parser.on('readable', function () {
while(line = parser.read()) {
inserter.push(line);
}
});
parser.on('end', function (count) {
inserter.drain = function() {
doneLoadingCallback();
}
});
input.pipe(parser);
回答by Clement
You can use Sequelize's built in bulkCreatemethod to achieve this.
您可以使用 Sequelize 的内置bulkCreate方法来实现这一点。
User.bulkCreate([
{ username: 'barfooz', isAdmin: true },
{ username: 'foo', isAdmin: true },
{ username: 'bar', isAdmin: false }
]).then(() => { // Notice: There are no arguments here, as of right now you'll have to...
return User.findAll();
}).then(users => {
console.log(users) // ... in order to get the array of user objects
})
回答by Evan Siroky
If you really want to use bulkInsert, than my previous answer is sufficient. However, you'll run out of memory if you have a lot of data! It really is best to use some built-in database method is best for this. The problem is that you're loading all the data into memory until the bulkCreate executes. If you got a million rows, you'll probably run out of memory before it even executes. Even still, if you queue it up using something like async.cargo, you'll still be waiting for the db to get back to you all while the data asyncrhonously consumes all your memory.
如果您真的想使用bulkInsert，那么我之前的回答就足够了。但是，如果您有大量数据，则会耗尽内存！确实最好使用一些内置的数据库方法最适合这个。问题是您将所有数据加载到内存中，直到执行 bulkCreate。如果你有 100 万行，你可能会在它执行之前耗尽内存。即便如此，如果您使用async.cargo 之类的东西将其排队，您仍然会等待数据库返回给您，而数据异步消耗您的所有内存。
My solution was to ditch sequelize for the loading of data (at least until they implement streaming or something (see their github issue #2454)). I ended up creating db-streamer, but it just has pg support for now. You'll want to look at streamsqlfor mysql.
我的解决方案是放弃加载数据的续集（至少在他们实现流媒体或其他东西之前（请参阅他们的github 问题 #2454））。我最终创建了db-streamer，但它现在只有 pg 支持。您将需要查看mysql 的streamsql。
回答by vitaly-t
The following question has the same answer that you need here: NodeJS, promises, streams - processing large CSV files
以下问题与您在此处需要的答案相同： NodeJS、promises、streams - processing large CSV files
- use a stream to read the data in and to parse it;
- use the combination of methods stream.readand sequencefrom spexto read the stream and execute the queries one by one.
- 使用流读取数据并解析它；
- 使用方法stream.read和来自spex 的序列的组合来读取流并一一执行查询。
