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.52k stars 1.51k forks source link

Problems with the convert_bigquery_to_postgres.sh script #1393

Closed marceliwac closed 1 year ago

marceliwac commented 1 year ago

Prerequisites

Description

There are few problems with the conversion to postgres using the convert_bigquery_to_postgres.sh which render the created SQL scripts partially broken (some scripts fail while others proceed).

  1. The script does not replace the ROUND statement correctly. More specifically, the regex appears to treat the next sequence of characters up until whitespace as the group to enclose in the brackets, and not the next "token/statement" if that makes sense.

That results in the following:

https://github.com/MIT-LCP/mimic-code/blob/892c21cec2a5ea046d432148d2e8d5e16d2781f4/mimic-iv/concepts/demographics/icustay_detail.sql#L19

being replaced to:

https://github.com/MIT-LCP/mimic-code/blob/892c21cec2a5ea046d432148d2e8d5e16d2781f4/mimic-iv/concepts/postgres/demographics/icustay_detail.sql#L21

instead of:

, ROUND( CAST( DATETIME_DIFF(ie.outtime, ie.intime, 'HOUR') as numeric) /24.0, 2) as los_icu
---   the " as numeric)"               ^ instead of here   ^ should go here
  1. The script also does not replace the date-parts (YEAR, MONTH etc.) for the function call to DATETIME with its quotemark-encapsulated counterparts. This is currently done for DATETIME_DIFF and DATETIME_TRUNC, but not DATETIME, which is needed by Postgres.

That results in the following:

https://github.com/MIT-LCP/mimic-code/blob/892c21cec2a5ea046d432148d2e8d5e16d2781f4/mimic-iv/concepts/demographics/icustay_detail.sql#L9

being replaced to:

https://github.com/MIT-LCP/mimic-code/blob/892c21cec2a5ea046d432148d2e8d5e16d2781f4/mimic-iv/concepts/postgres/demographics/icustay_detail.sql#L11

instead of:

, DATETIME_DIFF(adm.admittime, DATETIME(pat.anchor_year, 1, 1, 0, 0, 0), 'YEAR') + pat.anchor_age as admission_age
---                                         add missing quotemarks here  ^    ^
  1. The script in mimic-iv/concepts/postgres/measurement/oxygen_delivery.sql uses mimic_icu.chartevents instead of mimiciv_icu.chartevents.
marceliwac commented 1 year ago

For your convenience, here is a temporary patch of changes that make the scripts in mimic-iv/concepts/postgres usable again.

fix_conversion_problems.patch (txt)

alistairewj commented 1 year ago

I think this is all fixed now, so I will close it, but feel free to reopen if I missed something. Thanks for raising the issue!