Connect BigQuery With R

[adamwallace3]

Hello, 

I've seen a few comments where users were able to connect to BigQuery using R-Script. I am able to connect in R-studio to BigQuery without issue, but I cannot connect from Power Bi to BigQuery using R. I have a feeling that I need to pass the Auth code somewhere- but I haven't found any documentation on how to do it.

Here is what I have:

library(bigrquery)
project <-"Project_Name"
sql <- "select * from Database.Table"
data <- query_exec(sql, project = project, useLegacySql = FALSE)

Error Message:

Details: "ADO.NET: R script error.
Warning message:
package 'bigrquery' was built under R version 3.3.3 
httpuv not installed, defaulting to out-of-band authentication
Error: Can only use oob authentication in an interactive session
Execution halted
"

Edit: Caps & add error code

[adamwallace3 · Accepted solution]

I was able to read through the BigrQuery package and figure it out. If you are looking for a way to connect to PowerBi to Bigquery through R follow these steps.

1. Install R & R Studio

2. Type install.packages("bigrquery") into the R-Studio Console

3. Enter the following:

library(bigrquery)
project <-"Project_Name"
sql <- "select * from Database.Table"
data <- query_exec(sql, project = project, useLegacySql = FALSE)

4. Type 1 when promoted to "Use a local file ('.httr-oauth'), to cache OAuth access credentials between R sessions?"

5, You will be prompt to sign into google. Copy the corresponding Auth Code

6. Run a query to test that the connection is working.

7. Open Power Bi >Get Data> Other > R-Script 

8. Enter the following with path being the path to .httr-oauth 

    a. Default location is Documents folder

    b. You will need to enter and additional "\" for each "\" in the filepath

library(bigrquery)
project <-"Project_Name"
sql <- "select * from Database.Table"
options("httr_oauth_cache"="PATH")
data <- query_exec(sql, project = project, useLegacySql = FALSE)

Enjoy!