2

Since today our Airflow service is not able to access queries in BigQuery. All jobs fail with the following message:

[2021-03-12 10:17:28,079] {taskinstance.py:1150} ERROR - Reason: 403 GET https://bigquery.googleapis.com/bigquery/v2/projects/waipu-app-prod/queries/e62030d7-36eb-4420-b482-b5327f4f6c7e?maxResults=0&timeoutMs=900&location=EU: Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.

We haven't changed anything in recent days. Therefore we are quite puzzled what the reason might be. Is there a temporary bug? Or might we have to check any settings?

Thanks & Best regards Albrecht

Albrecht
  • 43
  • 1
  • 4
  • 1
    We have just solved it: Reason was that we had referenced a table which is synchronized with a google sheet. Therefore the query worked if executed in BQ, but had authentication problems when triggered from external. – Albrecht Mar 12 '21 at 20:41
  • 1
    your question might be more helpful to others if you post what you commented above as a reply, with some details on _how you made it work_ ( besides _why it wasn't working_, which you detailed in the comment) – kadu Mar 19 '21 at 20:08

1 Answers1

3

I solved this by:

  1. Giving the Airflow service account email access to Google Sheet where BigQuery table is derived from

  2. Adding https://www.googleapis.com/auth/cloud-platform,https://www.googleapis.com/auth/bigquery,https://www.googleapis.com/auth/drive to scopes in the Airflow connection

  3. Regenerating the service account JSON keyfile and pasting into the Keyfile JSON in the Airflow connection

Mwangi Kabiru
  • 423
  • 2
  • 10