cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adamwallace3
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
adamwallace3
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
adamwallace3
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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors