Configure WebAPI to use Redshift

Developers
1

Dear OHDSI Community -

Struggling to configure OHDSI with my redshift database.

Using the AWS driver I run into a known issue with FlyWay described here.

The solutions is to adjust “Flyway setting the connection property enableFetchRingBuffer=false.” How can we do this in WebAPI?

Current Steps:

  1. Rebuild the WebAPI docker image with `MAVEN_PROFILE=webapi-docker,webapi-redshift``
  2. Download the driver
  3. Set environment variables:
export DATASOURCE_DIALECT="redshift"
export DATASOURCE_URL="jdbc:redshift://$host:$port/$database"
export DATASOURCE_DRIVERCLASSNAME="com.amazon.redshift.Driver"

Note: I also tried with an old Redshift driver but ran into other connectivity issues.

Thank you for any help!

2

You can not host the WebAPI database in Redshift. Only Postgres is supported.

Please don’t confuse this with the databases referenced in the SOURCE table, which can be any database that is supported by SQLRender.

1 Like
4

Thank you so much for your response! Are you willing to elaborate on why WebAPI cannot be hosted on a Redshift Database? Evidently there’s a flyway snag, but what else? Is this on the WebAPI roadmap?

Furthermore, can you expand on WebAPI & OHDSI (Achilles) with redshift spectrum? Can SQLRender be configured to work with external schemas?

5

We used to support oracle and mssql server, but since it was a lot to maintain, we narrowed it down to just postgres for support purposes (we are a volunteer op, afterall)

Redshift is not a RDBMS, it’s a MPP, meaning it supports features that lend itself to data analytics (distribution of tables on a key for example), but does not support ‘application’ features such as sequences, primary keys, foreign keys, etc.

Achilles (one of the analytics supported by HADES, among others) are written to produce statistics from CDM formatted data. This is a distinct data model from WebAPI.