I have an AWS Redshift Serverless database that I'm querying through the AWS Go SDK's redshiftdataapiservice
. Querying and all that works, but the records come back in a format that's hard to work with/comprehend.
My code is thus:
import (
"fmt"
"log"
"time"
"os"
"context"
"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 (~/.aws/config)
cfg, err := config.LoadDefaultConfig(context.TODO(), config.WithRegion(os.Getenv("AWS_REGION")))
if err != nil {
return nil, err
}
// Create a 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
}
The 2D array of *Field
s is what I'm finding hard to handle. How would I (preferably easily) map this to usable JSON, or to say an array of type structs? Or is there a way to request JSON from Redshift? I want to keep all this entirely within my Golang app if possible.
I found no official way, but the below works by creating slices of maps of column names to column values, then unmarshaling from there.
// Extracts the column name from column metadata for a given column index
func getColumnName(metadata []types.ColumnMetadata, index int) string {
if index < len(metadata) {
// We assume the metadata is in the same order as the columns in the record.
// If the column name is not set or empty, we can fallback to a default naming convention.
if metadata[index].Name != nil {
return *metadata[index].Name
}
return fmt.Sprintf("column_%d", index)
}
// Fallback if the index is out of range of the metadata slice.
return fmt.Sprintf("unknown_column_%d", index)
}
// Converts query results to JSON bytes for easy unmarshaling to structs
func queryResultsToJSON(query_results *redshiftdata.GetStatementResultOutput) ([]byte, error) {
// Convert the records to a slice of maps for JSON conversion
var records []map[string]interface{}
for _, row := range query_results.Records {
record := make(map[string]interface{})
for idx, col := range row {
// Use the column metadata to determine the name of the column
columnName := getColumnName(query_results.ColumnMetadata, idx)
// Check the type of the value and assign it to the record map
var value interface{}
switch v := col.(type) {
case *types.FieldMemberBlobValue:
value = v.Value
case *types.FieldMemberBooleanValue:
value = v.Value
case *types.FieldMemberDoubleValue:
value = v.Value
case *types.FieldMemberIsNull:
value = nil
case *types.FieldMemberLongValue:
value = v.Value
case *types.FieldMemberStringValue:
value = v.Value
}
record[columnName] = value
}
records = append(records, record)
}
// Marshal the records to JSON
jsonBytes, err := json.Marshal(records)
if err != nil {
log.Error("failed to marshal records to JSON, " + err.Error())
return nil, err
}
return jsonBytes, nil
}