public async Task <string> LoadPartitionIfNotExists(string tableName, string keyFieldAssignment, string s3Location)
{
if (awsAthenaOptions.SQSOptions != null)
{
AWSSQSAPI awsSQSAPI = new AWSSQSAPI(awsAthenaOptions.SQSOptions);
var request = new StartQueryExecutionRequest()
{
QueryString = $@"ALTER TABLE {tableName} ADD IF NOT EXISTS PARTITION ({keyFieldAssignment}) LOCATION '{s3Location}'",
ResultConfiguration = new ResultConfiguration()
{
OutputLocation = awsAthenaOptions.DefaultOutputLocation //"s3://aws-athena-query-results-855250023996-ap-southeast-2/"
}
};
await awsSQSAPI.SendMessage(JsonConvert.SerializeObject(request));
return($"SQS-{awsAthenaOptions.SQSOptions.Url}");
}
else
{
var queryInfo = await amazonAthenaClient.StartQueryExecutionAsync(new StartQueryExecutionRequest()
{
QueryString = $@"ALTER TABLE {tableName} ADD IF NOT EXISTS PARTITION ({keyFieldAssignment}) LOCATION '{s3Location}'",
ResultConfiguration = new ResultConfiguration()
{
OutputLocation = awsAthenaOptions.DefaultOutputLocation //"s3://aws-athena-query-results-855250023996-ap-southeast-2/"
}
});
return(queryInfo.QueryExecutionId);
}
}
public async Task <int> LoadPartitionViaSQS()
{
int result = 0;
if (awsAthenaOptions.SQSOptions != null)
{
AWSSQSAPI awsSQSAPI = new AWSSQSAPI(awsAthenaOptions.SQSOptions);
var messages = await awsSQSAPI.ReceiveMessage(10, 30);
if (messages.Any())
{
foreach (var message in messages)
{
try
{
var request = JsonConvert.DeserializeObject <StartQueryExecutionRequest>(message.Body);
Console.WriteLine($"Execute Query: {request.QueryString}");
await ExecuteQuery(request); // this will wait until the query completes
await awsSQSAPI.DeleteMessage(message.ReceiptHandle);
}
catch (Exception ex)
{
if (ex.Message != null && ex.Message.Contains("Partition already exists"))
{
Console.WriteLine($"Partition already exists on Athena Load Partition.");
await awsSQSAPI.DeleteMessage(message.ReceiptHandle);
}
else
{
Console.WriteLine($"Exception on Load Partition: {ex.Message}");
Console.WriteLine(ex.Source);
Console.WriteLine(ex.StackTrace);
}
}
result += 1;
}
// invoke next function
if (awsAthenaOptions.LambdaOptions != null && !string.IsNullOrWhiteSpace(awsAthenaOptions.LoaderFunction))
{
AWSLambdaAPI awsLambdaAPI = new AWSLambdaAPI(awsAthenaOptions.LambdaOptions);
awsLambdaAPI.Invoke(awsAthenaOptions.LoaderFunction, JsonConvert.SerializeObject(""));
awsLambdaAPI.Invoke(awsAthenaOptions.LoaderFunction, JsonConvert.SerializeObject(""));
Thread.Sleep(2000);
}
}
}
return(result);
}
public async Task <string> LoadPartition(string tableName, string keyFieldAssignment, string s3Location)
{
/*
* ALTER TABLE twilio_log.twilogs ADD PARTITION (logdate=20181203)
* LOCATION 's3://datascience-twilio-sms-logs/twilio-sms-log-2018-12-03/'
*/
if (awsAthenaOptions.SQSOptions != null)
{
AWSSQSAPI awsSQSAPI = new AWSSQSAPI(awsAthenaOptions.SQSOptions);
var request = new StartQueryExecutionRequest()
{
QueryString = $@"ALTER TABLE {tableName} ADD PARTITION ({keyFieldAssignment}) LOCATION '{s3Location}'",
ResultConfiguration = new ResultConfiguration()
{
OutputLocation = awsAthenaOptions.DefaultOutputLocation //"s3://aws-athena-query-results-855250023996-ap-southeast-2/"
}
};
await awsSQSAPI.SendMessage(JsonConvert.SerializeObject(request));
return($"SQS-{awsAthenaOptions.SQSOptions.Url}");
}
else
{
var queryInfo = await amazonAthenaClient.StartQueryExecutionAsync(new StartQueryExecutionRequest()
{
QueryString = $@"ALTER TABLE {tableName} ADD PARTITION ({keyFieldAssignment}) LOCATION '{s3Location}'",
ResultConfiguration = new ResultConfiguration()
{
OutputLocation = awsAthenaOptions.DefaultOutputLocation //"s3://aws-athena-query-results-855250023996-ap-southeast-2/"
}
});
return(queryInfo.QueryExecutionId);
}
//var queryStatus = amazonAthenaClient.GetQueryExecutionAsync(new GetQueryExecutionRequest() { QueryExecutionId = queryInfo.QueryExecutionId });
//while(!queryStatus.IsCanceled && !queryStatus.IsCompleted && !queryStatus.IsFaulted)
//{
// Thread.Sleep(100);
// queryStatus = amazonAthenaClient.GetQueryExecutionAsync(new GetQueryExecutionRequest() { QueryExecutionId = queryInfo.QueryExecutionId });
//}
//if (queryStatus.IsCanceled) return "canceled";
//if (queryStatus.IsCompleted) return "completed";
//if (queryStatus.IsFaulted) return "faulted";
//return "unknown";
}