I am Using Broadsea . here I have the external Database PostgreSql. where I have made all the CDM and WebAPI Configuration correctly. But in Atlas the Data source is not reflecting.
I am having the Webapi schema in the Broadsea . my external database consists of the CDM Schema,Vocabulary Schema, Results Schema.
While setting the Configuration i am not facing any error. But the Data source isn’t available in the Atlas.
I would Appreciate your Support for my issue .
Adding CDMs to Atlas isn’t handled in Broadsea, so this may be why you’re not seeing them there. You’ll need to add CDMs using either the Atlas Configuration GUI (if you’ve enabled security), or by following the steps here:
Hi @Ajit_Londhe @anthonysena
I am writing to seek your assistance with the reflection of an external database in the Atlas. Our current setup involves having the CDM database and R Studio on separate servers. We are utilizing Broadsea for this purpose which is in another server. Despite following each and every step outlined in the updated CDM configuration document, we have been unable to achieve the desired outcome.
Furthermore, we have thoroughly checked the web API configuration and verified that our database and configurations are visible there. We are at a loss as to why the reflection process is not yielding any significant changes or progress. In an effort to provide you with a clear understanding of our current setup, I have attached a snapshot of the Atlas and the web API configuration for your reference.
Given the circumstances, we kindly request your expertise in identifying any potential issues or misconfigurations that may be hindering the reflection process. We have exhausted our available resources and are hopeful that your insights and guidance will help us overcome this challenge.
Can you please confirm which version of Atlas and WebAPI you have installed?
If it is version 2.13.0, then it’s possible that a ‘no data’ result was cached into the WebAPI database in the webapi.achilles_cache table. I believe there is a hotfix for this behavior, but hasn’t been released yet. In any case, can you do the following:
- Truncate webapi.achilles_cache.
- Refresh the report page
If that fails, you should check your logs, it may be possible that there’s a failure when fetching report data from your CDM results and that’s resulting in a cache failure. The other thing to investigate is that there is a data condition in your CDM that is causing zero people to be identified (ie: no one has a birth year).
hi @Chris_Knoll @Ajit_Londhe @anthonysena
1.I have tried truncating the webapi.achilles_cache. but there is no reflection the Atlas 2. I have also gone through the logs . Its showing me error which i have attached below.
error i have received when i got to check the logs
ohdsi-webapi | org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is org.postgresql.util.PSQLException: FATAL: password authentication failed for user “postgres”
name=warming cache: synthea1kpostgres]] completed with the following parameters: [{jobName=warming cache: synthea1kpostgres, source_key=synthea1kpostgres, source_id=-1, time=1684980000061, jobAuthor=anonymous}] and the following status: [FAILED]
ohdsi-webapi | 2023-05-25 02:00:00.292 INFO taskExecutor-5 org.springframework.batch.core.launch.support.SimpleJobLauncher$1 - [] - Job: [SimpleJob: [name=warming cache: synthea1k postgres]] completed with the following parameters: [{jobName=warming cache: synthea1k postgres, source_key=synthea1k postgres, source_id=-1, time=1684980000037, jobAuthor=anonymous}] and the following status: [FAILED]
#################
Here the catch is my external postgres database username is ohdsi but its showing error as user postgres .
Firstly let me explain you briefly what schema i have in which server . Basically in our organization we have kept a separate server for the atlas/webapi and we have the external postgres database in another server .
In the atlas/ Webapi server we have setup this Broadsea through docker as you know . which has default Postgres database in this we have the Webapi schema along side of all the default schemas which comes with Broadsea of the Eunomia Database .
Now i want to connect my external Postgres Database which is in another server which has the following schemas in it
- cdm data and vocabulary in one schema
- cdm results schema
- temp schema
I have tried the cdm configuration steps exacctly same as instructed and when I run this url http://localhost:8080/WebAPI/source/sources . I am getting the external database details as well but in the atlas its not only reflecting the external database.
Secondly I have also tried to changing the compose.yml file with the external database details . but its of no use (maybe i have done it in the wrong way )
I have few doubts
- should i just add the external database details in the source and source_daimon table which is present in the webapi schema ?
- Do I need to the change the compose.yml file can you please specify at which part should i change it (i have changed it partially but i am not sure whether i have done it in the right way ) ?
environment:
compose .yml
DATASOURCE_DRIVERCLASSNAME: org.postgresql.Driver
DATASOURCE_URL: jdbc:postgresql://10.2.1.40:5432/OHDSI
DATASOURCE_USERNAME: ohdsi
DATASOURCE_PASSWORD: mypass
DATASOURCE_OHDSI_SCHEMA: webapi
SPRING_JPA_PROPERTIES_HIBERNATE_DIALECT: org.hibernate.dialect.PostgreSQLDialect
SPRING_JPA_PROPERTIES_HIBERNATE_DEFAULT_SCHEMA:
SPRING_BATCH_REPOSITORY_TABLEPREFIX: webapi.BATCH_
FLYWAY_DATASOURCE_DRIVERCLASSNAME: org.postgresql.Driver
FLYWAY_DATASOURCE_URL: jdbc:postgresql://10.2.1.40:5432/OHDSI
FLYWAY_DATASOURCE_USERNAME: ohdsi
FLYWAY_DATASOURCE_PASSWORD: mypass
FLYWAY_LOCATIONS: classpath:db/migration/postgresql
FLYWAY_PLACEHOLDERS_OHDSISCHEMA: webapi
FLYWAY_SCHEMAS: synthea1kpostgres
FLYWAY_BASELINEONMIGRATE: "true"
FLYWAY_TABLE: schema_history
flyway_baselineVersionAsString: "2.2.5.20180212152023" # this env var is case sensitive
FLYWAY_BASELINEDESCRIPTION: Base Migration
SECURITY_CORS_ENABLED: "true"
SECURITY_ORIGIN: "http://${BROADSEA_HOST}"
depends_on:
- broadsea-atlasdb
Please let me know the right way of approaching how to resolve this issue .
So, there could be one element if the UI isn’t showing the sources, and that is that we cache datasource information in a browser ‘local storage’, so you may have to clear it.
If you have security enabled, there’s a ‘Clear Configuration Cache’ under the ‘Configuration’ nav. Click it and it should clear everything cached in local. if you do not have security enabled. I can provide additional details.
However, whatever is in your browser cache isn’t seen by the server, and your server error of password authentication failed for user “postgres” is something in your server configuration.
Can you post the results of select * from webapi.source on your WebAPI instance? (you can exclude columns like ‘password’ but we want to see the URLs and the usernames)
There should be no problem adding additional records to webapi.source in order to point to external DBs. It sounds like you have set it up correctly with the schemas that you described, and it is actually the recommended case where your webapi database is separate from your CDM databases.
hi @Chris_Knoll
here i have attached the webapi.source table
I do not have security enabled for the atlas . Please provide me the additional details how to clear the cache .
Can you verify: achilles_analysis, achilles_results, and achilles_results_dist are in your results schema?
So you do have 2 Soruces with similar names, the one in row 2 does specify a user as ‘postgres’, so that would explain why you get an error that the user postgres is not authorized.
Can you remove the problem souce?
To clear the memory cache in WebAPI, you open a browser and navigate to your WebAPI:
{host}/WebAPI/source/refresh
You can also just restart the WebAPI service.
In the browser, open up Atlas homepage. Then enter the dev console (cttrl-shift-i) and in the console input this command: localStorage.clear(). That will clear out any browser-side configuration cache and if you reload your browser, you should see the sources refreshed.
It would be usefull if this function was exposed in the AtlasUI in non-security enabled context, and I’ll see if we can prioritize this.
Hi @Ajit_Londhe @Chris_Knoll
I would like to take this moment and appreciate both of you for taking your time and trying to helps us throughout process with the issue.
Now I can view my CDM data in atlas .
As I had multiple entries of the same CDM Data where the issue has come . now I have removed those and tried I can see my CDM data source in atlas .
I