Error executing query

[Mark_Moralls]

Hello,

I’ve looked in vain for an answer to my issue. I have a db web app that runs fine locally and I have deployed for users to access. It has predefined SQL queries that can be executed with results from BigQuery. It uses pandas_gbq library to process the queries. I have the secrets file saved accordingly and the contents in my app’s secret section. I get the following error message when I try and execute a query:

pandas_gbq.exceptions.GenericGBQException: Reason: 401 POST https://bigquery.googleapis.com/bigquery/v2/projects/s4a-prod/jobs?prettyPrint=false: Request had invalid authentication credentials. Expected OAuth 2 access token, login cookie or other valid authentication credential. See Integrating Google Sign-In into your web app  |  Google Sign-In for Websites  |  Google Developers.

BigQuery API is enabled. In the Credentials tab I have an API key with HTTP referrers and an OAuth 2.0 Client ID. BigQuery API and BigQuery Data Transfer API are the API restricted. I’ve added https://oauth2.googleapis.com/token in the Authorized redirect URI field and https://share.streamlit.io in the Authorized Javascript origins field.

I don’t know what I am missing. Any help would be appreciated!

[randyzwitch]

That’s a strange one @Mark_Moralls. When you copied your secrets into the interface, did you make sure to convert it to toml instead of the JSON that Google usually provides?

[randyzwitch]

Hi @Mark_Moralls -

We played around internally a bit, we think this might be a case of not setting a project_id. Because Streamlit Cloud is hosted on Google Cloud, it looks like the Python library infers a project you don’t have access to.

So check that value in the Secrets interface and let us know if that resolves it.

Best,
Randy

[Mark_Moralls]

Hi Randy,

Thanks for looking into my issue. Yes, it was a strange one and I did verify the secrets file to TOML format. I thought the credentials line was enough to make the connection to the project id.

credentials = service_account.Credentials.from_service_account_info(
st.secrets[“gcp_service_account”]

So after going back to the pandas_gbq documentation, I did set a project id variable and added project_id and credentials to each of my df variable entries:

df = pandas_gbq.read_gbq(contents, project_id, credentials=credentials)

So now each query works as I intended! Glad this was resolved. Thanks again!