Create AWS Lambda Function to Connect Amazon Redshift with C-Sharp in Visual Studio

Tutorial shows AWS Lambda serverless application developers how to connect Amazon Redshift database using Lambda function codes built in Visual Studio project. I created the Visual Studio project codes in C-Sharp but .NET Core programmers can easily convert C# codes into VB.NET codes. One more important note about the solution provided in this AWS Lambda tutorial is that the target Amazon Redshift cluster is created in a private VPC (Virtual Private Cloud). If the Redshift Cluster is created in a public VPC (public accessible) then the criteria and Lambda function configuration will be different. Assuming that the datawarahouse platform will be in private cloud this tutorial will be helpful at least for PoC developments for connecting from Lambda function to Amazon Redshift databases and execute SQL statements on the database.

connect Amazon Redshift using AWS Lambda function

Create Visual Studio C-Sharp Project for AWS Lambda Function

It maybe interesting but I am going to start project creation steps without using Visual Studio at the beginning.

Create a folder for your C-Sharp project solution on File Explorer.

Visual Studio project folder for AWS Lambda function code

Launch Command Prompt application with Run as Administrator option

launch Command Prompt using Run as Administrator option

On Command Prompt change the directory to the file folder you have created for the Visual Studio solution.


 cd C:\My\VSProjects\ConnectRedshift

Code

Visual Studio project folder on Command Prompt

Create the new class library project using below "dotnet new" command targeting .NET Core 2.1 framework.
Please note I did not mention about C# as programming language parameter since it is default language option for "dotnet new"


 dotnet new classlib -n ConnectRedshift -f netcoreapp2.1

Code

Visual Studio programmers can refer to official reference documentation on arguments and use of dotnet new command

create C# project using dotnet new command

The command output on Command Prompt screen is as follows:

C:\My\VSProjects\ConnectRedshift>dotnet new classlib -n ConnectRedshift -f netcoreapp2.1
Hazırlanıyor...
"Class library" şablonu başarıyla oluşturuldu.

Oluşturma sonrası eylemler işleniyor...
ConnectRedshift\ConnectRedshift.csproj üzerinde 'dotnet restore' çalıştırılıyor...
C:\My\VSProjects\ConnectRedshift\ConnectRedshift\ConnectRedshift.csproj için paketler geri yükleniyor...
MSBuild dosyası C:\My\VSProjects\ConnectRedshift\ConnectRedshift\obj\ConnectRedshift.csproj.nuget.g.props oluşturuluyor.
MSBuild dosyası C:\My\VSProjects\ConnectRedshift\ConnectRedshift\obj\ConnectRedshift.csproj.nuget.g.targets oluşturuluyor.
198,76 ms içinde C:\My\VSProjects\ConnectRedshift\ConnectRedshift\ConnectRedshift.csproj için geri yükleme işlemi tamamlandı.

Geri yükleme başarılı oldu.

And the created project has following folder structure:
\ConnectRedshift
\ConnectRedshift
  Class1.cs
  ConnectRedshift.csproj
  \obj
   ConnectRedshift.csproj.nuget.cache
   ConnectRedshift.csproj.nuget.g.props
   ConnectRedshift.csproj.nuget.g.targets
   project.assets.json

C# project folder structure for Lambda function creation

At this step, C-Sharp programmer can step into Project folder on Command Prompt (using the one which is running as administrator). Then following "dotnet build" command can be executed successfully.

compile Visual Studio project using dotnet build command

If you execute the same command for the second time, you should have a similar success message

C:\My\VSProjects\ConnectRedshift\ConnectRedshift>dotnet build
Microsoft (R) Build Engine version 15.8.169+g1ccb72aefa for .NET Core
Copyright (C) Microsoft Corporation. All rights reserved.

