connecting to Google BigQuery via `bigrquery`

I am attempting to integrate BigQuery with my data workflow, but would prefer to interface with it via the bigrquery package for R. Presently, it does not appear KNIME allows this type of interface, and there are no warnings thrown when a node is executed – it just never runs the code.

Has anyone found a sufficient workaround?

Edit: Clarification that this is using the R language

Hello @ay_1030_useR -

I was able to get this to work using the Community R Snippet node. (I found that the standard R Snippet node was running indefinitely - probably because it was hung up waiting for input authorization from the user. Maybe this is what you were running into.) I was switching back and forth between KNIME and RStudio as I played around with this, so it’s possible I’m making this easier than it actually is.

At any rate, here’s what I did, based on Hadley Wickham’s simple example on Github: https://github.com/r-dbi/bigrquery

2019-01-18%2015_33_31-Data%20table%20returned%20from%20the%20snippet%20-%200_45%20-%20R%20Snippet

Note that you have to be running RServe locally for this to work:

Rserve(args = "--vanilla")

I hope this is helpful. I’d never experimented with the bigrquery() package before, so hopefully I didn’t leave out something important.

3 Likes

RServe! of course. Thanks for the quick reply

1 Like

Hi @ay_1030_useR,

I am working on a similar project where I want to connect to a public Kaggle data set that is hosted on BigQuery here.

I would like to connect directly via the KNIME Database connector node, or through RStudio using the bigrquery package you used. Can you share some of the lessons you learned or point me to some useful resources? BTW, I’m new to BigQuery.

Thanks,
Haystack

Hi there @Haystack,

just wondering have you done any progress regarding your idea? Generally you can connect to BigQuery using KNIME Database Connector node. Before that you should download and register SIMBA driver within KNIME.

Here is more on public data sets hosted on BigQuery so take a look how you can access them. Solution @ScottF provided will work for sure while not so sure about KNIME Database Connector but give it a try and let us know :wink:

Br,
Ivan

1 Like

Hi @ipazin,

Which version of the driver do I download? ODBC or JDBC? Also, I recall reading somewhere that only the previous (older) version of the SIMBA drivers work with KNIME. Is that so? Lastly, I assume I need to associate the driver in KNIME by going to preferences > databases > add file (zip or jar). Correct?

Thank you!

Hi @Haystack ,

The driver is JDBC. I have downloaded the newest and it worked very well. You are right regarding driver registration.

Br,
Ivan