public void CreateDatabase(string query)
{
var sqlConnectionStringBuilder = new OdbcConnectionStringBuilder(connectionString);
var database = sqlConnectionStringBuilder["database"];
// TMP
if (connectionString.ToLower().Contains("amazon redshift"))
{
sqlConnectionStringBuilder["database"] = "poc";
}
else
{
sqlConnectionStringBuilder["database"] = "master";
}
using (var connection = SqlConnectionHelper.OpenOdbcConnection(sqlConnectionStringBuilder.ConnectionString))
{
query = string.Format(query, database);
foreach (var subQuery in query.Split(new[] { "\r\nGO", "\nGO" }, StringSplitOptions.None))
{
using (var command = new OdbcCommand(subQuery, connection))
{
command.CommandTimeout = 30000;
command.ExecuteNonQuery();
}
}
}
if (schemaName.ToLower().Trim() != "dbo")
{
CreateSchema();
}
}
public override void CopyVocabulary()
{
// Move data to S3 and then copy to Redshift
var vocabQueriesPath = Path.Combine(Settings.Settings.Current.Builder.Folder, "Common", "Redshift", "v5.2",
"Vocabulary");
Parallel.ForEach(Directory.GetFiles(vocabQueriesPath), filePath =>
{
var tableName = Path.GetFileNameWithoutExtension(filePath);
using (var connection =
SqlConnectionHelper.OpenOdbcConnection(
Settings.Settings.Current.Building.VocabularyConnectionString))
{
var schemaName = Settings.Settings.Current.Building.VocabularySchemaName;
var query = File.ReadAllText(filePath);
query = query.Replace("{sc}", schemaName);
using (var c = new OdbcCommand(query, connection))
{
c.CommandTimeout = 0;
using (var reader = c.ExecuteReader())
{
Write(null, null, reader, tableName);
}
}
}
});
}
public string GetSourceReleaseDate()
{
try
{
string query = "SELECT VERSION_DATE VERSION_DATE FROM " + _schemaName + "._Version";
using (var connection = SqlConnectionHelper.OpenOdbcConnection(_connectionString))
using (var c = new OdbcCommand(query, connection)
{
CommandTimeout = 0
})
{
using (var reader = c.ExecuteReader())
{
while (reader.Read())
{
var dateString = reader.GetString("VERSION_DATE");
var date = DateTime.Parse(dateString);
return(date.ToShortDateString());
}
}
}
}
catch (Exception)
{
return(DateTime.MinValue.ToShortDateString());
}
return(DateTime.MinValue.ToShortDateString());
}
public void DropVocabularyTables(string query)
{
using (var connection = SqlConnectionHelper.OpenOdbcConnection(connectionString))
{
query = query.Replace("{sc}", schemaName);
//const string query =
// "IF OBJECT_ID (N'AGG_DESCENDANT_SOURCECODES', N'U') IS NOT NULL truncate table AGG_DESCENDANT_SOURCECODES; " +
// "IF OBJECT_ID (N'attribute_definition', N'U') IS NOT NULL truncate table attribute_definition; " +
// "IF OBJECT_ID (N'cohort_definition', N'U') IS NOT NULL truncate table cohort_definition; " +
// "IF OBJECT_ID (N'CONCEPT', N'U') IS NOT NULL truncate table CONCEPT; " +
// "IF OBJECT_ID (N'concept_ancestor', N'U') IS NOT NULL truncate table concept_ancestor; " +
// "IF OBJECT_ID (N'concept_class', N'U') IS NOT NULL truncate table concept_class; " +
// "IF OBJECT_ID (N'concept_relationship', N'U') IS NOT NULL truncate table concept_relationship; " +
// "IF OBJECT_ID (N'CONCEPT_SYNONYM', N'U') IS NOT NULL truncate table CONCEPT_SYNONYM; " +
// "IF OBJECT_ID (N'DESCENDANT_CONCEPT_COUNT', N'U') IS NOT NULL truncate table DESCENDANT_CONCEPT_COUNT; " +
// "IF OBJECT_ID (N'DESCENDANT_SOURCE_CODE_COUNT', N'U') IS NOT NULL truncate table DESCENDANT_SOURCE_CODE_COUNT; " +
// "IF OBJECT_ID (N'domain', N'U') IS NOT NULL truncate table domain; " +
// "IF OBJECT_ID (N'DRUG_APPROVAL', N'U') IS NOT NULL truncate table DRUG_APPROVAL; " +
// "IF OBJECT_ID (N'DRUG_STRENGTH', N'U') IS NOT NULL truncate table DRUG_STRENGTH; " +
// "IF OBJECT_ID (N'relationship', N'U') IS NOT NULL truncate table relationship; " +
// "IF OBJECT_ID (N'source_to_concept_map', N'U') IS NOT NULL truncate table source_to_concept_map; " +
// "IF OBJECT_ID (N'VOCABULARY', N'U') IS NOT NULL truncate table VOCABULARY; ";
using (var command = new OdbcCommand(query, connection))
{
command.CommandTimeout = 0;
command.ExecuteNonQuery();
}
}
}
public override void Write(int?chunkId, int?subChunkId, IDataReader reader, string tableName)
{
var bucket = string.Format("{0}/{1}/{2}", Settings.Current.Bucket, Settings.Current.Building.Vendor,
Settings.Current.Building.Id);
if (!tableName.ToLower().StartsWith("_chunks"))
{
bucket = bucket + "/" + Settings.Current.Building.DestinationSchemaName;
}
if (chunkId.HasValue)
{
bucket = bucket + "/" + "sets";
}
var fileName = MoveToS3(currentClient, bucket, chunkId, subChunkId, reader, tableName);
if (tableName.ToLower().StartsWith("_chunks") || !chunkId.HasValue)
{
var schemaName = Settings.Current.Building.DestinationSchemaName;
if (tableName.ToLower().StartsWith("_chunks"))
{
schemaName = Settings.Current.Building.SourceSchemaName;
tableName = "_chunks";
}
using (var currentConnection = SqlConnectionHelper.OpenOdbcConnection(connectionString))
using (var currentTransaction = currentConnection.BeginTransaction())
{
CopyToRedshift(bucket, schemaName, tableName, fileName, currentConnection, currentTransaction);
currentTransaction.Commit();
}
}
}
private static void SaveSet(int setIndex)
{
var chunkIds = GetChunkIds(setIndex);
var timer = new Stopwatch();
timer.Start();
using (var connection = SqlConnectionHelper.OpenOdbcConnection(Settings.Current.Building.DestinationConnectionString, 10))
using (var transaction = connection.BeginTransaction())
{
try
{
SaveTable(connection, transaction, setIndex, "PERSON");
SaveTable(connection, transaction, setIndex, "OBSERVATION_PERIOD");
SaveTable(connection, transaction, setIndex, "PAYER_PLAN_PERIOD");
SaveTable(connection, transaction, setIndex, "CONDITION_OCCURRENCE");
SaveTable(connection, transaction, setIndex, "DEATH");
SaveTable(connection, transaction, setIndex, "DRUG_EXPOSURE");
SaveTable(connection, transaction, setIndex, "OBSERVATION");
SaveTable(connection, transaction, setIndex, "VISIT_OCCURRENCE");
SaveTable(connection, transaction, setIndex, "PROCEDURE_OCCURRENCE");
SaveTable(connection, transaction, setIndex, "DRUG_ERA");
SaveTable(connection, transaction, setIndex, "CONDITION_ERA");
SaveTable(connection, transaction, setIndex, "DEVICE_EXPOSURE");
SaveTable(connection, transaction, setIndex, "MEASUREMENT");
SaveTable(connection, transaction, setIndex, "COHORT");
SaveTable(connection, transaction, setIndex, "NOTE");
if (Settings.Current.Building.CDM == CDMVersions.v5)
{
SaveTable(connection, transaction, setIndex, "DRUG_COST");
SaveTable(connection, transaction, setIndex, "DEVICE_COST");
SaveTable(connection, transaction, setIndex, "VISIT_COST");
SaveTable(connection, transaction, setIndex, "PROCEDURE_COST");
}
else
{
SaveTable(connection, transaction, setIndex, "COST");
}
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
Logger.Write(null, LogMessageTypes.Debug, "Rollback - Complete");
foreach (var chunkId in chunkIds)
{
Logger.WriteError(chunkId, e);
}
}
}
SetChunksAsComplete(chunkIds, timer);
}
public string GetVocabularyVersion(string vocabularyConnectionString)
{
const string query = "SELECT VOCABULARY_VERSION FROM vocabulary WHERE VOCABULARY_ID = 'None'";
try
{
using (var connection = SqlConnectionHelper.OpenOdbcConnection(vocabularyConnectionString))
{
using (var cmd = new OdbcCommand(query, connection))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
return(reader.GetString("VOCABULARY_VERSION"));
}
}
}
}
}
catch (Exception)
{
return(null);
}
return(null);
}
public void DropDatabase()
{
var sqlConnectionStringBuilder = new OdbcConnectionStringBuilder(connectionString);
var destination = sqlConnectionStringBuilder["Database"];
sqlConnectionStringBuilder["Database"] = "master";
using (var connection = SqlConnectionHelper.OpenOdbcConnection(sqlConnectionStringBuilder.ConnectionString))
{
var query = string.Format("ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;", destination);
using (var command = new OdbcCommand(query, connection))
{
command.CommandTimeout = 30000;
command.ExecuteNonQuery();
}
query = string.Format("DROP DATABASE [{0}]", destination);
using (var command = new OdbcCommand(query, connection))
{
command.CommandTimeout = 30000;
command.ExecuteNonQuery();
}
}
}
private void LoadPregnancyDrug(bool storeToS3)
{
var sql = File.ReadAllText(Path.Combine(Settings.Settings.Current.Builder.Folder,
@"Core\Lookups\PregnancyDrug.sql"));
sql = sql.Replace("{sc}", Settings.Settings.Current.Building.VocabularySchemaName);
Console.WriteLine("PregnancyDrug - Loading...");
using (var connection =
SqlConnectionHelper.OpenOdbcConnection(Settings.Settings.Current.Building.VocabularyConnectionString))
using (var command = new OdbcCommand(sql, connection)
{
CommandTimeout = 0
})
using (var reader = command.ExecuteReader())
{
if (storeToS3)
{
var fileName =
$"{Settings.Settings.Current.Building.Vendor}/{Settings.Settings.Current.Building.Id}/Lookups/PregnancyDrug.txt.gz";
Console.WriteLine("PregnancyDrug - store to S3 | " + fileName);
using (var client = new AmazonS3Client(
Settings.Settings.Current.S3AwsAccessKeyId,
Settings.Settings.Current.S3AwsSecretAccessKey,
new AmazonS3Config
{
Timeout = TimeSpan.FromMinutes(60),
RegionEndpoint = Amazon.RegionEndpoint.USEast1,
MaxErrorRetry = 20,
}))
{
AmazonS3Helper.CopyFile(client, Settings.Settings.Current.Bucket,
fileName,
reader);
}
}
else
{
Console.WriteLine("PregnancyDrug - filling");
var lookup = new Lookup();
while (reader.Read())
{
lookup.Add(new LookupValue
{
ConceptId = int.Parse(reader[0].ToString()),
SourceCode = reader[0].ToString()
});
}
_lookups.Add("PregnancyDrug", lookup);
}
}
Console.WriteLine("PregnancyDrug - Done");
}
public void ExecuteQuery(string query)
{
using (var connection = SqlConnectionHelper.OpenOdbcConnection(_connectionString))
{
query = query.Replace("{sc}", _schemaName);
using (var command = new OdbcCommand(query, connection))
{
command.CommandTimeout = 0;
command.ExecuteNonQuery();
}
}
}
public void GrantAccessToChunkTable()
{
var query = $"grant all on table {_schemaName}._chunks to rhealth_etl, rhealth_sa with grant option;";
using (var connection = SqlConnectionHelper.OpenOdbcConnection(_connectionString))
{
using (var command = new OdbcCommand(query, connection))
{
command.CommandTimeout = 0;
command.ExecuteNonQuery();
}
}
}
public void CreateSchema()
{
using (var connection = SqlConnectionHelper.OpenOdbcConnection(_connectionString))
{
var query = $"create schema [{_schemaName}]";
using (var command = new OdbcCommand(query, connection))
{
command.CommandTimeout = 0;
command.ExecuteNonQuery();
}
}
}
public void FillPostBuildTable(string query)
{
query = query.Replace("{sc}", schemaName);
var sqlConnectionStringBuilder = new OdbcConnectionStringBuilder(connectionString);
using (var connection = SqlConnectionHelper.OpenOdbcConnection(sqlConnectionStringBuilder.ConnectionString))
{
using (var command = new OdbcCommand(query, connection))
{
command.ExecuteNonQuery();
}
}
}
public void TruncateLookup(string query)
{
using (var connection = SqlConnectionHelper.OpenOdbcConnection(connectionString))
{
//var query = File.ReadAllText(Path.Combine(folder, "TruncateLookup.sql"));
query = query.Replace("{sc}", schemaName);
using (var command = new OdbcCommand(query, connection))
{
command.CommandTimeout = 0;
command.ExecuteNonQuery();
}
}
}
public void DropChunkTable()
{
var query = File.ReadAllText(Path.Combine(_folder, "DropChunkTable.sql"));
query = query.Replace("{sc}", _schemaName);
using (var connection = SqlConnectionHelper.OpenOdbcConnection(_connectionString))
using (var cmd = new OdbcCommand(query, connection)
{
CommandTimeout = 0
})
{
cmd.ExecuteNonQuery();
}
}
public override IEnumerable <string> GetAllTables()
{
const string query = "select distinct(tablename) from pg_table_def where schemaname = 'public'";
using (var conn = SqlConnectionHelper.OpenOdbcConnection(Settings.Current.Building.SourceConnectionString))
using (var c = new OdbcCommand(query, conn))
using (var reader = c.ExecuteReader())
{
while (reader.Read())
{
yield return(reader.GetString(0));
}
}
}
public override IEnumerable <string> GetAllColumns(string tableName)
{
var query = string.Format(@"select ""column"" from pg_table_def where tablename = '{0}'", tableName);
using (var conn = SqlConnectionHelper.OpenOdbcConnection(Settings.Current.Building.SourceConnectionString))
using (var c = new OdbcCommand(query, conn))
using (var reader = c.ExecuteReader())
{
while (reader.Read())
{
yield return(reader.GetString(0));
}
}
}
public override void Write(int?chunkId, int?subChunkId, IDataReader reader, string tableName)
{
if (Settings.Settings.Current.SaveOnlyToS3 && !chunkId.HasValue && !tableName.ToLower().StartsWith("_chunks"))
{
var b =
$"{Settings.Settings.Current.Bucket}/{Settings.Settings.Current.Building.Vendor}/{Settings.Settings.Current.Building.Id}/{Settings.Settings.Current.CDMFolder}";
if (Settings.Settings.Current.StorageType == S3StorageType.Parquet)
{
SaveToS3Snappy(b, tableName, reader);
}
else
{
SaveToS3CSV(_currentClient, b, null, subChunkId, reader, tableName);
}
return;
}
var bucket =
$"{Settings.Settings.Current.Bucket}/{Settings.Settings.Current.Building.Vendor}/{Settings.Settings.Current.Building.Id}";
if (!tableName.ToLower().StartsWith("_chunks"))
{
bucket = bucket + "/" + Settings.Settings.Current.Building.DestinationSchemaName;
}
if (chunkId.HasValue)
{
bucket = bucket + "/" + "chunks";
}
var fileName = SaveToS3CSV(_currentClient, bucket, chunkId, subChunkId, reader, tableName);
var schemaName = Settings.Settings.Current.Building.DestinationSchemaName;
if (tableName.ToLower().StartsWith("_chunks"))
{
schemaName = Settings.Settings.Current.Building.SourceSchemaName;
tableName = "_chunks";
}
using (var currentConnection = SqlConnectionHelper.OpenOdbcConnection(_connectionString))
using (var currentTransaction = currentConnection.BeginTransaction())
{
CopyToRedshift(bucket, schemaName, tableName, fileName, currentConnection, currentTransaction);
currentTransaction.Commit();
}
}
private static void StoreMetadataToS3(QueryDefinition queryDefinition, string query)
{
Console.WriteLine("StoreMetadataToS3 start - " + queryDefinition.FileName);
var sql = query + " limit 1";
var metadataKey =
$"{Settings.Settings.Current.Building.Vendor}/{Settings.Settings.Current.Building.Id}/raw/metadata/{queryDefinition.FileName + ".txt"}";
using (var conn =
SqlConnectionHelper.OpenOdbcConnection(Settings.Settings.Current.Building.SourceConnectionString))
using (var c = Settings.Settings.Current.Building.SourceEngine.GetCommand(sql, conn))
using (var reader = c.ExecuteReader(CommandBehavior.SchemaOnly))
{
using (var source = new MemoryStream())
using (TextWriter writer = new StreamWriter(source, new UTF8Encoding(false, true)))
using (var csv = new CsvWriter(writer, new CsvHelper.Configuration.Configuration
{
HasHeaderRecord = false,
Delimiter = ",",
Encoding = Encoding.UTF8
}))
{
for (var i = 0; i < reader.FieldCount; i++)
{
csv.WriteField(reader.GetName(i));
}
csv.NextRecord();
writer.Flush();
using (var client = new AmazonS3Client(Settings.Settings.Current.S3AwsAccessKeyId,
Settings.Settings.Current.S3AwsSecretAccessKey, Amazon.RegionEndpoint.USEast1))
using (var directoryTransferUtility = new TransferUtility(client))
{
directoryTransferUtility.Upload(new TransferUtilityUploadRequest
{
BucketName = Settings.Settings.Current.Bucket,
Key = metadataKey,
ServerSideEncryptionMethod = ServerSideEncryptionMethod.AES256,
StorageClass = S3StorageClass.Standard,
InputStream = source
});
}
}
}
Console.WriteLine("StoreMetadataToS3 end - " + queryDefinition.FileName);
}
public void CopyVocabulary(string query, string vocabularyConnectionString)
{
if (string.IsNullOrEmpty(query.Trim()))
{
return;
}
var vocab = new OdbcConnectionStringBuilder(vocabularyConnectionString);
using (var connection = SqlConnectionHelper.OpenOdbcConnection(connectionString))
{
query = string.Format(query, vocab["server"], vocab["database"]);
using (var command = new OdbcCommand(query, connection))
{
command.CommandTimeout = 0;
command.ExecuteNonQuery();
}
}
}
public void CreateIndexes(string query)
{
if (string.IsNullOrEmpty(query.Trim()))
{
return;
}
using (var connection = SqlConnectionHelper.OpenOdbcConnection(connectionString))
{
foreach (var subQuery in query.Split(new[] { "\r\nGO", "\nGO" }, StringSplitOptions.None))
{
using (var command = new OdbcCommand(subQuery, connection))
{
command.CommandTimeout = 0;
command.ExecuteNonQuery();
}
}
}
}
public IEnumerable <long> GetPersonIds(int chunkId)
{
var sql = string.Format("SELECT PERSON_ID FROM {0}._chunks where ChunkId = {1}", schemaName, chunkId);
using (var connection = SqlConnectionHelper.OpenOdbcConnection(connectionString))
using (var c = new OdbcCommand(sql, connection)
{
CommandTimeout = 0
})
{
using (var reader = c.ExecuteReader())
{
while (reader.Read())
{
yield return(reader.GetInt64(0));
}
}
}
}
public IEnumerable <IDataReader> GetPersonKeys(string batchScript, long batches, int batchSize)
{
batchScript = batchScript.Replace("{sc}", schemaName);
var sql = batches > 0 ? string.Format(batchScript, "TOP " + batches * batchSize) : string.Format(batchScript, "");
using (var connection = SqlConnectionHelper.OpenOdbcConnection(connectionString))
using (var c = new OdbcCommand(sql, connection)
{
CommandTimeout = 0
})
{
using (var reader = c.ExecuteReader())
{
while (reader.Read())
{
yield return(reader);
}
}
}
}
public void Load(QueryDefinition qd, EntityDefinition ed)
{
var sql = qd.GetSql(Settings.Current.Building.SourceEngine.Database, Settings.Current.Building.Vendor, schemaName);
if (string.IsNullOrEmpty(sql))
{
return;
}
using (var conn = SqlConnectionHelper.OpenOdbcConnection(connectionString))
{
using (var c = new OdbcCommand(sql, conn))
{
c.CommandTimeout = 30000;
using (var reader = c.ExecuteReader())
{
while (reader.Read())
{
Concept conceptDef = null;
if (ed.Concepts != null && ed.Concepts.Any())
{
conceptDef = ed.Concepts[0];
}
var concept = (T)ed.GetConcepts(conceptDef, reader, null).ToList()[0];
var key = concept.GetKey();
if (key == null)
{
continue;
}
if (!lookup.ContainsKey(key))
{
lookup.Add(key, concept);
}
}
}
}
}
}
public void ExecuteQuery(string query)
{
using (var connection = SqlConnectionHelper.OpenOdbcConnection(_connectionString))
{
query = query.Replace("{sc}", _schemaName);
foreach (var subQuery in query.Split(new[] { "\r\nGO", "\nGO", ";" }, StringSplitOptions.None))
{
if (string.IsNullOrEmpty(subQuery))
{
continue;
}
using (var command = new OdbcCommand(subQuery, connection))
{
command.CommandTimeout = 30000;
command.ExecuteNonQuery();
}
}
}
}
public void CreateIndexesChunkTable()
{
var query = File.ReadAllText(Path.Combine(folder, "CreateIndexesChunkTable.sql"));
query = query.Replace("{sc}", schemaName);
if (string.IsNullOrEmpty(query.Trim()))
{
return;
}
using (var connection = SqlConnectionHelper.OpenOdbcConnection(connectionString))
{
foreach (var subQuery in query.Split(new[] { "GO" + "\r\n", "GO" + "\n" }, StringSplitOptions.RemoveEmptyEntries))
{
using (var command = new OdbcCommand(subQuery, connection))
{
command.CommandTimeout = 0;
command.ExecuteNonQuery();
}
}
}
}
public virtual void Load()
{
string sql = string.Empty;
try
{
lookup.Clear();
var sourceToStandard = File.ReadAllText(Path.Combine(Settings.Current.Builder.Folder, @"Common\Lookups\Source_to_Standard.sql"));
var sourceToSource = File.ReadAllText(Path.Combine(Settings.Current.Builder.Folder, @"Common\Lookups\Source_to_Source.sql"));
var mapsToValue = File.ReadAllText(Path.Combine(Settings.Current.Builder.Folder, @"Common\Lookups\Maps_to_Value.sql"));
sql = File.ReadAllText(sqlFileDestination);
sql = sql.Replace("{Source_to_Standard}", sourceToStandard);
sql = sql.Replace("{Source_to_Source}", sourceToSource);
sql = sql.Replace("{Maps_to_Value}", mapsToValue);
sql = sql.Replace("{sc}", schemaName);
using (var connection = SqlConnectionHelper.OpenOdbcConnection(connectionString))
using (var command = new OdbcCommand(sql, connection)
{
CommandTimeout = 0
})
using (var reader = command.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (reader.Read())
{
AddRecord(reader);
}
}
}
catch (Exception)
{
Logger.WriteWarning("Lookup error [file]: " + sqlFileDestination);
Logger.WriteWarning("Lookup error [query]: " + sql);
throw;
}
}
private void Load(IEnumerable <EntityDefinition> definitions)
{
if (definitions == null)
{
return;
}
foreach (var ed in definitions)
{
ed.Vocabulary = this;
if (ed.Concepts == null)
{
continue;
}
foreach (var c in ed.Concepts)
{
if (c.ConceptIdMappers == null)
{
continue;
}
foreach (var conceptIdMapper in c.ConceptIdMappers)
{
if (!string.IsNullOrEmpty(conceptIdMapper.Lookup))
{
if (!_lookups.ContainsKey(conceptIdMapper.Lookup))
{
string sql = string.Empty;
var vendorFolder = Settings.Current.Building.VendorFolder;
var baseSql = string.Empty;
var sqlFileDestination = string.Empty;
baseSql = File.ReadAllText(Path.Combine(Settings.Current.BuilderFolder,
@"ETL\Common\Lookups\Base.sql"));
sqlFileDestination = Path.Combine(Settings.Current.BuilderFolder,
vendorFolder,
"Lookups",
conceptIdMapper.Lookup + ".sql");
sql = File.ReadAllText(sqlFileDestination);
sql = sql.Replace("{base}", baseSql);
sql = sql.Replace("{sc}", Settings.Current.Building.VocabSchema);
try
{
Console.WriteLine(conceptIdMapper.Lookup + " - Loading...");
var timer = new Stopwatch();
timer.Start();
Logger.Write(null, LogMessageTypes.Info, conceptIdMapper.Lookup + " - Loading into RAM...");
using (var connection = SqlConnectionHelper.OpenOdbcConnection(Settings.Current.Building.VocabularyConnectionString))
using (var command = new OdbcCommand(sql, connection)
{
CommandTimeout = 0
})
using (var reader = command.ExecuteReader())
{
Console.WriteLine(conceptIdMapper.Lookup + " - filling");
var lookup = new Lookup();
while (reader.Read())
{
var lv = CreateLookupValue(reader);
lookup.Add(lv);
}
_lookups.Add(conceptIdMapper.Lookup, lookup);
}
Console.WriteLine(conceptIdMapper.Lookup + " - Done");
timer.Stop();
Logger.Write(null, LogMessageTypes.Info,
$"DONE - {timer.ElapsedMilliseconds} ms | KeysCount={_lookups[conceptIdMapper.Lookup].KeysCount}");
}
catch (Exception e)
{
Console.WriteLine("Lookup error [file]: " + sqlFileDestination);
Console.WriteLine("Lookup error [query]: " + sql);
Logger.WriteWarning("Lookup error [file]: " + sqlFileDestination);
Logger.WriteWarning("Lookup error [query]: " + sql);
throw;
}
}
}
}
}
}
}
private static void MoveChunkDataToS3(IEnumerable <int> chunkIds)
{
var baseFolder = string.Format("{0}/{1}/{2}/raw", Settings.Current.Bucket, Settings.Current.Building.Vendor, Settings.Current.Building.Id);
//foreach (var cId in chunkIds)
Parallel.ForEach(chunkIds, new ParallelOptions {
MaxDegreeOfParallelism = 2
}, cId =>
{
var chunkId = cId;
Parallel.ForEach(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 = queryDefinition.GetSql(Settings.Current.Building.SourceEngine.Database,
Settings.Current.Building.Vendor, 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;
//sql = sql.Replace("'", @"\'");
var folder = string.Format("{0}/{1}/{2}", baseFolder, chunkId, queryDefinition.FileName);
var fileName = string.Format(@"{0}/{1}", folder, queryDefinition.FileName);
var unloadQuery = string.Format(@"create table {0} 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.Current.S3AwsAccessKeyId, //4
Settings.Current.S3AwsSecretAccessKey); //5
using (var connection = SqlConnectionHelper.OpenOdbcConnection(Settings.Current.Building.SourceConnectionString))
using (var c = new OdbcCommand(unloadQuery, connection))
{
c.CommandTimeout = 999999999;
c.ExecuteNonQuery();
}
}
catch (Exception e)
{
Logger.WriteError(chunkId, e);
throw;
}
});
});
}
private void Load(IEnumerable <EntityDefinition> definitions, bool storeToS3)
{
if (definitions == null)
{
return;
}
foreach (var ed in definitions)
{
ed.Vocabulary = this;
if (ed.Concepts == null)
{
continue;
}
foreach (var c in ed.Concepts)
{
if (c.ConceptIdMappers == null)
{
continue;
}
foreach (var conceptIdMapper in c.ConceptIdMappers)
{
if (!string.IsNullOrEmpty(conceptIdMapper.Lookup))
{
if (!_lookups.ContainsKey(conceptIdMapper.Lookup))
{
string sql = string.Empty;
var vendorFolder = Settings.Settings.Current.Building.Vendor.GetAttribute <FolderAttribute>().Value;
var sqlFileDestination = Path.Combine(Settings.Settings.Current.Builder.Folder, "Core", "Transformation",
vendorFolder, "Lookups", conceptIdMapper.Lookup + ".sql");
var baseSql =
File.ReadAllText(Path.Combine(Settings.Settings.Current.Builder.Folder, @"Core\Lookups\Base.sql"));
sql = File.ReadAllText(sqlFileDestination);
sql = sql.Replace("{base}", baseSql);
sql = sql.Replace("{sc}", Settings.Settings.Current.Building.VocabularySchemaName);
try
{
Console.WriteLine(conceptIdMapper.Lookup + " - Loading...");
using (var connection = SqlConnectionHelper.OpenOdbcConnection(Settings.Settings.Current.Building.VocabularyConnectionString))
using (var command = new OdbcCommand(sql, connection)
{
CommandTimeout = 0
})
using (var reader = command.ExecuteReader())
{
if (storeToS3)
{
var fileName =
$"{Settings.Settings.Current.Building.Vendor}/{Settings.Settings.Current.Building.Id}/Lookups/{conceptIdMapper.Lookup}.txt.gz";
Console.WriteLine(conceptIdMapper.Lookup + " - store to S3 | " + fileName);
using (var client = new AmazonS3Client(
Settings.Settings.Current.S3AwsAccessKeyId,
Settings.Settings.Current.S3AwsSecretAccessKey,
new AmazonS3Config
{
Timeout = TimeSpan.FromMinutes(60),
RegionEndpoint = Amazon.RegionEndpoint.USEast1,
MaxErrorRetry = 20,
}))
{
AmazonS3Helper.CopyFile(client, Settings.Settings.Current.Bucket,
fileName,
reader);
}
}
else
{
Console.WriteLine(conceptIdMapper.Lookup + " - filling");
var lookup = new Lookup();
while (reader.Read())
{
var lv = CreateLookupValue(reader);
lookup.Add(lv);
}
_lookups.Add(conceptIdMapper.Lookup, lookup);
}
}
Console.WriteLine(conceptIdMapper.Lookup + " - Done");
}
catch (Exception)
{
Console.WriteLine("Lookup error [file]: " + sqlFileDestination);
Console.WriteLine("Lookup error [query]: " + sql);
Logger.WriteWarning("Lookup error [file]: " + sqlFileDestination);
Logger.WriteWarning("Lookup error [query]: " + sql);
throw;
}
}
}
}
}
}
}
