OHDSI Home | Forums | Wiki | Github

Atlas local instance cannot retrieve data from backend CDM tables

Developers
,
#1

Hi @Chris_Knoll and all,

So here in our team, we have a production version of Atlas running, pointing to the backend CDM tables (omop_PHI and omop_noPHI).

Now, I am trying to create a local webAPI instance + a local Atlas instance, where the local webAPI talks to the same production CDM tables (omop_PHI, omop_noPHI).

What I have now is:

  1. running local webAPI instance
  2. running local Atlas instance
  3. and the local webAPI instance is configured to point to the production CDM tables.

Problem I have:

the local Atlas instance, when started, cannot see any data coming back:

image
image3184×1804 495 KB

Now, if I go to Configuration in my local Atlas instance, I see this:

image
image3142×1256 446 KB

but if I go to Configuration in the production Atlas instance, I see this:

image
image3398×1256 391 KB

So looks like in my local instance, the vocabulary is not loaded correctly, and also note that in the working instance, the omop_CDM names are clickable, but in my local instance they are not. I checked the CDM configuration in the database tables (source, source_daimon), they are the same (except for the priority column).

What else should I check? any hint on this will be really helpful!

Note that I did not create Achilles local instance, so it is only local WebAPI, local Atlas, with local WebAPI talking to the backend CDM tables… not sure if this could be a problem?

Thanks!
Liyang

#2

just a little bit more information:

I am following this document to configure the backend CDM for WebAPI to consume:

now, the following part is taken from the above document:

Once the Results schema is established, you will need to generate the SQL script for your CDM dialect to establish the tables that WebAPI will require to run. To do this, WebAPI provides a URL that takes parameters via the query string to generate the proper database script for your setup:

http://<server:port>/WebAPI/ddl/results?dialect=<your_cdm_database_dialect>&schema=<your_results_schema>&vocabSchema=<your_vocab_schema>&tempSchema=<your_temp_schema>&initConceptHierarchy=true

… some details omitted here…

Once you have created the URL for your environment, open a browser and navigate to that URL. The resulting SQL will be displayed in the browser and your database administrator can use this script to establish the results schema.

So the last part I did not do. So I will need to ask our DBA to run the generated script? could this be the root cause?

#3

I’d double check (3): if your source and source_daimon table has the same data in it (which includes a JDBC url and user/password) as your production environment, you should be seeing the exact same on the configuration screen.

Check your local WEBAPI logs for any errors. It could be that an error is being hidden.

The first thing (which you did) is to see if the simple stuff works (like you see a vocabulary version in configuration) forget the other elements for now until you clear that bridge.

It is not recommended that you point 2 WebAPI instances to the same results schema, so your prod config can be something like:

source_daimon table qualifier
CDM omop_cdm
Vocab omop_cdm
Results results

But then your local should be configured as:

source_daimon table qualifier
CDM omop_cdm
Vocab omop_cdm
Results results_local

The reason: each instance of webapi is going to maintain their own IDs for cohort definitions, characterization projects, incidence rate analyses, etc. You don’t want 2 different WebAPIs to overwrite another’s results, so you should have separate results schemas for each of the WebAPI instances. This is what we have in our own envrionment, we have results_dev1 … results_dev2 on the same CDM so that we can refrence the same patient level data, but separate the results from the different WebAPIs. For Achilles, this means you can just copy the achilles tables from one results schema to another (or create a view) without havign to run the achilles process multiple times.

Check your JDBC urls in your local databases, and ensure that they are properly configured to point to the right database.

#4

@Chris_Knoll thank you! let me check the JDBC first before anything else.

#5

@Chris_Knoll yes, that was it - the mis-spelling in the JDBC string!!

Now I see data back, although it is not entirely same as the production site:

image
image3184×1946 722 KB

but other things are empty, and production site is not empty:

image
image3184×1946 551 KB

so still something not totally right…

But thank you so much @Chris_Knoll!

#6

@Chris_Knoll, so could not setting up security (therefore no sign-in link show) be the reason for not being able to see contents other than Data Sources?

#7

I am still struggling with this issue - in my local Atlas instance, most of the menu items on the left-hand side of Atlas will show empty content. One additional information is that when I hit this URL:

http://localhost:8089/WebAPI/source/sources

the correct results show up, but I see the following warnings in the log:

WARNING: The following warnings have been detected: WARNING: The (sub)resource method createAnalysis in org.ohdsi.webapi.service.IRAnalysisResource contains empty path annotation.

