public void Process() { try { var channelFactory = ServicesManager.GetChannelFactory <IVocabularyService>("net.pipe://localhost/VocabularyServiceEndpoint"); vocabularyService = channelFactory.CreateChannel(); var part = new ChunkPart(chunkId, createPersonBuilder, "0"); part.Load(); part.Build(vocabularyService); part.Save(); var dbChunk = new DbChunk(Settings.Current.Building.BuilderConnectionString); dbChunk.ChunkComplete(chunkId); channelFactory.Close(); } catch (Exception e) { Logger.WriteError(chunkId, e); throw; } }
private static void SetChunksAsComplete(int[] chunkIds, Stopwatch timer) { var attempt = 0; var saved = false; while (!saved) { try { attempt++; var dbChunk = new DbChunk(Settings.Current.Building.BuilderConnectionString); foreach (var chunkId in chunkIds) { dbChunk.ChunkComplete(chunkId); } timer.Stop(); Logger.Write(null, LogMessageTypes.Info, string.Format("Save - {0} ms", timer.ElapsedMilliseconds)); saved = true; } catch (Exception) { if (attempt <= 10) { Thread.Sleep(1000); } else { throw; } } } }
public void Process() { try { Console.WriteLine("DatabaseChunkBuilder"); var dbChunk = new DbChunk(Settings.Settings.Current.Building.BuilderConnectionString); var part = new DatabaseChunkPart(_chunkId, _createPersonBuilder, "0", 0); var timer = new Stopwatch(); timer.Start(); part.Load(); Logger.Write(_chunkId, LogMessageTypes.Info, $"Loaded - {timer.ElapsedMilliseconds} ms | {(GC.GetTotalMemory(false) / 1024f) / 1024f} Mb"); part.Build(); part.Save(); dbChunk.ChunkComplete(_chunkId, Settings.Settings.Current.Building.Id.Value); Console.WriteLine($"ChunkId={_chunkId} was complete"); } catch (Exception e) { Logger.WriteError(_chunkId, e); throw; } }
public ChunkController() { _dbChunk = new DbChunk(Settings.Settings.Current.Building.BuilderConnectionString); _dbSource = new DbSource(Settings.Settings.Current.Building.SourceConnectionString, Path.Combine(new[] { Settings.Settings.Current.Builder.Folder, "Common", Settings.Settings.Current.Building.SourceEngine.Database.ToString() }), Settings.Settings.Current.Building.SourceSchemaName); }
public void Build() { var dbChunk = new DbChunk(Settings.Current.Building.BuilderConnectionString); dbChunk.MarkUncompletedChunks(Settings.Current.Building.Id.Value, Settings.Current.Builder.Id.Value); PerformAction(() => { Parallel.For(0, Settings.Current.Builder.MaxDegreeOfParallelism, i => { while (!chunkController.AllChunksStarted()) { var arguments = string.Format(@"<{0}>{1}</{0}>", "cs", Settings.Current.Building.BuilderConnectionString) + string.Format(@"<{0}>{1}</{0}>", "s3keyid", Settings.Current.S3AwsAccessKeyId) + string.Format(@"<{0}>{1}</{0}>", "s3accesskey", Settings.Current.S3AwsSecretAccessKey) + string.Format(@"<{0}>{1}</{0}>", "ec2keyid", Settings.Current.Ec2AwsAccessKeyId) + string.Format(@"<{0}>{1}</{0}>", "ec2accesskey", Settings.Current.Ec2AwsSecretAccessKey) + string.Format(@"<{0}>{1}</{0}>", "SubChunkSize", Settings.Current.SubChunkSize) + string.Format(@"<{0}>{1}</{0}>", "bucket", Settings.Current.Bucket); var psi = new ProcessStartInfo(Path.Combine(Settings.Current.Builder.Folder, "org.ohdsi.cdm.presentation.builderprocess.exe")) { Arguments = arguments, CreateNoWindow = true }; Thread.Sleep(1000); using (var p = Process.Start(psi)) { p.WaitForExit(); p.Close(); } RefreshState(); if (Builder.State != BuilderState.Running) { break; } } }); }); }
private static void Build(BuilderController builderController) { var dbChunk = new DbChunk(Settings.Current.Building.BuilderConnectionString); int? chunkId = null; while (true) { try { builderController.RefreshState(); if (builderController.Builder.State == BuilderState.Stopping) break; if (builderController.Builder.State == BuilderState.Stopped) break; if (builderController.Builder.State == BuilderState.Error) break; if (builderController.Builder.State == BuilderState.Unknown || builderController.Builder.State == BuilderState.Idle) continue; chunkId = dbChunk.TakeChunk(Settings.Current.Building.Id.Value); if (!chunkId.HasValue) break; var builder = new ChunkBuilder(new ChunkData { Id = chunkId.Value }, GetBuilderType(Settings.Current.Building.Vendor)); var loaded_chunk = builder.Load(); var built_chunk = loaded_chunk.Build(); var task = built_chunk.Save(); tasks.Add(task); } catch (Exception e) { if (chunkId.HasValue) Logger.WriteError(chunkId, e); else Logger.Write(null, Settings.Current.Building.Id.Value, null, LogMessageTypes.Error, Logger.CreateExceptionString(e)); builderController.UpdateState(BuilderState.Error); } } }
public void Process() { try { var channelFactory = ServicesManager.GetChannelFactory <IVocabularyService>("net.pipe://localhost/VocabularyServiceEndpoint"); vocabularyService = channelFactory.CreateChannel(); var dbChunk = new DbChunk(Settings.Current.Building.BuilderConnectionString); var part = new ChunkPart(chunkId, createPersonBuilder, "0"); var timer = new Stopwatch(); timer.Start(); part.Load(); dbChunk.ChunkLoaded(chunkId); Logger.Write(chunkId, LogMessageTypes.Info, string.Format("Loaded - {0} ms | {1} Mb", timer.ElapsedMilliseconds, (GC.GetTotalMemory(false) / 1024f) / 1024f)); part.Build(vocabularyService); part.Save(); if (Settings.Current.Building.DestinationEngine.Database == Database.Redshift) { ServicesManager.AddToSaveQueue(chunkId); Logger.Write(null, LogMessageTypes.Debug, "AddToSaveQueue " + chunkId); } else { dbChunk.ChunkComplete(chunkId); } channelFactory.Close(); } catch (Exception e) { Logger.WriteError(chunkId, e); throw; } }
public void Start() { Logger.Write(null, LogMessageTypes.Debug, "sss RedshiftService Start()"); indexSize.Clear(); var dbChunk = new DbChunk(Settings.Current.Building.BuilderConnectionString); chunksIndexes = dbChunk.SplitChunks(Settings.Current.Building.Id.Value, SizeOfSet); foreach (var index in chunksIndexes.Values) { if (!indexSize.ContainsKey(index)) { indexSize.Add(index, 0); } indexSize[index] = indexSize[index] + 1; } if (Settings.Current.Builder.IsLead && Settings.Current.Building.DestinationEngine.Database == Database.Redshift) { if (saver != null) { saver.DoWork -= saver_DoWork; saver.RunWorkerCompleted -= saver_RunWorkerCompleted; saver.Dispose(); saver = null; } saver = new BackgroundWorker { WorkerSupportsCancellation = true }; saver.DoWork += saver_DoWork; saver.RunWorkerCompleted += saver_RunWorkerCompleted; saver.RunWorkerAsync(); Logger.Write(null, LogMessageTypes.Debug, "sss saver_DoWork"); } }
public void Save(ChunkData chunk) { var timer = new Stopwatch(); timer.Start(); try { UpdateOffset(chunk.KeyMasterOffset); Write(chunk.Id, new PersonDataReader(chunk.Persons.ToList()), "PERSON"); Write(chunk.Id, new ObservationPeriodDataReader(chunk.ObservationPeriods.ToList()), "OBSERVATION_PERIOD"); Write(chunk.Id, new PayerPlanPeriodDataReader(chunk.PayerPlanPeriods.ToList(), chunk.KeyMasterOffset), "PAYER_PLAN_PERIOD"); Write(chunk.Id, new ConditionOccurrenceDataReader(chunk.ConditionOccurrences.ToList(), chunk.KeyMasterOffset), "CONDITION_OCCURRENCE"); Write(chunk.Id, new DeathDataReader(chunk.Deaths.ToList()), "DEATH"); Write(chunk.Id, new DrugExposureDataReader(chunk.DrugExposures.ToList(), chunk.KeyMasterOffset), "DRUG_EXPOSURE"); Write(chunk.Id, new DrugCostDataReader(chunk.DrugCost.ToList(), chunk.KeyMasterOffset), "DRUG_COST"); Write(chunk.Id, new ObservationDataReader(chunk.Observations.ToList(), chunk.KeyMasterOffset), "OBSERVATION"); Write(chunk.Id, new VisitOccurrenceDataReader(chunk.VisitOccurrences.ToList(), chunk.KeyMasterOffset), "VISIT_OCCURRENCE"); Write(chunk.Id, new ProcedureOccurrenceDataReader(chunk.ProcedureOccurrences.ToList(), chunk.KeyMasterOffset), "PROCEDURE_OCCURRENCE"); Write(chunk.Id, new ProcedureCostDataReader(chunk.ProcedureCost.ToList(), chunk.KeyMasterOffset), "PROCEDURE_COST"); Write(chunk.Id, new DrugEraDataReader(chunk.DrugEra.ToList()), "DRUG_ERA"); Write(chunk.Id, new ConditionEraDataReader(chunk.ConditionEra.ToList()), "CONDITION_ERA"); Write(chunk.Id, new CohortDataReader(chunk.Cohort.ToList()), "COHORT"); Commit(); var dbChunk = new DbChunk(Settings.Current.Building.BuilderConnectionString); dbChunk.ChunkComplete(chunk.Id); } catch (Exception e) { Logger.WriteError(chunk.Id, e); Rollback(); Logger.Write(chunk.Id, LogMessageTypes.Debug, "Rollback - Complete"); } timer.Stop(); }
private static void Build(BuilderController builderController) { var dbChunk = new DbChunk(Settings.Current.Building.BuilderConnectionString); int?chunkId = null; while (true) { try { builderController.RefreshState(); if (builderController.Builder.State == BuilderState.Stopping) { break; } if (builderController.Builder.State == BuilderState.Stopped) { break; } if (builderController.Builder.State == BuilderState.Error) { break; } if (builderController.Builder.State == BuilderState.Unknown || builderController.Builder.State == BuilderState.Idle) { continue; } chunkId = dbChunk.TakeChunk(Settings.Current.Building.Id.Value, Settings.Current.Builder.Id.Value, GetRandomBoolean()); if (!chunkId.HasValue) { break; } Console.WriteLine($"ChunkId={chunkId} in progress..."); var attempt = 0; var processing = false; var chunk = Settings.Current.Building.SourceEngine.GetChunkBuilder(chunkId.Value, CreatePersonBuilder); while (!processing) { try { attempt++; chunk.Process(); processing = true; } catch (Exception ex) { if (attempt <= 3) { Logger.Write(chunkId, LogMessageTypes.Warning, "chunk.Process attempt=" + attempt + ") " + Logger.CreateExceptionString(ex)); chunk = Settings.Current.Building.SourceEngine.GetChunkBuilder(chunkId.Value, CreatePersonBuilder); } else { throw; } } } chunkId = null; } catch (Exception e) { if (chunkId.HasValue) { Logger.WriteError(chunkId, e); dbChunk.ChunkFailed(chunkId.Value, Settings.Current.Building.Id.Value); } else { Logger.Write(null, Settings.Current.Building.Id.Value, null, LogMessageTypes.Error, Logger.CreateExceptionString(e)); } builderController.UpdateState(BuilderState.Error); } } }
public void Process() { try { var channelFactory = ServicesManager.GetChannelFactory <IVocabularyService>("net.pipe://localhost/VocabularyServiceEndpoint"); vocabularyService = channelFactory.CreateChannel(); var dbChunk = new DbChunk(Settings.Current.Building.BuilderConnectionString); var timer = new Stopwatch(); timer.Start(); var folder = string.Format("{0}/{1}/raw", Settings.Current.Building.Vendor, Settings.Current.Building.Id); Parallel.ForEach(Settings.Current.Building.SourceQueryDefinitions, qd => { if (qd.Providers != null) { return; } if (qd.Locations != null) { return; } if (qd.CareSites != null) { return; } var sql = qd.GetSql(Settings.Current.Building.SourceEngine.Database, Settings.Current.Building.Vendor, Settings.Current.Building.SourceSchemaName); if (string.IsNullOrEmpty(sql)) { return; } qd.FieldHeaders = new Dictionary <string, int>(StringComparer.OrdinalIgnoreCase); var metadataKey = string.Format("{0}/metadata/{1}", folder, qd.FileName + ".txt"); using (var client = new AmazonS3Client(Settings.Current.S3AwsAccessKeyId, Settings.Current.S3AwsSecretAccessKey, Amazon.RegionEndpoint.USEast1)) using (var stream = new MemoryStream()) using (var sr = new StreamReader(stream)) { var request = new GetObjectRequest { BucketName = Settings.Current.Bucket, Key = metadataKey }; using (var response = client.GetObject(request)) { response.ResponseStream.CopyTo(stream); } stream.Position = 0; var index = 0; foreach (var fieldName in sr.ReadLine().Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries)) { try { qd.FieldHeaders.Add(fieldName, index); index++; } catch (Exception) { throw new Exception("[RestoreMetadataFromS3] fieldName duplication: " + fieldName + " - " + qd.FileName); } } } }); Parallel.ForEach(GetParts(), new ParallelOptions { MaxDegreeOfParallelism = 2 }, p => //Parallel.ForEach(GetParts(), p => { Logger.Write(chunkId, LogMessageTypes.Info, "load part=" + p); var part = new ChunkPart(chunkId, createPersonBuilder, p); LoadPart(part, p); part.Build(vocabularyService); SavePart(part, p); }); dbChunk.ChunkLoaded(chunkId); Logger.Write(chunkId, LogMessageTypes.Info, string.Format("Loaded - {0} ms | {1} Mb", timer.ElapsedMilliseconds, (GC.GetTotalMemory(false) / 1024f) / 1024f)); if (Settings.Current.Building.DestinationEngine.Database == Database.Redshift) { ServicesManager.AddToSaveQueue(chunkId); Logger.Write(null, LogMessageTypes.Debug, "AddToSaveQueue " + chunkId); } else { dbChunk.ChunkComplete(chunkId); } channelFactory.Close(); } catch (Exception e) { Logger.WriteError(chunkId, e); throw; } }
private void SaveSet(int setIndex) { var dbAvailableOnS3 = new DbAvailableOnS3(Settings.Current.Building.BuilderConnectionString); Logger.Write(null, LogMessageTypes.Debug, "sss SaveSet BEGIN index - " + setIndex); var chunkIds = dbAvailableOnS3.GetChunksId(Settings.Current.Building.Id.Value, setIndex).ToArray(); Logger.Write(null, LogMessageTypes.Debug, "sss SaveSet chunkIds: " + string.Join(",", chunkIds)); var timer = new Stopwatch(); timer.Start(); using ( var connection = SqlConnectionHelper.OpenOdbcConnection(Settings.Current.Building.DestinationConnectionString)) 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"); 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 if (Settings.Current.Building.CDM == CDMVersions.v501) { SaveTable(connection, transaction, setIndex, "COST"); } transaction.Commit(); } catch (Exception e) { foreach (var chunkId in chunkIds) { Logger.WriteError(chunkId, e); transaction.Rollback(); Logger.Write(chunkId, LogMessageTypes.Debug, "Rollback - Complete"); } } } var dbChunk = new DbChunk(Settings.Current.Building.BuilderConnectionString); foreach (var chunkId in chunkIds) { dbChunk.ChunkComplete(chunkId); } timer.Stop(); Logger.Write(null, LogMessageTypes.Info, string.Format("Save - {0} ms", timer.ElapsedMilliseconds)); }
private static void Build(BuilderController builderController) { var dbChunk = new DbChunk(Settings.Current.Building.BuilderConnectionString); int?chunkId = null; while (true) { try { builderController.RefreshState(); if (builderController.Builder.State == BuilderState.Stopping) { break; } if (builderController.Builder.State == BuilderState.Stopped) { break; } if (builderController.Builder.State == BuilderState.Error) { break; } if (builderController.Builder.State == BuilderState.Unknown || builderController.Builder.State == BuilderState.Idle) { continue; } chunkId = dbChunk.TakeChunk(Settings.Current.Building.Id.Value); if (!chunkId.HasValue) { break; } var builder = new ChunkBuilder(new ChunkData { Id = chunkId.Value }, GetBuilderType(Settings.Current.Building.Vendor)); var loaded_chunk = builder.Load(); var built_chunk = loaded_chunk.Build(); var task = built_chunk.Save(); tasks.Add(task); } catch (Exception e) { if (chunkId.HasValue) { Logger.WriteError(chunkId, e); } else { Logger.Write(null, Settings.Current.Building.Id.Value, null, LogMessageTypes.Error, Logger.CreateExceptionString(e)); } builderController.UpdateState(BuilderState.Error); } } }
public void Process() { try { var dbChunk = new DbChunk(Settings.Settings.Current.Building.BuilderConnectionString); var timer = new Stopwatch(); timer.Start(); var folder = $"{Settings.Settings.Current.Building.Vendor}/{Settings.Settings.Current.Building.Id}/raw"; Parallel.ForEach(Settings.Settings.Current.Building.SourceQueryDefinitions, qd => { if (qd.Providers != null) { return; } if (qd.Locations != null) { return; } if (qd.CareSites != null) { return; } //var sql = qd.GetSql(Settings.Current.Building.SourceEngine.Database, // Settings.Current.Building.Vendor, Settings.Current.Building.SourceSchemaName); var sql = GetSqlHelper.GetSql(Settings.Settings.Current.Building.SourceEngine.Database, qd.GetSql(Settings.Settings.Current.Building.Vendor, Settings.Settings.Current.Building.SourceSchemaName), Settings.Settings.Current.Building.SourceSchemaName); if (string.IsNullOrEmpty(sql)) { return; } qd.FieldHeaders = new Dictionary <string, int>(StringComparer.OrdinalIgnoreCase); var metadataKey = $"{folder}/metadata/{qd.FileName + ".txt"}"; using (var client = new AmazonS3Client(Settings.Settings.Current.S3AwsAccessKeyId, Settings.Settings.Current.S3AwsSecretAccessKey, Amazon.RegionEndpoint.USEast1)) using (var stream = new MemoryStream()) using (var sr = new StreamReader(stream)) { var request = new GetObjectRequest { BucketName = Settings.Settings.Current.Bucket, Key = metadataKey }; var getObject = client.GetObjectAsync(request); getObject.Wait(); using (var response = getObject.Result.ResponseStream) { response.CopyTo(stream); } stream.Position = 0; var index = 0; foreach (var fieldName in sr.ReadLine().Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries)) { try { qd.FieldHeaders.Add(fieldName, index); index++; } catch (Exception) { throw new Exception("[RestoreMetadataFromS3] fieldName duplication: " + fieldName + " - " + qd.FileName); } } } }); Parallel.ForEach(GetParts(), new ParallelOptions { MaxDegreeOfParallelism = 2 }, p => { Logger.Write(_chunkId, LogMessageTypes.Info, "load part=" + p); var part = new DatabaseChunkPart(_chunkId, _createPersonBuilder, p, 0); LoadPart(part, p); part.Build(); SavePart(part, p); }); Logger.Write(_chunkId, LogMessageTypes.Info, $"Loaded - {timer.ElapsedMilliseconds} ms | {(GC.GetTotalMemory(false) / 1024f) / 1024f} Mb"); dbChunk.ChunkComplete(_chunkId, Settings.Settings.Current.Building.Id.Value); } catch (Exception e) { Logger.WriteError(_chunkId, e); throw; } }
public ChunkController() { dbKeyOffset = new DbKeyOffset(Settings.Current.Building.BuilderConnectionString); dbChunk = new DbChunk(Settings.Current.Building.BuilderConnectionString); dbSource = new DbSource(Settings.Current.Building.SourceConnectionString); }