/**
* Wait for an Athena query to complete, fail or to be cancelled. This is done by polling Athena over an
* interval of time. If a query fails or is cancelled, then it will throw an exception.
*/
private static void waitForQueryToComplete(AmazonAthenaClient athenaClient, String queryExecutionId)
{
var getQueryExecutionRequest = new GetQueryExecutionRequest()
{
QueryExecutionId = queryExecutionId
};
GetQueryExecutionResponse getQueryExecutionResponse = null;
bool isQueryStillRunning = true;
while (isQueryStillRunning)
{
getQueryExecutionResponse = athenaClient.GetQueryExecution(getQueryExecutionRequest);
var queryState = getQueryExecutionResponse.QueryExecution.Status.State;
if (queryState == QueryExecutionState.FAILED)
{
throw new Exception("Query Failed to run with Error Message: " + getQueryExecutionResponse.QueryExecution.Status.StateChangeReason);
}
else if (queryState == QueryExecutionState.CANCELLED)
{
throw new Exception("Query was cancelled.");
}
else if (queryState == QueryExecutionState.SUCCEEDED)
{
isQueryStillRunning = false;
}
else
{
// Sleep an amount of time before retrying again.
Thread.Sleep(TimeSpan.FromMilliseconds(ExampleConstants.SLEEP_AMOUNT_IN_MS));
}
Console.WriteLine("Current Status is: " + queryState);
}
}
async static 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;
/* Declare query execution request object */
GetQueryExecutionRequest qReq = new GetQueryExecutionRequest()
{
QueryExecutionId = id
};
/* Poll API to determine when the query completed */
do
{
results = await client.GetQueryExecutionAsync(qReq);
q = results.QueryExecution;
await Task.Delay(5000); //Wait for 5sec before polling again
} while (q.Status.State == "RUNNING" || q.Status.State == "QUEUED");
Console.WriteLine("Data Scanned for {0}: {1} Bytes", id, q.Statistics.DataScannedInBytes);
/* Declare query results request object */
GetQueryResultsRequest resReq = new GetQueryResultsRequest()
{
QueryExecutionId = id,
MaxResults = 10
};
GetQueryResultsResponse resResp = null;
/* Page through results and request additional pages if available */
do
{
resResp = await client.GetQueryResultsAsync(resReq);
/* Loop over result set and create a dictionary with column name for key and data for value */
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 List of dictionary per row containing column name and value */
return(items);
}
/// <summary>
/// Unmarshaller the response from the service to the response class.
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public override AmazonWebServiceResponse Unmarshall(JsonUnmarshallerContext context)
{
GetQueryExecutionResponse response = new GetQueryExecutionResponse();
context.Read();
int targetDepth = context.CurrentDepth;
while (context.ReadAtDepth(targetDepth))
{
if (context.TestExpression("QueryExecution", targetDepth))
{
var unmarshaller = QueryExecutionUnmarshaller.Instance;
response.QueryExecution = unmarshaller.Unmarshall(context);
continue;
}
}
return(response);
}
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);
}
public AthenaQueryExecutionException(string message, GetQueryExecutionResponse response, Exception innerException) : base(message, innerException)
{
GetQueryExecutionResponse = response;
}
public 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 queryExecution = null;
/* Declare query execution request object */
GetQueryExecutionRequest queryExReq = new GetQueryExecutionRequest()
{
QueryExecutionId = id
};
/* Poll API to determine when the query completed */
do
{
try
{
results = await client.GetQueryExecutionAsync(queryExReq);
queryExecution = results.QueryExecution;
_logger.LogInformation("Status: {0}... {1}", queryExecution.Status.State, queryExecution.Status.StateChangeReason);
await Task.Delay(5000); //Wait for 5sec before polling again
}
catch (InvalidRequestException e)
{
_logger.LogInformation("GetQueryExec Error: {0}", e.Message);
}
} while (queryExecution.Status.State == "RUNNING" || queryExecution.Status.State == "QUEUED");
_logger.LogInformation("Data Scanned for {0}: {1} Bytes", id, queryExecution.Statistics.DataScannedInBytes);
/* Declare query results request object */
GetQueryResultsRequest queryResultRequest = new GetQueryResultsRequest()
{
QueryExecutionId = id,
MaxResults = 10
};
GetQueryResultsResponse queryResult = null;
/* Page through results and request additional pages if available */
do
{
queryResult = await client.GetQueryResultsAsync(queryResultRequest);
/* Loop over result set and create a dictionary with column name for key and data for value */
foreach (Row row in queryResult.ResultSet.Rows)
{
Dictionary <String, String> dict = new Dictionary <String, String>();
for (var i = 0; i < queryResult.ResultSet.ResultSetMetadata.ColumnInfo.Count; i++)
{
dict.Add(queryResult.ResultSet.ResultSetMetadata.ColumnInfo[i].Name, row.Data[i].VarCharValue);
}
items.Add(dict);
}
if (queryResult.NextToken != null)
{
queryResultRequest.NextToken = queryResult.NextToken;
}
} while (queryResult.NextToken != null);
/* Return List of dictionary per row containing column name and value */
return(items);
}
async static 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;
/* Declare query execution request object */
GetQueryExecutionRequest qReq = new GetQueryExecutionRequest()
{
QueryExecutionId = id
};
/* Poll API to determine when the query completed */
do
{
List <Dictionary <string, string> > lists = new List <Dictionary <string, string> >();
try
{
results = await client.GetQueryExecutionAsync(qReq);
if (results == null)
{
Dictionary <string, string> dic1 = new Dictionary <string, string>();
dic1.Add("error", "results is null");
lists.Add(dic1);
return(lists);
}
q = results.QueryExecution;
if (q == null)
{
Dictionary <string, string> dic3 = new Dictionary <string, string>();
dic3.Add("error", "q is null");
lists.Add(dic3);
return(lists);
}
Console.WriteLine("Status: {0}... {1}", q.Status.State, q.Status.StateChangeReason);
await Task.Delay(5000); //Wait for 5sec before polling again
}
catch (InvalidRequestException e)
{
Dictionary <string, string> dic2 = new Dictionary <string, string>();
dic2.Add("error", "exception : " + " (Run method) " + " : " + e.Message);
lists.Add(dic2);
Console.WriteLine("GetQueryExec Error: {0}", e.Message);
return(lists);
}
} while (q.Status.State == "RUNNING" || q.Status.State == "QUEUED");
Console.WriteLine("Data Scanned for {0}: {1} Bytes", id, q.Statistics.DataScannedInBytes);
/* Declare query results request object */
GetQueryResultsRequest resReq = new GetQueryResultsRequest()
{
QueryExecutionId = id,
MaxResults = 20
};
GetQueryResultsResponse resResp = null;
/* Page through results and request additional pages if available */
Dictionary <String, String> dic = new Dictionary <String, String>();
List <Dictionary <String, String> > l = new List <Dictionary <String, String> >();
do
{
resResp = await client.GetQueryResultsAsync(resReq);
//l.Add(dict);
/* Loop over result set and create a dictionary with column name for key and data for value */
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);
if (items == null)
{
dic.Add("error", "items are null here");
l.Add(dic);
return(l);
}
/* Return List of dictionary per row containing column name and value */
return(items);
}
