/// <summary>
/// Asynchronously but eagerly fetches a set of rows, up to the specified count, providing a page of results with a next page token
/// if more results are available. This is typically used within web applications, where the next page token
/// is propagated to the client along with the results, so that the next page can be retrieved in another request.
/// </summary>
/// <param name="pageSize">The maximum number of rows to retrieve. Must be positive.</param>
/// <param name="cancellationToken">The token to monitor for cancellation requests.</param>
/// <returns>A task representing the asynchronous operation. When complete, the result is
/// an in-memory result set of at most the given number of rows.</returns>
public async Task <BigQueryPage> ReadPageAsync(int pageSize, CancellationToken cancellationToken = default)
{
GaxPreconditions.CheckArgumentRange(pageSize, nameof(pageSize), 1, int.MaxValue);
GetQueryResultsOptions clonedOptions = _options?.Clone() ?? new GetQueryResultsOptions();
List <BigQueryRow> rows = new List <BigQueryRow>(pageSize);
// Work out whether to use the response we've already got, or create a new one.
GetQueryResultsResponse response = _response;
if (response.Rows?.Count > pageSize)
{
// Oops. Do it again from scratch, with a useful page size.
clonedOptions.PageSize = pageSize;
response = await _client.GetRawQueryResultsAsync(JobReference, clonedOptions, timeoutBase : null, cancellationToken).ConfigureAwait(false);
}
// First add the rows from the existing response.
rows.AddRange(ConvertResponseRows(response));
string pageToken = response.PageToken;
clonedOptions.StartIndex = null;
// Now keep going until we've filled the result set or know there's no more data.
while (rows.Count < pageSize && pageToken != null)
{
clonedOptions.PageToken = pageToken;
clonedOptions.PageSize = pageSize - rows.Count;
var nextResponse = await _client.GetRawQueryResultsAsync(JobReference, clonedOptions, timeoutBase : null, cancellationToken).ConfigureAwait(false);
rows.AddRange(ConvertResponseRows(nextResponse));
pageToken = nextResponse.PageToken;
}
return(new BigQueryPage(rows, Schema, JobReference, TableReference, pageToken));
}
internal BigQueryResults(BigQueryClient client, GetQueryResultsResponse response, TableReference tableReference, ListRowsOptions options)
{
_client = GaxPreconditions.CheckNotNull(client, nameof(client));
_response = GaxPreconditions.CheckNotNull(response, nameof(response));
TableReference = GaxPreconditions.CheckNotNull(tableReference, nameof(tableReference));
_options = options;
}
internal BigqueryResult(BigqueryClient client, GetQueryResultsResponse firstResult, Func<GetQueryResultsRequest> requestProvider)
{
_client = client;
Schema = firstResult.Schema;
var firstRows = firstResult.Rows ?? new TableRow[0];
Rows = firstRows.Concat(GetRemainingRows(requestProvider, firstResult.PageToken)).Select(row => new Row(this, row));
}
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>
/// Unmarshaller the response from the service to the response class.
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public override AmazonWebServiceResponse Unmarshall(JsonUnmarshallerContext context)
{
GetQueryResultsResponse response = new GetQueryResultsResponse();
context.Read();
int targetDepth = context.CurrentDepth;
while (context.ReadAtDepth(targetDepth))
{
if (context.TestExpression("results", targetDepth))
{
var unmarshaller = new ListUnmarshaller <List <ResultField>, ListUnmarshaller <ResultField, ResultFieldUnmarshaller> >(new ListUnmarshaller <ResultField, ResultFieldUnmarshaller>(ResultFieldUnmarshaller.Instance));
response.Results = unmarshaller.Unmarshall(context);
continue;
}
if (context.TestExpression("statistics", targetDepth))
{
var unmarshaller = QueryStatisticsUnmarshaller.Instance;
response.Statistics = unmarshaller.Unmarshall(context);
continue;
}
if (context.TestExpression("status", targetDepth))
{
var unmarshaller = StringUnmarshaller.Instance;
response.Status = unmarshaller.Unmarshall(context);
continue;
}
}
return(response);
}
/// <summary>
/// Unmarshaller the response from the service to the response class.
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public override AmazonWebServiceResponse Unmarshall(JsonUnmarshallerContext context)
{
GetQueryResultsResponse response = new GetQueryResultsResponse();
context.Read();
int targetDepth = context.CurrentDepth;
while (context.ReadAtDepth(targetDepth))
{
if (context.TestExpression("NextToken", targetDepth))
{
var unmarshaller = StringUnmarshaller.Instance;
response.NextToken = unmarshaller.Unmarshall(context);
continue;
}
if (context.TestExpression("ResultSet", targetDepth))
{
var unmarshaller = ResultSetUnmarshaller.Instance;
response.ResultSet = unmarshaller.Unmarshall(context);
continue;
}
if (context.TestExpression("UpdateCount", targetDepth))
{
var unmarshaller = LongUnmarshaller.Instance;
response.UpdateCount = unmarshaller.Unmarshall(context);
continue;
}
}
return(response);
}
/// <summary>
/// Constructs a new set of results.
/// </summary>
/// <remarks>
/// This is public to allow tests to construct instances for production code to consume;
/// production code should not normally construct instances itself.
/// </remarks>
/// <param name="client">The client to use for further operations. Must not be null.</param>
/// <param name="response">The response to a GetQueryResults API call. Must not be null.</param>
/// <param name="tableReference">A reference to the table containing the results.
/// May be null. (For example, script queries don't store results in tables.)</param>
/// <param name="options">Options to use when fetching results. May be null.</param>
public BigQueryResults(BigQueryClient client, GetQueryResultsResponse response, TableReference tableReference, GetQueryResultsOptions options)
{
_client = GaxPreconditions.CheckNotNull(client, nameof(client));
_response = GaxPreconditions.CheckNotNull(response, nameof(response));
TableReference = tableReference;
_options = options;
_fieldNames = response.Schema?.IndexFieldNames();
}
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);
}
private QueryResponse(BigQueryContext context, string query, TimeSpan executionTime,
GetQueryResultsResponse queryResponse, bool isDynamic, IRowsParser rowsParser)
: this(
context, query, executionTime, isDynamic, rowsParser, queryResponse.Rows, queryResponse.Schema,
queryResponse.CacheHit, queryResponse.ETag, queryResponse.Kind,
queryResponse.PageToken, queryResponse.TotalBytesProcessed, queryResponse.TotalRows,
queryResponse.JobComplete, queryResponse.JobReference)
{
}
public static List <List <object> > ReadData(this GetQueryResultsResponse getQueryResultsResponse, int skip = 1)
{
var columnInfos = getQueryResultsResponse.ResultSet.ResultSetMetadata.ColumnInfo;
var results = new List <List <object> >();
foreach (var row in getQueryResultsResponse.ResultSet.Rows.Skip(skip))
{
results.Add(row.ReadRowAsObjects(columnInfos));
}
return(results);
}
public static Dictionary <string, string> ToSchemaDictionary(this GetQueryResultsResponse getQueryResultsResponse)
{
var columnInfos = getQueryResultsResponse.ResultSet.ResultSetMetadata.ColumnInfo;
var results = new Dictionary <string, string>();
foreach (var column in columnInfos)
{
results.Add(column.Name, column.MapCSharpType());
}
return(results);
}
// [END build_service]
public IList <TableRow> ExecuteQuery(string querySql, BigqueryService bigquery, string projectId)
{
var request = new Google.Apis.Bigquery.v2.JobsResource.QueryRequest(
bigquery, new Google.Apis.Bigquery.v2.Data.QueryRequest()
{
Query = querySql,
}, projectId);
var query = request.Execute();
GetQueryResultsResponse queryResult = bigquery.Jobs.GetQueryResults(
projectId, query.JobReference.JobId).Execute();
return(queryResult.Rows);
}
public static List <T> ReadRows <T>(this GetQueryResultsResponse getQueryResultsResponse, int skip = 1) where T : class, new()
{
var columnInfos = getQueryResultsResponse.ResultSet.ResultSetMetadata.ColumnInfo;
var type = typeof(T);
var propertyInfos = type.GetProperties().ToDictionary(p => p.Name.ToLower(), p => p);
var results = new List <T>();
foreach (var row in getQueryResultsResponse.ResultSet.Rows.Skip(skip))
{
results.Add(row.ReadRowAs <T>(columnInfos, propertyInfos));
}
return(results);
}
/// <summary>
/// this is the code generation tool to build C# type
/// </summary>
/// <param name="getQueryResultsResponse"></param>
/// <returns></returns>
public static string ToCSharpType(this GetQueryResultsResponse getQueryResultsResponse)
{
var columnInfos = getQueryResultsResponse.ResultSet.ResultSetMetadata.ColumnInfo;
StringBuilder stb = new StringBuilder();
stb.AppendLine($"public class QueryResult");
stb.AppendLine($"{{");
foreach (var column in columnInfos)
{
stb.AppendLine($"\tpublic {column.MapCSharpType()} {column.Name} {{ get; set; }}");
}
stb.AppendLine("}}");
return(stb.ToString());
}
public static List <FieldMapping> ToFieldMapping(this GetQueryResultsResponse getQueryResultsResponse)
{
var columnInfos = getQueryResultsResponse.ResultSet.ResultSetMetadata.ColumnInfo;
var results = new List <FieldMapping>();
foreach (var column in columnInfos)
{
results.Add(new FieldMapping()
{
SourceFieldName = column.Name,
MappedName = column.Name,
MappedType = column.ToAthenaType()
});
}
return(results);
}
/// <summary>
/// Unmarshaller the response from the service to the response class.
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public override AmazonWebServiceResponse Unmarshall(JsonUnmarshallerContext context)
{
GetQueryResultsResponse response = new GetQueryResultsResponse();
context.Read();
int targetDepth = context.CurrentDepth;
while (context.ReadAtDepth(targetDepth))
{
if (context.TestExpression("ErrorMessage", targetDepth))
{
var unmarshaller = StringUnmarshaller.Instance;
response.ErrorMessage = unmarshaller.Unmarshall(context);
continue;
}
if (context.TestExpression("NextToken", targetDepth))
{
var unmarshaller = StringUnmarshaller.Instance;
response.NextToken = unmarshaller.Unmarshall(context);
continue;
}
if (context.TestExpression("QueryResultRows", targetDepth))
{
var unmarshaller = new ListUnmarshaller <List <Dictionary <string, string> >, ListUnmarshaller <Dictionary <string, string>, DictionaryUnmarshaller <string, string, StringUnmarshaller, StringUnmarshaller> > >(new ListUnmarshaller <Dictionary <string, string>, DictionaryUnmarshaller <string, string, StringUnmarshaller, StringUnmarshaller> >(new DictionaryUnmarshaller <string, string, StringUnmarshaller, StringUnmarshaller>(StringUnmarshaller.Instance, StringUnmarshaller.Instance)));
response.QueryResultRows = unmarshaller.Unmarshall(context);
continue;
}
if (context.TestExpression("QueryStatistics", targetDepth))
{
var unmarshaller = QueryStatisticsUnmarshaller.Instance;
response.QueryStatistics = unmarshaller.Unmarshall(context);
continue;
}
if (context.TestExpression("QueryStatus", targetDepth))
{
var unmarshaller = StringUnmarshaller.Instance;
response.QueryStatus = unmarshaller.Unmarshall(context);
continue;
}
}
return(response);
}
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);
}
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 BigQueryResults(BigQueryClient client, GetQueryResultsResponse response, GetQueryResultsOptions options)
{
_client = GaxPreconditions.CheckNotNull(client, nameof(client));
_response = GaxPreconditions.CheckNotNull(response, nameof(response));
_options = options;
}
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 BigQueryResults(BigQueryClient client, GetQueryResultsResponse response, TableReference tableReference, ListRowsOptions options)
: this(client, response, tableReference, options?.ToGetQueryResultsOptions())
{
}
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);
}
