Hey everyone,
I’ve been trying to setup the following 3 components:
- CDM - OMOP
- WebAPI
- Atlas
To do this I’ve done the following steps:
Provision infrastructure in Azure
Configure CDM on SQL server
## First, install the package from GitHub
install.packages("devtools")
devtools::install_github("OHDSI/CommonDataModel")
## List the currently supported SOL dialects
CommonDataModel::listSupportedDialects()
## There are multiple ways to generate the DDLs
## 1. Use the buildRelease function to generate the text files in the dialect you choose.
## This function will put the output files in the folder you specify
CommonDataModel::buildRelease(
cdmVersions="5.4",
targetDialects = "sql server",
outputfolder = "/Users/frederDS/repos/medemotion/OMOP/CDM_demo"
)
## 2. If you have an empty schema ready to go, the package will connect and instantiate the tables for you.
### 2a. To start, you need to download DatabaseConnector in order to connect to your database.
devtools::install_github("OHDSI/DatabaseConnector")
Sys.setenv("DATABASECONNECTOR_JAR_FOLDER" = "/Users/frederDS/repos/medemotion/OMOP/drivers")
DatabaseConnector::downloadJdbcDrivers("sql server",Sys.getenv("DATABASECONNECTOR_JAR_FOLDER"),"auto")
cd <- DatabaseConnector::createConnectionDetails(dbms = "sql server",
server = "sql-mem-dpl-sdev-01.database.windows.net",
user = "omop",
password = "*********************",
pathToDriver = "/Users/frederDS/repos/medemotion/OMOP/drivers",
extraSettings = "database=********************; encrypt=true; trustServerCertificate=false;"
)
CommonDataModel::executeDdl(
connectionDetails = cd,
cdmVersion = "5.4",
cdmDatabaseSchema = "cdm"
)
This created my SQL Server tables.
The next step is to load data in there. To do this I’ve created a synthea set. I’ve made sure to use the compatible version (3.0.0) (following this method https://towardsdatascience.com/generating-synthetic-patient-data-b7901c3bd397).
Afterwards the downloaded data was loaded with the following commands:
devtools::install_github("OHDSI/ETL-Synthea")
library(ETLSyntheaBuilder)
# We are loading a version 5.4 CDM into a local PostgreSQL database called "synthea10".
# The ETLSyntheaBuilder package leverages the OHDSI/CommonDataModel package for CDM creation.
# Valid CDM versions are determined by executing CommonDataModel::listSupportedVersions().
# The strings representing supported CDM versions are currently "5.3" and "5.4".
# The Synthea version we use in this example is 2.7.0. Since Synthea's MASTER branch is always active, we currently
# only support version 2.7.0.
# The schema to load the Synthea tables is called "native".
# The schema to load the Vocabulary and CDM tables is "cdm_synthea10".
# The username and pw are "postgres" and "lollipop".
# The Synthea and Vocabulary CSV files are located in /tmp/synthea/output/csv and /tmp/Vocabulary_20181119, respectively.
# For those interested in seeing the CDM changes from 5.3 to 5.4, please see: http://ohdsi.github.io/CommonDataModel/cdm54Changes.html
cd <- DatabaseConnector::createConnectionDetails(
dbms = "sql server",
server = "***********************",
user = "omop",
password = "***********",
pathToDriver = "/Users/frederDS/repos/medemotion/OMOP/drivers",
extraSettings = "database=****************; encrypt=true; trustServerCertificate=false;"
)
cdmSchema <- "cdm"
cdmVersion <- "5.4"
syntheaVersion <- "3.0.0"
syntheaSchema <- "synthea" # SHOULD ALREADY EXIST
syntheaFileLoc <- "/Users/frederDS/repos/medemotion/OMOP/synthea"
vocabFileLoc <- "/Users/frederDS/repos/medemotion/OMOP/vocabulary"
#
ETLSyntheaBuilder::CreateCDMTables(connectionDetails = cd, cdmSchema = cdmSchema, cdmVersion = cdmVersion)
ETLSyntheaBuilder::CreateSyntheaTables(connectionDetails = cd, syntheaSchema = syntheaSchema, syntheaVersion = syntheaVersion)
ETLSyntheaBuilder::LoadSyntheaTables(connectionDetails = cd, syntheaSchema = syntheaSchema, syntheaFileLoc = syntheaFileLoc)
ETLSyntheaBuilder::LoadVocabFromCsv(connectionDetails = cd, cdmSchema = cdmSchema, vocabFileLoc = vocabFileLoc)
ETLSyntheaBuilder::LoadEventTables(connectionDetails = cd, cdmSchema = cdmSchema, syntheaSchema = syntheaSchema, cdmVersion = cdmVersion, syntheaVersion = syntheaVersion)
-- remove any previously added database connection configuration data
DELETE webapi.source_daimon; -- noqa: PRS
DELETE webapi.source;
-- TODO: Should this exist in the webapi instead? For now, allowing null based on insert
ALTER TABLE [webapi].[source]
ALTER COLUMN [krb_auth_method] [varchar](10) NULL -- NOT NULL in existing setup, setting to NULL based on insert
-- SET webapi CDM source
INSERT INTO webapi.source( source_id, source_name, source_key, source_connection, source_dialect)
VALUES (
1,
'webapi CDM V5 Database',
'webapi-CDMV5',
'jdbc:sqlserver://**************************************',
'sql server');
-- CDM daimon
INSERT INTO webapi.source_daimon( source_daimon_id, source_id, daimon_type, table_qualifier, priority) VALUES (1, 1, 0, 'cdm', 2);
-- VOCABULARY daimon
INSERT INTO webapi.source_daimon( source_daimon_id, source_id, daimon_type, table_qualifier, priority) VALUES (2, 1, 1, 'cdm', 2);
-- RESULTS daimon
INSERT INTO webapi.source_daimon( source_daimon_id, source_id, daimon_type, table_qualifier, priority) VALUES (3, 1, 2, 'webapi', 2);
-- EVIDENCE daimon
INSERT INTO webapi.source_daimon( source_daimon_id, source_id, daimon_type, table_qualifier, priority) VALUES (4, 1, 3, 'results', 2);
I’ve retrieved the vocabulary content on Athena. What seems strange to me is that you cannot select any vocabulary version to download. I assume this is why my Configuration page in atlas shows v5.0
Additional information:
- to do this I had to disable my constraints temporarily
- Some schemas had to be provided beforehand. I did this manuallly.
My next step is to configure OMOP WebAPI + Atlas. I’m trying to run this locally with the following docker container + env vars.
FROM ohdsi/webapi:2.13.0
ENV DATASOURCE_DRIVERCLASSNAME="org.postgresql.Driver"
ENV DATASOURCE_URL="jdbc:postgresql://*********.database.azure.com/OHDSI"
ENV DATASOURCE_USERNAME="ohdsi_app_user@**************"
ENV DATASOURCE_PASSWORD="***********************"
ENV DATASOURCE_OHDSI_SCHEMA="webapi"
ENV SPRING_JPA_PROPERTIES_HIBERNATE_DIALECT="org.hibernate.dialect.PostgreSQLDialect"
ENV SPRING_JPA_PROPERTIES_HIBERNATE_DEFAULT_SCHEMA="webapi"
ENV SPRING_BATCH_REPOSITORY_TABLEPREFIX="webapi.BATCH_"
ENV FLYWAY_DATASOURCE_DRIVERCLASSNAME="org.postgresql.Driver"
ENV FLYWAY_DATASOURCE_URL="jdbc:postgresql://*************.database.azure.com/OHDSI"
ENV FLYWAY_DATASOURCE_USERNAME="ohdsi_admin_user@*************"
ENV FLYWAY_DATASOURCE_PASSWORD="***************"
ENV FLYWAY_LOCATIONS="classpath:db/migration/postgresql"
ENV FLYWAY_PLACEHOLDERS_OHDSISCHEMA="webapi"
ENV FLYWAY_SCHEMAS="webapi"
ENV FLYWAY_BASELINEONMIGRATE="true"
ENV FLYWAY_TABLE="schema_history"
ENV flyway_baselineVersionAsString="2.2.5.20180212152023"
ENV FLYWAY_BASELINEDESCRIPTION="Base Migration"
ENV SECURITY_CORS_ENABLED="true"
Atlas
from ohdsi/atlas:2.13.0
ENV WEBAPI_URL='http://localhost:8080/WebAPI/'
# ENV ATLAS_HOSTNAME=""?
Issues I have:
-
In Atlas the profiles cannot be retrieved. I’m getting not found but in the db I can see there’s a profile with this id.
-
Some of the reports do not load. (with red arrow)
-
When creating a new cohort definition there’s always one error
Questions:
- Is this the correct way of working?
- Do you see any issues with this configuration?
