Errors during LoadVocabFromCsv and missing data in cdm_synthea10
ehinderer opened this issue · 1 comments
I believe my issue stems from the same question posed in #89 as I am unsure what is required in the vocabFileLoc parameter listed in the example.
I've posted full details on this issue on the OHDSI user forums: https://forums.ohdsi.org/t/etl-synthea-produces-empty-postgres-tables/16792
Basically, I downloaded all available vocabularies from Athena (for which I had access) and running the CPT4 script with my UMLS API key. I used this directory as the path for vocabFileLoc. All other steps in the example appeared to run fine, but this step (ETLSyntheaBuilder::LoadVocabFromCsv...) produced errors similar to the following:
ERROR: current transaction is aborted, commands ignored until end of transaction block
java.sql.BatchUpdateException: Batch entry 0 INSERT INTO cdm_synthea10.CONCEPT (concept_id,concept_name,domain_id,vocabulary_id,concept_class_id,standard_concept,concept_code,valid_start_date,valid_end_date,invalid_reason) VALUES(45266969,'phendimetrazine tartrate 35mg/1 ORAL TABLET','Drug','NDC','9-digit NDC',NULL,'004637500','2000-03-29 -05','2099-12-31 -05',NULL) was aborted: ERROR: current transaction is aborted, commands ignored until end of transaction block Call getNextException to see other errors in the batch.
at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:165)
at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:871)
at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:910)
at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1663)
at org.ohdsi.databaseConnector.BatchedInsert.executeBatch(BatchedInsert.java:114)
Caused by: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:316)
at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:868)
... 3 more
Caused by: org.postgresql.util.PSQLException: ERROR: null value in column "concept_name" of relation "concept" violates not-null constraint
Detail: Failing row contains (36311145, null, Meas Value, LOINC, Answer, S, LA12334-1, 1970-01-01, 2099-12-31, null).
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2099)
at org.postgresql.core.v3.QueryExecutorImpl.flushIfDeadlockRisk(QueryExecutorImpl.java:1456)
at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1481)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:546)
at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:887)
... 3 moreERROR: current transaction is aborted, commands ignored until end of transaction block
java.sql.BatchUpdateException: Batch entry 0 INSERT INTO cdm_synthea10.CONCEPT (concept_id,concept_name,domain_id,vocabulary_id,concept_class_id,standard_concept,concept_code,valid_start_date,valid_end_date,invalid_reason) VALUES(45266969,'phendimetrazine tartrate 35mg/1 ORAL TABLET','Drug','NDC','9-digit NDC',NULL,'004637500','2000-03-29 -05','2099-12-31 -05',NULL) was aborted: ERROR: current transaction is aborted, commands ignored until end of transaction block Call getNextException to see other errors in the batch.
at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:165)
at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:871)
at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:910)
at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1663)
at org.ohdsi.databaseConnector.BatchedInsert.executeBatch(BatchedInsert.java:114)
Caused by: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:316)
at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:868)
... 3 more
Caused by: org.postgresql.util.PSQLException: ERROR: null value in column "concept_name" of relation "concept" violates not-null constraint
Detail: Failing row contains (36311145, null, Meas Value, LOINC, Answer, S, LA12334-1, 1970-01-01, 2099-12-31, null).
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2099)
at org.postgresql.core.v3.QueryExecutorImpl.flushIfDeadlockRisk(QueryExecutorImpl.java:1456)
at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1481)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:546)
at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:887)
... 3 more
I was able to run the final script to load the event tables, but noticed that several of the tables, including condition_occurence, were empty. The conditions table in my Synhtea output csv is not empty, and I'm wondering if I'm doing something wrong or if I'm misunderstanding what is intended to be output.
@ehinderer I believe your issue is actually related to this one here: #125. I had the same problem and it was fixed by removing the conversion of string "NA" to NULL in the LoadVocabFromCsv script for the concept table. Then all of the concepts get loaded and downstream event table population works correctly.