cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate II
Advocate II

Connect BigQuery With R

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

1 ACCEPTED SOLUTION
Advocate II
Advocate II

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!

 

View solution in original post

1 REPLY 1
Advocate II
Advocate II

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!

 

View solution in original post

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors