vocab load fails on null values in vocabulary.vocabulary_reference about Health Plans

[turbomam]

Thanks, this is a great tool.

When running the vocab load part of your pipeline, I got

ubuntu@ip-172-31-88-67:~/mimic-omop$ psql "$OMOP" -f "omop/build-omop/postgresql/omop_vocab_load.sql"
Password for user mimicuser: 
BEGIN
TRUNCATE TABLE
TRUNCATE TABLE
TRUNCATE TABLE
TRUNCATE TABLE
TRUNCATE TABLE
TRUNCATE TABLE
TRUNCATE TABLE
TRUNCATE TABLE
TRUNCATE TABLE
COPY 6136766
COPY 356
psql:omop/build-omop/postgresql/omop_vocab_load.sql:59: ERROR:  null value in column "vocabulary_reference" violates not-null constraint
DETAIL:  Failing row contains (Plan, Health Plan - contract to administer healthcare transactions by ..., null, null, 32471).
CONTEXT:  COPY vocabulary, line 14: "Plan   Health Plan - contract to administer healthcare transactions by the payer, facilitated by the s..."
psql:omop/build-omop/postgresql/omop_vocab_load.sql:60: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:omop/build-omop/postgresql/omop_vocab_load.sql:61: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:omop/build-omop/postgresql/omop_vocab_load.sql:62: ERROR:                       ,dbname = getValueFromConfFile(connectionFile,"dbname")
                     ,host = getValueFromConfFile(connectionFile,"host")
                     ,port = getValueFromConfFile(connectionFile,"port")
                     ,user = getValueFromConfFile(connectionFile,"user")
                     ,password = getValueFromConfFile(connectionFile,"password")current transaction is aborted, commands ignored until end of transaction block
psql:omop/build-omop/postgresql/omop_vocab_load.sql:63: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:omop/build-omop/postgresql/omop_vocab_load.sql:64: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:omop/build-omop/postgresql/omop_vocab_load.sql:65: ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK
ubuntu@ip-172-31-88-67:~/mimic-omop$ 

Which I think I traced down to

ubuntu@ip-172-31-88-67:~/mimic-omop$ grep "contract to administer healthcare transactions by the payer" /opt/mimicomop/data/vocab/*
/opt/mimicomop/data/vocab/CONCEPT.csv:32475 Health Plan - contract to administer healthcare transactions by the payer, facilitated by the sponsor   Metadata    Domain  Domain      OMOP generated  19700101    20991231    
/opt/mimicomop/data/vocab/CONCEPT.csv:32471 Health Plan - contract to administer healthcare transactions by the payer, facilitated by the sponsor   Metadata    Vocabulary  Vocabulary      OMOP generated  19700101    20991231    
/opt/mimicomop/data/vocab/DOMAIN.csv:Plan   Health Plan - contract to administer healthcare transactions by the payer, facilitated by the sponsor   32475
/opt/mimicomop/data/vocab/VOCABULARY.csv:Plan   Health Plan - contract to administer healthcare transactions by the payer, facilitated by the sponsor           32471

So I did

ALTER TABLE omop.vocabulary
    ALTER COLUMN vocabulary_reference DROP NOT NULL;

And everything seems OK now

I'm using every vocabulary that doesn't require a license. I did the cpt pull from UMLS.

[phdjsep]

Thanks for that @turbomam

Helped move me forward. Seems like the MIMIC dataset and these sets of OMOP tools have fallen out of date. I find the pgtap tests to fail as well since they're expecting certain counts, but not getting them.

[alistairewj]

This was built using the v5 model (early 2018) so it may be related to more recent changes in the vocab. Unfortunately work on this project was paused for a time due to competing priorities, but we plan to revisit it over the coming year.

[aparrot89]

We converted MIMIC in OMOP v5.3. The new OMOP version is OMOP v6.0 but all the OHDSI tools are still not working in OMOP 6.0. So we will wait a little.

[turbomam]

Thanks all for the feedback.

I have also raised some issues for the Synthea ETL, like https://github.com/OHDSI/ETL-Synthea/issues/19. Are any of you in a position to reply to any of them?

I'm doing these conversions for a paper that will be submitted soon. I'm using 5.x schemas.

Thanks for the great work.

[fdefalco]

It will likely be the case that the OHDSI tools will not support the CDM 6.0 until early 2020. That being said we have plans to release an update to ETL-Synthea that converts to 6.0 much sooner. It will then be used by OHDSI development teams for regression testing of tools as CDM 6.0 is adopted.