search

MIT-LCP / mimic-code

MIMIC Code Repository: Code shared by the research community for the MIMIC family of databases
https://mimic.mit.edu
MIT License
2.61k stars 1.53k forks source link

The installation of MIMIC-III Clinical Database CareVue subset #1412

Open ningyile opened 2 years ago

ningyile commented 2 years ago

Prerequisites

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

alistairewj commented 1 year ago

Yeah I think it is correct to remove these hadm_id entirely. I used an admittedly naive "delete all of these subject_id" approach, and it looks like that misses these hadm_id which do not have an associated subject_id .. or are associated with another one? Not sure exactly. It's annoying and it would be nice to fix... who knows when I'll get around to updating the dataset and pushing a new release to PhysioNet though!