public async Task <GetQueryResultsResponse> ReadOneResult(GetQueryResultsRequest request)
{
var response = await amazonAthenaClient.GetQueryResultsAsync(request);
request.NextToken = response.NextToken;
return(response);
}
/**
* This code calls Athena and retrieves the results of a query.
* The query must be in a completed state before the results can be retrieved and
* paginated. The first row of results are the column headers.
*/
private static void processResultRows(AmazonAthenaClient athenaClient, String queryExecutionId)
{
GetQueryResultsRequest getQueryResultsRequest = new GetQueryResultsRequest()
{
// Max Results can be set but if its not set, it will choose the maximum page size
// As of the writing of this code, the maximum value is 1000
// MaxResults = 1000
QueryExecutionId = queryExecutionId
};
var getQueryResultsResponse = athenaClient.GetQueryResults(getQueryResultsRequest);
while (true)
{
var results = getQueryResultsResponse.ResultSet.Rows;
foreach (Row row in results)
{
// Process the row. The first row of the first page holds the column names.
processRow(row, getQueryResultsResponse.ResultSet.ResultSetMetadata.ColumnInfo);
}
// If nextToken is null, there are no more pages to read. Break out of the loop.
if (String.IsNullOrEmpty(getQueryResultsResponse.NextToken))
{
break;
}
getQueryResultsRequest.NextToken = getQueryResultsResponse.NextToken;
getQueryResultsResponse = athenaClient.GetQueryResults(getQueryResultsRequest);
}
}
internal virtual GetQueryResultsResponse GetQueryResults(GetQueryResultsRequest request)
{
var marshaller = GetQueryResultsRequestMarshaller.Instance;
var unmarshaller = GetQueryResultsResponseUnmarshaller.Instance;
return(Invoke <GetQueryResultsRequest, GetQueryResultsResponse>(request, marshaller, unmarshaller));
}
/// <summary>
/// Returns the results of a single query execution specified by <code>QueryExecutionId</code>.
/// This request does not execute the query but returns results. Use <a>StartQueryExecution</a>
/// to run a query.
/// </summary>
/// <param name="request">Container for the necessary parameters to execute the GetQueryResults service method.</param>
///
/// <returns>The response from the GetQueryResults 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>
/// <seealso href="http://docs.aws.amazon.com/goto/WebAPI/athena-2017-05-18/GetQueryResults">REST API Reference for GetQueryResults Operation</seealso>
public GetQueryResultsResponse GetQueryResults(GetQueryResultsRequest request)
{
var marshaller = new GetQueryResultsRequestMarshaller();
var unmarshaller = GetQueryResultsResponseUnmarshaller.Instance;
return(Invoke <GetQueryResultsRequest, GetQueryResultsResponse>(request, marshaller, unmarshaller));
}
private async Task ProcessResultRows(AmazonAthenaClient client, string queryExecutionId, ParsedAthenaResponse niceAthenaResult)
{
GetQueryResultsRequest getQueryResultsRequest = new GetQueryResultsRequest();
// Max Results can be set but if its not set,
// it will choose the maximum page size
// As of the writing of this code, the maximum value is 1000
// .withMaxResults(1000)
getQueryResultsRequest.QueryExecutionId = queryExecutionId;
var getQueryResultsResponse = await client.GetQueryResultsAsync(getQueryResultsRequest);
List <ColumnInfo> columnInfoList = getQueryResultsResponse.ResultSet.ResultSetMetadata.ColumnInfo;
ResultSet responseData = getQueryResultsResponse.ResultSet;
ReadHeadersFromColumnInfo(getQueryResultsResponse.ResultSet, niceAthenaResult);
while (true)
{
//Convert the returned response to a serialisable JSON object
ProcessRow_NameCounts(responseData, niceAthenaResult);
// If the nextToken is null, there are no more pages to read. Break out of the loop.
if (getQueryResultsResponse.NextToken == null)
{
break;
}
getQueryResultsResponse = await client.GetQueryResultsAsync(
new GetQueryResultsRequest { NextToken = getQueryResultsResponse.NextToken });
Console.WriteLine("getting more data from response...");
}
}
public override void Invoke(AWSCredentials creds, RegionEndpoint region, int maxItems)
{
AmazonAthenaConfig config = new AmazonAthenaConfig();
config.RegionEndpoint = region;
ConfigureClient(config);
AmazonAthenaClient client = new AmazonAthenaClient(creds, config);
GetQueryResultsResponse resp = new GetQueryResultsResponse();
do
{
GetQueryResultsRequest req = new GetQueryResultsRequest
{
NextToken = resp.NextToken
,
MaxResults = maxItems
};
resp = client.GetQueryResults(req);
CheckError(resp.HttpStatusCode, "200");
foreach (var obj in resp.UpdateCount)
{
AddObject(obj);
}
foreach (var obj in resp.ResultSet)
{
AddObject(obj);
}
}while (!string.IsNullOrEmpty(resp.NextToken));
}
/// <summary>
/// Initiates the asynchronous execution of the GetQueryResults operation.
/// </summary>
///
/// <param name="request">Container for the necessary parameters to execute the GetQueryResults 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/GetQueryResults">REST API Reference for GetQueryResults Operation</seealso>
public virtual Task <GetQueryResultsResponse> GetQueryResultsAsync(GetQueryResultsRequest request, System.Threading.CancellationToken cancellationToken = default(CancellationToken))
{
var marshaller = GetQueryResultsRequestMarshaller.Instance;
var unmarshaller = GetQueryResultsResponseUnmarshaller.Instance;
return(InvokeAsync <GetQueryResultsRequest, GetQueryResultsResponse>(request, marshaller,
unmarshaller, cancellationToken));
}
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);
}
internal virtual GetQueryResultsResponse GetQueryResults(GetQueryResultsRequest request)
{
var options = new InvokeOptions();
options.RequestMarshaller = GetQueryResultsRequestMarshaller.Instance;
options.ResponseUnmarshaller = GetQueryResultsResponseUnmarshaller.Instance;
return(Invoke <GetQueryResultsResponse>(request, options));
}
/// <summary>
/// Initiates the asynchronous execution of the GetQueryResults operation.
/// </summary>
///
/// <param name="request">Container for the necessary parameters to execute the GetQueryResults 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/GetQueryResults">REST API Reference for GetQueryResults Operation</seealso>
public virtual Task <GetQueryResultsResponse> GetQueryResultsAsync(GetQueryResultsRequest request, System.Threading.CancellationToken cancellationToken = default(CancellationToken))
{
var options = new InvokeOptions();
options.RequestMarshaller = GetQueryResultsRequestMarshaller.Instance;
options.ResponseUnmarshaller = GetQueryResultsResponseUnmarshaller.Instance;
return(InvokeAsync <GetQueryResultsResponse>(request, options, cancellationToken));
}
public void ModifyRequest_BothPageTokenAndStartIndexSet()
{
var options = new GetQueryResultsOptions
{
StartIndex = 10,
PageToken = "foo"
};
GetQueryResultsRequest request = new GetQueryResultsRequest(new BigqueryService(), "project", "job");
Assert.Throws <ArgumentException>(() => options.ModifyRequest(request));
}
public void ModifyRequest_NoneSet()
{
var options = new GetQueryResultsOptions();
GetQueryResultsRequest request = new GetQueryResultsRequest(new BigqueryService(), "project", "job");
options.ModifyRequest(request);
Assert.Equal(null, request.StartIndex);
Assert.Equal(null, request.PageToken);
Assert.Equal(null, request.MaxResults);
Assert.Equal(null, request.TimeoutMs);
}
/// <summary>
/// you have to skip the first row, which is the schema
/// </summary>
/// <param name="request"></param>
/// <param name="responseReceived"></param>
/// <returns></returns>
public IEnumerable <Row> EnumerateRows(GetQueryResultsRequest request, Action <GetQueryResultsResponse> responseReceived)
{
do
{
var response = ReadOneResult(request).GetAwaiter().GetResult();
responseReceived?.Invoke(response);
foreach (var row in response.ResultSet.Rows)
{
yield return(row);
}
}while (request.NextToken != null);
}
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 void ModifyRequest_NoOp()
{
var options = new GetQueryResultsOptions
{
PageSize = 25,
PageToken = "foo",
Timeout = TimeSpan.FromSeconds(5),
};
GetQueryResultsRequest request = new GetQueryResultsRequest(new BigqueryService(), "project", "job");
options.ModifyRequest(request);
Assert.Null(request.PageToken);
Assert.Null(request.MaxResults);
Assert.Null(request.TimeoutMs);
}
internal void ModifyRequest(GetQueryResultsRequest request)
{
if (PageSize != null)
{
request.MaxResults = PageSize;
}
if (StartIndex != null)
{
request.StartIndex = StartIndex;
}
if (Timeout != null)
{
request.TimeoutMs = (long)Timeout.Value.TotalMilliseconds;
}
}
public void ModifyRequest_AllSetExceptPageToken()
{
var options = new GetQueryResultsOptions
{
StartIndex = 10,
PageSize = 25,
Timeout = TimeSpan.FromSeconds(5),
};
GetQueryResultsRequest request = new GetQueryResultsRequest(new BigqueryService(), "project", "job");
options.ModifyRequest(request);
Assert.Equal(10UL, request.StartIndex);
Assert.Equal(25, request.MaxResults);
Assert.Equal(5 * 1000, request.TimeoutMs);
}
public void ModifyRequest()
{
var options = new GetQueryResultsOptions
{
PageSize = 25,
PageToken = "foo",
Timeout = TimeSpan.FromSeconds(5),
};
GetQueryResultsRequest request = new GetQueryResultsRequest(new BigqueryService(), "project", "job");
options.ModifyRequest(request);
Assert.Equal("foo", request.PageToken);
Assert.Equal(25, request.MaxResults);
// ModifyRequest doesn't modify the timeout, as that's done externally
Assert.Null(request.TimeoutMs);
}
public IEnumerable <T> EnumerateResults <T>(GetQueryResultsRequest request) where T : class, new()
{
int first = 1;
do
{
var response = ReadOneResult(request).GetAwaiter().GetResult();
foreach (var item in response.ReadRows <T>(first))
{
yield return(item);
}
if (first == 1)
{
first = 0;
}
}while (request.NextToken != null);
}
internal void ModifyRequest(GetQueryResultsRequest request)
{
if (PageToken != null && StartIndex != null)
{
throw new ArgumentException($"Cannot specify both {nameof(PageToken)} and {nameof(StartIndex)}");
}
if (PageSize != null)
{
request.MaxResults = PageSize;
}
if (PageToken != null)
{
request.PageToken = PageToken;
}
if (StartIndex != null)
{
request.StartIndex = StartIndex;
}
}
public async Task <List <Dictionary <string, string> > > GetQueryResultsAsync(string queryExecutionId)
{
var items = new List <Dictionary <string, string> >();
var getQueryResultsRequest = new GetQueryResultsRequest()
{
QueryExecutionId = queryExecutionId,
MaxResults = 1000
};
var client = _amazonAthenaClientFactory.Create();
GetQueryResultsResponse getQueryResultsResponse = null;
/* Page through results and request additional pages if available */
do
{
getQueryResultsResponse = await client.GetQueryResultsAsync(getQueryResultsRequest);
getQueryResultsResponse.EnsureSuccessStatusCode();
/* Loop over result set and create a dictionary with column name for key and data for value */
foreach (Row row in getQueryResultsResponse.ResultSet.Rows)
{
var dict = new Dictionary <string, string>();
for (var i = 0; i < getQueryResultsResponse.ResultSet.ResultSetMetadata.ColumnInfo.Count; i++)
{
dict.Add(getQueryResultsResponse.ResultSet.ResultSetMetadata.ColumnInfo[i].Name, row.Data[i].VarCharValue);
}
items.Add(dict);
}
if (getQueryResultsResponse.NextToken != null)
{
getQueryResultsRequest.NextToken = getQueryResultsResponse.NextToken;
}
}while (getQueryResultsResponse.NextToken != null);
return(items);
}
internal void ModifyRequest(GetQueryResultsRequest request)
{
if (PageToken != null && StartIndex != null)
{
throw new ArgumentException($"Cannot specify both {nameof(PageToken)} and {nameof(StartIndex)}");
}
if (PageSize != null)
{
request.MaxResults = PageSize;
}
if (PageToken != null)
{
request.PageToken = PageToken;
}
if (StartIndex != null)
{
request.StartIndex = StartIndex;
}
if (Timeout != null)
{
request.TimeoutMs = (long)Timeout.Value.TotalMilliseconds;
}
}
public async Task <AthenaQueryFlatResult> GetFlatResult(GetQueryResultsRequest request)
{
bool columnsRead = false;
var data = new List <List <string> >();
var result = new AthenaQueryFlatResult()
{
Data = data
};
do
{
var response = await ReadOneResult(request);
if (!columnsRead)
{
result.Columns = response.ResultSet.ResultSetMetadata.ColumnInfo.Select(c => c.Name).ToList();
}
foreach (var row in response.ResultSet.Rows)
{
data.Add(row.Data.Select(c => c.VarCharValue).ToList());
}
}while (request.NextToken != null);
return(result);
}
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 <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 void ModifyRequest(GetQueryResultsRequest request)
{
// Nothing to do? We may have something later...
}
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);
}
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);
}