Restoring packages for C:\My\VSProjects\ConnectRedshift\ConnectRedshift\ConnectRedshift.csproj...
Generating MSBuild file C:\My\VSProjects\ConnectRedshift\ConnectRedshift\obj\ConnectRedshift.csproj.nuget.g.props.
Restore completed in 179.22 ms for C:\My\VSProjects\ConnectRedshift\ConnectRedshift\ConnectRedshift.csproj.
ConnectRedshift -> C:\My\VSProjects\ConnectRedshift\ConnectRedshift\bin\Debug\netcoreapp2.1\ConnectRedshift.dll

Build succeeded.
0 Warning(s)
0 Error(s)

Time Elapsed 00:00:02.07

Now C-Sharp developer is ready to launch Visual Studio 2017 with Run as Administrator option.

launch Visual Studio as Administrator for Lambda function project

Open the project we have created recently using Visual Studio IDE following menu options File > Open > Project/Solution

open C-Sharp project with Visual Studio

Select ConnectRedshift.csproj to open within Visual Studio 2017 IDE

C# project to create AWS Lambda function in Visual Studio 2017

This is the view of the project being created for AWS Lambda function on Solution Explorer windows of Visual Studio 2017.

AWS Lambda project structure on Visual Studio Solution Explorer

Delete the "Class1.cs" and create a new class file with a meaningful name. For example RedshiftConnector.cs
This class name will be a part of the Lambda function handler

Following screen is a part of the AWS Lambda function definition. So it is better to have meaningful handler name here. I will try to explain the function handler name and how to define it properly in Lambda creation screen in detail later. Actually, there is not a task for the developer at this step. We will cover AWS Lambda creation later.

AWS Lambda function handler

On Project Explorer window right click on the Dependencies and display context menu to choose "Manage NuGet Packages..." command.

manage NuGet packages within Visual Studio

C-Sharp programmers can install following NuGet packages:
Amazon.Lambda.Core
Amazon.Lambda.Serialization.Json
Npgsql.EntityFrameworkCore.PostgreSQL
besides default installed package
Microsoft.NETCore.App

In Browse tab searh for the NuGet package. When you find the correct package select it then click Install

install NuGet packages on Visual Studio 2017

In fact, Npgsql.EntityFrameworkCore.PostgreSQL package is important for developers which will enable us connect to Redshift databases which is based on PostgreSQL.

Npgsql.EntityFrameworkCore.PostgreSQL package for Redshift database connection

Now the project Dependencies has changed into following

project dependencies after NuGet packages are installed

If Visual Studio programmer displays ConnectRedshift.csproj file content using menu options "Project > Edit ConnectRedshift.csproj", the same dependencies can be viewed.

ConnectRedshift.csproj project file contents:


 <Project Sdk="Microsoft.NET.Sdk">

 <PropertyGroup>

 <TargetFramework>netcoreapp2.1</TargetFramework>

 </PropertyGroup>

 <ItemGroup>

 <PackageReference Include="Amazon.Lambda.Core" Version="1.1.0" />

 <PackageReference Include="Amazon.Lambda.Serialization.Json" Version="1.5.0" />

 <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="2.2.0" />

 </ItemGroup>

 </Project>

Code

At this point, Visual Studio developer can test executing "dotnet lambda package" command which we will use later for deployment. Please keep in mind that we have not yet implemented required coding in our C# project for Lambda requirement.


 dotnet lambda package -c Release -o ../RedshiftConnector.zip -f netcoreapp2.1

Code

I think the programmers have experienced following error message

No executable found matching command "dotnet-lambda"

This is because we did not install one require NuGet package from Amazon "Amazon.Lambda.Tools"
Unfortunately, it is not possible to install this package using NuGet Package Manager.
The developer will probably experience following error message

Package 'Amazon.Lambda.Tools 3.2.3' has a package type 'DotnetTool' that is not supported by project 'ConnectRedshift'.

package has a type DotnetTool that is not supported by project

Just open the project file from Visual Studio menu as I described before and add following line in "ItemGroup" section


 <DotNetCliToolReference Include="Amazon.Lambda.Tools" Version="2.2.0" />

