search

OHDSI / Achilles

Automated Characterization of Health Information at Large-scale Longitudinal Evidence Systems (ACHILLES) - descriptive statistics about a OMOP CDM database
https://ohdsi.github.io/Achilles/
130 stars 121 forks source link

Achilles fails merging scratch tables #518

Closed akiehl2000 closed 3 years ago

akiehl2000 commented 3 years ago

@alondhe Thanks for your response over on the OHDSI forums. Here is everything I can provide that may help diagnose the issue.

Expected behavior

We are attempting to run Achilles at the end of our ETL process with the following function call. By setting createTable to TRUE, Achilles should generate tables such as achilles_results in our results schema.

Screen Shot 2021-01-18 at 11 24 57 AM

Actual behavior

Rather than executing properly, Achilles encounters an error while 'Merging scratch Achilles tables,' specifically, s_tmpach_4. The relevant lines from achilles_log.txt are shown below and the specific error report with the errant SQL call is attached as well.

Screen Shot 2021-01-18 at 11 26 08 AM

errorReportSql.txt

Steps to reproduce behavior

We are working with a near-vanilla version of Postgres as well as a near-vanilla OMOP CDM. The few edits we did make to the CDM consisted of adding fields useful for veterinary purposes. No fields were removed. I believe that we created the Achilles tables manually before first running Achilles and currently, the tables appear in our database like this. The DDL script for achilles_results is also included below.

Screen Shot 2021-01-18 at 11 41 54 AM Screen Shot 2021-01-18 at 11 43 26 AM

Should we attempt to delete all of the Achilles tables and rerun the ETL from scratch? Is it possible we're working with outdated DDL scripts for those tables? Issue #430 diagnoses a problem with 'how the scripts were adjusted to compensate for DB collations.' What exactly does this mean and how can we make the appropriate changes to our DB/ETL process? If there's more information I can provide or if you have any more questions, please don't hesitate to reach out. Thank you so much for your time!

alondhe commented 3 years ago

Hi @akiehl2000 can you post the entire log_achilles.txt file? The lines I'm interested in are for Main Analysis 4. These lines are about the merge operation, which will certainly fail since analysis 4 didn't create the staging table it's expecting.

akiehl2000 commented 3 years ago

@alondhe Here it is! log_achilles.txt

akiehl2000 commented 3 years ago

@alondhe Also, I made a mistake in my previous post. I was under the impression we had added several fields without deleting any but it appears that we removed three fields related to race: race_concept_id, race_source_concept_id, and race_source_value. Should we add those fields and try running Achilles again?

alondhe commented 3 years ago

So there's a few errors to resolve:

  1. Adding race_concept_id back to person
  2. Creating a death table that conforms to the CDM spec
  3. Creating a drug_era table that conforms to the CDM spec

After addressing these 3 items, re-run Achilles.

akiehl2000 commented 3 years ago

@alondhe Thank you- I'll make those changes and report back.

akiehl2000 commented 3 years ago

@alondhe Everything works well now. I'm not sure why drug_era and death were messed up seeing as we downloaded the official CDM DDL scripts. Anyways, thank you for your time and help- you should be able to close this issue now.