OMOP Odyssey - Celebration (House of Hades)Contestant

Hades DatabaseConnector 6.4.0 now does IRIS stuff!

A well deserved celebration 🎉 that OHDSI officially announced IRIS as a supported platform and dialect with DatabaseConnector 6.4.0 in the OHDSI Hades Project!  Lets showcase this accomplishment and create the OMOP CDM 5.4 on InterSystems IRIS, persona time!

Ms. TLS - I am running InterSystems Cloud Document (Health Connect Cloud or equivalent )

I have a running IRIS Cloud Document workload and managed to create the OMOPCDM54 DATABASE with minimal steps on my system, The Cloud Portal and RStudio.

System

However you import certs on your system, it needs to be done here to connect to InterSystems Cloud Document deployment.  I use the following script loosely across solutions, where I paste in the cert I downloaded from the portal and it imports the cert for me.
 

#!/bin/bash

cat << 'EOF' > /usr/local/share/ca-certificates/cloudsql-gar2.crt
-----BEGIN CERTIFICATE-----
PEM FROM CLOUD DEPLOYMENT HERE
-----END CERTIFICATE-----
EOF

update-ca-certificates

PEM_FILE="/etc/ssl/certs/cloudsql-gar2pem"
PASSWORD="changeit"
JAVA_HOME=$(readlink -f /usr/bin/java | sed "s:bin/java::")
KEYSTORE="$JAVA_HOME/lib/security/cacerts"
CERTS=$(grep 'END CERTIFICATE' $PEM_FILE| wc -l)
echo $KEYSTORE
# To process multiple certs with keytool, you need to extract
# each one from the PEM file and import it into the Java KeyStore.
for N in $(seq 0 $(($CERTS - 1))); do
  ALIAS="$(basename $PEM_FILE)-$N"
  echo "Adding to keystore with alias:$ALIAS"
  cat $PEM_FILE |
    awk "n==$N { print }; /END CERTIFICATE/ { n++ }" |
    keytool -noprompt -import -trustcacerts \
            -alias $ALIAS -keystore $KEYSTORE -storepass $PASSWORD
done

The Cloud Portal

This can be done alternate ways over the listener as well, but to highlight it can be done in the Cloud Portal, we can use the SQL Query Tools.

RStudio

Next step is Rstudio, where the OHDSI packages will do most of the work after a little bit of setup.
 

install.packages("devtools")
devtools::install_github("OHDSI/CommonDataModel")
install.packages("DatabaseConnector")
install.packages("SqlRender")
Sys.setenv("DATABASECONNECTOR_JAR_FOLDER" = "/home/sween/Desktop/OMOP/iris-omop-extdb/jars")
library(DatabaseConnector)
downloadJdbcDrivers("iris")

Back to RStudio

We can check to see if the iris dialect (now included with OHDSI tooling) is actually in there.
 

CommonDataModel::listSupportedDialects()

Crowd goes wild, indeed IRIS is in there.

I was ready to go here, but for starters I wanted to just dump the ddls it creates to take a peak at them, and check them in somewhere, this dump the ddls in "dry run" fashion.

CommonDataModel::buildRelease(cdmVersions = "5.4",
                              targetDialects = "iris",
                              outputfolder = "./ddl/iris/")

Inspecting our output folder:



Now lets target our InterSystems Cloud Document instance, and actually create the OMOP database, in two steps, one to connect, and one to execute them.

Connect

cd <- DatabaseConnector::createConnectionDetails(dbms = "iris",
                                                 connectionString = "jdbc:IRIS://k8s-0a6bc2ca-a096b3cb-578f6ec1b7-8b0e928263a4990a.elb.us-east-2.amazonaws.com:443/USER/:::true",
                                                 user = "SQLAdmin",
                                                 port = "443",
                                                 password = "PASSWORD",
                                                 pathToDriver = "./jars",
)

Execute

CommonDataModel::executeDdl(connectionDetails = cd,
                            cdmVersion = "5.4",
                            cdmDatabaseSchema = "OMOPCDM54"
                            )

If everything goes well, no errors will be reported in RStudio

Check your work on the InterSystems Cloud Document deployment, its empty, but its there!

👣🔫 Alert, and will mention this one again on the OMOP Journey.  In the CDM for IRIS, there is something to be aware of with a name colission of sorts with the "DOMAIN" table.  IF you look in the screenshot above, you can see DOMAIN is all caps.  This is because domain is a keyword on InterSystems SQL Workloads.  So keep in mind that you have to account for quoting the table name for operations to that table if they exist.
 

for table in cdmtables:
    try:
        if table == "domain":
            sql = f'(select * from OMOPCDM54."{table}") AS temp_table;'
        else:
            sql = f'(select * from OMOPCDM54.{table}) AS temp_table;'

 

Mr. Developer ( I am using IRIS and cant be bothered with implementation overhead like TLS )

Im in a hurry, and security is somebody elses job, and want the OMOP database on my local IRIS Community, Eval, Container or POD. Most of the steps apply above, outside a new connection string and we will create the schema through the SMP.

Now back in RStudio  again, point to the developer instance.

cd <- DatabaseConnector::createConnectionDetails(dbms = "iris",
                                                 connectionString =
"jdbc:IRIS://192.168.1.162:1972/USER",
                                                 user = "_SYSTEM",
                                                 port = "1972",
                                                 password = "SYS",
                                                 pathToDriver = "./jars",
)

CommonDataModel::executeDdl(connectionDetails = cd,
                            cdmVersion = "5.4",
                            cdmDatabaseSchema = "OMOPCDM54"
                            )

Connecting with nostalgic credentials...



Now Refresh the SMP and expand the OMOPCDM54 schema.



Woooo!

🙌 🎉 

Excellent work and shout outs for those involved in this effort!