OHDSI Home | Forums | Wiki | Github

Snowflake drivers not loading

Implementers
, ,
#1

We are installing OHDSI-WebAPI in aws EKS cluster but the latest WebAPI image is not loading the snowflake driver. Below snowflake profile is already added in settings.xml as per instructions. I even logged into the PODs and checked under /var/lib/ohdsi/webapi/WEB-INF/lib/ and not snowflake drivers are present. Any suggestion?

When checking the logs of PODs after adding the snowflake connection in WebAPI DB “source” and “source_daemon” tables, we can see below errors:

Error: 2023-12-29 18:47:29.401 INFO http-nio-8080-exec-9 com.odysseusinc.logging.LoggingService - [] - Could not get JDBC Connection; nested exception is java.sql.SQLException: No suitable driver found for jdbc:snowflake://?warehouse=&db=&user=&password=

webapi-snowflake true 3.13.22 net.snowflake snowflake-jdbc ${snowflake.driver.version}
#2

If you’re building WebAPI from scratch, you’d need to include the webapi-snowflake profile in the build steps.

If you’re using Broadsea, we’ve added a pathway to handle bringing your own JDBC driver: GitHub - OHDSI/Broadsea at develop

#3

Thank you for your response. I did included the webapi-snowflake profile in the settings.xml while building the image but it is still not loading. Any other suggestion?

image
image813×404 32.4 KB

#4

Just a suggestion, but even if you add the profile in the settings.xml, I think you need to activate it at the command line by adding webapi-snowflake after the `webapi-postgres’, ie:

mvn clean package -DskipUnitTests -DskipITtests -s WebAPIConfig/settings.xml -P
webapi-postgresql,webapi-snowflake
1 Like
#5

Thanks @Chris_Knoll . Totally forgot about that. It helped. Now the drivers are loaded but another issue comes up. Now atlas application is unable to connect to the WebAPI and we can see below error in the WebAPI POD logs. PFB the JDBC connection URL we are giving for our external CDM Snowflake source. Any idea what could be causing this.

JDBC Connection for Snowflake CDM:

jdbc:snowflake://emeaprod01.eu-west-1.privatelink.snowflakecomputing.com?warehouse=&db=RWD_PROD&user=&password=password

Source Table:

image
image1246×49 19.5 KB

Source_daimon table:

source_daimon_id source_id daimon_type table_qualifier priority
4 4 0 IQVIA_OMOP_EHR_FRANCE 1

Error in WebAPI POD:
2024-01-03 15:43:09.400 ERROR http-nio-8080-exec-5 net.snowflake.client.log.JDK14Logger - [] - Stop retrying since elapsed time due to network issues has reached timeout. Elapsed: 74,231(ms), timeout: 60,000(ms)

#6

Can you use something like SQL Workbench/J to make a JDBC connection to that database from the same host/server you are running WebAPI on? A timeout (tho, a 60ms timeout feels a little aggressive for timeouts) implies a network connectivity issue, so you should try to do it in another tool before assuming WebAPI has the problem.

#7

One issue I see is the source dialect is sql server, should be snowflake.

#8

Also for Snowflake connection “warehouse” value is required. You cannot leave it blank.

1 Like
#9

Right, in our deployment, which uses private key auth, we have this convention:

jdbc:snowflake://<some_host_url>?db=<some_db>&warehouse=<some_warehouse>&role=<some_role>&private_key_file=<some_pk_file>&private_key_file_pwd=<some_pwd>

The private key needs to be in your WebAPI build. We have a pattern for this in Broadsea (develop):

t