public void ParseQuereis()
{
for (int i = 1; i < 6; i++)
{
AthenaParserSetting athenaParserLogger = new AthenaParserSetting();
Debug.WriteLine($"****** Begin File {i} ******");
var filename = $"{AppContext.BaseDirectory}/query{i}.sql";
Debug.WriteLine($"File {i}: {filename}");
var query = File.ReadAllText(filename);
try
{
var pipes = query.ParseAthenaPipes(athenaParserLogger);
Debug.WriteLine($"****** End File {i} ******");
Debug.WriteLine($"****** Json File {i} ******");
var tree = JsonConvert.SerializeObject(pipes, Formatting.Indented);
Debug.WriteLine($"****** Parsed File {i} ******");
Debug.WriteLine(pipes.ToQueryString().StripEmptyLines());
}
catch (Exception ex)
{
Debug.WriteLine(athenaParserLogger.ToString());
Debug.Write(ex.Message);
}
}
}
public async void StateQueryTests()
{
int i = 0;
StateMachineExecutionResult result;
StateMachineQueryContext context;
{
i = 1;
AthenaParserSetting athenaParserLogger = new AthenaParserSetting();
Debug.WriteLine($"****** Begin File {i} ******");
var filename = $"{AppContext.BaseDirectory}/query{i}.sql";
Debug.WriteLine($"File {i}: {filename}");
var query = File.ReadAllText(filename);
try
{
var pipes = query.ParseAthenaPipes(athenaParserLogger);
Debug.WriteLine($"****** End File {i} ******");
Debug.WriteLine($"****** Json File {i} ******");
var tree = JsonConvert.SerializeObject(pipes, Formatting.Indented);
Debug.WriteLine($"****** Parsed File {i} ******");
bool seeking = false;
context = new StateMachineQueryContext();
// context.ExecuteStateMachineQueryContext()
result = pipes.LoadNextStateMachineQuery(new AthenaParserSetting(), new LinkedList <int>(), context, ref seeking);
seeking = true;
result = pipes.LoadNextStateMachineQuery(new AthenaParserSetting(), new LinkedList <int>(), context, ref seeking);
Debug.WriteLine(pipes.ToQueryString().StripEmptyLines());
}
catch (Exception ex)
{
Debug.WriteLine(athenaParserLogger.ToString());
Debug.Write(ex.Message);
}
}
}
/// <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;
}
}
