如何使用node.js读取真正的大型JSON文件并将该文件的数据插入到MYSQL数据库中？

Question

我有一个大的JSON文件(22GB(。我想读取该文件，并使用node.js将该文件的数据输入到MySql数据库中。如何做到这一点？

这是我的数据库查询：

var query = connection.query("INSERT INTO hotels (property_id, name, address,city, state_province_name, postal_code, 
country_code, star_rate, latitude, longitude, category, rank, collect, property_collect, featured_image, breakfast_included, free_wifi_available) 
VALUES ('" + .... + "', .........;

Answer 1

我找到了一个解决方案。谢谢所有试图帮助我的人。

这是我的数据库连接(DB.js(

var mysql = require('mysql');
//connect to db
var dbCon  = mysql.createPool({
connectionLimit : 50,
host: 'xxxxx',
user: 'xxx',
password: 'xxx',
database: 'xxxxx',
waitForConnections: true,
queueLimit: 0,
});
dbCon.on('connection', function (connection) {
console.log('db pool connection');
connection.query("SET time_zone='+5:30'");
});
dbCon.on('release', function (connection) {
console.log('Connection %d released', connection.threadId);
});
module.exports = {
dbCon
};

这是我的解决方案

var express = require('express');
var app   = express();
var dbCon = require('./config/db').dbCon;
var fs = require('fs');
var readline = require('readline');
var stream = require('stream');
var data = '';
// Create a readable stream
var readerStream = fs.createReadStream('./data/myJson.jsonl');
// Set the encoding to be utf8. 
readerStream.setEncoding('UTF8');
var outstream = new stream();
//createInterface - read through the stream line by line and print out data from it
var r1 = readline.createInterface(readerStream, outstream);
var lineCount = 0;
r1.on('line', function (line) {
// increment line count
lineCount++;
data = JSON.parse(line);
saveRecord(data);
})

function saveRecord(data) {
if (typeof data["city "] !== 'undefined') {
var cityTemp = data["city"];
} else {
cityTemp = "";
}
var property_id = data["property_id"];
var name = data["name"];
var city = cityTemp;
var sql = "INSERT INTO hotels (property_id, name, city) VALUES (?, ?, ?)";
dbCon.query(sql, [property_id, name, city]);
console.log('data inserted');
}
console.log("Program Ended");

Answer 2

对于这个大文件，流媒体是最好的方式。有一个名为etl的包可以为您完成这项工作。像这个

etl.file('somefile')
.pipe(etl.mysql.upsert(pool,'testschema','testtable',{concurrency:4 }))