public async Task <GetQueryResultsRequest> ExecuteQuery(string query) // Task<GetQueryResultsResponse>
{
var startResponse = await amazonAthenaClient.StartQueryExecutionAsync(new StartQueryExecutionRequest()
{
QueryString = query,
ResultConfiguration = new ResultConfiguration()
{
OutputLocation = awsAthenaOptions.DefaultOutputLocation,
}
});
var queryId = startResponse.QueryExecutionId;
GetQueryExecutionRequest getQueryExecutionRequest = new GetQueryExecutionRequest()
{
QueryExecutionId = queryId
};
bool isStillingRunning = true;
while (isStillingRunning)
{
var executionResponse = await amazonAthenaClient.GetQueryExecutionAsync(getQueryExecutionRequest);
var state = executionResponse.QueryExecution.Status.State;
if (state == QueryExecutionState.FAILED)
{
throw new Exception($"Query Failed to run with Error Message: \n{executionResponse.QueryExecution.Status.StateChangeReason}");
}
else if (state == QueryExecutionState.CANCELLED)
{
throw new Exception($"Query was cancelled.");
}
else if (state == QueryExecutionState.SUCCEEDED)
{
isStillingRunning = false;
}
}
return(new GetQueryResultsRequest()
{
QueryExecutionId = queryId
});
//var resultResponse = await amazonAthenaClient.GetQueryResultsAsync(new GetQueryResultsRequest()
//{
// QueryExecutionId = queryId
//});
//return resultResponse;
}
private async Task WaitForQueryToComplete(AmazonAthenaClient client, string queryExecutionId)
{
GetQueryExecutionRequest getQueryExecutionRequest = new GetQueryExecutionRequest();
getQueryExecutionRequest.QueryExecutionId = queryExecutionId;
bool isQueryStillRunning = true;
while (isQueryStillRunning)
{
var queryExecutionResponse = await client.GetQueryExecutionAsync(getQueryExecutionRequest);
QueryExecutionStatus queryStatus = queryExecutionResponse.QueryExecution.Status;
if (queryStatus.State == QueryExecutionState.FAILED)
{
throw new Exception("Query Failed to run with Error Message: " + queryExecutionResponse.QueryExecution.Status.StateChangeReason);
}
else if (queryStatus.State == QueryExecutionState.CANCELLED)
{
throw new Exception("Query was cancelled.");
}
else if (queryStatus.State == QueryExecutionState.SUCCEEDED)
{
isQueryStillRunning = false;
}
// Sleep an amount before retrying again.
Console.WriteLine("Current Status is: " + queryStatus.State.Value);
Thread.Sleep(new TimeSpan(0, 0, 1));
}
}
public static async Task <GetQueryResultsResponse> QueryAsyncLight(this AmazonAthenaClient client, StartQueryExecutionRequest request, int timeoutSeconds)
{
var executionResult = await client.StartQueryExecutionAsync(request);
var queryExecutionRequest = new GetQueryExecutionRequest
{
QueryExecutionId = executionResult.QueryExecutionId
};
return(await Task.Run <GetQueryResultsResponse>(async() =>
{
var start = DateTime.Now;
while ((DateTime.Now - start).Seconds < timeoutSeconds)
{
await Task.Delay(1000);
var response = await client.GetQueryExecutionAsync(queryExecutionRequest);
switch (response.QueryExecution.Status.State)
{
case var queued when queued == QueryExecutionState.QUEUED:
case var running when running == QueryExecutionState.RUNNING:
continue;
case var cancelled when cancelled == QueryExecutionState.CANCELLED:
{
throw new AthenaQueryExecutionException($"The query({executionResult.QueryExecutionId}) has been calceled", response);
}
case var failed when failed == QueryExecutionState.FAILED:
{
throw new AthenaQueryExecutionException($"The query({executionResult.QueryExecutionId}) failed", response);
}
case var secceeded when secceeded == QueryExecutionState.SUCCEEDED:
{
var resultRequest = new GetQueryResultsRequest
{
QueryExecutionId = executionResult.QueryExecutionId
};
var result = await client.GetQueryResultsAsync(resultRequest);
return result;
}
default:
throw new AthenaQueryExecutionException($"Unrecognized query({response.QueryExecution.QueryExecutionId}) State", response);
}
}
throw new AthenaQueryExecutionException($"query({executionResult.QueryExecutionId}) Timeout", null);
}));
}
public APIGatewayProxyResponse Get(APIGatewayProxyRequest request, ILambdaContext context)
{
context.Logger.LogLine("Get Request\n");
using (var client = new AmazonAthenaClient(Amazon.RegionEndpoint.USEast1))
{
String date = JsonConvert.DeserializeObject <String>(request?.Body);
QueryExecutionContext qContext = new QueryExecutionContext();
qContext.Database = ATHENA_DB;
ResultConfiguration resConf = new ResultConfiguration();
resConf.OutputLocation = ATHENA_TEMP_PATH;
Console.WriteLine("Created Athena Client");
run(client, qContext, resConf, date).Wait();
}
async Task run(IAmazonAthena client, QueryExecutionContext qContext, ResultConfiguration resConf, String date)
{
StartQueryExecutionRequest qReq = new StartQueryExecutionRequest()
{
QueryString = "SELECT emp_name FROM emp_table WHERE date = " + date + "limit 10;",
QueryExecutionContext = qContext,
ResultConfiguration = resConf
};
try
{
StartQueryExecutionResponse qRes = await client.StartQueryExecutionAsync(qReq);
List <Dictionary <String, String> > items = await getQueryExecution(client, qRes.QueryExecutionId);
foreach (var item in items)
{
foreach (KeyValuePair <String, String> pair in item)
{
Console.WriteLine("Col: {0}", pair.Key);
Console.WriteLine("Val: {0}", pair.Value);
}
}
}
catch (InvalidRequestException e)
{
Console.WriteLine("Run Error: {0}", e.Message);
}
}
async Task <List <Dictionary <String, String> > > getQueryExecution(IAmazonAthena client, String id)
{
List <Dictionary <String, String> > items = new List <Dictionary <String, String> >();
GetQueryExecutionResponse results = null;
QueryExecution q = null;
GetQueryExecutionRequest qReq = new GetQueryExecutionRequest()
{
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 (InvalidRequestException e)
{
Console.WriteLine("GetQueryExec Error: {0}", e.Message);
}
} while (q.Status.State == "RUNNING" || q.Status.State == "QUEUED");
Console.WriteLine("Data Scanned for {0}: {1} Bytes", id, q.Statistics.DataScannedInBytes);
GetQueryResultsRequest resReq = new GetQueryResultsRequest()
{
QueryExecutionId = id,
MaxResults = 10
};
GetQueryResultsResponse resResp = null;
do
{
resResp = await client.GetQueryResultsAsync(resReq);
foreach (Row row in resResp.ResultSet.Rows)
{
Dictionary <String, String> dict = new Dictionary <String, String>();
for (var i = 0; i < resResp.ResultSet.ResultSetMetadata.ColumnInfo.Count; i++)
{
dict.Add(resResp.ResultSet.ResultSetMetadata.ColumnInfo[i].Name, row.Data[i].VarCharValue);
}
items.Add(dict);
}
if (resResp.NextToken != null)
{
resReq.NextToken = resResp.NextToken;
}
} while (resResp.NextToken != null);
return(items);
}
var response = new APIGatewayProxyResponse
{
StatusCode = (int)HttpStatusCode.OK,
Body = "GET Method Executed",
Headers = new Dictionary <string,
string> {
{ "Content-Type", "application/json" }
}
};
return(response);
}
