CODEWITHSUNDEEP
goamazon-redshift
Ewan Valentine
Ewan Valentine

Reputation: 3931

Redshift returns a []uint8 instead of an integer, converting between them returns incorrect values

I have a service which takes a SQL Query, runs the query on Amazon Redshift, using the database/sql drivers. However, I can't convert the result to a struct, because the queries are big data tasks on various tables, not created within this service. So I have to return a 'loose' data structure. I'm parsing the data returned into JSON and storing it in S3.

However, I'm having some odd issues with the data types returned. The queries, for numeric columns, return a map of uint8's instead of a numeric value. I understand that this is because the database driver can't have an opinion on what to convert it to because it could be imprecise. But I can't seem to be able to convert between []uint8 and an integer either.

Here's my code that queries the database:

// Execute executes SQL commands
func (r *Runner) Execute(query string, args ...interface{}) (types.Results, error) {
    var results types.Results
    rows, err := r.db.Query(query, args...)
    if err != nil {
        return results, err
    }

    columns, _ := rows.Columns()
    colNum := len(columns)

    values := make([]interface{}, colNum)
    for i := range values {
        var ii interface{}
        values[i] = &ii
    }

    for rows.Next() {
        rows.Scan(values...)
        result := make(types.Result)
        for i, colName := range columns {
            rawValue := *(values[i].(*interface{}))
            if reflect.TypeOf(rawValue).String() == "[]uint8" {
                byteVal := rawValue.([]byte)
                val := Intfrombytes(byteVal)
                log.Println("Converted:", val)
            }
            result[colName] = rawValue
        }
        results = append(results, result)
    }
    return results, nil
}

I created the following function to attempt to convert between []uint8 and uint32. 

func Intfrombytes(bytes []uint8) uint16 {
    bits := binary.LittleEndian.Uint16(bytes)
    return bits
}

However, if I insert 200 into that table, I get back 12339. The approach feels pretty flaky, generally. I'm doubting my decision to use Go for this as I'm dealing with undefined, loose data structures.

Is there a better approach to generic queries such as my example, or is there a way I can convert my numeric results into an integer?

Upvotes: 1

Views: 555

Answers (1)

Mark R.
Mark R.

Reputation: 1323

I think you might be interpreting a string ([]uint8 == []byte), actually. See https://play.golang.org/p/Rfpey2NPiI7

originalValue := []uint8{0x32, 0x30, 0x30} // "200"
bValue := []byte(originalValue) // byte is a uint8 anyway
fmt.Printf("Converted to uint16: %d\n", binary.LittleEndian.Uint16(bValue))
fmt.Printf("Actual value: %s", string(bValue))

This has bitten me before when dealing with pq and some crypto code.

Upvotes: 1

Related Questions