BigQuery API and MIMIC data

[jhgennari]

Greetings!

We're trying to setup access the to the MIMIC III data into a Python environment. We're familiar with MIMIC, and with BigQuery cloud access to that data, but we're now trying to invoke it from within Python, using the BigQuery API.

Can anyone confirm that one can do this? Once we know it is possible, then we can more seriously chase down bugs.

We're stuck at the step where you do "from google.cloud import bigquery" -- at this point, we get an error message claiming no such module...

Thanks in advance!

-John Gennari & Shivank Mistry

[arielhasidim]

This is more BigQuery issue then MIMICs if I understand correctly.
I successfully tried with nodejs and not python, but the basics are the same.

This is the instruction from BigQuery docs that's relevant.

If you haven't been to BigQuery console, I find it great place to begin and see that your user has the proper access for starters.

Hope I understood the situation correctly, GL

[tompollard]

Hi @jhgennari

we're now trying to invoke it from within Python, using the BigQuery API. ... Can anyone confirm that one can do this? Once we know it is possible, then we can more seriously chase down bugs.

Yes, connecting to BigQuery from a Python Notebook is certainly possible (and it's my favored way of working with MIMIC, eICU-CRD, etc). The easiest way to get started is using Google Colab.

You could use one of our SCCM Datathon notebooks as reference: https://github.com/MIT-LCP/sccm-datathon. The steps below outline the process for connecting from a Colab Notebook:

1. First, make sure that you have permission to access the dataset. You'll need to follow the steps at: https://mimic.mit.edu/docs/gettingstarted/. To summarize:

   • Create an account on PhysioNet and add your Google account (gmail) details to: https://physionet.org/settings/cloud/
   • Complete the credentialing process for PhysioNet (submit your application from: https://physionet.org/settings/credentialing/).

2. Find the relevant project (e.g. https://physionet.org/content/mimiciv/), navigate to the "Files" section, and then click "Request access using BigQuery".

3. Open a sample notebook and click the "Open in Colab" button at the top: https://github.com/MIT-LCP/sccm-datathon/blob/master/01_explore_patients.ipynb (note that some of the example notebooks are using the eICU database, but the process is the same).

4. Run the cells! Note that:
   1. You will need to make sure that auth.authenticate_user() is using the same account that you have registered with PhysioNet.
   2. You'll also need to replace the project_id='sccm-datathon' variable with your own personal ID that you have created on Google Cloud. This project ID is used for billing purposes.

We're stuck at the step where you do "from google.cloud import bigquery" -- at this point, we get an error message claiming no such module...

My guess is that you are attempting to connect to BigQuery from a local Python environment (rather than a cloud environment) and that you do not have BigQuery package/tools installed. The process would be similar to the above once the BigQuery tools are installed.

For instructions on installing the BigQuery tools locally, see: https://googleapis.dev/python/bigquery/latest/index.html (essentially you need to run: pip install google-cloud-bigquery).

[tkolarik]

@tkolarik Just to clarify, the project ID must be your own project ID (set up yourself via Google Cloud). This project is used for billing. See: https://developers.google.com/workspace/guides/create-project

Thanks for the clarification! So project should be set to physionet-data and project_id needs to be set to the project name of the project you created for billing

[tompollard]

@tkolarik yes, that's right! I find the cloud terminology a little confusing to be honest...as I understand it, we have a project (physionet-data) that holds various datasets.

In the lab, we have permission to bill against this project (which is why you found project_id = "physionet-data" in the example). For people outside our lab, you need to specify a personal project_id for billing.

If you search around, you can often find free cloud credit for research (e.g. https://cloud.google.com/edu/researchers)

[tompollard]

(Side note, I'd like to spend some time cleaning up the MIMIC and PhysioNet documentation around cloud usage, but we're just completely swamped at the moment!).

[jhgennari]

Tom P and @tkolarik

I initiated this thread a year ago, as I was preparing to do BigQuery analysis of Mimic data associated with my University course. It's a year later and I'm trying to do it again. Unfortunately, things have clearly changed. Last year, my entire class used the physionet-data account as the project_id, with no problem. This probably meant that any Google cloud costs were billed to MIT (Sorry!). Now, it no longer allows this, giving the error message:
"User does not have bigquery.jobs.create permission "

If, as Tom P suggests above, I create my own project and set up billing, and then set the project_id to that, everything works. I get $300 credit for free (start-up bonus, I guess), so no cost so far (this might change if I do expensive queries, of course).

But I can't possibly ask all of my undergraduate students to set up billing with Google. For one thing, it asks for a credit card.

Is there any way around this? I see that BigQuery has a free sandbox where you can run standalone SQL queries without a billing account, but I'd really like the queries to be embedded inside of python. Google colab seemed the natural way to do this, but that doesn't seem to be allowed without a credit card.

Thoughts and ideas would be greatly appreciated.

-John Gennari

[tompollard]

Hi @jhgennari, I'm pretty sure that we've been able to create accounts without the use of a credit card in the past, but I don't quite remember the process. It wasn't anything clever...it was clicking the right buttons that are probably in different places now.

It is really a question for people at Google Cloud, so it might be worth dropping their support team a message? If you send us an email using the address at https://physionet.org/about/#contact_us, then we could connect you with our contacts there.

This probably meant that any Google cloud costs were billed to MIT (Sorry!). Now, it no longer allows this, giving the error message:
"User does not have bigquery.jobs.create permission "

Not a problem, in fact we were intentionally covering the costs! Unfortunately this wasn't sustainable, so we have now moved to a "requestor pays" approach, which means that we cover storage costs while you cover compute.

Sorry I can't give you a clear answer right now, but hopefully we can work this out. Side note, but we (well, mostly @Chrystinne) are in the process of integrating PhysioNet with AWS, so at some point this will provide you with an alternative mechanism for cloud analysis.

[tkolarik]

Any update on this?

[tompollard]

@tkolarik please could you let us know what kind of update you are looking for? As far as we are concerned, the system is working (though people occasionally get stuck, usually because they have not created a project_id for billing purposes).

Our laboratory uses the same Google tools (e.g. BigQuery, Colab) for research and teaching and we've not run into any issues that haven't been solved by the steps above.

We are working on a few updates to cloud integration, including support for AWS, but this work is being done alongside our own research and various other competing priorities (e.g. creating new versions of MIMIC).

[tkolarik]

Yep I posted the request for an update when I was struggling to figure out the project_id aspect, now that in understand how to set up the billing and project it is all good! Through our discussions I understand it now, thanks!

[tompollard]

Thanks Thomas, I appreciate your reply. Always good to know that we haven't left people stuck!

[tompollard]

The process for pinning a project on BigQuery has recently changed. See: #1453 for a step-by-step description of the new process.

[ricardo-santos-fhp]

Hello everyone!

I have a question on this topic that I'm having a hard time finding a solution to. I intend to perform queries to MIMIC-IV from a local node to speed up the data processing with multiple CPU (derived tables are not sufficient for my objectives). I believe I have set up everything using the available tutorials (access the dataset, billing account).
The problem is that I am getting the "User does not have permission to query table physionet-data:mimiciv_hosp.d_labitems, or perhaps it does not exist in location US." error, which is generated from the following code snippet adapted from a previous comment:

from google.cloud import bigquery

credentials = service_account.Credentials.from_service_account_file(path_to_json)
bigquery_client = bigquery.Client(credentials=credentials, project=my_billing_project)
query = "SELECT * FROM physionet-data.mimiciv_hosp.d_labitems"
query_job = self.client.query(query) 
df = query_job.to_dataframe()

I have also tried the pandas_gbq lib, but without success. If I perform the authentication with my account in the console, queries are successful. However, this is manual and not even advised as I understood. Service accounts should always be used to perform queries from the outside.
I guess the problem is in the fact that I'm using a service account, which doesn't inherit the view permissions to the physionet-data project (while I do). Although I tried to add the roles to the service on my project, I imagine since the database is external I can't affect it.

I'm new to BigQuery so I wonder if anyone has an idea how to address this issue, or if it's not possible. I read the comments above but I couldn't find a solution.

Thanks for the help!

[alistairewj]

If you have created a service account, then the problem is that the service account does not have the recommended permissions to query the table. When you click the "request access on BigQuery" button on the project page, PhysioNet checks if you are a valid user and provides access to the Google account in your cloud profile. This is why your current e-mail works, but the service account does not. You don't have the ability to provide read access to the data, so you won't be able to do it without PhysioNet's help.

That being said, you shouldn't need a service account for this purpose. It's perfectly fine to use your own user account as you are acting on your own behalf. Service accounts are recommended when you are setting up fully automated systems, web apps, etc. You should be able to authenticate once with your user and re-use the same authentication for your entire session.

[ricardo-santos-fhp]

Thank you for the hint, I was indeed able to use my user authentication to run queries from my Python script. I'll leave my solution here in case anyone faces the same issue I had.

1. Install the Google Cloud SDK.
2. Initiate Google Cloud services gcloud init.
3. Set up Application Default Credentials gcloud auth application-default login

From here I was able to query through:

from google.cloud import bigquery

client = bigquery.Client()

query = "SELECT * FROM physionet-data.mimiciv_hosp.d_labitems"
query_job = client.query(query) 
df = query_job.to_dataframe()

[alistairewj]

Great to hear!