Code

The last status of the AWS Lambda project file will be as follows

<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<TargetFramework>netcoreapp2.1</TargetFramework>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Amazon.Lambda.Core" Version="1.1.0" />
<PackageReference Include="Amazon.Lambda.Serialization.Json" Version="1.5.0" />
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="2.2.0" />
<DotNetCliToolReference Include="Amazon.Lambda.Tools" Version="2.2.0" />
</ItemGroup>
</Project>

Programmers will realize that we have enabled Amazon.Lambda.Tools of version "2.2.0"
On the other hand, using NuGet Package Manager we tried to install version "3.2.3"
It is possible to install the version "2.2" using NuGet Package Manager by selecting the correct version from version list instead of trying to install the latest stable version of Amazon.Lambda.Tools package

Now, using the Command Prompt that we have launched as administrator, it is possible to execute "dotnet lambda" command successfully.


 dotnet lambda package -c Release -o ../RedshiftConnector.zip -f netcoreapp2.1

Code

Here is the result

C:\My\VSProjects\ConnectRedshift\ConnectRedshift>dotnet lambda package -c Release -o ../RedshiftConnector.zip -f netcoreapp2.1
Amazon Lambda Tools for .NET Core applications (2.2.0)
Project Home: https://github.com/aws/aws-extensions-for-dotnet-cli, https://github.com/aws/aws-lambda-dotnet

Executing publish command
... invoking 'dotnet publish', working folder 'C:\My\VSProjects\ConnectRedshift\ConnectRedshift\bin\Release\netcoreapp2.1\publish'
... publish: .NET Core için Microsoft (R) Build Engine sürüm 15.8.169+g1ccb72aefa
... publish: Telif Hakkı (C) Microsoft Corporation. Tüm hakları saklıdır.
... publish: C:\My\VSProjects\ConnectRedshift\ConnectRedshift\ConnectRedshift.csproj için paketler geri yükleniyor...
... publish: 38,69 ms içinde C:\My\VSProjects\ConnectRedshift\ConnectRedshift\ConnectRedshift.csproj için geri yükleme işlemi tamamlandı.
... publish: 405,43 ms içinde C:\My\VSProjects\ConnectRedshift\ConnectRedshift\ConnectRedshift.csproj için geri yükleme işlemi tamamlandı.
... publish: ConnectRedshift -> C:\My\VSProjects\ConnectRedshift\ConnectRedshift\bin\Release\netcoreapp2.1\linux-x64\ConnectRedshift.dll
... publish: ConnectRedshift -> C:\My\VSProjects\ConnectRedshift\ConnectRedshift\bin\Release\netcoreapp2.1\publish\
Zipping publish folder C:\My\VSProjects\ConnectRedshift\ConnectRedshift\bin\Release\netcoreapp2.1\publish to C:\My\VSProjects\ConnectRedshift\RedshiftConnector.zip
... zipping: Amazon.Lambda.Core.dll
... zipping: Amazon.Lambda.Serialization.Json.dll
... zipping: ConnectRedshift.deps.json
... zipping: ConnectRedshift.dll
... zipping: ConnectRedshift.pdb
... zipping: ConnectRedshift.runtimeconfig.json
... zipping: Microsoft.EntityFrameworkCore.Abstractions.dll
... zipping: Microsoft.EntityFrameworkCore.dll
... zipping: Microsoft.EntityFrameworkCore.Relational.dll
... zipping: Microsoft.Extensions.Caching.Abstractions.dll
... zipping: Microsoft.Extensions.Caching.Memory.dll
... zipping: Microsoft.Extensions.Configuration.Abstractions.dll
... zipping: Microsoft.Extensions.Configuration.Binder.dll
... zipping: Microsoft.Extensions.Configuration.dll
... zipping: Microsoft.Extensions.DependencyInjection.Abstractions.dll
... zipping: Microsoft.Extensions.DependencyInjection.dll
... zipping: Microsoft.Extensions.Logging.Abstractions.dll
... zipping: Microsoft.Extensions.Logging.dll
... zipping: Microsoft.Extensions.Options.dll
... zipping: Microsoft.Extensions.Primitives.dll
... zipping: Newtonsoft.Json.dll
... zipping: Npgsql.dll
... zipping: Npgsql.EntityFrameworkCore.PostgreSQL.dll
... zipping: Remotion.Linq.dll
... zipping: System.Interactive.Async.dll
... zipping: System.Runtime.CompilerServices.Unsafe.dll
Lambda project successfully packaged: C:\My\VSProjects\ConnectRedshift\RedshiftConnector.zip

