What are some ways of inserting data into Amazon Redshift using node.js?
This should be pretty straightforward, but I was unable to find any concrete example for efficient loading.
Add a comment
|
1 Answer
One way of doing that would be to load the data into S3 using AWS node.js SDK (there's an example in the documentation), then use node-pg to COPY the data into Redshift :
var pg = require('pg');
var conString = "postgres://user:password@db-endpoint:port/schema";
var client = new pg.Client(conString);
client.connect(function(err) {
if(err) {
return console.error('could not connect to postgres', err);
}
//assuming credentials are exported as enviornment variables,
//both CLI- and S3cmd-style are supported here.
//Also, you may want to specify the file's format (e.g. CSV),
//max errors, etc.
var copyCmd = 'copy my_redshift_table from \'s3://your_bucket/your_file\' credentials \'aws_access_key_id='
+ (process.env.AWS_ACCESS_KEY || process.env.AWS_ACCESS_KEY_ID)
+ ';aws_secret_access_key='
+ (process.env.AWS_SECRET_KEY || process.env.AWS_SECRET_ACCESS_KEY)
+ '\'';
client.query(copyCmd, function(err, result) {
if(err) {
return console.error('error running query', err);
}
logger.info("redhshift load: no errors, seem to be successful!");
client.end();
});
});
Note that you don't need any special drivers for this to run.
3 Comments
etov
Accepted own answer because question is resolved and enough time had passed. However I'll happily change to any better answer if one appears.
jdelman
Do you have any experience using pg's
pool module to issue COPY commands?etov
@js87 - no, I didn't try using the pool module. If you do and it turns out to be better than plain pg please post an additional answer!