OHDSI Home | Forums | Wiki | Github

Brodsea 3.0 - Quick Start : How to add my own DB in Atlas (omop)

Implementers
,
#1

Hello OHDSI Community,

I am a new member of this community and currently working on installing ATLAS and related tools in my secure environment, which has restricted internet access. I opted for the Broadsea docker container with the Quick Start installation (default setting). However, I am facing challenges in changing the original AtlasDB (EUMONIA) to my own PostgreSQL database for the Atlas tool.

I apologize for the basic nature of this question, but I know that many members have encountered similar issues. Any guidance or assistance would be greatly appreciated.

Thank you,
Ahmed

#2

Hi @Ahmed ,

I’d highly recommend shifting to using the develop branch version of Broadsea, which has our upcoming 3.1 release of Broadsea.

And then, you’d need to modify your env file Section 3, particularly these env variables, to match your own PG database:

WEBAPI_DATASOURCE_URL
WEBAPI_DATASOURCE_USERNAME
WEBAPI_DATASOURCE_PASSWORD_FILE
WEBAPI_DATASOURCE_OHDSI_SCHEMA

And, since you’re not using a pre-filled WebAPI schema, change this to false:
FLYWAY_BASELINE_ON_MIGRATE="false"

#3

Thank you for your assistance, @Ajit_Londhe. I appreciate your help. However, I am encountering some challenges in implementing this. Could you please clarify the purpose of the “WEBAPI_DATASOURCE_OHDSI_SCHEMA”? Currently, my database consists of a single schema named “public” containing all the tables needed. Additionally, I am experiencing a FATAL error in the WebApi log, indicating an issue with the password. I’ve already updated the password in the ./secret/webapi/WEBAPI_DATASOURCE_PASSWORD file, yet the error persists. I apologize for any beginner-level nature of these questions.

#4

Set that schema to public. In terms of the password, just confirm that the path is correct, the default one is:

./secrets/webapi/WEBAPI_DATASOURCE_PASSWORD

#5

The WebApi service continues to exhibit issues. It initiates but then repeatedly starts and restarts without stabilization.

To provide you with more context and facilitate a comprehensive resolution, could you guide me on where to locate specific logs that may hold crucial information?

My postgresql is listenning on port 5433 :

image
image1037×46 5.74 KB

My .env file modification :

If not using a pre-filled WebAPI schema, change this to false

FLYWAY_BASELINE_ON_MIGRATE=“false”

WebAPI logging levels, set to other log4j levels to expose more verbose logging

WEBAPI_LOGGING_LEVEL_ROOT=“info” # for the entire application
WEBAPI_LOGGING_LEVEL_ORG_OHDSI=“info” # for any ohdsi library logging
WEBAPI_LOGGING_LEVEL_ORG_APACHE_SHIRO=“warn” # for shiro, the security authentication library

Shiro logging level, for authentication provider logging

WEBAPI_SHIRO_LOGGING_LEVEL=“warn”

Keep as-is if using Broadsea to launch the WebAPI postgres, replace if using an external postgres instance

WEBAPI_DATASOURCE_URL=“jdbc:postgresql://localhost:5433/omop”
WEBAPI_DATASOURCE_USERNAME=“postgres”
WEBAPI_DATASOURCE_PASSWORD_FILE="./secrets/webapi/WEBAPI_DATASOURCE_PASSWORD"
WEBAPI_DATASOURCE_OHDSI_SCHEMA=“public”

A part of the logs from webAPI :

2023-12-14 14:25:57 2023-12-14 13:25:57.451 WARN main org.springframework.context.support.AbstractApplicationContext - [] - Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name ‘shiroFilter’ defined in class path resource [org/ohdsi/webapi/ShiroConfiguration.class]: Unsatisfied dependency expressed through method ‘shiroFilter’ parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘flyway’ defined in class path resource [org/ohdsi/webapi/FlywayConfig.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.dbsupport.FlywaySqlException:
2023-12-14 14:25:57 Unable to obtain Jdbc connection from DataSource
2023-12-14 14:25:57 ------------------------------------------------
2023-12-14 14:25:57 SQL State : 08001
2023-12-14 14:25:57 Error Code : 0
2023-12-14 14:25:57 Message : Connection to localhost:5433 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
2023-12-14 14:25:57

And the table in my DB :

image
image979×858 111 KB

Thank you so much for your time and effort.

#6

Is your PG instance hosted on the same server, but not via Broadsea?

#7

My PG instance is on my laptop, so I guess, yeah, it’s on the same machine, but it’s not inside the Docker. You might notice that I’m a beginner in all of this.

#8

Ah okay. I think this is a pattern we’ll need to figure out how to address. The 2 patterns we know work are:

  1. PG via Broadsea (default profile)
  2. PG hosted elsewhere (e.g. AWS RDS or other server), so no conflicts in localhost.
#9

Is there a straightforward way to use PostgreSQL via Broadsea (so the default profile) but with my OMOP database? Perhaps, could I dump my database and use it within the Docker container in a certain manner ?

#10

Bringing that omop db into the Broadsea PG instance (named broadsea-atlasdb) might be the easiest workaround, aside from moving that omop db to another server.

#11

Could you please explain the process for doing this, or guide me to the resources that would help me execute it? Thank you so much for your time and assistance!

#12

I think the PG documentation covers this: PostgreSQL: Documentation: 8.0: Backup and Restore

Using the PG command line client (but you could use pgAdmin), you’d want to take a backup (dump file) of your omop db. Then you can connect to the Broadsea PG instance and restore it to its own schema (or own db, whichever keeps it separate from the webapi schema).

#13

Once I have the database dump, how can I access a file stored locally on the machine from within the Docker container (the brodsea-atlasDB PG instance)? Should I make any changes in the docker-compose file for this?
Apologies for all these questions, and thank you for your assistance.

#14

You shouldn’t need to do this within the container. The Broadsea PG is available to your host machine under localhost:

jdbc:postgresql://127.0.0.1:5432/postgres

#15

Ahmed,

You have another option. The pg on your laptop, lets call it “pg-native” can be re-assigned another port (e.g. 5440). This way you don’t have to bring all your data into the pg in the container, lets call that “pg-docker” which should be running on the default pg port of 5432.

Then when you add a data source in the webapi tables in your pg-container, all you have to do is point it at the pg-native database. This is how I run my Broadsea installs.

#16

@Ajit_Londhe, I’m sorry, I don’t understand where you’re heading with this. Thank you for your patience.

#17

@Sanjay_Udoshi Hi, my “pg-native” is already assigned on an other port (5433).

Could you explain how to add the data source to the WebAPI tables and configure it to point to the pg-native ?

Thank you very much

#18

2023-12-14_10-32
2023-12-14_10-32506×890 54.6 KB

#19

In your pg-docker, open the webapi schema and look for two tables:
source
source_daimon

In source you should find only one entry:
EUNOMIA

2023-12-14_10-36
2023-12-14_10-361200×289 21 KB

In source_daimon you will see:
2023-12-14_10-37
2023-12-14_10-37760×205 18.1 KB

#20

You will need to add your source as an entry in the source table with source_id=2, source_key=NameofYourSource, etc, etc. fill out that row as it is fill out for EUNOMIA

Replicate the entries in source_daimon for your source but source_id=2 and just follow the pattern that exists there for EUNOMIA.

Join our Discord… it might be more convenient to solve your problem there: https://discord.gg/hjw2KAY3b5

t