Lambda project successfully packaged

Actually, it is important to see the message "Lambda project successfully packaged" to be sure that you have built the zip file for Lambda function deployment.

If as a Visual Studio .NET programmer try to package the AWS Lambda project by executing dotnet lambda package command on a non-administrator Command Prompt, following errors will occur.

NuGet.targets error: Unable to load the service index for source

Output message "Failed to create application package" and following error messages should warn the Lambda developer to check permissions for the Command Prompt tool and (Visual Studio IDE) affecting the execution of "dotnet" and "dotnet lambda" commands.

C:\Program Files\dotnet\sdk\2.1.403\NuGet.targets(114,5): error : Unable to load the service index for source https://api.nuget.org/v3/index.json
C:\Program Files\dotnet\sdk\2.1.403\NuGet.targets(114,5): error : No such host is known

C# programmers can now start coding for Lambda function which will enable us to connect to Amazon Redshift databases in a serverless model.

Open RedshiftConnector.cs class code and import following libraries with "using namespaces" statement.


 using Amazon.Lambda.Core;

 using Npgsql;

Code

Additionally we require to use LambdaSerializer attribute.
There is an explanation on LambdaSerializer requirement and how it is managed in a project following this documentation.
But briefly, we can say for use of input and output data in a AWS Lambda function programmers require this serialization library.


 [assembly: LambdaSerializer(typeof(Amazon.Lambda.Serialization.Json.JsonSerializer))]

Code

The AWS Lambda handler function is structured as follows:


 public string FunctionHandler(string input, ILambdaContext context)

 {

 ...

 }

Code

Now we can add basic C-Sharp codes that will connect to Amazon Redshift within this AWS Lambda function and execute a SQL Select statement on Redshift database.

For the Amazon Redshift database connection, I use NpgsqlConnectionStringBuilder object as follows:


 NpgsqlConnectionStringBuilder builder = new NpgsqlConnectionStringBuilder();

 builder.Host = "172.31.17.125"; // use private IP address of Redshift cluster

 builder.Port = 5439; // default port

 builder.Database = "dev"; // default Redshift database

 builder.Username = "awsuser"; // default Redshift master user

 builder.Password = "****"; // password of the database user

 builder.ServerCompatibilityMode = ServerCompatibilityMode.Redshift;

Code

If you are new with Redshift and did not yet create an additional Redshift database and additional Redshift database users, you can build the connection string using the entries you have provided at Redshift cluster creation steps.

configure Redshift database and master user

For the private IP address of your Redshift database to use in database connection string, open Redshift service on related region using AWS Management Console.
On Redshift dashboard, click to Clusters menu item on the left.
From the Redshift clusters list, click on your target cluster and display details.
The private IP address will be displayed at the bottom of the cluster details screen under "Node IP Addresses" section

Redshift cluster private IP address on AWS Management Console

At this step, I assume the Lambda developer has correctly created the connection string using the NpgsqlConnectionStringBuilder object.
If as a programmer, you have used the Public IP address or the endpoint of the Redshift cluster, since these addresses will not be available from AWS Lambda function by default you will probably experience time out errors when Lambda function is executed.

