mimic-iv

MIMIC-IV Concepts

• buildmimic - Scripts to build MIMIC-IV in various relational database management system (RDMS), in particular postgres is a popular open source option
• concepts - SQL scripts to extract data from MIMIC-IV including demographics, organ failure scores, severity of illness scores, durations of treatment, and so on. These concepts are written in the BigQuery dialect.
  • concepts_postgres - above concepts converted to the PostgreSQL dialect
  • concepts_duckdb - above concepts converted to the DuckDB dialect
• notebooks - Jupyter notebooks to demonstrate how to use the data in MIMIC-IV
• mapping - Mapping concepts within MIMIC-IV to standard ontologies

Concepts

The MIMIC-IV concepts are written in an SQL syntax compatible with BigQuery. The BigQuery physionet-data.mimic_derived dataset contains the output of the SQL scripts present in the concepts folder. These tables are generated using the code in the latest release on GitHub. Access to this dataset is available to MIMIC-IV approved users: see the cloud instructions.

• List of the concept folders and their content
• Generating the concept tables on BigQuery
• Generating the concept tables on PostgreSQL

Generating the concepts

If you just want to use the data generated by the concepts scripts, you can access each table as physionet-data.mimiciv_derived.* on BigQuery. See the cloud instructions for access details.

These concepts assume the output schema is mimiciv_derived. If you would like a different schema, you will need to make a few edits to the scripts.

All concepts are originally written in the BigQuery Standard SQL Dialect. A Python package is used to convert these BigQuery scripts into other dialects such as PostgreSQL. These scripts have been converted to PostgreSQL and DuckDB by a transpilation script. To generate the concepts in PostgreSQL, see the MIMIC-IV postgresql concepts subfolder. See below for how scripts in non-bigquery dialects were generated.

BigQuery

Generating the concepts requires the Google Cloud SDK to be installed. A shell script, make_concepts.sh, is provided which iterates over each folder and creates a table with the same name as the concept file. Concept names have been chosen to avoid collisions.

Generating a single concept can be done by calling the Google Cloud SDK as follows:

bq query --use_legacy_sql=False --replace --destination_table=my_bigquery_dataset.age < demographics/age.sql

PostgreSQL

The concepts_postgres folder contains concepts in a PostgreSQL compatible dialect. To run them against a local PostgreSQL database:

psql -d mimiciv -f mimic-iv/concepts_postgres/postgres-make-concepts.sql

DuckDB

The concepts_duckdb folder contains concepts in a DuckDB compatible dialect. To load all derived concepts into an existing DuckDB database (e.g. one built from CSV files):

import duckdb

con = duckdb.connect("~/data/mimic-iv.duckdb")
con.execute("SET search_path = mimiciv_derived")

import os
from pathlib import Path
concepts_dir = Path("mimic-iv/concepts_duckdb")
duckdb_sql = (concepts_dir / "duckdb.sql").read_text()
for line in duckdb_sql.splitlines():
    line = line.strip()
    if line.startswith(".read "):
        sql = (concepts_dir / line[6:]).read_text()
        con.execute(sql)

Or from the duckdb CLI:

duckdb ~/data/mimic-iv.duckdb -c "SET search_path = mimiciv_derived" \
  < mimic-iv/concepts_duckdb/duckdb.sql

Transpile

The Python package sqlglot is used to convert concepts from BigQuery SQL to other dialects ("transpile"). It parses the SQL into an abstract syntax tree (AST) and re-writes it for the target dialect. The mimic_utils helper package handles functions not natively supported by sqlglot; see transpile.py.

The transpiled files are already committed to concepts_postgres and concepts_duckdb. You only need to re-run transpilation if you modify the source BigQuery SQL in concepts/.

First install the package from the repo root:

pip install -e .

To transpile a single file:

# mimic_utils convert_file <source_file> <destination_file> [--destination_dialect postgres|duckdb]
mimic_utils convert_file mimic-iv/concepts/demographics/age.sql age.sql --destination_dialect duckdb

To re-transpile all concepts at once:

# To DuckDB:
mimic_utils convert_folder mimic-iv/concepts mimic-iv/concepts_duckdb --destination_dialect duckdb

# To PostgreSQL:
mimic_utils convert_folder mimic-iv/concepts mimic-iv/concepts_postgres --destination_dialect postgres