Querying AWS Athena using Golang SDK

main.go

package main

import (
	"fmt"
	"time"

	"github.com/aws/aws-sdk-go/aws"
	"github.com/aws/aws-sdk-go/aws/session"
	"github.com/aws/aws-sdk-go/service/athena"
)

func main() {
	awscfg := &aws.Config{}
	awscfg.WithRegion("us-east-1")
	// Create the session that the service will use.
	sess := session.Must(session.NewSession(awscfg))

	svc := athena.New(sess, aws.NewConfig().WithRegion("us-east-1"))
	var s athena.StartQueryExecutionInput
	s.SetQueryString("select PageURL from testtable limit 10")

	var q athena.QueryExecutionContext
	q.SetDatabase("testdb")
	s.SetQueryExecutionContext(&q)

	var r athena.ResultConfiguration
	r.SetOutputLocation("s3://TestBucket")
	s.SetResultConfiguration(&r)

	result, err := svc.StartQueryExecution(&s)
	if err != nil {
		fmt.Println(err)
		return
	}
	fmt.Println("StartQueryExecution result:")
	fmt.Println(result.GoString())

	var qri athena.GetQueryExecutionInput
	qri.SetQueryExecutionId(*result.QueryExecutionId)

	var qrop *athena.GetQueryExecutionOutput
	duration := time.Duration(2) * time.Second // Pause for 2 seconds

	for {
		qrop, err = svc.GetQueryExecution(&qri)
		if err != nil {
			fmt.Println(err)
			return
		}
		if *qrop.QueryExecution.Status.State != "RUNNING" {
			break
		}
		fmt.Println("waiting.")
		time.Sleep(duration)

	}
	if *qrop.QueryExecution.Status.State == "SUCCEEDED" {

		var ip athena.GetQueryResultsInput
		ip.SetQueryExecutionId(*result.QueryExecutionId)

		op, err := svc.GetQueryResults(&ip)
		if err != nil {
			fmt.Println(err)
			return
		}
		fmt.Printf("%+v", op)
	} else {
		fmt.Println(*qrop.QueryExecution.Status.State)

	}
}

While the sdk is excellent, the documentation for Athena was quite confusing. One way to figure out things is to follow the Java examples.

This is a handy example, thank you!

Agreed, documentation is very confusing. Thanks for posting this.

this is a great start, but has anyone any tips for parsing the results? ie. parsing the response VarCharValue into the proper value type?

edit: there's a java example on the official docs, seems simple enough to implement
https://docs.aws.amazon.com/athena/latest/ug/code-samples.html

@pwmcintyre: There's also https://github.com/segmentio/go-athena, which tries to simplify precisely that in Go.

Thanks - incredibly useful! For others QueryExecution.Status.StateChangeReason is useful to get the FAILED state reason.

@pwmcintyre @kartiksura: where you able to get the parsing working?

I had used this in production, so tell me what you want to do and i will refer my prod code and share with you. Regards, Kartik Sura

…

On Fri, Oct 4, 2019 at 9:47 AM Krithika3 ***@***.***> wrote: @pwmcintyre <https://github.com/pwmcintyre>: where you able to get the parsing working? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <https://gist.github.com/93160be1078648a14ec0ddc125c35546?email_source=notifications&email_token=ABBPYWLZR7LPKUYWWY7SV7LQM27TXA5CNFSM4HLSEDN2YY3PNVWWK3TUL52HS4DFVNDWS43UINXW23LFNZ2KUY3PNVWWK3TUL5UWJTQAFZ3YW#gistcomment-3045259>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ABBPYWNX4OOMRRFM42WYOJLQM27TXANCNFSM4HLSEDNQ> .

Thanks a lot. I was just planning to parse the returned output into a properly formatted Json which I can send downstream to one of our processes. I was trying to look at some of the Java examples but it’s not very descriptive

