Athena .NET integration

Question

I am beginning work on a project to integrate an existing VB.net webform app to new data stored on AWS. I have downloaded an Athena SDK that suggests it can do exactly that but I am unable to get it to work. Here is the module I have so far:

Imports System
Imports System.Threading
Imports System.Threading.Tasks
Imports System.Collections.Generic
Imports Amazon
Imports Amazon.Athena
Imports Amazon.Athena.Model

Namespace athena_api
Class AthenaQuery

    Private Const ATHENA_TEMP_PATH As String = "s3://xxxxxxxxxxx"
    Private Const ATHENA_DB As String = "xxxxx"
    Public Sub New()

        Using client = New AmazonAthenaClient(Amazon.RegionEndpoint.USEast1)
            Dim qContext As QueryExecutionContext = New QueryExecutionContext()
            qContext.Database = ATHENA_DB
            Dim resConf As ResultConfiguration = New ResultConfiguration()
            resConf.OutputLocation = ATHENA_TEMP_PATH
            Console.WriteLine("Created Athena Client")
            run(client, qContext, resConf).Wait()
        End Using

    End Sub

    Public Shared Async Function run(ByVal client As IAmazonAthena, ByVal qContext As QueryExecutionContext, ByVal resConf As ResultConfiguration) As Task
        Dim qReq As StartQueryExecutionRequest = New StartQueryExecutionRequest() With {
            .QueryString = "select count(*) from testTable",
            .QueryExecutionContext = qContext,
            .ResultConfiguration = resConf
        }

        Try
            Dim qRes As StartQueryExecutionResponse = Await client.StartQueryExecutionAsync(qReq)
            Dim items As List(Of Dictionary(Of String, String)) = Await getQueryExecution(client, qRes.QueryExecutionId)

            For Each item In items

                For Each pair As KeyValuePair(Of String, String) In item
                    Console.WriteLine("Col: {0}", pair.Key)
                    Console.WriteLine("Val: {0}", pair.Value)
                Next
            Next

        Catch e As InvalidRequestException
            Console.WriteLine("Run Error: {0}", e.Message)
        End Try


    End Function

    Public Shared Async Function getQueryExecution(ByVal client As IAmazonAthena, ByVal id As String) As Task(Of List(Of Dictionary(Of String, String)))
        Dim items As List(Of Dictionary(Of String, String)) = New List(Of Dictionary(Of String, String))()
        Dim results As GetQueryExecutionResponse = Nothing
        Dim q As QueryExecution = Nothing
        Dim qReq As GetQueryExecutionRequest = New GetQueryExecutionRequest() With {
            .QueryExecutionId = id
        }

        Do

            Try
                results = Await client.GetQueryExecutionAsync(qReq)
                q = results.QueryExecution
                Console.WriteLine("Status: {0}... {1}", q.Status.State, q.Status.StateChangeReason)
                Await Task.Delay(5000)
            Catch e As InvalidRequestException
                Console.WriteLine("GetQueryExec Error: {0}", e.Message)
            End Try
        Loop While q.Status.State = "RUNNING" OrElse q.Status.State = "QUEUED"

        Console.WriteLine("Data Scanned for {0}: {1} Bytes", id, q.Statistics.DataScannedInBytes)
        Dim resReq As GetQueryResultsRequest = New GetQueryResultsRequest() With {
            .QueryExecutionId = id,
            .MaxResults = 10
        }
        Dim resResp As GetQueryResultsResponse = Nothing

        Do
            resResp = Await client.GetQueryResultsAsync(resReq)

            For Each row As Row In resResp.ResultSet.Rows
                Dim dict As Dictionary(Of String, String) = New Dictionary(Of String, String)()

                For i = 0 To resResp.ResultSet.ResultSetMetadata.ColumnInfo.Count - 1
                    dict.Add(resResp.ResultSet.ResultSetMetadata.ColumnInfo(i).Name, row.Data(i).VarCharValue)
                Next

                items.Add(dict)
            Next

            If resResp.NextToken IsNot Nothing Then
                resReq.NextToken = resResp.NextToken
            End If
        Loop While resResp.NextToken IsNot Nothing

        Return items
    End Function
End Class

End Namespace

When running I get the following error:

An unhandled exception of type 'System.AggregateException' occurred in mscorlib.dll

I also am unclear how this api makes an authentication to AWS... there is no secret key or token being requested so it would seem anyone with the S3 path and the DB name could access.

Thanks everyone, this is my first stab at a project using AWS so bear with me here!

Answer 1

Add this line to add client connection using the access key and secret key

var client = new AmazonAthenaClient("ACCESS_KEY", "SECRET_ACCESS_ID", RegionEndpoint.REGIONNAME);

Answer 2

On the authentication piece...For Visual Studio...Install the AWS kit for Visual Studio... that will include AWS Explorer. You can create and manage profiles from there - and then reference the profile names in AppSettings... something like:

<appSettings>
    <add key="AWSProfileName" value="YOUR-PROFILE-NAME"/>
    <add key="AWSRegion" value="us-east-1" />
</appSettings>

Good starting point: https://docs.aws.amazon.com/sdk-for-net/v3/developer-guide/welcome.html

On the error... how to fix "'System.AggregateException' occurred in mscorlib.dll"

On Athena... c#, but close enough I would think: https://docs.aws.amazon.com/sdkfornet/v3/apidocs/items/Athena/NAthena.html