A Redshift Lambda Story

I ventured on a technical journey that has proven more difficult than I anticipated. On the cusp of success, I reflect on what has transpired.

I wanted to issue Amazon Redshift commands from AWS Lambda. I wanted to do this with .NET Core, as it would elegantly fit into a solution written in the same.

The issues I came across:

  • ODBC drivers don’t yet exist in .NET Core to connect to Redshift. They are supposedly coming in .NET Core 2.1, but who knows when we will have them in Lambda.
  • Node.js sucks, and I didn’t want to get involved with npm packages to talk with Redshift. I could use a Java Lambda that would have ODBC drivers available, but the rest of the solution was in .NET Core.
  • Redshift differs from PostgreSql enough that I was hesitant to use Npgsql to connect to Redshift from .NET Core.

I went through each of these issues one by one, identifying them and working through solutions. In the end, I settled on trying to get a solution deployed and working using Npgsql in .NET Core.

This presented many problems. I got everything working locally first without issue. I used a service account to connect to Redshift using SSL, created a transaction, and issued UPDATE, INSERT and DELETE statements.

However, when I tried to publish to AWS, the build failed.

It turns out that the package manifest for Npgsql 3.2.X was not correct, and there were some version mismatches in the System.Data namespace (amongst others). To get around this, I deprecated my project to .NET Core 1.0 and the version of Npgsql to 3.1.0. Alas, it published.

But on testing, it failed.

But the error was more promising - a timeout when connecting to Redshift. As I suspected, there were network issues connecting from AWS Lambda to the Redshift instance. I was close. So I enabled VPC access to the Lambda via Security Groups and worked through some DNS resolution issues in the VPCs.

And it worked.

The satisfaction was short-lived, as I ended up writing to Kinesis Firehose (and that wrote to Redshift) by the time the solution went to production.

But I was happy to prove it could be done with the tools at hand.

Tagged: #code

Posted on Jun 05, 2018