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