Now, we can define a database connection then open this connection and exexcute our SQL command using below code block.
Of course, the CSharp programmers should do exception handling with Try-Catch, use variables from Lambda function input parameters, etc according to the requirement.
In this AWS Lambda tutorial, I will just concantrate on building and testing database connection to Redshift cluster.


 string tableList = string.Empty;

 using (var conn = new NpgsqlConnection(builder.ConnectionString.ToString()))

 {

  conn.Open();

  if (conn.State == System.Data.ConnectionState.Open)

  {

   using (var cmd = new NpgsqlCommand())

   {

    cmd.Connection = conn;

    cmd.CommandText = "select table_name from information_schema.tables;";

    using (var reader = cmd.ExecuteReader())

    {

     while (reader.Read())

     {

      tableList = string.Concat(tableList, ",", reader.GetString(0));

     }

    }

   }

  }

 }

 return tableList;

Code

That is all for C# coding part for the Lambda function handler

Following is the complete C-Sharp code required for this Amazon Redshift Connector Lambda function


 using System;

 using System.Collections.Generic;

 using System.Text;

 

 using Amazon.Lambda.Core;

 using Npgsql;

 

 [assembly: LambdaSerializer(typeof(Amazon.Lambda.Serialization.Json.JsonSerializer))]

 

 namespace ConnectRedshift

 {

  class RedshiftConnector

  {

   public string FunctionHandler(string input, ILambdaContext context)

   {

    string tableList = string.Empty;

 

    NpgsqlConnectionStringBuilder builder = new NpgsqlConnectionStringBuilder();

    builder.Host = "172.31.17.125"; // use private IP address of Redshift cluster

    builder.Port = 5439; // default port

    builder.Database = "dev"; // default Redshift database

    builder.Username = "awsuser"; // default Redshift master user

    builder.Password = "****"; // password of the database user

    builder.ServerCompatibilityMode = ServerCompatibilityMode.Redshift;

 

    using (var conn = new NpgsqlConnection(builder.ConnectionString.ToString()))

    {

     conn.Open();

     if (conn.State == System.Data.ConnectionState.Open)

     {

      using (var cmd = new NpgsqlCommand())

      {

       cmd.Connection = conn;

       cmd.CommandText = "select table_name from information_schema.tables;";

       using (var reader = cmd.ExecuteReader())

       {

        while (reader.Read())

        {

         tableList = string.Concat(tableList, ",", reader.GetString(0));

        }

       }

      }

     }

    }

    return tableList;

 

   }

  }

 }

Code

Let's return to Command Prompt tool (in Administrator mode) to build the Visual Studio project and package into a zip file for deployment as AWS Lambda function.


 C:\My\VSProjects\ConnectRedshift\ConnectRedshift>dotnet build

 C:\My\VSProjects\ConnectRedshift\ConnectRedshift>dotnet lambda package -c Release -o ../RedshiftConnector.zip -f netcoreapp2.1

Code

If you open the file folder where the solution is using File Explorer, you will see the package in zip file format. We will soon use this zip file as the code source of our AWS Lambda function.

C-Sharp package for AWS Lambda function deployment

Create Lambda Function on AWS Management Console

Now, AWS Lambda serverless application developers are ready to create their Redshift Connector Lambda function using Amazon AWS Management Console. First log on to your account at AWS Management Console and switch to the region where you have your Amazon Redshift Cluster. Open AWS Lambda service and click on Functions menu item on the left of the screen which will navigate you to the list of Lambda functions.

Click on "Create function" button.
We are starting to build our serverless function with "Authot from scratch" option. We will not use a blueprint template or use an existing serverless app from AWS Serverless Application Repository
You can name your serverless Lambda function as "RedshiftConnector"
From "Runtime" options select ".Net Core 2.1 (C#/PowerShell)"
From "Permissions" you can choose option "Create a new role with basic Lambda permissions" or select "Use an existing role" option. What is important at this step is that, to execute this Redshift-Connector Lambda function we will require "AWSLambdaVPCAccessExecutionRole" policy attached to the selected role.

AWS serverless lambda function creation

Press "Create function" button to continue.

