The BigQuery API key is not recognized in the Streamlit Webapp

☁️ Community Cloud
1

Hi, I’m creating this topic because I can’t find a problem like mine. My BigQuery API key is recognized when I use my streamlit web application locally, but it is not recognized when I deploy it on GitHub although it is the same code.
The issue is : Access Denied: Project xxx: User does not have bigquery.jobs.create permission in project xxx.
(The xxx are just there to hide my confidential informations)
My code is :

import streamlit as st
from google.oauth2 import service_account
from google.cloud import bigquery

def test():
    # credentials = service_account.Credentials.from_service_account_info(
    # st.secrets["gcp_service_account"]
    # )
    # client = bigquery.Client(project = "xxx", credentials=credentials)
    client = bigquery.Client()
    sql = """
    SELECT
    *
    FROM
    `xxx.xxx`
    LIMIT 1
    """
    df = client.query(sql).to_dataframe()
    return df

print(test())

I put this in the secrets :

[gcp_service_account]
type = "service_account"
project_id = "xxx"
private_key_id = "xxx"
private_key = "xxx"
client_email = "xxx"
client_id = "xxx"
auth_uri = "https://accounts.google.com/o/oauth2/auth"
token_uri = "https://oauth2.googleapis.com/token"
auth_provider_x509_cert_url = "https://www.googleapis.com/oauth2/v1/certs"
client_x509_cert_url = "xxx"

when I use :

client = bigquery.Client()

It works locally but when I use :

credentials = service_account.Credentials.from_service_account_info(
 st.secrets["gcp_service_account"]
 )
client = bigquery.Client(project = "xxx", credentials=credentials)

It works neither locally and deployed.
Thank you for your help

2

Hi @Louis_Delsol,

All of the “xxx” values are meant to be replaced with the real values from your GCP account. For example, the project_id = "xxx" should become project_id = "<whatever-your-project-id-is>". If you followed the steps here Connect Streamlit to Google BigQuery - Streamlit Docs, and downloaded the JSON file with your credentials, you should be able to see all of values you’ll need to put into the .toml file. Once that works locally, you can just copy and paste the values from the secrets.toml file and put them in as the secrets for your app on Community Cloud.

1 Like
3

Hi @blackary, and thank you for your response ! The xxx are just there to hide my confidential informations. Indeed, it works localy but when I deploy it, it won’t work. While I put all the information from my secrets.toml in the streamlit secrets.

4

Ah, so if I understand correctly, it works locally when you don’t pass any credentials (just do bigquery.Client()), but it doesn’t work locally or remotely when you do pass the credentials (bigquery.Client(project=...)?

In that case, the issue is probably being described accurately from the error message – you probably need to grant more permissions to the service account you created so that it has bigquery.jobs.create permission for your project. Here is some more detail about BigQuery permissions: Access control with IAM  |  BigQuery  |  Google Cloud

1 Like
5

That’s right, my code works fine locally but not when I deploy it. And my service account already has all the permissions on my project.

6

But, it only works locally if you don’t pass the credentials, right? If you pass the credentials locally, it doesn’t work, and gives you this error:

Access Denied: Project xxx: User does not have bigquery.jobs.create permission in project xxx.

Is that correct?

That implies that the issue is the service account permissions.

7

Hey there, I have the exact same issue as above. My service account credentials work perfectly locally, can read in data and do everything I need it but it doesn’t work on the server.

The wierd thing is that I don’t know why it would need create permissions wrt my code. Does streamlit caching somehow involve writing through BQ though it sounds far fetched?

8

hi,
I had same issue and just resolved it removing cache_data decorator from my app.
For me it’s not a perfect solution, but worked immediately.

9

You might try using st.cache_resource for caching the connection itself, rather than st.cache_data.

But, st.cache_data should work fine for caching the results of bigquery queries.

10

yes, it works for query results. But my problem was the complete opposite: I loaded data into big query. I will try the resource caching option, thanks.

1 Like