BroadSea - Core OHDSI Walkthrough with InterSystems OMOP

So the DatabaseConnector now supports InterSystems IRIS and the Journey thus far now brings us to the OHDSI ecosystem of powerful open source  apps , targeted at deriving value from large scale analytics from Healthcare data.  A good starting point is the OHDSI/Broadsea solution, which puts core OHDSI technologies like Atlas, R and Hades to work for you locally or on a dedicated instance of your choice.  Here we will walk through the Broadsea solution and how to get to it to work with the InterSystems OMOP Cloud Service.  Being able to carry out these steps on the OHDSI/BroadSea repository is proof of the step forward to inclusion of the `iris` dialect in OHDSI Tools.
 

There are efforts on the way to make this more seamless from ISC ( https://github.com/isc-krakshith/InterSystems-Broadsea ), but in the spirit of impatience and expiring trials, this walkthrough can get you to the same spot.

Clone

First up, clone the github.com/OHDSI/Broadsea  repository locally, review the requirements.

git clone https://github.com/OHDSI/Broadsea.git
cd Broadsea

ℹ Attention Windows Users

The containers in Broadsea have issues with CTRL characters in the secrets file, to alleviate this issue, ensure after cloning the following:

git add --renormalize .
git config --global core.autocrlf false
dos2unix secrets/webapi/

 

Modify

The repo is very accommodating to customization, but there were some things either I had to work around to get to the point (I did mention I was impatient).

In the `.env` file, we need to use the latest build from github for the new IRIS inclusion in the webapi, to do this, change the following environment variable as shown.  

- WEBAPI_GITHUB_URL="https://github.com/OHDSI/WebAPI.git#rc-2.13.0"
+ WEBAPI_GITHUB_URL="https://github.com/OHDSI/WebAPI.git"

Next, we need to set our sights on compose/ohdsi-webapi.yml in the repo and make a couple of changes.

Add the environment variable for spring to include the classpath and SSLConfig.properties like below in JAVA_OPTS

    environment:
      + JAVA_OPTS: "-Dcom.intersystems.SSLConfigFile=/tmp/SSLConfig.properties"
      CLASSPATH: ":/var/lib/ohdsi/webapi/lib/additional/broadsea_mounted.jar"
      DATASOURCE_DRIVERCLASSNAME: org.postgresql.Driver
      DATASOURCE_URL: ${WEBAPI_DATASOURCE_URL}

I also set the container to be privileged and set read_only to false to make my life a little easier in post configuration.

Get InterSystems JDBC Driver

The webapi profile has a built in mechanism for including a third party driver by downloading it to `jdbc/none.jar` in the repository.  So from the root of the repo, run the following to get the latest iris driver from maven.  With this bit of Harry Potter wizardry from @Stefan Wittmann you can always get the latest.

cat << 'EOF' > pom.xml
<project>
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.mycompany.sandbox</groupId>
  <artifactId>pull-artifacts</artifactId>
  <version>1</version>
  <dependencies>
    <dependency>
        <groupId>com.intersystems</groupId>
        <artifactId>intersystems-jdbc</artifactId>
        <version>[3.10.3,4.0.0)</version>
    </dependency>
  </dependencies>
</project>
EOF
mvn dependency:copy-dependencies -DoutputDirectory=.
cp intersystems-jdbc-3.10.3.jar jdbc/none.jar

Provision

We are good to provision Broadsea by using the following docker compose profile string.  The thing to note here is that we are using the `webapi-from-git` profile as the ohdsi/webapi container has not been built in a year and will not have IRIS goodness baked in. 

To launch BroadSea, execute the following compose profiles in the repo.

docker-compose --env-file .env --profile webapi-from-git --profile content --profile hades --profile atlasdb --profile atlas-from-image up -d

Break

Take a break and let the containers come up for the solution.

Now, navigate to your localhost through the traefik router, and you should see your BroadSea Applications launch portal...

Next lets put IRIS inside of Hades and Atlas so we can join the OHDSI party.

Post Configuration

Now that Broadsea is up and running, lets add a connection to InterSystems OMOP Cloud service and take it for a spin.  Ill break up the pieces here and include the post configuration script in a teaser at the end for clarity on what needs to be done and what its doing.

Grab the details from your InterSystems OMOP Deployment and set those as variables; this includes the endpoint url, user/pass, description, and certificate.

export IRIS_USER="SQLAdmin"
export IRIS_PASS="REDACTED"
export IRIS_JDBC="jdbc:IRIS://k8s-0a6bc2ca-a2668ebb-2be01ed66b-df29055c4af0630d.elb.us-east-2.amazonaws.com:443/USER/:::true"
export IRIS_DESCRIPTION="InterSystems OMOP Stage"
cat << 'EOF' > certificateSQLaaS.pem
-----BEGIN CERTIFICATE-----
HBLABLAHBLAHBLA
-----END CERTIFICATE-----
EOF

The next block will add the source to AtlasDB for use with Atlas.

cat << EOF > 200_populate_iris_source_daimon.sql
INSERT INTO webapi.source( source_id, source_name, source_key, source_connection, source_dialect, username, password)
VALUES (2, '$IRIS_DESCRIPTION', 'IRIS', '$IRIS_JDBC', 'iris', '$IRIS_USER', '$IRIS_PASS');
INSERT INTO webapi.source_daimon( source_daimon_id, source_id, daimon_type, table_qualifier, priority) VALUES (4, 2, 0, 'OMOPCDM54', 0);
INSERT INTO webapi.source_daimon( source_daimon_id, source_id, daimon_type, table_qualifier, priority) VALUES (5, 2, 1, 'OMOPCDM54', 10);
INSERT INTO webapi.source_daimon( source_daimon_id, source_id, daimon_type, table_qualifier, priority) VALUES (6, 2, 2, 'OMOPCDM54_RESULTS', 0);
EOF

docker cp 200_populate_iris_source_daimon.sql broadsea-atlasdb:/docker-entrypoint-initdb.d/200_populate_iris_source_daimon.sql
docker-compose exec broadsea-atlasdb psql -U postgres -f "/docker-entrypoint-initdb.d/200_populate_iris_source_daimon.sql"

The next steps here add the certificate to the Hades and land the appropriate SSLConfig.properties.

docker cp certificateSQLaaS.pem broadsea-hades:/home/ohdsi/
docker cp SSLConfigHades.properties broadsea-hades:/home/ohdsi/SSLConfig.properties

docker-compose exec broadsea-hades bash -c "/usr/bin/keytool -importcert -file /home/ohdsi/certificateSQLaaS.pem -keystore /home/ohdsi/keystore.jks -alias IRIScert -storepass changeit -noprompt"

Moving right along to post configuring the web api for TLS connectivity to our InterSystems OMOP deployment, execute the following to generate the keystore, and to hit the refresh api on the webapi container for the source to be activated.

docker exec ohdsi-webapi bash -c "keytool -importcert -file /tmp/certificateSQLaaS.pem -alias IRIScert2 -keystore /tmp/keystore.jks -storepass changeit -noprompt"
wget http://127.0.0.1/WebAPI/source/refresh/

Probably the most invasive post configuration step is the creation of two TLS/SSL dependent files inside of the container.  The previous instructions require the SSLProperties.config and manually paste the certiicate as well in the `/tmp` folder.

docker exec --user root -it ohdsi-webapi bash
cat > /tmp/certificateSQLaaS.pem
-----BEGIN CERTIFICATE-----
HBLABLAHBLAHBLA
-----END CERTIFICATE-----
<CTRL-D>

cat > /tmp/SSLConfig.properties
trustStore=/tmp/keystore.jks
trustStorePassword=changeit
<CTRL-D>

If you can get `docker cp` or a volume mount to work with this container, please let me know.
 

Finally, add the driver to Hades, navigate to Hades on http://127.0.0.1/hades and in RStudio, run updates.

This might fire up some fans on your system and take a little bit, but when you are all done, you should be able to get the driver via the DatabaseConnector package inside Hades using R Studio.

library(DatabaseConnector)
downloadJdbcDrivers("iris", pathToDriver = Sys.getenv("DATABASECONNECTOR_JAR_FOLDER"), method = "auto")

👣🔫 You will notice that we used OHDSI tools to get the IRIS driver, and for webapi we uploaded it manually through the use of compose services, and through Hades we used the DatabaseConnector package... this may differ versions for those components. If you want to stay in sync, you can skip using OHDSI DatabaseConnector tools for the driver and just cp it up instead so they match.  

docker cp intersystems-jdbc-3.10.3.jar broadsea-hades:/opt/hades/jdbc_drivers/

 

Smoke

Hades

Lets see if Hades can connect to our InterSystems OMOP deployment

cd <- DatabaseConnector::createConnectionDetails(dbms = "iris", connectionString = "jdbc:IRIS://k8s-0a6bc2ca-adb040ad-c7bf2ee7c6-e6b05ee242f76bf2.elb.us-east-1.amazonaws.com:443/USER/:::true", user = "SQLAdmin", port = "443", password = "REDACTED", pathToDriver = "./jars")
conn <- connect(cd)
querySql(conn, "SELECT TOP 3 * FROM OMOPCDM54.care_site")

boom.

Atlas

Next, lets go over to atlas and confirm we are talking to our InterSystems OMOP deployment at http://127.0.0.1.

Immediately, head to the configuration section, and set the vocabulary and record counts to defaults.

Next, select the "InterSystems OMOP Stage" option in the Profiles section and select a known practitioner id.

boom.

Head to the search screen and search for something medical, here I am searching the InterSystems OMOP deployment for "lung"

boom.

export IRIS_USER="SQLAdmin"
export IRIS_PASS="REDACTED"
export IRIS_JDBC="jdbc:IRIS://k8s-0a6bc2ca-a2668ebb-2be01ed66b-df29055c4af0630d.elb.us-east-2.amazonaws.com:443/USER/:::true"
export IRIS_DESCRIPTION="InterSystems OMOP Stage"
cat << 'EOF' > certificateSQLaaS.pem
-----BEGIN CERTIFICATE-----
HBLABLAHBLAHBLA
-----END CERTIFICATE-----
EOF
cat << EOF > 200_populate_iris_source_daimon.sql
INSERT INTO webapi.source( source_id, source_name, source_key, source_connection, source_dialect, username, password)
VALUES (2, '$IRIS_DESCRIPTION', 'IRIS', '$IRIS_JDBC', 'iris', '$IRIS_USER', '$IRIS_PASS');
INSERT INTO webapi.source_daimon( source_daimon_id, source_id, daimon_type, table_qualifier, priority) VALUES (4, 2, 0, 'OMOPCDM54', 0);
INSERT INTO webapi.source_daimon( source_daimon_id, source_id, daimon_type, table_qualifier, priority) VALUES (5, 2, 1, 'OMOPCDM54', 10);
INSERT INTO webapi.source_daimon( source_daimon_id, source_id, daimon_type, table_qualifier, priority) VALUES (6, 2, 2, 'OMOPCDM54_RESULTS', 0);
EOF
docker cp 200_populate_iris_source_daimon.sql broadsea-atlasdb:/docker-entrypoint-initdb.d/200_populate_iris_source_daimon.sql
docker-compose exec broadsea-atlasdb psql -U postgres -f "/docker-entrypoint-initdb.d/200_populate_iris_source_daimon.sql"
docker cp certificateSQLaaS.pem broadsea-hades:/home/ohdsi/
docker cp SSLConfigHades.properties broadsea-hades:/home/ohdsi/SSLConfig.properties
docker-compose exec broadsea-hades bash -c "/usr/bin/keytool -importcert -file /home/ohdsi/certificateSQLaaS.pem -keystore /home/ohdsi/keystore.jks -alias IRIScert -storepass changeit -noprompt"
docker exec ohdsi-webapi bash -c "keytool -importcert -file /tmp/certificateSQLaaS.pem -alias IRIScert2 -keystore /tmp/keystore.jks -storepass changeit -noprompt"
wget http://127.0.0.1/WebAPI/source/refresh/

Welcome to BroadSea against the InterSystems OMOP server and the OHDSI Community!