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 + "\"}");
}
}
public static IDisposable Dump(IList <string> etlfiles,
Action <Type> onStart,
Action <object> onNext,
Action <Exception> onError,
string query)
{
StringWriter writer = new StringWriter();
StringWriter error = new StringWriter();
QueryExecutionContext context = QueryExecutionContext.CreateFromFiles(etlfiles, onStart, onNext);
QueryCompiler.CompileAndRun(context,
query,
error,
writer);
string output = error.ToString();
if (!String.IsNullOrEmpty(output))
{
if (onError != null)
{
onError(new Exception(output));
}
}
return(context);
}
/**
* 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);
}
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);
}
}
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>
/// Applies term filters to an <see cref="IQuery{T}"/>
/// </summary>
public async ValueTask <IQuery <T> > ExecuteAsync(QueryExecutionContext <T> context)
{
var visitor = new QueryFilterVisitor <T>();
foreach (var term in _terms.Values)
{
// TODO optimize value task.
await VisitTerm(TermOptions, context, visitor, term);
}
// Execute always run terms. These are not added to the terms list.
foreach (var termOption in TermOptions)
{
if (!termOption.Value.AlwaysRun)
{
continue;
}
if (!_terms.ContainsKey(termOption.Key))
{
var alwaysRunNode = new NamedTermNode(termOption.Key, new UnaryNode(String.Empty, OperateNodeQuotes.None));
await VisitTerm(TermOptions, context, visitor, alwaysRunNode);
}
}
return(context.Item);
}
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();
}
public void SetUp()
{
record = new Record();
document = new Document();
mapper = new MockRepository().StrictMock <IDocumentMapper <Record> >();
executor = new TestableLuceneQueryExecutor <Record>(new Context(new RAMDirectory(), new object()), _ => record, mapper);
context = new QueryExecutionContext();
}
public static async Task Save(QueryExecutionContext query, QueryResponseContext response)
{
string?statement = NormalizeSql(query.GetSqlStatement());
if (statement is null)
{
return;
}
string sql;
string code = QueryHistory.GetCode(ComputeHash(statement), query.Server.Type);
var history = await LoadByCode(code);
if (history is null)
{
history = QueryHistory.Create(code, statement, query, response);
sql = "INSERT INTO query (code, status, type, sql, star, execution_count, last_executed_on, last_environment, last_database) VALUES " +
"( " +
$"'{history.Code}', " +
$"{(int)history.Status}, " +
$"{(int)history.Type}, " +
$"'{history.Sql.Replace("'", "''")}', " +
$"{(history.Star ? 1 : 0)}, " +
$"{history.ExecutionCount}, " +
$"'{history.LastExecutedOn:yyyy-MM-dd HH:mm:ss}', " +
$"'{history.Stats.Last().Environment}', " +
$"'{history.Stats.Last().Database}' " +
");";
}
else
{
history.UpdateStatistics(query, response);
sql = $"UPDATE query SET " +
$"status = {(int)history.Status}, " +
$"execution_count = {history.ExecutionCount}, " +
$"last_executed_on = '{history.LastExecutedOn:yyyy-MM-dd HH:mm:ss}', " +
$"last_environment = '{history.Stats.Last().Environment}', " +
$"last_database = '{history.Stats.Last().Database}' " +
$"WHERE code = '{history.Code}';";
}
var stat = history.Stats.Last();
sql += "INSERT INTO stat (code, status, executed_on, environment, database, server_connection, elapsed, row_count, records_affected) VALUES " +
"( " +
$"'{history.Code}', " +
$"{(int)stat.Status}, " +
$"'{stat.ExecutedOn:yyyy-MM-dd HH:mm:ss}', " +
$"'{stat.Environment}', " +
$"'{stat.Database}', " +
$"'{stat.ServerConnection}', " +
$"{stat.Elapsed}, " +
$"{stat.RowCount}, " +
$"{stat.RecordsAffected}" +
");";
await ServerConnection.ExecuteNonQuery(sql);
}
public Task <ApiResponse <TResult> > Process <TConnection, TQuery, TResult>(
Func <QueryExecutionContext <TConnection, TQuery, TResult>, Task <ApiResponse <TResult> > > next,
QueryExecutionContext <TConnection, TQuery, TResult> context
)
where TConnection : IHattemConnection
where TQuery : IQuery <TResult>
{
CapturedContextStorage <TConnection, TQuery, TResult> .CapturedContext.Add(context);
return(next(context));
}
public Task <ApiResponse <TResult> > Process <TResult>(TConnection connection, IQuery <TResult> query)
{
var queryHandler = _handlerProvider.GetQueryHandler <TResult>(query.GetType());
var context = QueryExecutionContext.Create(
connection,
queryHandler,
query);
return(_executor.Process(context));
}
public async Task QueryAsync(List <object> results, QueryStep queryStep, IGraphRequestContext graphRequestContext,
QueryExecutionContext queryExecutionContext,
List <ConnectionEdgeDestinationFilter> connectionEdgeDestinationFilters)
{
var selections = GetFirstComplexSelectValues(queryStep);
if (selections != null && selections.Count > 0)
{
await QueryAndPopulateEdgeConnections(selections, results, graphRequestContext, connectionEdgeDestinationFilters, queryExecutionContext);
}
}
public ActionResult <IEnumerable <CodeLens> > GetCodeLenses(QueryExecutionContext context)
{
string key = $"CodeLens-{context.Id}";
if (!_cache.TryGetValue <IEnumerable <CodeLens> >(key, out var lenses))
{
lenses = context.Server.GetDatabaseSystem().LoadCodeLens(context.GetSqlStatement());
_cache.Set(key, lenses, CodeLensCacheEntryOptions);
}
return(Ok(lenses));
}
static void Main(string[] args)
{
using (var client = new AmazonAthenaClient(Amazon.RegionEndpoint.USEast1)) {
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).Wait();
}
}
private async Task <List <Dictionary <String, String> > > queryAthena(String date)
{
using (var athenaClient = new AmazonAthenaClient(Amazon.RegionEndpoint.USEast2)) {
QueryExecutionContext qContext = new QueryExecutionContext();
qContext.Database = ATHENA_DB;
ResultConfiguration resConf = new ResultConfiguration();
resConf.OutputLocation = ATHENA_TEMP_PATH;
List <Dictionary <String, String> > items = await runQuery(athenaClient, qContext, resConf, date);
return(items);
}
}
private List <object> GetEmail(QueryExecutionContext queryExecutionContext)
{
var graphRequestContext = queryExecutionContext.RequestContext;
if (graphRequestContext.HttpRequest.Security != null)
{
return(new List <object> {
graphRequestContext.HttpRequest.Security.ClaimsPrincipal.GetEmail()
});
}
throw new NotImplementedException();
}
public static async Task <JRaw> QueryAthenaAndSend(string datestring)
{
using (var client = new AmazonAthenaClient(Amazon.RegionEndpoint.USEast2))
{
QueryExecutionContext qContext = new QueryExecutionContext();
qContext.Database = ATHENA_DB;
ResultConfiguration resConf = new ResultConfiguration();
resConf.OutputLocation = ATHENA_TEMP_PATH;
Console.WriteLine("Created Athena Client");
List <Dictionary <String, String> > items = await Run(client, qContext, resConf, datestring);
if (items.Count == 1 && items[0].ContainsKey("error"))
{
items[0].TryGetValue("error", out string errorinfo);
return(new JRaw(errorinfo));
}
// return items.ToString();
JObject obj = new JObject();
if (items.Count == 0)
{
obj.Add("count", "zero");
return(obj.ToObject <JRaw>());
}
obj.Add("count", items.Count);
for (int i = 1; i < items.Count; i++)
{
JProperty nameProp = null;
JProperty idProp = null;
foreach (KeyValuePair <String, String> pair in items[i])
{
if (pair.Key == "emp_name")
{
nameProp = new JProperty("name", pair.Value);
}
if (pair.Key == "emp_id")
{
idProp = new JProperty("id", pair.Value);
}
}
if (nameProp != null && idProp != null)
{
obj.Add("emp " + i, new JObject(nameProp, idProp));
}
else
{
return(null);
}
}
return(obj.ToObject <JRaw>());
}
}
public override List <T> LoadWhere <T>(Entity entity, ICompiled query, Parameter[] parameters, int page = 0, int pageSize = 0, bool ascending = true, params Property[] orderBy)
{
Transaction trans = Transaction.RunningTransaction;
QueryExecutionContext context = query.GetExecutionContext();
foreach (Parameter queryParameter in parameters)
{
if (queryParameter.Value is null)
{
context.SetParameter(queryParameter.Name, null);
}
else
{
context.SetParameter(queryParameter.Name, entity.Parent.PersistenceProvider.ConvertToStoredType(queryParameter.Value.GetType(), queryParameter.Value));
}
}
StringBuilder sb = new StringBuilder();
sb.Append(context.CompiledQuery.QueryText);
if (orderBy != null && orderBy.Length != 0)
{
Property odd = orderBy.FirstOrDefault(item => !entity.IsSelfOrSubclassOf(item.Parent));
if (odd != null)
{
throw new InvalidOperationException(string.Format("Order property '{0}' belongs to the entity '{1}' while the query only contains entities of type '{2}'.", odd.Name, odd.Parent.Name, entity.Name));
}
sb.Append(" ORDER BY ");
sb.Append(string.Join(", ", orderBy.Select(item => string.Concat("node.", item.Name))));
if (ascending == false)
{
sb.Append(" DESC ");
}
}
if (pageSize > 0)
{
sb.Append(" SKIP ");
sb.Append(page * pageSize);
sb.Append(" LIMIT ");
sb.Append(pageSize);
}
Dictionary <string, object?>?customState = null;
var args = entity.RaiseOnNodeLoading(trans, null, sb.ToString(), context.QueryParameters, ref customState);
var result = trans.Run(args.Cypher, args.Parameters);
return(Load <T>(entity, args, result, trans));
}
public static async Task <QueryResponseContext> ExecuteQuery(QueryExecutionContext context)
{
string queryId = Check.NotNull(context.Id, nameof(context.Id));
var cancellationToken = CreateToken(queryId);
try
{
return(await context.ExecuteQuery(cancellationToken));
}
finally
{
ReleaseToken(queryId);
}
}
public async Task <string> FunctionHandler(ILambdaContext context)
{
string strResult = "";
using (var client = new AmazonAthenaClient(Amazon.RegionEndpoint.APSouth1))
{
QueryExecutionContext qContext = new QueryExecutionContext();
qContext.Database = ATHENA_DB;
ResultConfiguration resConf = new ResultConfiguration();
resConf.OutputLocation = ATHENA_TEMP_PATH;
strResult = await run(client, qContext, resConf);
}
return(strResult);
}
public async Task <ActionResult <QueryResponseContext> > ExecuteQuery(QueryExecutionContext context)
{
if (string.IsNullOrWhiteSpace(context.GetSqlStatement()))
{
ModelState.AddModelError("Sql", $"The Sql field is required.");
}
if (ModelState.ErrorCount != 0)
{
return(BadRequest(new ValidationProblemDetails(ModelState)
{
Status = StatusCodes.Status400BadRequest,
}));
}
return(Ok(await QueryManager.ExecuteQuery(context)));
}
public static async Task Save(QueryExecutionContext query, QueryResponseContext response)
{
string?statement = NormalizeSql(query.GetSqlStatement());
if (statement is null)
{
return;
}
string sql;
string hash = ComputeHash(statement);
var history = await LoadByHash(hash);
if (history is null)
{
history = QueryHistory.Create(statement, hash, query, response);
sql = "INSERT INTO [data] (type, server_connection, sql, hash, executed_on, status, elapsed, row_count, records_affected, execution_count, star) VALUES " +
"( " +
$"{(int)history.Type}, " +
$"'{history.ServerConnection}', " +
$"'{history.Sql.Replace("'", "''")}', " +
$"'{history.Hash}', " +
$"'{history.ExecutedOn:yyyy-MM-dd HH:mm:ss}', " +
$"{(int)history.Status}, " +
$"{history.Elapsed}, " +
$"{history.RowCount}, " +
$"{history.RecordsAffected}, " +
$"{history.ExecutionCount}, " +
$"{(history.Star ? 1 : 0)}" +
");";
}
else
{
history.UpdateStatistics(query, response);
sql = "UPDATE [data] " +
$"SET server_connection = '{history.ServerConnection}', " +
$"executed_on = '{history.ExecutedOn:yyyy-MM-dd HH:mm:ss}', " +
$"status = {(int)history.Status}, " +
$"elapsed = {history.Elapsed}, " +
$"row_count = {history.RowCount}, " +
$"records_affected = {history.RecordsAffected}, " +
$"execution_count = {history.ExecutionCount} " +
$"WHERE id = {history.Id};";
}
await ServerConnection.ExecuteNonQuery(sql);
}
private async Task <object> GetValue(
TypeAccessor edgeObjectTypeAccessor,
ConnectionEdge connectionEdge,
IGraphRequestContext graphRequestContext,
List <ConnectionEdgeDestinationFilter> connectionEdgeDestinationFilters,
QueryExecutionContext queryExecutionContext)
{
var member = edgeObjectTypeAccessor.GetMembers().Single(x => x.Name == connectionEdge.MetaFieldName);
var destTypeAccessor = TypeAccessor.Create(member.Type);
var destQueryMember = destTypeAccessor.GetMembers().Single(m => m.GetAttribute(typeof(KeyAttribute), true) != null);
var qp = new QueryStep();
qp.QueryParameters.Add(new QueryParameter
{
ContextValue = new ContextValue {
Comparison = Comparisons.Equal, Values = new List <object> {
connectionEdge.DestinationId
}
},
MemberModel = new ModelMember(member.Type, destTypeAccessor, destQueryMember, false)
});
if (connectionEdgeDestinationFilters != null)
{
connectionEdgeDestinationFilters.Where(x => x.Type == member.Type.AssemblyQualifiedName)
.ToList().ForEach(connectionEdgeDestinationFilter =>
{
qp.QueryParameters.Add(new QueryParameter
{
ContextValue = new ContextValue {
Comparison = Comparisons.Equal, Values = connectionEdgeDestinationFilter.Mapper(queryExecutionContext)
},
MemberModel = connectionEdgeDestinationFilter.ModelMember
});
});
}
var entities = (await _graphQlRepositoryProvider.QueryAsync(EntityQueryName, qp, graphRequestContext)).ToList();
if (entities.Count > 1)
{
throw new InvalidOperationException(MultipleEntitiesDetectedError);
}
return(entities.SingleOrDefault());
}
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);
}
}
}
protected override async Task ExecuteAsync(CancellationToken stoppingToken)
{
_logger.LogInformation("Starting Owin Normalizing Service");
if (_deploymentSettings.IsFirstDeploy)
{
_logger.LogInformation("Fetching AWS Security Credentials");
var securityCredentials = await _securityProvider.GetTemporaryCredentialsAsync();
_logger.LogInformation($"Security Credientials fetched correctly - Temp Token = {securityCredentials.GetCredentials().Token} ");
using (var athenaClient = new AmazonAthenaClient(securityCredentials,
Amazon.RegionEndpoint.EUWest1))
{
var queryContext = new QueryExecutionContext
{
Catalog = AthenaConstants.ATHENA_CATALOG,
Database = AthenaConstants.ATHENA_DEFAULT_DATABASE
};
var ressultConfig = new ResultConfiguration();
ressultConfig.OutputLocation = AthenaConstants.ATHENA_OUTPUT_BUCKET;
_logger.LogInformation("Created Athena Client");
var listOfCountryData = await _athenaDataService.Run(athenaClient, queryContext, ressultConfig);
foreach (var countryEnrichedData in listOfCountryData)
{
_logger.LogInformation($"CountryCode: {countryEnrichedData.IsoCode}, Population: {countryEnrichedData.Population}, Median Age: {countryEnrichedData.MedianAge}, GPD Per Capita: {countryEnrichedData.GdpPerCapita}");
}
List <CountryEnrichedData> list = new List <CountryEnrichedData>();
await _athenaUpdateService.MapAndUpdate(listOfCountryData);
}
}
_logger.LogInformation($"Data is currently up to date, shutting down Owin Normalizer");
}
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 void SetUp()
{
document = new Document();
sample = new Sample();
queryExecutionContext = new QueryExecutionContext();
}
public async Task <ActionResult <IEnumerable <DatabaseObjectNode> > > GetDatabaseObjects(QueryExecutionContext context)
{
return(Ok(await context.Server.GetDatabaseSystem().LoadDatabaseObjectsAsync(context.Database, context.DatabaseObject)));
}
private static async Task <QueryResponseContext> ExecuteQuery(this QueryExecutionContext context, CancellationToken cancellationToken)
{
return(await context.Server.Execute(context.Database, context.GetSqlStatement() !, async (dbCommand, ct) =>
{
var response = new QueryResponseContext(context.Id);
var sw = new Stopwatch();
try
{
sw.Start();
using var ctr = ct.Register(() => dbCommand.Cancel());
using var dataReader = await dbCommand.ExecuteReaderAsync(ct);
do
{
var columnNames = new List <string>();
response.Columns.Clear();
response.Rows.Clear();
for (int i = 0; i < dataReader.FieldCount; i++)
{
columnNames.Add(columnNames.Contains(dataReader.GetName(i))
? dataReader.GetName(i) + (i + 1)
: dataReader.GetName(i));
response.Columns.Add(new ColumnDefinition(columnNames[i], dataReader.GetDataTypeName(i)));
}
while (await dataReader.ReadAsync(ct))
{
var dataRow = new ExpandoObject() as IDictionary <string, object?>;
for (int i = 0; i < dataReader.FieldCount; i++)
{
var value = dataReader[i];
dataRow.Add(columnNames[i], value is DBNull ? null : value);
}
response.Rows.Add(dataRow);
}
} while (await dataReader.NextResultAsync(ct));
response.RecordsAffected = dataReader.RecordsAffected;
}
catch (Exception ex)
{
if (ct.IsCancellationRequested)
{
response.Status = QueryResponseStatus.Canceled;
response.Columns.Clear();
response.Rows.Clear();
}
else
{
response.Status = QueryResponseStatus.Failed;
response.Error = ex.Message;
if (int.TryParse(ex.Data["Position"]?.ToString(), out int position))
{
response.ErrorPosition = position;
}
}
}
finally
{
response.Elapsed = sw.ElapsedMilliseconds;
await IgnoreErrorsAsync(() => History.Save(context, response));
}
return response;
}, dbCommand => dbCommand.CommandTimeout = 0, cancellationToken));
}
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> >());
}
}
