CODEWITHSUNDEEP
rauthenticationgoogle-bigquerygoogle-colaboratorybigrquery
Ben
Ben

Reputation: 55

Authentication for Bigquery using bigrquery from an R in Google Colab

I try to access my own data tables stored on Google BigQuery in my Google Colab sheet (with a R runtime) by running the following code:

# install.packages("bigrquery")
library("bigrquery")
bq_auth(path = "mykeyfile.json")
projectid = "work-366734"
sql <- "SELECT * FROM `Output.prepared_data`"

Running

tb <- bq_project_query(projectid, sql)

results in the following access denied error:

Access Denied: BigQuery BigQuery: Permission denied while globbing file pattern. [accessDenied]

For clarification, I already created a service account (under Google Cloud IAM and admin), gave it the roles ‘BigQuery Admin’ and ‘BigQuery Data Owner’, and extracted the above-mentioned json Key file ‘mykeyfile.json’. (as suggested here)

Additionally, I added the Role of the service account to the dataset (BigQuery – Sharing – Permissions – Add Principal), but still, the same error shows up…

Of course, I already reset/delete and reinitialized the runtime.

Am I missing giving additional permissions somewhere else? Thanks!

Not sure if it is relevant, but I add it just in case: I also tried the authentication process via

bq_auth(use_oob = TRUE, cache = FALSE)

which opens an additional window, where I have to allow access (using my Google Account, which is also the Data Owner) and enter an authorization code. While this steps works, bq_project_query(projectid, sql) still gives the same Access Denied error.

Trying to authorize access to Google BigQuery using python and the following commands, works flawless (using the same account/credentials).

from google.colab import auth
auth.authenticate_user()
project_id = "work-366734"
client = bigquery.Client(project=project_id)
df = client.query( '''
   SELECT
     *
   FROM
     `work-366734.Output.prepared_data`
 ''' ).to_dataframe()

Upvotes: 1

Views: 721

Answers (2)

user22659285
user22659285

Reputation: 1

I had the same problem. With me it worked once I run library(tidyverse) before the credentials, it seems to me that the credentials are working via that package.

Upvotes: 0

fischerj
fischerj

Reputation: 21

I would recommend trying to run this without a service account at all, and instead, going through the notebook authorization flow.

First, in your notebook, you'll want to install bigrquery (which it looks like you've done).

install.packages("bigrquery")
library(bigrquery)

After that, you can run the following code as a sample query:

# Store the project ID
projectid = "PROJECT_ID"

# Set your query
sql <- "SELECT * FROM `bigquery-public-data.usa_names.usa_1910_current` LIMIT 10"

# Run the query; this returns a bq_table object that you can query further
tb <- bq_project_query(projectid, sql)

# Store the first 10 rows of the data in a tibble
sample <-bq_table_download(tb, n_max = 10)

# Print the 10 rows of data
sample

This will prompt you to choose whether to store the credentials you set up, then will provide a URL for you to log in with your credentials (at tidyverse.org), and then you paste in the resulting code in your notebook.

Once that's done, you can query to your heart's content!

Source: https://cloud.google.com/vertex-ai/docs/workbench/user-managed/use-r-bigquery

Upvotes: 1

Related Questions