The installation of MIMIC-III Clinical Database CareVue subset

Prerequisites

• Put an X between the brackets on this line if you have done all of the following:
  • Checked the online documentation: https://mimic.mit.edu/
  • Checked that your issue isn't already addressed: https://github.com/MIT-LCP/mimic-code/issues/1331

Description

The CareVue(CV) subset of MIMIC III database was released on Physionet a few days ago: MIMIC-III Clinical Database CareVue subset. Many thanks and respect to Alistair Johnson for your hard work on the new subset database. I have completed the SQL codes for the installation of MIMIC-III Clinical Database CareVue subset(delete the SQL query for Metavision subset and convert BigQuery function to Postgresql function), and I have written a shell script that can install database and Concepts on Mac, Linux and Windows(install git to provide a Unix-like terminal for Windows). For MIMIC new beginners, they only need to type sh 01-config.sh and sh 02-install.sh in sequence on the terminal, and then the database can be automatically configured and installed with these two commands. I will submit a pull request recently. Although the process is simple enough, I plan to upload a tutorial for mac, linux, and Windows on Youtube.

However, the installation seemed to be perfect, except after the constraints codes were run. The terminal sent the following 6 errors when adding constraints:

psql:postgres_add_constraints.sql:65: ERROR:  insert or update on table "chartevents" violates foreign key constraint "chartevents_fk_hadm_id"
DETAIL:  Key (hadm_id)=(100069) is not present in table "admissions".

psql:postgres_add_constraints.sql:122: ERROR:  insert or update on table "datetimeevents" violates foreign key constraint "datetimeevents_fk_hadm_id"
DETAIL:  Key (hadm_id)=(100069) is not present in table "admissions".

psql:postgres_add_constraints.sql:217: ERROR:  insert or update on table "inputevents_cv" violates foreign key constraint "inputevents_cv_fk_hadm_id"
DETAIL:  Key (hadm_id)=(100069) is not present in table "admissions".

psql:postgres_add_constraints.sql:249: ERROR:  insert or update on table "labevents" violates foreign key constraint "labevents_fk_hadm_id"
DETAIL:  Key (hadm_id)=(100069) is not present in table "admissions".

psql:postgres_add_constraints.sql:309: ERROR:  insert or update on table "noteevents" violates foreign key constraint "noteevents_fk_hadm_id"
DETAIL:  Key (hadm_id)=(100151) is not present in table "admissions".

psql:postgres_add_constraints.sql:335: ERROR:  insert or update on table "outputevents" violates foreign key constraint "outputevents_fk_hadm_id"
DETAIL:  Key (hadm_id)=(100069) is not present in table "admissions".

It seems that the admissions table does not contain the information of these two patients with hadm_id 100069 and 100151. I would like to ask whether there is an update plan for CareVue subset for this bug in the nearfuture. You can delete these related data of the two patients from the 6 tables mentioned above(chartevevts, datetimeevents, inputevents_cv, labevents, noteevents, outputevents), or add the information of the two patients to admissions. I know that the above error is only caused by the fact that admissions do not have the information of hadm_id 100069 and 100151, which has no impact on the subsequent data query, and I just don't want anyone using my scripts to misunderstand the terminal's error. Or I can insert the null information of these two patients(only hadm_id) into the admissions table with shell script in the terminal before adding constraints, then no error will be generated.

@alistairewj

Start with postgres_add_constraints.sql at the reported constraint lines and inspect the missing admissions rows for hadm_id 100069 and 100151. Then review 01-config.sh and 02-install.sh to understand how installation invokes the constraint step. Done means the maintainer-approved CareVue correction is reflected in the installation flow and the reported constraint errors no longer appear.