Problems with the convert_bigquery_to_postgres.sh script

[marceliwac]

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?utf8=%E2%9C%93&q=

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:

mimic-code/mimic-iv/concepts/demographics/icustay_detail.sql

Line 19 in 892c21c

, ROUND(DATETIME_DIFF(ie.outtime, ie.intime, HOUR)/24.0, 2) as los_icu

being replaced to:

mimic-code/mimic-iv/concepts/postgres/demographics/icustay_detail.sql

Line 21 in 892c21c

, ROUND( CAST( DATETIME_DIFF(ie.outtime as numeric),ie.intime, 'HOUR')/24.0, 2) as los_icu

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

---

2. 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:

mimic-code/mimic-iv/concepts/demographics/icustay_detail.sql

Line 9 in 892c21c

, DATETIME_DIFF(adm.admittime, DATETIME(pat.anchor_year, 1, 1, 0, 0, 0), YEAR) + pat.anchor_age as admission_age

being replaced to:

mimic-code/mimic-iv/concepts/postgres/demographics/icustay_detail.sql

Line 11 in 892c21c

, DATETIME_DIFF(adm.admittime, DATETIME(pat.anchor_year, 1, 1, 0, 0, 0), YEAR) + pat.anchor_age as admission_age

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  ^    ^

---

3. The script in mimic-iv/concepts/postgres/measurement/oxygen_delivery.sql uses mimic_icu.chartevents instead of mimiciv_icu.chartevents.

[marceliwac]

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]

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!