How to connect to BigQuery in R recipe Jupyter Notebook

Beach_Bum

Hi all,

Looking for some advice on how to connect to a BigQuery database inside of Dataiku's R jupyter notebooks. We'd prefer to go this route instead of using the dataset menu to pre-define inputs and outputs, as we do a lot of adhoc work, and the tables can change on the fly depending on the client we're servicing and what we find in the data.

In a non-DSS environment (R Studio or Jupyter notebook), connecting to BigQuery can be done using the DBI and Bigrquery packages. One simply uses the DBI::DBConnect function and calls bigrquery() to define the database. Authentication occurs through stored credentials or through a pop-up web browser that asks you to login.

Dataiku seems to be preventing the authentication pop-up, and the credentials stored in the DSS settings are showing that we don not have the correct permissions to access our bigquery datasets. Is there a way to either allow the popup or alter the credentials so that we can write and read to BigQuery? In a SQL notebook, we can connect fine; it's just in R notebooks that we are having trouble.

Thanks!

JeanC

Hi,

What do you mean exactly by "the credentials stored in the DSS settings are showing that we don not have the correct permissions to access our bigquery datasets"?

FWIW, I'm able to connect to BigQuery using the following code:

library(dataiku)
library(DBI)
library(bigrquery)

project_id <- "my-project"
conn_info <- dkuGetConnectionInfo("my-connection")

bigrquery::bq_auth(
  path = conn_info$params$keyPath,
  cache = FALSE
)

con <- DBI::dbConnect(
  bigrquery::bigquery(),
  project = project_id,
  billing = project_id
)

result <- DBI::dbGetQuery(con, "
  SELECT
    table_schema,
    table_name
  FROM `my-project.region-eu.INFORMATION_SCHEMA.TABLES`
  LIMIT 10
")

print(result)

DBI::dbDisconnect(con)

Allowing the authentication popup would require significant changes in the way the kernel is started, so it's not so straightforward to do.

Hope that it helps :)