/** * 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); }