missing ventilation_events table in the pivoted-oasis.sql.

[duanxiangjie]

Hello, alistairewj. I would like to inquire about the missing ventilation_events table in the recently updated pivoted-oasis.sql. Could you provide the original code? Thank you.

[huangzhuohua]

Yes, when I was running pivoted-oasis.sql, I found that the ventilation_events table could not be found, did you forget it? In addition, pivoted-oasis.sql cannot be run directly in postgresql, is it not suitable for direct use in postgresql?

[josephcscarpa]

I was also curious about this as well. In my case, the pivoted_gcs table is unavailable.

[josephcscarpa]

I was able to create the pivoted_gcs table (I was just missing an SQL file, but I now have the same error as @duanxiangjie and @huangzhuohua

[josephcscarpa]

Not a fix, but in context, ventilation_events is one of 4 different detectors for whether a patient is ventilated. I am not sure how redundant each of these 4 cases are with each other, but if your goal is to get started manipulating the resultant data rather than its immediate correctness, you can modify the code to only create vent_2 - vent_4 and just use those in determining whether vent_oasis is 9.

[mirkompcr]

Hi everyone,

Thank you for your interest in our work. I just checked the code and found a small mistake (wrongly placed ) in lines 304-306). It should read:

MAX( CASE WHEN event = "mechvent start" OR event = "mechvent end" THEN 1
ELSE NULL
END) as vent_1

Otherwise it runs on Bigquery.
Did you download the database locally?
Both pivoted_gcs and ventilation_events are tables provided by the LCP team at “physionet-data.eicu_crd_derived”
@huangzhuohua: We wrote the scripts for Google's Bigquery dialect. If you want to use postgresql you would need to translate the script.

Best regards,

Tristan

[josephcscarpa]

So I was confused about how to access the derived dataset. I was able to do it after much conversation (thank you @mirkompcr) and some Googling luck.

1. Go to this domain: https://physionet.org/content/eicu-crd/2.0/
2. If you have access, you will see this:
   

Go ahead and request access to both Google services (I don't know if both are necessary, but I requested access to both).

3. Once you have done this go to Google BigQuery: https://console.cloud.google.com/bigquery. You should see something that looks like this:

4. Next to explorer, click Add.
5. In the list of Additional Sources, click "Star a project by name"
6. Type physionet-data and enter it in.
7. You should now be able to examine all csvs in the derived folder in this resource. You can view the corresponding Google Sheet and export as a CSV for local use.

The script will have to be modified for use with Postgres:

• Postgres vs. BigQuery for views in concepts mimic-code#717 (A useful reference for translation)
• When this doesn't cover it (or even when it does ... some of these alternatives don't work), Google can usually readily supply the answer.

This link was the missing piece for me. https://mimic.mit.edu/docs/gettingstarted/cloud/bigquery/. It is not just applicable for MIMIC, it appears to show all datasets you have access to (in my case, MIMIC-III and eICU)

Hopefully this resolves the issue!

[tompollard]

Thanks @josephcscarpa, Google recently updated the BigQuery console and we're aware that our previous instructions are now incorrect. Your summary of the fix is helpful! We will update our documentation shortly.

[huangzhuohua]

Thanks @josephcscarpa, Google recently updated the BigQuery console and we're aware that our previous instructions are now incorrect. Your summary of the fix is helpful! We will update our documentation shortly.

@tompollard Thanks!

Under certain conditions, many people cannot smoothly access or even use Google BigQuery, so I suggest that your team modify the code to be based on a postgresql database, so that more researchers can use it together.

[tompollard]

@huangzhuohua the issue should be resolved, and you should be able to use MIMIC etc on Google BigQuery. Installing PostgreSQL is an alternative option, but in general I think it is a lot more work than accessing a cloud instance.

Under certain conditions, many people cannot smoothly access or even use Google BigQuery, so I suggest that your team modify the code to be based on a postgresql database, so that more researchers can use it together.

If you prefer to use PostgreSQL (or if BigQuery is unavailable in your region), then you should be able to find build scripts in the Postgres folder in MIMIC Code Repository. If you'd like to contribute Postgres dialect code for analysis, we'd be happy to review pull requests to: https://github.com/MIT-LCP/mimic-code/tree/main

Our research team is very small (~3-4 people actively building, maintaining, and supporting resources including PhysioNet, MIMIC, WFDB tools, etc), so we are limited in what we can do ourselves.

[huangzhuohua]

@tompollard

Thanks, I have to use PostgreSQL.

But there is a problem here：I am converting pivoted-oasis.sql to be suitable for postgresql, but pivoted-oasis.sql itself needs to use the ventilation_events table. However, there is no code to generate this ventilation_events table in the previous SQL script.

I Can't figure out how to create the table and generate the relevant data. Because in the usage of BigQuery, the ventilation_events table has been generated, but there is no relevant script to generate this table in the code of https://github.com/MIT-LCP/eicu-code/.

[mirkompcr]

Dear @huangzhuohua I agree this is a problem, we have eICU tables without the supporting code.
@tompollard is there an easy way to publish those scripts?

[tompollard]

But there is a problem here：I am converting pivoted-oasis.sql to be suitable for postgresql, but pivoted-oasis.sql itself needs to use the ventilation_events table. However, there is no code to generate this ventilation_events table in the previous SQL script.

Thanks for explaining. We try to make sure code is available on GitHub, but it looks like we have unintentionally omitted some here. I'll try to dig out the ventilation_events script and will submit a pull request to the repository.