OHDSI Home | Forums | Wiki | Github

ERROR: relation "ohdsi.achilles_analysis" does not exist


(Vlasios Dimitriadis) #1

Hello everybody,

I am new to the OHDSI platform and I am trying to setup a local OHDSI stack environment using BroadSea and postgresql as database. I have followed the guide provided in the BroadSea documentation page(https://github.com/OHDSI/Broadsea ) and I get no error messages.

When I make specific GET requests, I get normal replies and the expected results (according to the documentation and forum discussions) e.g. :

http://<ipaddress>:<port>/WebAPI/info

Result: {“version”:“2.7.2”}

http://<ipaddress>:<port>/WebAPI/vocabulary/OHDSI-CDMV5-synthea/info

Result: {“version”:“v5.0 10-JAN-19”,“dialect”:“postgresql”}

http://<ipaddress>:<port>/WebAPI/source/sources

Result:
[{“sourceId”:1,“sourceName”:“OHDSI CDM synthea Database”,“sourceDialect”:“postgresql”,“sourceKey”:“OHDSI-CDMV5-synthea”,“daimons”:[{“sourceDaimonId”:1,“daimonType”:“CDM”,“tableQualifier”:“cdm”,“priority”:2},{“sourceDaimonId”:2,“daimonType”:“Vocabulary”,“tableQualifier”:“cdm”,“priority”:2},{“sourceDaimonId”:3,“daimonType”:“Results”,“tableQualifier”:“ohdsi”,“priority”:2},{“sourceDaimonId”:4,“daimonType”:“CEM”,“tableQualifier”:“ohdsi”,“priority”:2},{“sourceDaimonId”:5,“daimonType”:“Temp”,“tableQualifier”:“temp”,“priority”:2}]}]

I have setup three schemas in my database. One for cdm and vocabulary (cdm), one for results and evidence (ohdsi) and one for temp (temp).

From pgadmin and dbeaver I check the data and seem to have been loaded successfully from synthea csv files.

Some POST requests and the corresponding actions seem to have been executed successfully too e.g. :

http://<ipaddress>:<port>/WebAPI/ddl/results?dialect=postgresql&schema=ohdsi&vocabSchema=cdm&tempSchema=temp&initConceptHierarchy=true

Unfortunately when I hit:

http://<ipaddress>:<port>/atlas/#/datasources/OHDSI-CDMV5-synthea/dashboard

on browser, I get an Internal Server Error for:

http://<ipaddress>:<port>/WebAPI/cdmresults/OHDSI-CDMV5-synthea/dashboard

As far as I can understand, the specific error is:

ERROR: relation “ohdsi.achilles_analysis” does not exist

Does anybody have any clue, what the problem could be?

Thanks in advance


Local OHDSI software stack setup solutions
(Ajit Londhe) #2

You’ll need to run the Achilles package on your cdm data source. Once that runs successfully, restart your webapi instance.

The Achilles tables are used by the atlas data sources module, providing summary statistics about your data.


(Vlasios Dimitriadis) #3

Thank you very much for your reply @Ajit_Londhe .
Is there some kind of API request that I can call to do that or should I run something in R.
Please excuse my ignorance since I am pretty new to OHDSI technologies :slight_smile:


(Ajit Londhe) #4

In skimming the Broadsea page, it seems like you just run the Achilles R package yourself in the environment, it’s not something the container has pre-executed.

To run Achilles, refer to this guide: https://ohdsi.github.io/Achilles/


(Vlasios Dimitriadis) #5

Thank you once again. I have managed to run Achilles finally. However I must have done something wrong, during the data (synthea) import procedure since I see empty dashboard frames (without any error).

In the database there are records in some tables (e.g. allergies, procedures etc.), but other tables (such as patients or persons) are totally empty.

I have used ETL-synthea project. I have tried both the R (script) as well as the bulk load method. I have failed both ways.

I would really appreciate it if anybody had some useful advice on that!


(Ajit Londhe) #6

How were the Synthea tables built? Did you run this process, or was it done for you in BroadSea?


(Vlasios Dimitriadis) #7

During my first effort I run:

ETLSyntheaBuilder::DropVocabTables(cd,“cdm”)
ETLSyntheaBuilder::DropEventTables(cd,“cdm”)
ETLSyntheaBuilder::DropSyntheaTables(cd,“cdm”)
ETLSyntheaBuilder::DropMapAndRollupTables(cd,“cdm”)
ETLSyntheaBuilder::CreateVocabTables(cd,“cdm”)
ETLSyntheaBuilder::CreateEventTables(cd,“cdm”)
ETLSyntheaBuilder::CreateSyntheaTables(cd,“cdm”)
ETLSyntheaBuilder::LoadSyntheaTables(cd,“cdm”,"/path_to_synthea_csvs")
ETLSyntheaBuilder::LoadVocabFromCsv(cd,“cdm”,"/path_to_vocab_downloaded_from_Athena")
ETLSyntheaBuilder::CreateVocabMapTables(cd,“cdm”)
ETLSyntheaBuilder::CreateVisitRollupTables(cd,“cdm”,“cdm”)
ETLSyntheaBuilder::LoadEventTables(cd,“cdm”,“cdm”)

Unfortunately, that failed, so I dropped cdm schema and run bulk_load script with its args

There was no error, so I thought that everything went fine! Well, it seems that it did not! :frowning:


(Vlasios Dimitriadis) #8

I was looking in the wrong error_log file. Sorry!
It seems that the error responsible for the empty tables is:

ERROR: extra data after last expected column

CSV files contain more columns that the ones in the built tables in cdm schema in the database.

Is there something that I could do about that?
Should I change the csv files by hand for example and delete the extra columns?

Another thing that I can see (though it is a NOTICE and not an error) is:

psql:ETL/SQL/create_source_to_source_vocab_map.sql:4: NOTICE: table “source_to_source_vocab_map” does not exist, skipping

psql:ETL/SQL/create_source_to_standard_vocab_map.sql:5: NOTICE: table “source_to_standard_vocab_map” does not exist, skipping

Should I ignore that? Is it OK?


(Ajit Londhe) #9

Let’s start with the native load of Synthea first, not the ETL to transform it to the CDM. What steps specifically are you running?

Because it should run smoothly before you try anything else.


(Vlasios Dimitriadis) #10

I have followed the steps mentioned in the readme file. More specifically:

git clone https://github.com/synthetichealth/synthea.git
cd synthea
./gradlew build check test

In file ./src/main/resources/synthea.properties I have changed only the following option to true:

exporter.csv.export = true

And then run:
./run_synthea -s 6667 -p 2000

csv files were generated in the output/csv subdirectory.

But I have also tried to import data from:

https://storage.googleapis.com/synthea-public/synthea_sample_data_csv_jan2020.zip

Which I found at:

https://synthea.mitre.org/downloads

The same thing happened in both cases.


(Ajit Londhe) #11

So after running each of these methods, your native tables have 0 records in the patients table?


(Vlasios Dimitriadis) #12

Yes. That’s right!


(Ajit Londhe) #13

Okay good. So it’s the native table build that is failing somewhere. As an example, here’s my tables and row counts:

I will send you a direct message with a link to the native CSVs I built. Please try using them.


(Vlasios Dimitriadis) #14

I will @Ajit_Londhe and I’ll let you know. Thank you very much!


(Vlasios Dimitriadis) #15

Unfortunately, it seems that exactly the same thing happens with the data you have sent me. I have just noticed something else though that I had missed the other day.

Different kind of errors occur using bulk load vs the ETLSyntheaBuilder R functions. More specifically using those functions i.e. :

ETLSyntheaBuilder::DropVocabTables
ETLSyntheaBuilder::DropEventTables
ETLSyntheaBuilder::DropSyntheaTables
ETLSyntheaBuilder::DropMapAndRollupTables
ETLSyntheaBuilder::CreateVocabTables
ETLSyntheaBuilder::CreateEventTables
ETLSyntheaBuilder::CreateSyntheaTables
ETLSyntheaBuilder::LoadSyntheaTables
ETLSyntheaBuilder::LoadVocabFromCsv
ETLSyntheaBuilder::CreateVocabMapTables
ETLSyntheaBuilder::CreateVisitRollupTables
ETLSyntheaBuilder::LoadEventTables

imports data to the tables that end up empty with bulk_load script (e.g. patients) but other errors occur most of which (all of them in fact) appear to have the following “cause”:

Caused by: org.postgresql.util.PSQLException: ERROR: column “organization” of relation “encounters” does not exist

And when I try to run Achilles on the (bad ?) final schema and tables created (after the ETL script finishes despite the errors), the following error appears (as expected) after a while:

ERROR: relation ohdsi.achilles_results does not exist

Could it be that there is something going on with incompatible versions I might be using. Even though I am using vocabulary v5.x downloaded from Athena and for the ETL scripts versions I see v5.3.1 in the SQL filenames in the subdirectories. I am not sure whether the data csv files (produced or downloaded) are following a different version (since errors occuring during the import process with bulk_load complain about extra columns that do not exist in some tables???).

Do you think that I should maybe try Common Data Model version 6 or cutting out the offending columns (that raise error during bulk_load execution) in the data csv files?
Or is there something else you think I could try?

Thank you once again!


Problem with cdm schema and data import
t