How to import view from Google BigQuery that used tables from two projects

user_15215220 0
Viewed 215

I need to import from the view that is in one project when inside view there are joins to tables from other project.

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

#----------BigQuery Connect---------------
cred_1 = service_account.Credentials.from_service_account_file(filename="project1.json")
cred_2 = service_account.Credentials.from_service_account_file(filename="project2.json")
client = bigquery.Client(credentials=cred_1, project=cred.project_id)
medicare = client.dataset('dataset', project='project1')

# --------- from Bigquery-----------------------------------

statment = """select * FROM `project1.dataset.view`"""
query_job = client.query(statment )
result = query_job.result()
df = result.to_dataframe()
df.head()

Since there is in the view the use of tables from another project, I get an error:

Forbidden: 403 Access Denied: Table project2:dataset.table: User does not have permission to query table project2:dataset.table.

I have two JSON files with credentials to two projects, but it's not clear how to combine them to give access to two projects.

How can such a problem be solved?

service-accounts google-bigquery python
2 Answers

Ensure in IAM, your current project SA has access to the tables / views in other project. The error message is very clear

user_52805260

Please go with the option that fits your needs:

  1. Have an account that have access to both projects

Your credentials.json must have access to both datasets. For details about access to projects check this link. Also, check this stackoverflow answer: How can I connect two different project in google BigQuery in Python.

  1. Use Authorized View

Lets said that you are unable to have an account with access to both projects and have table restrictions. On that scenario, you will have to work with authorized view. As per definition:

Giving a view access to a dataset is also known as creating an authorized view in BigQuery. An authorized view lets you share query results with particular users and groups without giving them access to the underlying tables. You can also use the view's SQL query to restrict the columns (fields) the users are able to query.

Still, your users will need have access to the dataset that stores the view.

For your data analysts to query the view, they need to be granted the bigquery.dataViewer role on the dataset containing the view.

There is a complete guide you can follow to update the view into a shared view. Check Share Access Views page.

user_145515670
Related