Standard CDM database for testing / demonstrating

Vojtech_Huser · August 16, 2019, 7:44pm

I want to thank Eunomia creators and share our experience with it.

We were able to install the package.
For running SQL it was great.
For testing our HIV study, we needed temp tables and results schema (for covariates in Feature Extraction). We wrote some code (that I can share) to simply write eunomia tables into postgres.

One more time, thank you for the first beta release of Eunomia. It helped us a lot. We can have more datasets now to test our central processing of dataset results (next to SynPuf and real datasets).

jon_duke · March 15, 2021, 3:37am

@Vojtech_Huser Can we borrow your code to write push eunomia out to Postgres?

Thanks!

Jon

Chris_Knoll · September 8, 2021, 12:53pm

@jon_duke , I’m not sure if you got an answer for this, but this should help:

First, you can export the eunomia dataset into CSVs via R:

exportFolder <- "C:\\Documents\\Eunomia\\data";
options(scipen=9999)
Eunomia::exportToCsv(outputFolder = exportFolder);

You can change the export folder to whatever makes sense to you.

Then I generated the COPY commands that postgresql uses:

copyCommands <- lapply(gsub(pattern = "\\.csv$", "", list.files(exportFolder)), function(file) {
  return (paste0("TRUNCATE TABLE cdm.", file, ";\nCOPY cdm.", file, " FROM '", paste0(exportFolder, "\\", file, sep=""), ".csv' DELIMITER ',' CSV HEADER NULL AS '';"))
})

cat(paste0(copyCommands, collapse="\n"))

This resulted in the following output which I coppied into a PGAdmin session to execute:

