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