Skip to main content

Node.js - Querying from MS SqlServer and loading into DynamoDB

This is a script made with a collation of internet examples.

The goal here is to load a DynamoDB table with records from a SQLServer table.

For this example I'm using a local installation of DynamoDB.

var Connection = require('tedious').Connection;
var Request = require('tedious').Request;
var TYPES = require('tedious').TYPES;
var fs = require('fs');
var AWS = require("aws-sdk");

console.time("dbsave");

// SQL Server configuration
var config = {
userName: 'username',
password: 'password',
server: 'serverIP',

options: {
//instanceName: 'MSSQLSERVER', -- didnt have to use this option
port: 1433,
database: 'dbname'
}
}


// DynamoDB configuration
AWS.config.update({
region: "us-west-2",
endpoint: "http://localhost:8000"
});

docClient = new AWS.DynamoDB.DocumentClient();


// Connect to SQLServer
connection = new Connection(config);

connection.on('connect', function (err) {
if (err) return console.error(err); // <-
console.log("Connected");
executeStatement();
});



function executeStatement() {
var options = { keepNulls: true };
var result = [];
// Read rows from SQLServer table
request = new Request("select * from Movies", function (err, rowCount, rows) {
if (err) return console.error(err)
else console.log("request rowcount: " + rowCount); // <-
//console.log(rows);
});

// For each row create the result array[]{year,title}
request.on("row", function (columns) {
var item = {};
columns.forEach(function (column) {
item[column.metadata.colName] = column.value;
});
result.push(item);
});

// When all rows are read, save them to DynamoDB
request.on('requestCompleted', function (rowCount, more) {

console.log("Importing movies into DynamoDB. Please wait.");
result.forEach(function (movie) {
var params = {
TableName: "Movies",
Item: {
"year": movie.year,
"title": movie.title
}
};
docClient.put(params, function (err, data) {
if (err) {
console.error("Unable to add movie", movie.title);
// } else {
// console.log("PutItem succeeded:", movie.title);
}
});
});
console.log('PutItem Ended');
end();
});

// Start processing
connection.execSql(request);
};


end = function(err, saved) {
//var i;
//console.log(( err || !saved )?"Error":"Saved");
//if(--i === 1){
console.timeEnd("dbsave");
//}
};


Comments

Post a Comment

Popular posts from this blog

NodeJS MSSQL connection error "EINVALIDSTATE"

It took me some time to find the reasons for the connection error below: Requests can only be made in the LoggedIn state, n…", code: "EINVALIDSTATE" I was trying to connect to MS Sql server using Node JS TEDIOUS package. The code from the Microsoft  is very simple and didn't return a more complete error message. This is the original code: ... var connection = new Connection(config);  connection.on( 'connect' , function ( err ) {  // If no error, then good to proceed.  console .log( "Connected" );  executeStatement(); }); ... Checking the internet a bit more, I found a way to get more insight into the error: ... var  connection =  new  Connection(config);  connection.on( 'connect' ,  function ( err )  {  if (err) return console.error(err);   console .log( "Connected" );  executeStatement(); }); ... That raised: ConnectionError {message: "Failed to connect to 10.0.0.100:1433 - c...
2 comments
Read more

AWS Athena and S3 Partitioning

Athena is a great tool to query your data stored in S3 buckets. To have the best performance and properly organize the files I wanted to use partitioning. Although very common practice, I haven't found a nice and simple tutorial that would explain in detail how to properly store and configure the files in S3 so that I could take full advantage of the Athena partitioning features. After some testing, I managed to figure out how to set it up. This is what I did: Starting from a CSV file with a datetime column, I wanted to create an Athena table, partitioned by date. A basic google search led me to this  page , but It was lacking some more detailing. The biggest catch was to understand how the partitioning works. Based on a datetime column(processeddate), I had to split the date into the year, month and day components to create new derived columns, which in turn I'll use as the partition keys to my table Example of d...
4 comments
Read more

SQL Server connection using windows authentication from a different domain

I had to connect to a SQL Server database from my laptop that is not on the same domain as the DB server. I'm using JTDS to connect to the DB, in this case using Talend. On the first few attempts I received this error: The login is from an untrusted domain and cannot be used with Windows authentication After playing with many different parameter combinations,  I finally managed to have a successful connection using the following connection string: jdbc:jtds:sqlserver://<SERVERIP>:<PORT>/<DATABASE>;instance=<INSTANCE>;useNTLMv2=true;domain=<DOMAIN>;user=<LOGIN>;password=<PASSWORD> This is the screenshot of the connection configuration on Talend: And the output of the 'Test connection':
Post a Comment
Read more