Now Lambda developers can configure and create code of the serverless application.
By the way, a new role is also created for this Lambda application too. We will configure it soon and attach policy named AWSLambdaVPCAccessExecutionRole

First, "Function code" section.

upload AWS function code using .zip file

Click on "Upload" button and point to the .zip file we have created for our C-Sharp project

upload C# project package file for AWS Lambda function code

Runtime is already selected at previous screen.
There is a default function handler name in "Handler" textbox.
But we have to modify it according to your C-Sharp project settings.
If you click on the handler info link, you will see below tip for handler

A value of the form assembly::namespace.class-name::method-name. For example, "MyApp::Example.Hello::MyHandler" calls the MyHandler method defined in MyApp.dll.

According to this definition, out function handler will be:
ConnectRedshift::ConnectRedshift.RedshiftConnector::FunctionHandler

You can check it also from the Visual Studio project we have created. Open the RedshiftConnector.cs file codes. Here programmers can find the namespace, class and method names easily.

build AWS function handler name using project namespace class and function names

Also in Project Properties screen, you can find the assembly name

assembly name in Visual Studio project properties for Lambda function handler

We can again Save our Lambda project work up to this time.

Just to note for PostgreSQL developers who want to connect to PostgreSQL databases (there is also an Amazon RDS service including PostgreSQL databases), you can simply provide your connection details and remove the ServerCompatibilityMode code line in order to successfully connect to a PostgreSQL database instance and execute SQL commands. The modification in Lambda function after this point is only required for connecting to Redshift database. These additional steps required to connect Amazon Redshift cluster includes Network configuration and IAM role adjustments.

If the Lambda developer tries to connect to Amazon Redshift without implementing additional required configuration a task time out error message similar to "Task timed out after 15.02 seconds" will be the result of Lambda function test.

Let's continue with configuring Network settings.
By default the Virtual Private Cloud (VPC) option is set to "No VPC"

Since we want to reach Amazon Redshift cluster created with in a specific VPC from current Lambda function, we have to select the VPC (which the Redshift cluster is created in) from the dropdown list.

For Lambda serverless applications that don't require to reach to a specific resource and just consume other AWS services like translate service, there is not a need to configure this Network configuration section.

Of course when you select the VPC, you will have to select the Subnets too. Since there is not a place that a specific subnet preferred during Amazon Redshift cluster formation, I selected all defined subnets from the list.

network settings for VPC and Subnets for AWS Lambda function connecting to Redshift

Before we save the Lambda function configuration changes, we have to select VPC Security groups too. I choose the "default" security group at this step now.
In Inbound and Outbound rules tabs, the source and destination ports allowed can be viewed below.

VPC security groups for AWS Lambda function

Now Lambda developer should modify the IAM role created for the execution of this serverless application. You will see a link to IAM console to manage related role.

AWS Lambda function execution role

When the Summary screen is displayed for the role, by using "Attach policies" button filter for "AWSLambdaVPCAccessExecutionRole" policy and attach to the IAM Role that AWS Lambda function is using during execution.

Following configuration is showing after modifications providing access to VPC resources

enable AWS Lambda function to access VPC resources using IAM policy

After completion of IAM role modification for AWSLambdaVPCAccessExecutionRole policy, serverless Lambda developers can test their function.
It is now time to tet Amazon Redshift database connection from AWS Lambda function.

Press "Test" button and configure test event.
Since in this tutorial, we did not require an input parameter value, you can simply use "Hello World" template but replace all input with a dummy text as follows

create test event for AWS Lambda function

After test configuration is completed, press Test button to see if AWS Lambda function is successfull to connect to Amazon Redshift database.

test results of AWS serverless application Lambda function execution

You see the output or returned string from AWS serverless Lambda function RedshiftConnector is displaying the list of table names within Redshift database connected. The Amazon Redshift cluster was created in a private VPC and serverless application developers could connect with a AWS Lambda function which was developed with C# based on .Net Core 2.1