Google BigQuery: tabel name not found?

DocMusher

Hi dear RM friends,
I got RM connected to Google BigQuery as described earlier in this forum(https://community.rapidminer.com/discussion/comment/62988#Comment_62988).

However, finding the schema name is not a problem in a drop down list. For the tables, no suggestions are delivered. I also attempted just to enter a tabel name manually but without further success. 
I attached the screenshots I received while attempting to use the MIMIC-III database (on Google BigQuery) from inside RM. 

Anyone able to send me some feedback? 
Thanks
Sven

DocMusher

FIXED, copy dataset on BigQuery, new connection and this is the result

DocMusher

.... and its working 

phellinger

Hi @RM_Newbie,

Your connection is ready, so you may want to use it in an operator.

If you have the In-Database Process Extension, you should be able to find the In Database Nest operator. Add one and connect this Retrieve operator's output to its input. This way you point the operator to your BigQuery instance.

Then double click into the In Database Nest, and add a Retrieve (In Database) operator where, after a couple of seconds, you should be able to select a table from the dropdown parameters. If you connect the output to the In Database Nest output, and the In Database Nest output to the process output, you'll get the table content as a result. Be careful, as your dataset may be big, in which case, I recommend you to add a Sample (In Database), Filter Example Range (In Database) or Filter Examples (In Database) operator after the Retrieve (In Database).

I hope this helps,
Best,
Peter

sgenzer

ooh @DocMusher you figured out how to connect with MIMIC-III? That's great news!

Let me see if @phellinger has any ideas...

Scott

DocMusher

And this only the beginning because MIMIC-IV is almost ready: https://mimic-iv.mit.edu/
Hopefully, we can restart using RM for this type of datasets as they represent very good how multivariable time series are recorded in healthcare. For whom the bell tolls: an interesting paper: https://www.hindawi.com/journals/cmmm/2019/8152713/
Cheers Sven

sgenzer

@DocMusher can you share your RM process you're using now? You can send it via PM if you want. I'd like to take a look.

Scott

phellinger

Hi Sven,

thank you for the great screenshots!
Does the dropdown list not refresh after clicking the button next to it? What happens if you enter a valid name and run the process?

Can you see relevant log lines maybe when you open the Log panel via View->Show Panel (it can also be accessed by reading the file ~/.RapidMiner/rapidminer-studio.log).

Best,
Peter

DocMusher

Hi Peter, it gives message refreshing but with nothing appearing. I also tried to enter a table name myself but it did not accept or capture it. Logfiles I send 2morrow. Thanks

DocMusher

FIXED, copy dataset on BigQuery, new connection and this is the result

DocMusher

.... and its working 

RM_Newbie

Hello Experts,
I am new to Rapidminer and facing similar issue as in this thread and looking for some help to connect to Bigquery tables.

I created new connection in RM following steps suggested in 'Using Google Cloud Service connector' article , and test connection was also successful, however, I am not able to find either dataset or any tables or views in Rapidminer. Please help with your valuable suggestions.

Caperez

Hi @RM_Newbie, 

welcome to RM. from your picture I can see that you didn't connect the out port to the res port. Just double click on out port or single click and connect it to the res port in the righ hand. 

Best.   

RM_Newbie

Hi @ceaperez
Thanks for your response. I tried connecting out port to res port, but don't see any changes though, am I missing something? Please refer below screenshot.

Caperez

Hi @RM_Newbie,

Haven't you any result in the results panel?

the message is a warning about to share the process but it's not a execution error.

Best

phellinger

Hi @RM_Newbie,

Your connection is ready, so you may want to use it in an operator.

If you have the In-Database Process Extension, you should be able to find the In Database Nest operator. Add one and connect this Retrieve operator's output to its input. This way you point the operator to your BigQuery instance.

Then double click into the In Database Nest, and add a Retrieve (In Database) operator where, after a couple of seconds, you should be able to select a table from the dropdown parameters. If you connect the output to the In Database Nest output, and the In Database Nest output to the process output, you'll get the table content as a result. Be careful, as your dataset may be big, in which case, I recommend you to add a Sample (In Database), Filter Example Range (In Database) or Filter Examples (In Database) operator after the Retrieve (In Database).

I hope this helps,
Best,
Peter

RM_Newbie

Hi @phellinger,

Bingoo!! This is exactly what I was looking for. Thank you so much 

One quick question though, In Bigquery, under project-->schema(dataset in BQ terms)-->I have both tables and Views available. But in RM, I am able to find and read only from the tables but the Views are not showing up in the dropdown.

Does it mean RM does not support read from the views? or any pointers that you can direct me to?

TIA!

Update-- The BQ View names does not show up in the Retrieve(In Database) operator's 'table name' dropdown option, but if we explicitly mention the view name in the box then it reads the data perfectly from the view.