Here is a code which parses the output: Let me know if this works for you package reporter import ( "fmt" "time" "github.com/aws/aws-sdk-go/aws" "github.com/aws/aws-sdk-go/aws/session" "github.com/aws/aws-sdk-go/service/athena" ) //FireAthenaQuery does what it says func FireAthenaQuery(region string, db string, outputLocation string, query string) ([][]interface{}, error) { awscfg := &aws.Config{} awscfg.WithRegion(region) sess := session.Must(session.NewSession(awscfg)) svc := athena.New(sess, awscfg) var s athena.StartQueryExecutionInput s.SetQueryString(query) var q athena.QueryExecutionContext q.SetDatabase(db) s.SetQueryExecutionContext(&q) var r athena.ResultConfiguration r.SetOutputLocation(outputLocation) s.SetResultConfiguration(&r) result, err := svc.StartQueryExecution(&s) if err != nil { fmt.Println(err) return nil, err } fmt.Println("StartQueryExecution result:") fmt.Println(result.GoString()) var qri athena.GetQueryExecutionInput qri.SetQueryExecutionId(*result.QueryExecutionId) var qrop *athena.GetQueryExecutionOutput duration := time.Duration(2) * time.Second // Pause for 10 seconds for { qrop, err = svc.GetQueryExecution(&qri) if err != nil { fmt.Println(err) return nil, err } if *qrop.QueryExecution.Status.State != "RUNNING" { break } fmt.Println("waiting.") time.Sleep(duration) } if *qrop.QueryExecution.Status.State == "SUCCEEDED" { var ip athena.GetQueryResultsInput ip.SetQueryExecutionId(*result.QueryExecutionId) op, err := svc.GetQueryResults(&ip) if err != nil { fmt.Println(err) return nil, err } fmt.Printf("%+v", op) var rc [][]interface{} for i := range op.ResultSet.Rows { if i == 0 { continue } var temp []interface{} for j := range op.ResultSet.Rows[i].Data { temp = append(temp, *op.ResultSet.Rows[i].Data[j].VarCharValue) } rc = append(rc, temp) } return rc, nil } fmt.Println(*qrop.QueryExecution.Status.State) return nil, fmt.Errorf("%s", *qrop.QueryExecution.Status.StateChangeReason) } Regards, Kartik Sura

…

On Fri, Oct 4, 2019 at 10:05 AM Krithika3 ***@***.***> wrote: Thanks. I was just planning to parse the returned output into a properly formatted Json which I can send downstream to one of our processes. I was trying to look at some of the Java examples but it’s not very descriptive — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <https://mailtrack.io/trace/link/edfa6292ebd791a22b8f1a6473066cee5ed26995?url=https%3A%2F%2Fgist.github.com%2F93160be1078648a14ec0ddc125c35546%3Femail_source%3Dnotifications%26email_token%3DABBPYWOS6VHGTTKO2BE4ZU3QM3B2ZA5CNFSM4HLSEDN2YY3PNVWWK3TUL52HS4DFVNDWS43UINXW23LFNZ2KUY3PNVWWK3TUL5UWJTQAFZ3ZA%23gistcomment-3045264&userId=991805&signature=e5d533aaef329b4b>, or mute the thread <https://mailtrack.io/trace/link/6ae42db7e769a232e2f07bfd7029ba733beb8139?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FABBPYWJTLNYFXRV6GXZHIYDQM3B2ZANCNFSM4HLSEDNQ&userId=991805&signature=0cbeefd1bd7a8874> .

Thanks. Let me try this :)

This worked. Thanks a bunch

@kartiksura ... does this snippet (and aws-go-sdk) support pagination of Athena results implicitly?

I am not 100% sure as I had run it in prod couple of years back. But quick reading on the GetQueryResults API indicates that if the pagination token is present in case the result is truncated. https://docs.aws.amazon.com/athena/latest/APIReference/API_GetQueryResults.html <https://mailtrack.io/trace/link/da77fdd9b28808ee18cb4c31f46d096d2aeef001?url=https%3A%2F%2Fdocs.aws.amazon.com%2Fathena%2Flatest%2FAPIReference%2FAPI_GetQueryResults.html&userId=991805&signature=e4726ecc36ccc743> so we will have to check for the pagination token too. Regards, Kartik Sura

…

On Tue, Oct 15, 2019 at 9:58 AM Roman Valls Guimera < ***@***.***> wrote: @kartiksura <https://github.com/kartiksura> ... does this snippet (and aws-go-sdk) support pagination of Athena results implicitly? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <https://gist.github.com/93160be1078648a14ec0ddc125c35546?email_source=notifications&email_token=ABBPYWIFAP2YYAEBB6V4ZRTQOVBFLA5CNFSM4HLSEDN2YY3PNVWWK3TUL52HS4DFVNDWS43UINXW23LFNZ2KUY3PNVWWK3TUL5UWJTQAF2PQQ#gistcomment-3055368>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABBPYWMSN5WCVO75DLALE5LQOVBFLANCNFSM4HLSEDNQ> .

Thanks I'm seeking for the same.

I would rather read the results from s3 instead of Athena get result API which is not easy to go.

Hi @kartiksura,
Thanks for the useful example.
However, allow me to remind you there is another state that needs to wait: QUEUED, it will be perfect if add this condition inside the for loop.

Thanks for the tip Kevin!

On Fri, Aug 21, 2020 at 1:26 PM Kevin Hsiao ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ Hi @kartiksura <https://github.com/kartiksura>, Thanks for the useful example. However, allow me to remind you there is another state that needs to wait: QUEUED, it will be perfect if add this condition inside the for loop. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <https://gist.github.com/93160be1078648a14ec0ddc125c35546#gistcomment-3425935>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABBPYWLQWBFTOVMOZ33TQTLSBYSCZANCNFSM4HLSEDNQ> . --

Sent from Gmail Mobile