Method for Mapping Redshift Query Results to Structs

[MichaelBonnet]

I have found mapping Redshift Data service query results to be quite difficult to work with. Mapping records from the *redshiftdata.GetStatementResultOutput to a struct - something quite common in Go DB operations - is horrendously complicated.

For example, say I have two tables in Redshift, which are analogous to two structs modeling those tables:

package models

type Job struct {
	JobId      string `json:"job_id" db:"job_id" form:"job_id"`
	Status     string `json:"status" db:"status" form:"status"`
	StartTime  string `json:"start_time" db:"start_time" form:"start_time"`
	EndTime    string `json:"end_time" db:"end_time" form:"end_time"`
}

type Human struct {
	Name         string  `json:"name" db:"name" form:"name"`
	Gender       string  `json:"gender" db:"gender" form:"gender"`
	Age          int     `json:"age" db:"age" form:"age"`
	HeightInches int     `json:"height_in" db:"height_in" form:"height_in"`
	WeightLbs    float64 `json:"weight_lbs" db:"weight_lbs" form:"weight_lbs"`
	Employed     bool    `json:"employed" db:"employed" form:"employed"`
}

Because of a lack of an easy way to map the results of a query to a struct or even get a JSON representation of it, I have to write some overly verbose code, and have bespoke methods for each struct:

import (
	"context"
	"errors"
	"fmt"
	"os"
	"time"
	"project/models"

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

// Execute a Redshift query and return a result statement output
func executeRedshiftQuery(sql string) (*redshiftdata.GetStatementResultOutput, error) {
	// Load the Shared AWS Configuration
	cfg, err := config.LoadDefaultConfig(context.TODO(), config.WithRegion(os.Getenv("AWS_REGION")))
	if err != nil {
		return nil, err
	}

	// Create an Amazon S3 service client
	redshiftclient := redshiftdata.NewFromConfig(cfg)
	execStmt, err := redshiftclient.ExecuteStatement(context.TODO(), &redshiftdata.ExecuteStatementInput{
		WorkgroupName: aws.String(os.Getenv("WG_NAME")),
		Database:      aws.String(os.Getenv("DB_NAME")),
		Sql:           aws.String(sql),
	})
	if err != nil {
		return nil, err
	}

	// wait for query to finish
	for {
		descStmt, err := redshiftclient.DescribeStatement(context.TODO(), &redshiftdata.DescribeStatementInput{
			Id: execStmt.Id,
		})
		if err != nil {
			return nil, err
		}

		// return an error if the query failed or aborted
		if descStmt.Status == types.StatusStringFailed || descStmt.Status == types.StatusStringAborted {
			err := errors.New("the Redshift query failed or was aborted")
			return nil, err
		} else if descStmt.Status != types.StatusStringFinished {
			time.Sleep(1 * time.Second)
			continue
		}

		break
	}

	// get the results
	resultStmt, err := redshiftclient.GetStatementResult(context.TODO(), &redshiftdata.GetStatementResultInput{
		Id: execStmt.Id,
	})
	if err != nil {
		return nil, err
	}

	return resultStmt, nil
}

// Function to convert types.FieldMemberLongValue to int
func longFieldToInt64(field types.Field) (int64, error) {
	var field_int int64
	val, ok := field.(*types.FieldMemberLongValue)
	if ok {
		field_int = val.Value
		return field_int, nil
	}
	return field_int, fmt.Errorf("not an int field")
}

// Function to convert types.FieldMemberStringValue to string
func stringFieldToString(field types.Field) (string, error) {
	var field_string string
	val, ok := field.(*types.FieldMemberStringValue)
	if ok {
		field_string = val.Value
		return field_string, nil
	}
	return field_string, fmt.Errorf("not a string field")
}

// Function to convert types.FieldMemberDoubleValue to float64
func doubleFieldToFloat64(field types.Field) (float64, error) {
	var field_float float64
	val, ok := field.(*types.FieldMemberDoubleValue)
	if ok {
		field_float = val.Value
		return field_float, nil
	}
	return field_float, fmt.Errorf("not a double field")
}

// Function to convert types.FieldMemberBooleanValue to float64
func booleanFieldToBool(field types.Field) (bool, error) {
	var field_bool bool
	val, ok := field.(*types.FieldMemberBooleanValue)
	if ok {
		field_bool = val.Value
		return field_bool, nil
	}
	return field_bool, fmt.Errorf("not a bool field")
}

// Get the most recent job
func getMostRecentJob() (*models.Job, error) {
	q := "SELECT * FROM jobs WHERE status='complete' ORDER BY start_time DESC LIMIT 1;"
	result, err := executeRedshiftQuery(q)
	if err != nil {
		return nil, err
	}

	// Field extraction and evaluation
	record := result.Records[0]
	job_id, err := stringFieldToString(record[0])
	if err != nil {
		return nil, err
	}
	status, err := stringFieldToString(record[1])
	if err != nil {
		return nil, err
	}
	start_time, err := stringFieldToString(record[2])
	if err != nil {
		return nil, err
	}
	end_time, err := stringFieldToString(record[3])
	if err != nil {
		return nil, err
	}
	job := models.Job{
		JobId:      job_id,
		Status:     status,
		StartTime:  start_time,
		EndTime:    end_time,
	}

	return &job, err
}

// Gets the results of a query that returns a set of Human records
func getHumans(q string) ([]models.Human, error) {
	result, err := executeRedshiftQuery(q)
	if err != nil {
		return nil, err
	}

	var objects []models.Human
	for _, record := range result.Records {
		// Evaluate and convert each field
		name, err := stringFieldToString(record[0])
		if err != nil {
			return nil, err
		}
		gender, err := stringFieldToString(record[1])
		if err != nil {
			return nil, err
		}
		age, err := longFieldToInt64(record[2])
		if err != nil {
			return nil, err
		}
		height, err := longFieldToInt64(record[3])
		if err != nil {
			return nil, err
		}
		weight, err := doubleFieldToFloat64(record[4])
		if err != nil {
			return nil, err
		}
		employed, err := booleanFieldToBool(record[5])
		if err != nil {
			return nil, err
		}

		o := models.Human{
			Name:          id,
			Gender:        gender,
			Age:           int(age),
			HeightInches:  int(height),
			WeightLbs:     weight,
			Employed:      employed,
		}

		objects = append(objects, o)
	}

	return objects, nil
}

Conversion/extraction helpers, bespoke functions, and magic numbers, oh my. One can only imagine the headache for a table with 50+ columns or changing the order/number of columns.

I ask for either pointers to how to do this better/in a less verbose way, or for a built-in function that either lets one map results to a slice of structs or at least generates JSON for it.