ERROR: relation "ohdsi.achilles_analysis" does not exist

blasisd · March 11, 2020, 10:34am

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

Ajit_Londhe · March 11, 2020, 1:52pm

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.

blasisd · March 13, 2020, 9:12am

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

Ajit_Londhe · March 13, 2020, 4:06pm

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/

blasisd · March 17, 2020, 2:47pm

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 · March 17, 2020, 6:42pm

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

blasisd · March 17, 2020, 7:10pm

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!

blasisd · March 18, 2020, 12:26pm

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 · March 18, 2020, 12:43pm

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.

blasisd · March 18, 2020, 1:40pm

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 · March 20, 2020, 3:17am

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

blasisd · March 20, 2020, 7:37am

Yes. That’s right!

Ajit_Londhe · March 20, 2020, 1:54pm

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.

blasisd · March 20, 2020, 2:16pm

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

blasisd · March 24, 2020, 9:26am

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!

CRoeder · July 16, 2020, 7:56pm

I just ran into the one about the missing column. The new versions of synthea have added the organization column to the encounters table.
The issue is described here.

To fix, modify the tables in the ETL-Synthea file inst/sql/sql_server/create_synthea_tables.sql, by adding the column for organization as below.

–HINT DISTRIBUTE_ON_RANDOM
create table @synthea_schema.encounters (
id varchar(1000),
start date,
stop date,
patient varchar(1000),
organization varchar(1000),