Serialization problems about athenadriver HOT 8 CLOSED

Thanks @rtfpessoa for the great question. There is an example for querying time/date data with Athena: https://github.com/uber/athenadriver/blob/master/examples/query/dml_select_time.go

Can you post one sample data stored in created_at column in your database/table? My initial guess would be the format is not compatible with any of these supported by Athena:

"date", "time", "time with time zone", "timestamp", "timestamp with time zone"

Hope this can help.

from athenadriver.

Hi @henrywoo thanks for the help.
That makes sense since the date is in RFC3339 (e.g.: 2021-01-01T12:12:12Z)

But, this also happens to int64, is it unrelated? What can be the reason?

from athenadriver.

Hi again @henrywoo,
Thanks to your tip about the format of the dates I was able to get most things working.
I think now the issue is with nillable values. Do you have any idea?

Seems like any value that comes null from the database will be read as an empty string instead of nil.

from athenadriver.

Hi @rtfpessoa Great to hear that the date issue is resolved.

For missing value handling, please refer to: https://github.com/uber/athenadriver#missing-value-handling

By default, we use empty string to replace missing values and empty string is preferred to default data.

from athenadriver.

Thanks a lot for your help.
Now it makes sense, I think at some point I noticed the SetMissingAsEmptyString but probably got the error of not setting the SetMissingAsDefault to true and did not think I was going the right way.

It almost works now but I have fields that are nillable like *time.Time or even *int64 and I never get nil even if the value is not returned from the database.

If you write var myTime *time.Time in go code the default value will be nil, and this was the same expectation I had for this library.
Can this be a bug? Or am I still missing some detail?

from athenadriver.

Hi @henrywoo did you got a chance to see my message? I was wondering if you did not because I forgot to mention you.

from athenadriver.

Can you share me some reproducible code? It is hard for me to reproduce the issue you described.

from athenadriver.

Hi @henrywoo,
Unfortunately we are not using athena anymore due to some challenges we found.
But maybe this can help other people so I am going to try to leave here what we were using:

Instantiating the client

func NewAthenaStore(config AthenaStoreConfiguration) AthenaStore {
	conf := drv.NewNoOpsConfig()
	conf.SetDB(config.GlueDatabaseName)
	conf.SetRegion(config.OutputBucketRegion)
	// Even though the workgroup already contains this configuration,
	// it is necessary to add it here, otherwise the client fails to be instantiated.
	conf.SetOutputBucket("s3://" + config.OutputBucketName)
	workgroup := drv.NewWG(config.AthenaWorkgroupName, nil, nil)
	conf.SetWorkGroup(workgroup)
	conf.SetWGRemoteCreationAllowed(false)
	db, _ := sql.Open(drv.DriverName, conf.Stringify())
}

Query struct

type PullRequestMetrics struct {
	ClosedAt                    *time.Time  `db:"closed_at"`
	NumberOfCommits  *int64          `db:"number_of_commits"`
}

Query

SELECT
	closed_at AS closed_at,
	commits AS number_of_commits
FROM
        pull_request

The json in s3 queries by athena sometimes has a closed_at and/or number_of_commits and sometimes does not.
The deserialization fails if the values are not present in the json. With the error I pasted before.

from athenadriver.