Search code examples
pythongoogle-bigqueryservice-accountspandas.dataframe.to-gbq

Why GBQ doesn't work when I try to change it to a DataFrame?

I want to load and write data on my GBQ. I tried to use read_gbq() and followed the instructions for the authenticating. I used my service account for authentication. I referred to the official documentation of pandas_gbq shown below:

from google.oauth2 import service_account
import pandas_gbq

credentials = service_account.Credentials.from_service_account_file(
    'path/to/key.json',
)
df = pandas_gbq.read_gbq(sql, project_id="YOUR-PROJECT-ID", credentials=credentials)

This throws an error. This is the error I am getting:

AttributeError: module 'google.auth.credentials' has no attribute 'CredentialsWithTokenUri'

I then tried to use the BigQuery Client for Python. I used the BigQuery documentation to authenticate. This is what I did:

from google.cloud import bigquery
from google.oauth2 import service_account

key_path =  'creds/my-cred-file.json'

credentials = service_account.Credentials.from_service_account_file(key_path)

client = bigquery.Client(credentials=credentials, project=credentials.project_id)

When I ran the query through:

query_job = client.query(sql)

This returns the data where I can iterate through. So, the data from the tables are being read using this method. But when I tried this:

query_job.to_dataframe()

It throws me an error, PermissionDenied: 403 request failed: the user does not have 'bigquery.readsessions.create' permission for 'projects/my-project-staging'.

I want to know if something is wrong with my service account. I feel like it shouldn't be, because I am able to read data by iterating through query_job variable. Is there any sort of documentation that would help me?

I tried to read and load data from GBQ. I was expecting I could easily turn the result into pandas DataFrame.

Solution

  • Looking at the error it appears that the user has a missing permission. I would suggest to add this role to the service account to test if it help to resolve your error

    BigQuery Read Session User

    enter image description here

    Reference: https://cloud.google.com/bigquery/docs/information-schema-table-storage

    You can also check the status of your service account key at the keys tab when you select your SA:

    enter image description here

    Also I would suggest following this documentation on Pandas-gbq for your reference, and also this for installation of the package.