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 + "\"}"); } }
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); } }
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> /// Unmarshaller the response from the service to the response class. /// </summary> /// <param name="context"></param> /// <returns></returns> public override AmazonWebServiceResponse Unmarshall(JsonUnmarshallerContext context) { StartQueryExecutionResponse response = new StartQueryExecutionResponse(); context.Read(); int targetDepth = context.CurrentDepth; while (context.ReadAtDepth(targetDepth)) { if (context.TestExpression("QueryExecutionId", targetDepth)) { var unmarshaller = StringUnmarshaller.Instance; response.QueryExecutionId = unmarshaller.Unmarshall(context); continue; } } return(response); }
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); }
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 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); }