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);
}
}
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 <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);
}
public void SetUp()
{
_amazonAthenaClientFactory = A.Fake <IAmazonAthenaClientFactory>();
_amazonAthena = A.Fake <IAmazonAthena>();
A.CallTo(() => _amazonAthenaClientFactory.Create())
.Returns(_amazonAthena);
_athenaClientService = new AthenaClientService(_amazonAthenaClientFactory, new NullLogger <AthenaClientService>());
}
public AthenaSentimentReporter(
IAmazonAthena athena,
string database,
string table,
AnalyticsResources analyticsResources,
ILogger <AthenaSentimentReporter> logger)
{
this.athena = athena;
this.workgroup = analyticsResources.SentimentReportWorkgroup;
this.database = database;
this.table = table;
this.logger = logger;
}
static Entrypoint()
{
#if DEBUG
AWSConfigs.AWSProfileName = $"{Environment.UserName}-dev";
AWSConfigs.AWSProfilesLocation = $"{Environment.GetEnvironmentVariable("UserProfile")}\\.aws\\credentials";
StoredProfileAWSCredentials Creds = new StoredProfileAWSCredentials();
_S3Client = new AmazonS3Client(Creds);
_AthenaClient = new AmazonAthenaClient(Creds);
_SNSClient = new AmazonSimpleNotificationServiceClient(Creds);
#else
_S3Client = new AmazonS3Client();
_AthenaClient = new AmazonAthenaClient();
_SNSClient = new AmazonSimpleNotificationServiceClient();
#endif
_SNSTopic = Environment.GetEnvironmentVariable("SNS_TOPIC");
}
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);
}
private Amazon.Athena.Model.ListDatabasesResponse CallAWSServiceOperation(IAmazonAthena client, Amazon.Athena.Model.ListDatabasesRequest request)
{
Utils.Common.WriteVerboseEndpointMessage(this, client.Config, "Amazon Athena", "ListDatabases");
try
{
#if DESKTOP
return(client.ListDatabases(request));
#elif CORECLR
return(client.ListDatabasesAsync(request).GetAwaiter().GetResult());
#else
#error "Unknown build edition"
#endif
}
catch (AmazonServiceException exc)
{
var webException = exc.InnerException as System.Net.WebException;
if (webException != null)
{
throw new Exception(Utils.Common.FormatNameResolutionFailureMessage(client.Config, webException.Message), webException);
}
throw;
}
}
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> >());
}
}
protected override void ProcessRecord()
{
base.ProcessRecord();
Client = CreateClient(_CurrentCredentials, _RegionEndpoint);
}
internal ListDataCatalogsPaginator(IAmazonAthena client, ListDataCatalogsRequest request)
{
this._client = client;
this._request = request;
}
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);
}
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);
}
internal ListNamedQueriesPaginator(IAmazonAthena client, ListNamedQueriesRequest request)
{
this._client = client;
this._request = request;
}
internal GetQueryResultsPaginator(IAmazonAthena client, GetQueryResultsRequest request)
{
this._client = client;
this._request = request;
}
internal ListTagsForResourcePaginator(IAmazonAthena client, ListTagsForResourceRequest request)
{
this._client = client;
this._request = request;
}
internal ListPreparedStatementsPaginator(IAmazonAthena client, ListPreparedStatementsRequest request)
{
this._client = client;
this._request = request;
}
/// <summary>
/// Execute an SQL query using Amazon Athena, wait for the result of the query
/// and map the result to a C# Entity object. If the Amazon Athena did not complete
/// processing the giving query and the timeout is reached, this method
/// will throw exception and return the QueryExecutionId that
/// can be used later to get the result
/// </summary>
/// <typeparam name="T">Type of the entity result</typeparam>
/// <param name="athenaClient">Instance of Amazon Athena Client</param>
/// <param name="queryString">SQL query</param>
/// <param name="timeoutInMinutes"> default 2 minutes: timeout in minutes for the application to abort waiting.</param>
/// <returns>The enumerator, which supports a simple iteration over a collection of a specified type</returns>
public static async Task <IEnumerable <T> > QueryAsync <T>(this IAmazonAthena athenaClient, string queryString, int timeoutInMinutes = 2) where T : new()
{
if (athenaClient == null || string.IsNullOrEmpty(queryString))
{
return(default);
internal ListQueryExecutionsPaginator(IAmazonAthena client, ListQueryExecutionsRequest request)
{
this._client = client;
this._request = request;
}
internal AthenaPaginatorFactory(IAmazonAthena client)
{
this.client = client;
}
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>());
}
internal ListWorkGroupsPaginator(IAmazonAthena client, ListWorkGroupsRequest request)
{
this._client = client;
this._request = request;
}
internal ListTableMetadataPaginator(IAmazonAthena client, ListTableMetadataRequest request)
{
this._client = client;
this._request = request;
}
