> # --- > # > # This file has been addapted from the documentation for the ETL-Synthea project: > # https://github.com/OHDSI/ETL-Synthea > # > # The SHA code used to specify the version used here is for the 2022-12-02 commit. > # > # --- > > devtools::install_github("OHDSI/ETL-Synthea@bae1c8eca8dba7ba1129f6c5f758b985c5458405") Skipping install of 'ETLSyntheaBuilder' from a github remote, the SHA1 (bae1c8ec) has not changed since last install. Use `force = TRUE` to force installation > > library(ETLSyntheaBuilder) > > cd <- DatabaseConnector::createConnectionDetails( + dbms = "postgresql", + server = "localhost/OHDSI", + user = "postgres", + password = "ohdsi", + port = 5432, + pathToDriver = "C:\\_YES\\databases\\postgres\\drivers\\42.3.3" + ) > > cdmSchema <- "etl_synthea_1k" > cdmVersion <- "5.4" > syntheaVersion <- "2.7.0" > syntheaSchema <- "etl_synthea_1k_synthea_native" > syntheaFileLoc <- "C:\\_YES\\workspace\\fhir-to-omop\\src\\main\\resources\\test\\fhir\\csv\\synthmass-1k\\synthea_sample_data_csv_apr2020\\csv" > vocabFileLoc <- "C:\\_YES\\tools\\terminology\\fhir-to-omop\\for-testing\\csv" > > ETLSyntheaBuilder::CreateCDMTables(connectionDetails = cd, cdmSchema = cdmSchema, cdmVersion = cdmVersion) Connecting using PostgreSQL driver |====================================================================================================================================================| 100% Executing SQL took 0.205 secs > > ETLSyntheaBuilder::CreateSyntheaTables(connectionDetails = cd, syntheaSchema = syntheaSchema, syntheaVersion = syntheaVersion) Running synthea_version/v270/create_synthea_tables.sql Connecting using PostgreSQL driver |====================================================================================================================================================| 100% Executing SQL took 0.072 secs > > ETLSyntheaBuilder::LoadSyntheaTables(connectionDetails = cd, syntheaSchema = syntheaSchema, syntheaFileLoc = syntheaFileLoc) Connecting using PostgreSQL driver Loading: allergies.csv |====================================================================================================================================================| 100% Loading: careplans.csv |====================================================================================================================================================| 100% Loading: conditions.csv |====================================================================================================================================================| 100% Loading: devices.csv |====================================================================================================================================================| 100% Loading: encounters.csv |====================================================================================================================================================| 100% Loading: imaging_studies.csv |====================================================================================================================================================| 100% Loading: immunizations.csv |====================================================================================================================================================| 100% Loading: medications.csv |====================================================================================================================================================| 100% Loading: observations.csv |====================================================================================================================================================| 100% Loading: organizations.csv |====================================================================================================================================================| 100% Loading: patients.csv |====================================================================================================================================================| 100% Loading: payer_transitions.csv |====================================================================================================================================================| 100% Loading: payers.csv |====================================================================================================================================================| 100% Loading: procedures.csv |====================================================================================================================================================| 100% Loading: providers.csv |====================================================================================================================================================| 100% Loading: supplies.csv > > ETLSyntheaBuilder::LoadVocabFromCsv(connectionDetails = cd, cdmSchema = cdmSchema, vocabFileLoc = vocabFileLoc) Connecting using PostgreSQL driver Working on file C:\_YES\tools\terminology\fhir-to-omop\for-testing\csv/CONCEPT.csv - reading file |--------------------------------------------------| |==================================================| - handling dates - type converting - uploading 5896259 rows of data in 1 chunks. |====================================================================================================================================================| 100% Executing SQL took 0.006 secs - chunk uploading started on 2023-03-06 08:52:46 for rows 1 to 5896259 |====================================================================================================================================================| 100% - Success Working on file C:\_YES\tools\terminology\fhir-to-omop\for-testing\csv/CONCEPT_ANCESTOR.csv - reading file - type converting - uploading 69838015 rows of data in 7 chunks. |============================================================================================================================| 100% Executing SQL took 0.0134 secs - chunk uploading started on 2023-03-06 09:01:45 for rows 1 to 10000001 |============================================================================================================================| 100% - chunk uploading started on 2023-03-06 09:03:01 for rows 10000002 to 20000002 |============================================================================================================================| 100% - chunk uploading started on 2023-03-06 09:04:14 for rows 20000003 to 30000003 |============================================================================================================================| 100% - chunk uploading started on 2023-03-06 09:05:27 for rows 30000004 to 40000004 |============================================================================================================================| 100% - chunk uploading started on 2023-03-06 09:06:41 for rows 40000005 to 50000005 |============================================================================================================================| 100% - chunk uploading started on 2023-03-06 09:07:53 for rows 50000006 to 60000006 |============================================================================================================================| 100% - chunk uploading started on 2023-03-06 09:09:07 for rows 60000007 to 69838015 |============================================================================================================================| 100% - Success Working on file C:\_YES\tools\terminology\fhir-to-omop\for-testing\csv/CONCEPT_CLASS.csv - reading file - type converting - uploading 416 rows of data in 1 chunks. |============================================================================================================================| 100% Executing SQL took 0.00607 secs - chunk uploading started on 2023-03-06 09:10:18 for rows 1 to 416 |============================================================================================================================| 100% - Success Working on file C:\_YES\tools\terminology\fhir-to-omop\for-testing\csv/CONCEPT_RELATIONSHIP.csv - reading file |--------------------------------------------------| |==================================================| - handling dates - type converting - uploading 47294986 rows of data in 5 chunks. |============================================================================================================================| 100% Executing SQL took 0.00931 secs - chunk uploading started on 2023-03-06 09:15:10 for rows 1 to 10000001 |============================================================================================================================| 100% - chunk uploading started on 2023-03-06 09:28:58 for rows 10000002 to 20000002 |============================================================================================================================| 100% - chunk uploading started on 2023-03-06 09:42:56 for rows 20000003 to 30000003 |============================================================================================================================| 100% - chunk uploading started on 2023-03-06 09:56:53 for rows 30000004 to 40000004 |============================================================================================================================| 100% - chunk uploading started on 2023-03-06 10:30:18 for rows 40000005 to 47294986 |===================================================================================================================| 100% - Success Working on file C:\_YES\tools\terminology\fhir-to-omop\for-testing\csv/CONCEPT_SYNONYM.csv - reading file - type converting - uploading 2034071 rows of data in 1 chunks. |===================================================================================================================| 100% Executing SQL took 0.00522 secs - chunk uploading started on 2023-03-06 10:35:14 for rows 1 to 2034071 |===================================================================================================================| 100% - Success Working on file C:\_YES\tools\terminology\fhir-to-omop\for-testing\csv/DOMAIN.csv - reading file - type converting - uploading 50 rows of data in 1 chunks. |===================================================================================================================| 100% Executing SQL took 0.00685 secs - chunk uploading started on 2023-03-06 10:35:40 for rows 1 to 50 |===================================================================================================================| 100% - Success Working on file C:\_YES\tools\terminology\fhir-to-omop\for-testing\csv/DRUG_STRENGTH.csv - reading file - handling dates - type converting - uploading 2936602 rows of data in 1 chunks. |===================================================================================================================| 100% Executing SQL took 0.00302 secs - chunk uploading started on 2023-03-06 10:35:47 for rows 1 to 2936602 |===================================================================================================================| 100% - Success Working on file C:\_YES\tools\terminology\fhir-to-omop\for-testing\csv/RELATIONSHIP.csv - reading file - type converting - uploading 672 rows of data in 1 chunks. |===================================================================================================================| 100% Executing SQL took 0.00361 secs - chunk uploading started on 2023-03-06 10:37:24 for rows 1 to 672 |===================================================================================================================| 100% - Success Working on file C:\_YES\tools\terminology\fhir-to-omop\for-testing\csv/VOCABULARY.csv - reading file - type converting - uploading 59 rows of data in 1 chunks. |===================================================================================================================| 100% Executing SQL took 0.00333 secs - chunk uploading started on 2023-03-06 10:37:24 for rows 1 to 59 |===================================================================================================================| 100% - Success Warning messages: 1: In data.table::fread(file = paste0(vocabFileLoc, "/", csv), stringsAsFactors = FALSE, : Found and resolved improper quoting out-of-sample. First healed line 9258: <<2614984 "y set" tubing for peritoneal dialysis Device HCPCS HCPCS S A4719 20020101 20991231 >>. If the fields are not quoted (e.g. field separator does not appear within any field), try quote="" to avoid this warning. 2: `type_convert()` only converts columns of type 'character'. - `df` has no columns of type 'character' 3: In data.table::fread(file = paste0(vocabFileLoc, "/", csv), stringsAsFactors = FALSE, : Found and resolved improper quoting out-of-sample. First healed line 71489: <<4014146 "Morning after" intrauterine contraceptive device fitted 4180186>>. If the fields are not quoted (e.g. field separator does not appear within any field), try quote="" to avoid this warning. > > # ETLSyntheaBuilder::LoadEventTables(connectionDetails = cd, cdmSchema = cdmSchema, syntheaSchema = syntheaSchema, cdmVersion = cdmVersion, syntheaVersion = syntheaVersion) > ETLSyntheaBuilder::LoadEventTables(connectionDetails = cd, cdmSchema = cdmSchema, syntheaSchema = syntheaSchema, cdmVersion = cdmVersion, syntheaVersion = syntheaVersion) Connecting using PostgreSQL driver Running: create_source_to_standard_vocab_map.sql |===================================================================================================================| 100% Executing SQL took 1.67 mins Running: create_source_to_source_vocab_map.sql |===================================================================================================================| 100% Executing SQL took 1.03 mins Connecting using PostgreSQL driver Running: AllVisitTable.sql |===================================================================================================================| 100% Executing SQL took 0.479 secs Running: AAVITable.sql |===================================================================================================================| 100% Executing SQL took 0.778 secs Running: final_visit_ids.sql |===================================================================================================================| 100% Executing SQL took 0.275 secs Connecting using PostgreSQL driver Running: insert_person.sql |===================================================================================================================| 100% Executing SQL took 0.0168 secs Running: insert_observation_period.sql |===================================================================================================================| 100% Executing SQL took 0.035 secs Running: insert_provider.sql |===================================================================================================================| 100% Executing SQL took 0.0224 secs Running: insert_visit_occurrence.sql |===================================================================================================================| 100% Executing SQL took 0.302 secs Running: insert_visit_detail.sql |===================================================================================================================| 100% Executing SQL took 0.288 secs Running: insert_condition_occurrence.sql |===================================================================================================================| 100% Executing SQL took 1.96 hours Running: insert_observation.sql |===================================================================================================================| 100% Executing SQL took 9.93 secs Running: insert_measurement.sql |===================================================================================================================| 100% Executing SQL took 5.68 secs Running: insert_procedure_occurrence.sql |===================================================================================================================| 100% Executing SQL took 18.1 secs Running: insert_drug_exposure.sql |===================================================================================================================| 100% Executing SQL took 0.396 secs Running: insert_condition_era.sql |===================================================================================================================| 100% Executing SQL took 0.0866 secs Running: insert_drug_era.sql |===================================================================================================================| 100% Executing SQL took 13.6 secs Running: insert_cdm_source.sql |===================================================================================================================| 100% Executing SQL took 0.00794 secs Running: insert_device_exposure.sql |===================================================================================================================| 100% Executing SQL took 0.0871 secs Running: insert_death.sql |===================================================================================================================| 100% Executing SQL took 0.0208 secs Running: insert_payer_plan_period.sql |===================================================================================================================| 100% Executing SQL took 0.0434 secs Running: insert_cost_v270.sql |===================================================================================================================| 100% Executing SQL took 41 secs