WARNING: The (sub)resource method getIRAnalysisList in org.ohdsi.webapi.service.IRAnalysisResource contains empty path annotation.

WARNING: The (sub)resource method list in org.ohdsi.webapi.feanalysis.FeAnalysisController contains empty path annotation.

WARNING: The (sub)resource method createAnalysis in org.ohdsi.webapi.feanalysis.FeAnalysisController contains empty path annotation.

WARNING: The (sub)resource method getConceptSets in org.ohdsi.webapi.service.ConceptSetService contains empty path annotation.

WARNING: The (sub)resource method createConceptSet in org.ohdsi.webapi.service.ConceptSetService contains empty path annotation.

WARNING: The (sub)resource method create in org.ohdsi.webapi.pathway.PathwayController contains empty path annotation.

WARNING: The (sub)resource method list in org.ohdsi.webapi.pathway.PathwayController contains empty path annotation.

WARNING: A HTTP GET method, public org.ohdsi.webapi.job.JobExecutionResource org.ohdsi.webapi.service.CDMResultsService.warmCache(java.lang.String,org.ohdsi.webapi.service.CDMResultsService), should not consume any entity.

WARNING: The (sub)resource method list in org.ohdsi.webapi.job.NotificationController contains empty path annotation.

WARNING: The (sub)resource method getInfo in org.ohdsi.webapi.info.InfoService contains empty path annotation.

WARNING: The (sub)resource method create in org.ohdsi.webapi.cohortcharacterization.CcController contains empty path annotation.

WARNING: The (sub)resource method list in org.ohdsi.webapi.cohortcharacterization.CcController contains empty path annotation.

WARNING: The (sub)resource method getCohortDefinitionList in org.ohdsi.webapi.service.CohortDefinitionService contains empty path annotation.

WARNING: The (sub)resource method createCohortDefinition in org.ohdsi.webapi.service.CohortDefinitionService contains empty path annotation.

WARNING: The (sub)resource method getPermissions in org.ohdsi.webapi.security.PermissionController contains empty path annotation.

WARNING: The (sub)resource method listJobs in org.ohdsi.webapi.user.importer.UserImportJobController contains empty path annotation.

WARNING: The (sub)resource method createJob in org.ohdsi.webapi.user.importer.UserImportJobController contains empty path annotation.

WARNING: The (sub)resource method createEstimation in org.ohdsi.webapi.estimation.EstimationController contains empty path annotation.

WARNING: The (sub)resource method getAnalysisList in org.ohdsi.webapi.estimation.EstimationController contains empty path annotation.

WARNING: The (sub)resource method getFeasibilityStudyList in org.ohdsi.webapi.service.FeasibilityService contains empty path annotation.

WARNING: The (sub)resource method createStudy in org.ohdsi.webapi.service.FeasibilityService contains empty path annotation.

WARNING: The (sub)resource method createSource in org.ohdsi.webapi.source.SourceController contains empty path annotation.

WARNING: The (sub)resource method getAnalysisList in org.ohdsi.webapi.prediction.PredictionController contains empty path annotation.

WARNING: The (sub)resource method createAnalysis in org.ohdsi.webapi.prediction.PredictionController contains empty path annotation.

not sure if this could be related? This empty lists could be the reason for the empty contents when I click most of the menu items on the left hand side? And I cannot compare to the production site, since the URL link in production site does not return anything.

#8

again, some other error messages in WebAPI log:

2021-02-23 19:56:25.304 INFO taskExecutor-1 org.springframework.beans.factory.xml.XmlBeanDefinitionReader -  - Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
2021-02-23 19:56:25.337 ERROR taskExecutor-1 org.ohdsi.webapi.cdmresults.CDMResultsCacheTasklet -  - Failed to warm cache for omop_cdm_noPHI. Exception: PreparedStatementCallback; bad SQL grammar [SELECT
  concept_id,
  record_count,
  descendant_record_count
FROM results.achilles_result_concept_count]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "results.achilles_result_concept_count" does not exist
  Position: 69
2021-02-23 19:56:25.337 WARN taskExecutor-1 org.ohdsi.webapi.cdmresults.CDMResultsCacheTasklet -  - Failed to warm cache for omop_cdm_noPHI. Trying to execute caching from scratch. Exception: PreparedStatementCallback; bad SQL grammar [SELECT
  concept_id,
  record_count,
  descendant_record_count
FROM results.achilles_result_concept_count]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "results.achilles_result_concept_count" does not exist
  Position: 69
t