Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Load data into Redshift using Node.js

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.

like image 675
etov Avatar asked Jun 07 '15 14:06

etov


1 Answers

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.

like image 157
etov Avatar answered Nov 15 '22 08:11

etov