public async Task <string> LoadPartitionIfNotExists(string tableName, string keyFieldAssignment, string s3Location) { if (awsAthenaOptions.SQSOptions != null) { AWSSQSAPI awsSQSAPI = new AWSSQSAPI(awsAthenaOptions.SQSOptions); var request = new StartQueryExecutionRequest() { QueryString = $@"ALTER TABLE {tableName} ADD IF NOT EXISTS PARTITION ({keyFieldAssignment}) LOCATION '{s3Location}'", ResultConfiguration = new ResultConfiguration() { OutputLocation = awsAthenaOptions.DefaultOutputLocation //"s3://aws-athena-query-results-855250023996-ap-southeast-2/" } }; await awsSQSAPI.SendMessage(JsonConvert.SerializeObject(request)); return($"SQS-{awsAthenaOptions.SQSOptions.Url}"); } else { var queryInfo = await amazonAthenaClient.StartQueryExecutionAsync(new StartQueryExecutionRequest() { QueryString = $@"ALTER TABLE {tableName} ADD IF NOT EXISTS PARTITION ({keyFieldAssignment}) LOCATION '{s3Location}'", ResultConfiguration = new ResultConfiguration() { OutputLocation = awsAthenaOptions.DefaultOutputLocation //"s3://aws-athena-query-results-855250023996-ap-southeast-2/" } }); return(queryInfo.QueryExecutionId); } }
public void ExecuteQuery(string sql) { var awsCredentials = new Amazon.Runtime.BasicAWSCredentials("A", "B"); var c = new AmazonAthenaClient(awsCredentials); QueryExecutionContext queryExecutionContext = new QueryExecutionContext(); queryExecutionContext.Database = ATHENA_DEFAULT_DATABASE; // The result configuration specifies where the results of the query should go in S3 and encryption options ResultConfiguration resultConfiguration = new ResultConfiguration(); // You can provide encryption options for the output that is written. // .withEncryptionConfiguration(encryptionConfiguration) resultConfiguration.OutputLocation = ATHENA_OUTPUT_BUCKET; // Create the StartQueryExecutionRequest to send to Athena which will start the query. StartQueryExecutionRequest startQueryExecutionRequest = new StartQueryExecutionRequest(); startQueryExecutionRequest.QueryString = sql; startQueryExecutionRequest.QueryExecutionContext = queryExecutionContext; startQueryExecutionRequest.ResultConfiguration = resultConfiguration; var startQueryExecutionResponse = c.StartQueryExecutionAsync(startQueryExecutionRequest); //Console.WriteLine($"Query ID {startQueryExecutionResponse.QueryExecutionId}"); // return startQueryExecutionResponse.QueryExecutionId(); }
/** * Submits a sample query to Athena and returns the execution ID of the query. */ private static String submitAthenaQuery(AmazonAthenaClient athenaClient) { // The QueryExecutionContext allows us to set the Database. var queryExecutionContext = new QueryExecutionContext() { Database = ExampleConstants.ATHENA_DEFAULT_DATABASE }; // The result configuration specifies where the results of the query should go in S3 and encryption options var resultConfiguration = new ResultConfiguration() { // You can provide encryption options for the output that is written. // EncryptionConfiguration = encryptionConfiguration OutputLocation = ExampleConstants.ATHENA_OUTPUT_BUCKET }; // Create the StartQueryExecutionRequest to send to Athena which will start the query. var startQueryExecutionRequest = new StartQueryExecutionRequest() { QueryString = ExampleConstants.ATHENA_SAMPLE_QUERY, QueryExecutionContext = queryExecutionContext, ResultConfiguration = resultConfiguration }; var startQueryExecutionResponse = athenaClient.StartQueryExecution(startQueryExecutionRequest); return(startQueryExecutionResponse.QueryExecutionId); }
/// <summary> /// Runs (executes) the SQL query statements contained in the <code>Query</code> string. /// /// /// <para> /// For code samples using the AWS SDK for Java, see <a href="http://docs.aws.amazon.com/athena/latest/ug/code-samples.html">Examples /// and Code Samples</a> in the <i>Amazon Athena User Guide</i>. /// </para> /// </summary> /// <param name="request">Container for the necessary parameters to execute the StartQueryExecution service method.</param> /// /// <returns>The response from the StartQueryExecution service method, as returned by Athena.</returns> /// <exception cref="Amazon.Athena.Model.InternalServerException"> /// Indicates a platform issue, which may be due to a transient condition or outage. /// </exception> /// <exception cref="Amazon.Athena.Model.InvalidRequestException"> /// Indicates that something is wrong with the input to the request. For example, a required /// parameter may be missing or out of range. /// </exception> /// <exception cref="Amazon.Athena.Model.TooManyRequestsException"> /// Indicates that the request was throttled. /// </exception> /// <seealso href="http://docs.aws.amazon.com/goto/WebAPI/athena-2017-05-18/StartQueryExecution">REST API Reference for StartQueryExecution Operation</seealso> public StartQueryExecutionResponse StartQueryExecution(StartQueryExecutionRequest request) { var marshaller = new StartQueryExecutionRequestMarshaller(); var unmarshaller = StartQueryExecutionResponseUnmarshaller.Instance; return(Invoke <StartQueryExecutionRequest, StartQueryExecutionResponse>(request, marshaller, unmarshaller)); }
async static Task <string> run(IAmazonAthena client, QueryExecutionContext qContext, ResultConfiguration resConf) { /* Execute a simple query on a table */ StartQueryExecutionRequest qReq = new StartQueryExecutionRequest() { QueryString = "SELECT * FROM tbl_logger7;", QueryExecutionContext = qContext, ResultConfiguration = resConf }; try { /* Executes the query in an async manner */ StartQueryExecutionResponse qRes = await client.StartQueryExecutionAsync(qReq); /* Call internal method to parse the results and return a list of key/value dictionaries */ List <Dictionary <String, String> > items = await getQueryExecution(client, qRes.QueryExecutionId); string json = JsonConvert.SerializeObject(items); return(json); } catch (InvalidRequestException e) { return("{\"Error\":\"" + e.Message + "\"}"); } }
private async Task <string> SubmitAthenaQuery(AmazonAthenaClient client, string theQuery, ParsedAthenaResponse niceAthenaResult) { // The QueryExecutionContext allows us to set the Database. QueryExecutionContext queryExecutionContext = new QueryExecutionContext(); queryExecutionContext.Database = Functions.DatabaseName; // The result configuration specifies where the results of the query should go in S3 and encryption options ResultConfiguration resultConfiguration = new ResultConfiguration(); resultConfiguration.OutputLocation = Functions.QueryOutputLocation; // Create the StartQueryExecutionRequest to send to Athena which will start the query. StartQueryExecutionRequest startQueryExecutionRequest = new StartQueryExecutionRequest(); startQueryExecutionRequest.QueryString = theQuery; //Now reading this dynamically niceAthenaResult.columnOrder = new List <string>(); startQueryExecutionRequest.QueryExecutionContext = queryExecutionContext; startQueryExecutionRequest.ResultConfiguration = resultConfiguration; var startQueryExecutionResponse = await client.StartQueryExecutionAsync(startQueryExecutionRequest); return(startQueryExecutionResponse.QueryExecutionId); }
async static Task run(IAmazonAthena client, QueryExecutionContext qContext, ResultConfiguration resConf, string searchkey) { /* Execute a simple query on a table */ StartQueryExecutionRequest qReq = new StartQueryExecutionRequest() { QueryString = "SELECT * FROM book where bookid='" + searchkey + "'", QueryExecutionContext = qContext, ResultConfiguration = resConf }; try { /* Executes the query in an async manner */ StartQueryExecutionResponse qRes = await client.StartQueryExecutionAsync(qReq); /* Call internal method to parse the results and return a list of key/value dictionaries */ 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); } }
internal virtual StartQueryExecutionResponse StartQueryExecution(StartQueryExecutionRequest request) { var marshaller = StartQueryExecutionRequestMarshaller.Instance; var unmarshaller = StartQueryExecutionResponseUnmarshaller.Instance; return(Invoke <StartQueryExecutionRequest, StartQueryExecutionResponse>(request, marshaller, unmarshaller)); }
/// <summary> /// https://docs.aws.amazon.com/athena/latest/ug/querying.html#saving-query-results /// </summary> public async Task <string> StartQueryExecutionAsync(string database, string queryString, string outputLocation) { _logger.LogDebug($"StartQueryExecutionAsync (Database: '{database}', 'QueryString': '{queryString}', 'OutputLocation': '{outputLocation}')"); var startQueryExecutionRequest = new StartQueryExecutionRequest { QueryString = queryString, QueryExecutionContext = new QueryExecutionContext { Database = database }, ResultConfiguration = new ResultConfiguration { OutputLocation = outputLocation } }; var client = _amazonAthenaClientFactory.Create(); var startQueryExecutionResponse = await client.StartQueryExecutionAsync(startQueryExecutionRequest); startQueryExecutionResponse.EnsureSuccessStatusCode(); var queryExecutionId = startQueryExecutionResponse.QueryExecutionId; _logger.LogDebug($"QueryExecution '{queryExecutionId}' started (Database: '{database}', 'QueryString': '{queryString}', 'OutputLocation': '{outputLocation}')"); return(queryExecutionId); }
public static List <Dictionary <String, String> > runQuery(String StrQuery) { var client = new AmazonAthenaClient("AKIAWR5GGX5MW7QKSAWV", "jEdL+v3zQa8oTg/3Pkx0nd9+y3j5ZUP9AULNRW/H", Amazon.RegionEndpoint.APSouth1); QueryExecutionContext qContext = new QueryExecutionContext(); qContext.Database = ATHENA_DB; ResultConfiguration resConf = new ResultConfiguration(); resConf.OutputLocation = ATHENA_TEMP_PATH; List <Dictionary <String, String> > items = new List <Dictionary <String, String> >(); try { /* Execute a simple query on a table */ StartQueryExecutionRequest qReq = new StartQueryExecutionRequest() { QueryString = StrQuery, QueryExecutionContext = qContext, ResultConfiguration = resConf }; /* Executes the query in an async manner */ StartQueryExecutionResponse qRes = client.StartQueryExecution(qReq); // await client.StartQueryExecutionAsync(qReq); /* Call internal method to parse the results and return a list of key/value dictionaries */ items = getQueryExecution(client, qRes.QueryExecutionId); //await getQueryExecution(client, qRes.QueryExecutionId); } catch (InvalidRequestException e) { Console.WriteLine("Run Error: {0}", e.Message); } return(items); }
/// <summary> /// Initiates the asynchronous execution of the StartQueryExecution operation. /// </summary> /// /// <param name="request">Container for the necessary parameters to execute the StartQueryExecution operation.</param> /// <param name="cancellationToken"> /// A cancellation token that can be used by other objects or threads to receive notice of cancellation. /// </param> /// <returns>The task object representing the asynchronous operation.</returns> /// <seealso href="http://docs.aws.amazon.com/goto/WebAPI/athena-2017-05-18/StartQueryExecution">REST API Reference for StartQueryExecution Operation</seealso> public virtual Task <StartQueryExecutionResponse> StartQueryExecutionAsync(StartQueryExecutionRequest request, System.Threading.CancellationToken cancellationToken = default(CancellationToken)) { var marshaller = StartQueryExecutionRequestMarshaller.Instance; var unmarshaller = StartQueryExecutionResponseUnmarshaller.Instance; return(InvokeAsync <StartQueryExecutionRequest, StartQueryExecutionResponse>(request, marshaller, unmarshaller, cancellationToken)); }
internal virtual StartQueryExecutionResponse StartQueryExecution(StartQueryExecutionRequest request) { var options = new InvokeOptions(); options.RequestMarshaller = StartQueryExecutionRequestMarshaller.Instance; options.ResponseUnmarshaller = StartQueryExecutionResponseUnmarshaller.Instance; return(Invoke <StartQueryExecutionResponse>(request, options)); }
public async Task <string> LoadPartition(string tableName, string keyFieldAssignment, string s3Location) { /* * ALTER TABLE twilio_log.twilogs ADD PARTITION (logdate=20181203) * LOCATION 's3://datascience-twilio-sms-logs/twilio-sms-log-2018-12-03/' */ if (awsAthenaOptions.SQSOptions != null) { AWSSQSAPI awsSQSAPI = new AWSSQSAPI(awsAthenaOptions.SQSOptions); var request = new StartQueryExecutionRequest() { QueryString = $@"ALTER TABLE {tableName} ADD PARTITION ({keyFieldAssignment}) LOCATION '{s3Location}'", ResultConfiguration = new ResultConfiguration() { OutputLocation = awsAthenaOptions.DefaultOutputLocation //"s3://aws-athena-query-results-855250023996-ap-southeast-2/" } }; await awsSQSAPI.SendMessage(JsonConvert.SerializeObject(request)); return($"SQS-{awsAthenaOptions.SQSOptions.Url}"); } else { var queryInfo = await amazonAthenaClient.StartQueryExecutionAsync(new StartQueryExecutionRequest() { QueryString = $@"ALTER TABLE {tableName} ADD PARTITION ({keyFieldAssignment}) LOCATION '{s3Location}'", ResultConfiguration = new ResultConfiguration() { OutputLocation = awsAthenaOptions.DefaultOutputLocation //"s3://aws-athena-query-results-855250023996-ap-southeast-2/" } }); return(queryInfo.QueryExecutionId); } //var queryStatus = amazonAthenaClient.GetQueryExecutionAsync(new GetQueryExecutionRequest() { QueryExecutionId = queryInfo.QueryExecutionId }); //while(!queryStatus.IsCanceled && !queryStatus.IsCompleted && !queryStatus.IsFaulted) //{ // Thread.Sleep(100); // queryStatus = amazonAthenaClient.GetQueryExecutionAsync(new GetQueryExecutionRequest() { QueryExecutionId = queryInfo.QueryExecutionId }); //} //if (queryStatus.IsCanceled) return "canceled"; //if (queryStatus.IsCompleted) return "completed"; //if (queryStatus.IsFaulted) return "faulted"; //return "unknown"; }
public async Task <GetQueryResultsRequest> ExecuteQuery(StartQueryExecutionRequest startQueryExecutionRequest) // Task<GetQueryResultsResponse> { var startResponse = await amazonAthenaClient.StartQueryExecutionAsync(startQueryExecutionRequest); 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; }
public static async Task AWSAthenaSearchByDescription(string searchkey) { using (var client = new AmazonAthenaClient(AWS_ACCESS_KEY, AWS_SECRET_KEY, Amazon.RegionEndpoint.USEast2)) { QueryExecutionContext qContext = new QueryExecutionContext(); qContext.Database = "bookdb"; ResultConfiguration resConf = new ResultConfiguration(); resConf.OutputLocation = "s3://test-bucket-kalam/"; Console.WriteLine("Created Athena Client"); /* Execute a simple query on a table */ StartQueryExecutionRequest qReq = new StartQueryExecutionRequest() { QueryString = "SELECT * FROM book where bookdescription like '%" + searchkey + "%'", QueryExecutionContext = qContext, ResultConfiguration = resConf }; try { /* Executes the query in an async manner */ StartQueryExecutionResponse qRes = await client.StartQueryExecutionAsync(qReq); /* Call internal method to parse the results and return a list of key/value dictionaries */ 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 static Task <List <Dictionary <String, String> > > Run(IAmazonAthena client, QueryExecutionContext qContext, ResultConfiguration resConf, string datestring) { /* Execute a simple query on a table */ StartQueryExecutionRequest qReq = new StartQueryExecutionRequest() { QueryString = $@"SELECT * FROM emptable where emp_dob = '{datestring}'", QueryExecutionContext = qContext, ResultConfiguration = resConf }; List <Dictionary <String, String> > items = null; try { /* Executes the query in an async manner */ StartQueryExecutionResponse qRes = await client.StartQueryExecutionAsync(qReq); /* Call internal method to parse the results and return a list of key/value dictionaries */ items = await getQueryExecution(client, qRes.QueryExecutionId); if (items == null) { Dictionary <string, string> dic1 = new Dictionary <string, string>(); dic1.Add("error", "items from query execution is empty"); items.Add(dic1); } } catch (InvalidRequestException e) { Dictionary <string, string> dic1 = new Dictionary <string, string>(); dic1.Add("error", "exception at " + " (Run method) " + " : " + e.Message); if (items == null) { items = new List <Dictionary <string, string> >(); } items.Add(dic1); Console.WriteLine("Run Error: {0}", e.Message); } return(items); }
static async Task Main(string[] args) { var client = new AmazonAthenaClient("AKIASR7SM37VRJWO6HO5", "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx", RegionEndpoint.APSoutheast2); var queryRequest = new StartQueryExecutionRequest { QueryString = "select * from demodb.rates_raw", ResultConfiguration = new ResultConfiguration { OutputLocation = "s3://result-folder/" } }; var result = await client.QueryAsyncLight(queryRequest, 5); var rows = result.ResultSet.Rows; foreach (var row in rows) { Console.WriteLine(string.Join(",", row.Data.Select(x => x.VarCharValue))); } }
async static Task <List <Dictionary <String, String> > > runQuery(IAmazonAthena client, QueryExecutionContext qContext, ResultConfiguration resConf, String dateToQuery) { /* Execute a simple query on a table */ StartQueryExecutionRequest qReq = new StartQueryExecutionRequest() { QueryString = $"SELECT * FROM employee where day(employee.emp_dob) = {dateToQuery}", QueryExecutionContext = qContext, ResultConfiguration = resConf }; try { StartQueryExecutionResponse qRes = await client.StartQueryExecutionAsync(qReq); /* Call internal method to parse the results and return a list of key/value dictionaries */ List <Dictionary <String, String> > items = await getQueryExecution(client, qRes.QueryExecutionId); return(items); } catch (InvalidRequestException e) { LambdaLogger.Log($"Run Error: {e.Message}"); return(new List <Dictionary <String, String> >()); } }
public async Task <List <CountryEnrichedData> > Run(IAmazonAthena client, QueryExecutionContext queryContext, ResultConfiguration resultConfig) { /* Execute a simple query on a table */ var queryRequest = new StartQueryExecutionRequest() { QueryString = AthenaConstants.ATHENA_COUNTRY_DATA_QUERY, QueryExecutionContext = queryContext, ResultConfiguration = resultConfig }; try { var ListOfCountryEnrichedData = new List <CountryEnrichedData>(); /* Executes the query in an async manner */ var queryResult = await client.StartQueryExecutionAsync(queryRequest); /* Call internal method to parse the results and return a list of key/value dictionaries */ List <Dictionary <String, String> > items = await GetQueryExecution(client, queryResult.QueryExecutionId); foreach (var item in items) { var countryUpdate = new CountryEnrichedData(); foreach (KeyValuePair <String, String> pair in item) { switch (pair.Key) { case "iso_code": if (pair.Value == "iso_code") { break; } else { countryUpdate.IsoCode = pair.Value; } break; case "population": if (pair.Value == "population") { break; } else { countryUpdate.Population = Convert.ToDouble(pair.Value); } break; case "population_density": if (pair.Value == "population_density") { break; } else { countryUpdate.PopulationDensity = Convert.ToDouble(pair.Value); } break; case "median_age": if (pair.Value == "median_age") { break; } else { countryUpdate.MedianAge = Convert.ToDouble(pair.Value); } break; case "aged_65_older": if (pair.Value == "aged_65_older") { break; } else { countryUpdate.Aged65Older = Convert.ToDouble(pair.Value); } break; case "aged_70_older": if (pair.Value == "aged_70_older") { break; } else { countryUpdate.Aged70Older = Convert.ToDouble(pair.Value); } break; case "gdp_per_capita": if (pair.Value == "gdp_per_capita") { break; } else { countryUpdate.GdpPerCapita = Convert.ToDouble(pair.Value); } break; case "diabetes_prevalence": if (pair.Value == "diabetes_prevalence") { break; } else { countryUpdate.DiabetesPrevalence = Convert.ToDouble(pair.Value); } break; case "handwashing_facilities": if (pair.Value == "handwashing_facilities") { break; } else { countryUpdate.HandwashingFacilities = Convert.ToDouble(pair.Value); } break; case "hospital_beds_per_thousand": if (pair.Value == "hospital_beds_per_thousand") { break; } else { countryUpdate.HospitalBedsPerThousand = Convert.ToDouble(pair.Value); } break; case "life_expectancy": if (pair.Value == "life_expectancy") { break; } else { countryUpdate.LifeExpectancy = Convert.ToDouble(pair.Value); } break; } } ListOfCountryEnrichedData.Add(countryUpdate); } return(ListOfCountryEnrichedData); } catch (InvalidRequestException e) { _logger.LogInformation("Run Error: {0}", e.Message); } return(new List <CountryEnrichedData>()); }
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 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 string ExecuteQuery(string query, ref QueryStageStatistics queryStats) { #region Setup QueryExecutionContext queryExecContext = new QueryExecutionContext() { Database = this.DataBase }; ResultConfiguration resultConfig = new ResultConfiguration() { OutputLocation = this.OutputLocation }; StartQueryExecutionRequest startQueryExecRequest = new StartQueryExecutionRequest() { QueryExecutionContext = queryExecContext, QueryString = query, ResultConfiguration = resultConfig }; #endregion var watch = new Stopwatch(); #region Query Execution //Start Query execution and wait till start command has completed var startQueryExecResult = _athenaClient.StartQueryExecutionAsync(startQueryExecRequest); //Start measurement watch.Start(); while (!startQueryExecResult.IsCompleted) { Thread.Sleep(100); Console.Write($"\r START {watch.Elapsed}"); } if (startQueryExecResult.Exception != null) { throw new Exception("Query Execution", startQueryExecResult.Exception); } if (startQueryExecResult.IsCanceled) { return("- Cancelled -"); } //Query if query execution has finished GetQueryExecutionRequest getQueryExecRequest = new GetQueryExecutionRequest() { QueryExecutionId = startQueryExecResult.Result.QueryExecutionId }; Task <Amazon.Athena.Model.GetQueryExecutionResponse> getQueryExecResult = null; bool isQueryRunning = true; while (isQueryRunning) { getQueryExecResult = _athenaClient.GetQueryExecutionAsync(getQueryExecRequest); var state = getQueryExecResult.Result.QueryExecution.Status.State; if (state == Amazon.Athena.QueryExecutionState.FAILED) { throw new Exception("Query Execution", getQueryExecResult.Exception); } else if (state == Amazon.Athena.QueryExecutionState.CANCELLED) { return("- Canceled -"); } else if (state == Amazon.Athena.QueryExecutionState.SUCCEEDED) { isQueryRunning = false; queryStats.QueryExecutionTime = watch.Elapsed; } else { Thread.Sleep(100); Console.Write($"\r EXEC {watch.Elapsed}"); } } queryStats.EngineExecutionTimeInMillis = getQueryExecResult.Result.QueryExecution.Statistics.EngineExecutionTimeInMillis; queryStats.DataScannedInBytes = getQueryExecResult.Result.QueryExecution.Statistics.DataScannedInBytes; #endregion watch.Restart(); #region Get Query Result GetQueryResultsRequest getQueryResultRequest = new GetQueryResultsRequest() { QueryExecutionId = getQueryExecResult.Result.QueryExecution.QueryExecutionId }; var getQueryResultsResult = _athenaClient.GetQueryResultsAsync(getQueryResultRequest); //No data process is taken account. Only take meassurements. long contentSize = 0, totalRows = 0; while (true) { while (!getQueryResultsResult.IsCompleted) { Thread.Sleep(100); Console.Write($"\r FETCH {watch.Elapsed}"); } if (getQueryResultsResult.Exception != null) { throw new Exception("Retrieving Query Rows", getQueryResultsResult.Exception); } if (getQueryResultsResult.IsCanceled) { return("- Canceled -"); } //while(getQueryResultsResult.Status == System.Threading.Tasks.TaskStatus.Running) //{ // Thread.Sleep(100); // Console.Write("."); //} totalRows += getQueryResultsResult.Result.ResultSet.Rows.Count; if (getQueryResultsResult.Result.NextToken == null || totalRows >= SampleContext.MAX_FETCHED_RECORDS) { queryStats.DataFetchingTime = watch.Elapsed; contentSize += getQueryResultsResult.Result.ContentLength; break; } getQueryResultsResult = _athenaClient.GetQueryResultsAsync(new GetQueryResultsRequest() { QueryExecutionId = getQueryResultRequest.QueryExecutionId, NextToken = getQueryResultRequest.NextToken } ); } #endregion return($"{contentSize} bytes took {queryStats.QueryExecutionTime} executing query and {queryStats.DataFetchingTime} fetching first {SampleContext.MAX_FETCHED_RECORDS} records."); }
public async Task <List <SentimentReportRow> > GenerateSentimentReportAsync(CancellationToken token) { var req = new StartQueryExecutionRequest() { QueryExecutionContext = new QueryExecutionContext() { Catalog = "AwsDataCatalog", Database = this.database }, QueryString = CreateQueryForTableName(this.database, this.table), WorkGroup = this.workgroup }; var response = await this.athena.StartQueryExecutionAsync(req, token); string queryId = response.QueryExecutionId; this.logger.LogInformation($"Started query execution with id {queryId}."); GetQueryExecutionResponse executionResponse; while (true) { await Task.Delay(TimeSpan.FromSeconds(3), token); executionResponse = await this.athena.GetQueryExecutionAsync(new GetQueryExecutionRequest() { QueryExecutionId = queryId }, token); if (executionResponse.QueryExecution.Status.State == QueryExecutionState.RUNNING) { continue; } break; } if (executionResponse.QueryExecution.Status.State == QueryExecutionState.FAILED) { this.logger.LogError(executionResponse.QueryExecution.Status.StateChangeReason); throw new Exception( $"Athena query failed because: {executionResponse.QueryExecution.Status.StateChangeReason}"); } var resultsResponse = await this.athena.GetQueryResultsAsync(new GetQueryResultsRequest() { QueryExecutionId = queryId, MaxResults = 1000 }, token); var results = resultsResponse.ResultSet.Rows; return(results.Skip(1).Select( r => new SentimentReportRow() { Username = r.Data[0].VarCharValue, NumberOfMessages = int.Parse(r.Data[1].VarCharValue), Positivity = r.Data[2].ToDouble(), Negativity = r.Data[3].ToDouble(), Neutrality = r.Data[4].ToDouble(), Mixed = r.Data[5].ToDouble(), }) .ToList()); }