TRUNCATE TABLE cdm.CARE_SITE;
COPY cdm.CARE_SITE FROM 'C:\Documents\Eunomia\data\CARE_SITE.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.CDM_SOURCE;
COPY cdm.CDM_SOURCE FROM 'C:\Documents\Eunomia\data\CDM_SOURCE.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.COHORT;
COPY cdm.COHORT FROM 'C:\Documents\Eunomia\data\COHORT.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.COHORT_ATTRIBUTE;
COPY cdm.COHORT_ATTRIBUTE FROM 'C:\Documents\Eunomia\data\COHORT_ATTRIBUTE.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.CONCEPT;
COPY cdm.CONCEPT FROM 'C:\Documents\Eunomia\data\CONCEPT.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.CONCEPT_ANCESTOR;
COPY cdm.CONCEPT_ANCESTOR FROM 'C:\Documents\Eunomia\data\CONCEPT_ANCESTOR.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.CONCEPT_CLASS;
COPY cdm.CONCEPT_CLASS FROM 'C:\Documents\Eunomia\data\CONCEPT_CLASS.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.CONCEPT_RELATIONSHIP;
COPY cdm.CONCEPT_RELATIONSHIP FROM 'C:\Documents\Eunomia\data\CONCEPT_RELATIONSHIP.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.CONCEPT_SYNONYM;
COPY cdm.CONCEPT_SYNONYM FROM 'C:\Documents\Eunomia\data\CONCEPT_SYNONYM.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.CONDITION_ERA;
COPY cdm.CONDITION_ERA FROM 'C:\Documents\Eunomia\data\CONDITION_ERA.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.CONDITION_OCCURRENCE;
COPY cdm.CONDITION_OCCURRENCE FROM 'C:\Documents\Eunomia\data\CONDITION_OCCURRENCE.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.COST;
COPY cdm.COST FROM 'C:\Documents\Eunomia\data\COST.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.DEATH;
COPY cdm.DEATH FROM 'C:\Documents\Eunomia\data\DEATH.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.DEVICE_EXPOSURE;
COPY cdm.DEVICE_EXPOSURE FROM 'C:\Documents\Eunomia\data\DEVICE_EXPOSURE.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.DOMAIN;
COPY cdm.DOMAIN FROM 'C:\Documents\Eunomia\data\DOMAIN.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.DOSE_ERA;
COPY cdm.DOSE_ERA FROM 'C:\Documents\Eunomia\data\DOSE_ERA.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.DRUG_ERA;
COPY cdm.DRUG_ERA FROM 'C:\Documents\Eunomia\data\DRUG_ERA.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.DRUG_EXPOSURE;
COPY cdm.DRUG_EXPOSURE FROM 'C:\Documents\Eunomia\data\DRUG_EXPOSURE.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.DRUG_STRENGTH;
COPY cdm.DRUG_STRENGTH FROM 'C:\Documents\Eunomia\data\DRUG_STRENGTH.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.FACT_RELATIONSHIP;
COPY cdm.FACT_RELATIONSHIP FROM 'C:\Documents\Eunomia\data\FACT_RELATIONSHIP.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.LOCATION;
COPY cdm.LOCATION FROM 'C:\Documents\Eunomia\data\LOCATION.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.MEASUREMENT;
COPY cdm.MEASUREMENT FROM 'C:\Documents\Eunomia\data\MEASUREMENT.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.METADATA;
COPY cdm.METADATA FROM 'C:\Documents\Eunomia\data\METADATA.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.NOTE;
COPY cdm.NOTE FROM 'C:\Documents\Eunomia\data\NOTE.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.NOTE_NLP;
COPY cdm.NOTE_NLP FROM 'C:\Documents\Eunomia\data\NOTE_NLP.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.OBSERVATION;
COPY cdm.OBSERVATION FROM 'C:\Documents\Eunomia\data\OBSERVATION.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.OBSERVATION_PERIOD;
COPY cdm.OBSERVATION_PERIOD FROM 'C:\Documents\Eunomia\data\OBSERVATION_PERIOD.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.PAYER_PLAN_PERIOD;
COPY cdm.PAYER_PLAN_PERIOD FROM 'C:\Documents\Eunomia\data\PAYER_PLAN_PERIOD.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.PERSON;
COPY cdm.PERSON FROM 'C:\Documents\Eunomia\data\PERSON.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.PROCEDURE_OCCURRENCE;
COPY cdm.PROCEDURE_OCCURRENCE FROM 'C:\Documents\Eunomia\data\PROCEDURE_OCCURRENCE.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.PROVIDER;
COPY cdm.PROVIDER FROM 'C:\Documents\Eunomia\data\PROVIDER.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.RELATIONSHIP;
COPY cdm.RELATIONSHIP FROM 'C:\Documents\Eunomia\data\RELATIONSHIP.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.SOURCE_TO_CONCEPT_MAP;
COPY cdm.SOURCE_TO_CONCEPT_MAP FROM 'C:\Documents\Eunomia\data\SOURCE_TO_CONCEPT_MAP.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.SPECIMEN;
COPY cdm.SPECIMEN FROM 'C:\Documents\Eunomia\data\SPECIMEN.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.VISIT_DETAIL;
COPY cdm.VISIT_DETAIL FROM 'C:\Documents\Eunomia\data\VISIT_DETAIL.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.VISIT_OCCURRENCE;
COPY cdm.VISIT_OCCURRENCE FROM 'C:\Documents\Eunomia\data\VISIT_OCCURRENCE.csv' DELIMITER ',' CSV HEADER NULL AS '';
TRUNCATE TABLE cdm.VOCABULARY;
COPY cdm.VOCABULARY FROM 'C:\Documents\Eunomia\data\VOCABULARY.csv' DELIMITER ',' CSV HEADER NULL AS '';

Btw, the DDL for cdm 5.3 calls for NOT NULL columns to appear in the vocabulary table, which I changed to NULL to allow the import to work. Also, if you’re using smething other than cdm as the schema name, then you can just modify the above script (around the TRUNCATE and COPY lines).

After that, I had my CDM tables built from Eunomia. I instantiated a results schema there for WebAPI, and so I should now have a demo environment that is based on the Eunomia dataset. Just need to insert the right records into source and source_daimon and I should be set.

Roberto_Gatta · August 23, 2023, 6:28am

Hello to everyone and thank you very much for the efforts you are putting into this project. I’ve installed Eunomia to move my first steps with OMOP-compliant sandbox but there is something I don’t understand. For example, why there are some duplicated MEASUREMENT_ID values in the table MEASUREMENT? According to the standard wouldn’t it be expected to be a primary key? (OMOP CDM v5.4)