public static async Task ClearAthenaTable(this EtlSettings etlSettings, string tableName, string s3Path)
{
if (etlSettings.SourceType != EtlSourceEnum.AmazonAthenaPipes)
{
return;
}
var pipesSource = etlSettings.AthenaQueryPipesSource;
var athenaApi = etlSettings.CreatePipesSourceAthenaAPI();
Console.WriteLine($"DROP TABLE IF EXISTS {tableName}");
var executionId = await athenaApi.StartQuery($"DROP TABLE IF EXISTS {tableName}");
while (!await athenaApi.IsExecutionCompleted(executionId))
{
Thread.Sleep(2000);
}
var s3Api = etlSettings.CreatePipesSourceS3API();
var s3Object = s3Path.ParseS3URI();
if (s3Object is S3Object)
{
Console.WriteLine($"Delete S3: {s3Path}");
var files = await s3Api.ListFiles(s3Object.Key, "/", s3Object.BucketName);
await s3Api.Delete(files.Select(key => $"{s3Object.Key}{key}"), s3Object.BucketName);
Console.WriteLine($"{s3Path}: {files.Count} S3 Files Deleted");
}
}
/// <summary>
/// run the athena query pipes
/// </summary>
/// <param name="etlSettings"></param>
/// <param name="useDate"></param>
/// <returns></returns>
public static async Task RunAthenaQueryPipes(this EtlSettings etlSettings, DateTime?useDate = null)
{
if (etlSettings.SourceType != EtlSourceEnum.AmazonAthenaPipes)
{
return;
}
var pipesSource = etlSettings.AthenaQueryPipesSource;
AthenaParserSetting parserSetting = new AthenaParserSetting();
parserSetting.DefaultExportPath = $"s3://{etlSettings.TargetS3BucketName}/{etlSettings.TargetS3Prefix}".FixPathEnd();
parserSetting.DefaultTableName = $"`{etlSettings.AthenaDatabaseName}`.`{etlSettings.AthenaTableName}`";
parserSetting.Date = useDate == null?DateTime.UtcNow.AddDays(-pipesSource.DaysAgo) : useDate.Value.AddDays(-pipesSource.DaysAgo);
parserSetting.DateFormat = pipesSource.DateFormat;
parserSetting.TempDatabase = pipesSource.TempDatabase;
parserSetting.TempTablePath = pipesSource.TempDataPath.FixPathEnd();
var caches = JsonConvert.DeserializeObject <List <CacheSetting> >(etlSettings.AthenaQueryPipesSource.Caches);
foreach (var cache in caches)
{
if (!cache.S3Path.EndsWith("/"))
{
cache.S3Path += "/";
}
parserSetting.Caches.Add(cache.Key, cache);
}
var parsed = pipesSource.AthenaSQL.ParseAthenaPipes(parserSetting);
await etlSettings.ExecuteControlFlow(parsed, parserSetting);
var athenaApi = etlSettings.CreatePipesSourceAthenaAPI();
foreach (var kvp in parserSetting.Partitions)
{
await athenaApi.LoadPartitionIfNotExists(parserSetting.DefaultTableName, kvp.Key, kvp.Value);
}
}
/// <summary>
/// compile the pipes and run the definition query
/// </summary>
/// <param name="etlSettings"></param>
/// <returns></returns>
public static async Task ParseAthenaQueryPipes(this EtlSettings etlSettings)
{
if (etlSettings.SourceType != EtlSourceEnum.AmazonAthenaPipes)
{
return;
}
var pipesSource = etlSettings.AthenaQueryPipesSource;
AthenaParserSetting parserLogger = new AthenaParserSetting();
parserLogger.DefaultExportPath = $"s3://{etlSettings.TargetS3BucketName}/{etlSettings.TargetS3Prefix}".FixPathEnd();
parserLogger.DefaultTableName = $"`{etlSettings.AthenaDatabaseName}`.`{etlSettings.AthenaTableName}`";
parserLogger.Date = DateTime.UtcNow.AddDays(-pipesSource.DaysAgo);
parserLogger.DateFormat = pipesSource.DateFormat;
parserLogger.TempDatabase = pipesSource.TempDatabase;
parserLogger.TempTablePath = pipesSource.TempDataPath.FixPathEnd();
pipesSource.ParseErrors = "";
if (!string.IsNullOrWhiteSpace(etlSettings.AthenaQueryPipesSource.Caches))
{
try
{
var caches = JsonConvert.DeserializeObject <List <CacheSetting> >(etlSettings.AthenaQueryPipesSource.Caches);
foreach (var cache in caches)
{
if (!cache.S3Path.EndsWith("/"))
{
cache.S3Path += "/";
}
parserLogger.Caches.Add(cache.Key, cache);
}
}
catch (Exception ex)
{
pipesSource.ParseErrors += ex.Message;
pipesSource.ParseErrors += "\n";
}
}
try
{
var parsed = pipesSource.AthenaSQL.ParseAthenaPipes(parserLogger);
pipesSource.ParsedQuery = parsed.ToQueryString();
}
catch (Exception ex)
{
pipesSource.ParseErrors = parserLogger.ToString();
pipesSource.ParseErrors += "\n";
pipesSource.ParseErrors += ex.Message;
pipesSource.ParsedQuery = "";
}
// run if there is definition query
if (Regex.IsMatch(pipesSource.AthenaDefinitionSQL, @"\S+"))
{
var athenaApi = etlSettings.CreatePipesSourceAthenaAPI();
var getResultRequest = await athenaApi.ExecuteQuery(pipesSource.AthenaDefinitionSQL);
var response = await athenaApi.ReadOneResult(getResultRequest);
etlSettings.Mappings = response.ToFieldMapping();
// load data schema to the etlsetting schema
var sample = new DataSample()
{
Rows = new List <DataRow>()
};
var data = response.ReadData();
foreach (var row in data)
{
var dataRow = new DataRow()
{
Items = row.Select(item => item.ToString()).ToList()
};
sample.Rows.Add(dataRow);
}
etlSettings.Sample = sample;
}
}