How to add SAP HANA to WebAPI

OHDSI WebAPI is an application, with several RESTful services, that you can use to access 1 or more databases for querying OMOP Common Data Model (CDM) data. WebAPI is developed by Observational Health Data Sciences and Informatics (OHDSI), an interdisciplinary collaborative program making analytics on large-scale health data possible.

WebAPI is a vast topic. It has many features that correspond to observational patient data, which aren’t in the scope of this blog. The focus of this article is to dissect WebAPI to show how it communicates with different CDM databases. We also explain the addition of the SAP HANA database to the list of existing ones.

Here's an overview of the steps:

1. Add the database driver as a dependency to the WebAPI.
2. Update ArachneCommons, a dependency library of WebAPI, with an entry of the new database.
3. Append the new patterns to the SqlRender library.
4. Build and push modified dependencies to a public or private repository.
5. Update the source repository of SqlRender and ArachneCommons in WebAPI.

WebAPI currently supports Postgres, Oracle, Redshift, Impala, SQL Server PDW, and BigQuery. It is a Spring Boot Java application with a dedicated database of its own. The dedicated database is used to store CDM databases configuration and connection details, which in turn is used to connect to the list of databases.

As it is a spring boot application, pom.xml has the dependencies that WebAPI needs. In addition to spring framework-related dependencies, it also includes JDBC drivers of every aforementioned database. To ensure every database driver is properly loaded, WebAPI fetches and verifies the class object of the corresponding driver. The list of required drivers are hard coded as part of the DataAccessConfig class.

Step 1:
Add HANA drivers library to the list of dependencies in pom.xml and add class object name to the drivers list in the DataAccessConfig class.

WebAPI has a few other dependencies like ArachneCommons and SqlRender that must be mentioned here. ArachneCommons is a library that contains common components used across projects of the OHDSI community. It contains an enum object DBMSType that has supported databases’ names and labels. Adding a new database to the WebAPI needs an entry in the enum object.

Step 2:
Add HDB(“Hana”, “hana”) in DBMSType enum.

On the other hand, SqlRender is a library that can translate SQL from Microsoft SQL dialect to other dialects. Apparently, other dialects supported by this library are the databases reflected in the DBMSType object in the ArachneCommons.

Functionally, SqlRender provides simple markup for writing SQL queries, and it can render the markup to produce equivalent executable SQL in a desired dialect. The magic of translation is based on different patterns present in the replacementPatterns.csv file. Therefore, we will need to replicate necessary patterns for a new database.

Step 3:
Add patterns in the hana,<from_pattern>,<to_pattern> format.

Above, the changes are scattered in WebAPI, ArachneCommons, and SqlRender. However, to ensure they work together, we must make more changes. As ArachneCommons and SqlRender are dependencies in WebAPI, these libraries are fetched from the OHDSI public nexus repository. In this use case we will need a private or public repository to save the modified jar files.

Step 4:
Build the modified ArachneCommons and SqlRender and push them to a public or private Maven repository.

Step 5:
In the WebAPI pom.xml, make changes to the SqlRender and ArachneCommons sources.

Related articles

Building an analytics platform using the OMOP common data model

Leveraging the OHDSI OMOP common data model for research using observational health data