private void MoveChunkDataToS3()
{
var chunkIds = _dbChunk.GetNotMovedToS3Chunks(Settings.Settings.Current.Building.Id.Value).ToArray();
if (chunkIds.Length == 0)
{
return;
}
var baseFolder =
$"{Settings.Settings.Current.Bucket}/{Settings.Settings.Current.Building.Vendor}/{Settings.Settings.Current.Building.Id}/raw";
Console.WriteLine("S3 raw folder - " + baseFolder);
Parallel.ForEach(Settings.Settings.Current.Building.SourceQueryDefinitions, queryDefinition =>
{
if (queryDefinition.Providers != null)
{
return;
}
if (queryDefinition.Locations != null)
{
return;
}
if (queryDefinition.CareSites != null)
{
return;
}
var sql = GetSqlHelper.GetSql(Settings.Settings.Current.Building.SourceEngine.Database,
queryDefinition.GetSql(Settings.Settings.Current.Building.Vendor,
Settings.Settings.Current.Building.SourceSchemaName),
Settings.Settings.Current.Building.SourceSchemaName);
if (string.IsNullOrEmpty(sql))
{
return;
}
sql = string.Format(sql, chunkIds[0]);
if (queryDefinition.FieldHeaders == null)
{
StoreMetadataToS3(queryDefinition, sql);
}
});
Parallel.ForEach(chunkIds, new ParallelOptions {
MaxDegreeOfParallelism = 2
}, cId =>
{
var chunkId = cId;
Parallel.ForEach(Settings.Settings.Current.Building.SourceQueryDefinitions,
new ParallelOptions {
MaxDegreeOfParallelism = 5
}, queryDefinition =>
{
try
{
if (queryDefinition.Providers != null)
{
return;
}
if (queryDefinition.Locations != null)
{
return;
}
if (queryDefinition.CareSites != null)
{
return;
}
var sql = GetSqlHelper.GetSql(Settings.Settings.Current.Building.SourceEngine.Database,
queryDefinition.GetSql(Settings.Settings.Current.Building.Vendor,
Settings.Settings.Current.Building.SourceSchemaName),
Settings.Settings.Current.Building.SourceSchemaName);
if (string.IsNullOrEmpty(sql))
{
return;
}
sql = string.Format(sql, chunkId);
//if (queryDefinition.FieldHeaders == null)
//{
// StoreMetadataToS3(queryDefinition, sql);
//}
var personIdField = queryDefinition.GetPersonIdFieldName();
var tmpTableName = "#" + queryDefinition.FileName + "_" + chunkId;
var folder = $"{baseFolder}/{chunkId}/{queryDefinition.FileName}";
var fileName = $@"{folder}/{queryDefinition.FileName}";
var unloadQuery = string.Format(@"create table {0} sortkey ({1}) distkey ({1}) as {2}; " +
@"UNLOAD ('select * from {0} order by {1}') to 's3://{3}' " +
@"DELIMITER AS '\t' " +
@"credentials 'aws_access_key_id={4};aws_secret_access_key={5}' " +
@"GZIP ALLOWOVERWRITE PARALLEL ON",
tmpTableName, //0
personIdField, //1
sql, //2
fileName, //3
Settings.Settings.Current.S3AwsAccessKeyId, //4
Settings.Settings.Current.S3AwsSecretAccessKey); //5
using (var connection =
SqlConnectionHelper.OpenOdbcConnection(Settings.Settings.Current.Building
.SourceConnectionString))
using (var c = new OdbcCommand(unloadQuery, connection))
{
c.CommandTimeout = 999999999;
c.ExecuteNonQuery();
}
}
catch (Exception e)
{
Logger.WriteError(chunkId, e);
throw;
}
});
_dbChunk.ChunkCreated(chunkId, Settings.Settings.Current.Building.Id.Value);
Console.WriteLine("Raw data for chunkId=" + chunkId + " is available on S3");
});
}