Golang Redshift Example

The Problem

AWS have a great selection of example Golang scripts here. However, at the time of writing this article, there is one notable exception: Redshift!

During this short article we will create two Lambdas that show two examples for querying data in a Redshift database.

The Solution

Option 1) Using lib/pq package

The first option is to make standard postgres queries using the pq package. This is the quick and easy option.

package main

import (
    "database/sql"
    "fmt"
    "log"

    "context"

    "github.com/aws/aws-lambda-go/events"
    "github.com/aws/aws-lambda-go/lambda"

    _ "github.com/lib/pq"
)

func handler(ctx context.Context, request events.APIGatewayProxyRequest) error {
    log.Println("Using the postgres approach to query Redshift")

    // The postgres apporach
    connStr := "postgres://user:pass@your-cluster:5439/database"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    var (
        tablename string
    )

    // Query the database
    q := "SELECT DISTINCT tablename FROM PG_TABLE_DEF WHERE schemaname = 'public';"
    rows, err := db.Query(q)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    // Print data to logs
    for rows.Next() {
        err := rows.Scan(&tablename)
        if err != nil {
            log.Fatal(err)
        }
        log.Println(tablename)
    }

    // Handle errors
    err = rows.Err()
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println(rows)

    return nil
}

func main() {
    lambda.Start(handler)
}

Option 2) Using RedshiftData API

The second option is to utilise the RedshiftData API.

AWS have a RedshiftData SDK ready to use here. At the time of writing the documentation is great - but the examples are non-existent so getting started quickly requires some effort.

The main benefit of using the RedshiftData API is that you don't need to persist a database connection when running long-lasting queries. This is great if you ran large queries and want to keep your lambda execution times down.

package main

import (
    "time"

    "github.com/aws/aws-sdk-go-v2/config"
    "github.com/aws/aws-sdk-go-v2/service/redshiftdata"
    "github.com/aws/aws-sdk-go/aws"

    "log"

    "context"

    "github.com/aws/aws-lambda-go/events"
    "github.com/aws/aws-lambda-go/lambda"
)

func handler(ctx context.Context, request events.APIGatewayProxyRequest) error {
    log.Println("Using the RedshiftData SDK to query Redshift")

    // Connect to RedshiftData API
    cfg, err := config.LoadDefaultConfig(context.TODO(), config.WithRegion("eu-west-2"))
    if err != nil {
        log.Fatalf("Unable to load SDK config, %v", err)
        return err
    }
    svc := redshiftdata.NewFromConfig(cfg)

    // Fire off a query
    statement, statementErr := svc.ExecuteStatement(ctx, &redshiftdata.ExecuteStatementInput{
        ClusterIdentifier: aws.String("CLUSTER"),
        Database:          aws.String("MYDATBASE"),
        Sql:               aws.String("SELECT DISTINCT tablename FROM PG_TABLE_DEF WHERE schemaname = 'public';"),
        SecretArn:         aws.String("arn:aws:secretsmanager:REGION:ACCOUNTID:secret:MYDATBASE/SOMEID"),
    })
    if statementErr != nil {
        log.Println("Error running query")
        log.Printf("%#v\n", statementErr.Error())
    }
    log.Println("Statement ID for retrieving results later", statement.Id)

    // Wait for a response. You can add an automatic retry here
    time.Sleep(1 * time.Second)

    // Retrieve results
    result, resultErr := svc.GetStatementResult(ctx, &redshiftdata.GetStatementResultInput{
        Id: statement.Id,
    })
    if resultErr != nil {
        log.Println(resultErr)
        return resultErr
    }

    // Do something with results
    log.Printf("%#v\n", result)
    return nil
}

func main() {
    lambda.Start(handler)
}

Summary

Hopefully this gives you a head start in your next project. AWS has great support for Golang SDKs, but unfortunately the documentation and example use cases are still lacking compared to boto3.

It's also worth noting that if you're querying the database directly then AWS SecretsManager has an excellent Golang snippet so that you don't need to hardcode any credentials.

If you're working in a private subnet, then don't forget to expose endpoint interfaces for SecretsManager and RedshiftData.