Оффлайн
- Регистрация
- 1 Мар 2015
- Сообщения
- 1,481
- Баллы
- 155
Hades DatabaseConnector 6.4.0 now does IRIS stuff!
A well deserved celebration ? that OHDSI officially announced and dialect with 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.
import_iris_certificate.sh
#!/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!
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!