[FIXED] Query BQ table with Jupyter Notebook across owned projects

Issue

I have an issue about acessing data in a BigQuery table in one project using a VertexAI in another project.

Now, I own both project and have service accounts in both project, which implies that I also have the key (credential.json in the project containing the data) which I can use to define my client:

import pandas as pd
import numpy as np
from google.cloud import bigquery
from google.oauth2 import service_account

credentials = service_account.Credentials.from_service_account_file('credentials.json')
project_id = 'cloud-billing-XXXX'
client = bigquery.Client(credentials= credentials,project=project_id)

which should be enough to run:

%%bigquery
SELECT * FROM `cloud-billing-XXXX.all_billing_detailed.gcp_billing_export` limit 100

but I get the error:

ERROR:
 403 Access Denied: Table cloud-billing-XXXX:all_billing_detailed.gcp_billing_export: User does not have permission to query table cloud-billing-XXXX:all_billing_detailed.gcp_billing_export.

I can, from the project containing my notebook, query the table in BigQuery. This makes me think that the problem is a VertexAI permission issue. I read somewhere that the service account used when defining the notebook must match the service account in the project the data resides in. I tried to create a workbench notebook with the service account in the first project and it is created but when I try to open it it refuses to do so and get an error message.

I've also tried to grant Editor and job user permissions across both project but that wouldn't work either.

Any experiences and ideas on how to solve this would be greatly appreciated.

Solution

1. As a mentioned in the comment: add your notebook service account to the first project (which contains your BigQuery data) and grant it with Bigquery Job User and BigQuery Data Viewer permissions.
2. You can query data directly in you python code (without using "magic" %%bigquery). Just add the next two rows:

pct_overlap_terms_by_days_apart = client.query("SELECT * FROM `cloud-billing-XXXX.all_billing_detailed.gcp_billing_export` limit 100").to_dataframe()
pct_overlap_terms_by_days_apart.head()

3. Check out table name. If table path is wrong, then you'll get the same error: 403 Access Denied.

Answered By - Max Zolotenko