creating bigquery auth. views in a different cloud project where source dataset is present using python

Question

can we create a authorized bigquery view in a google cloud project A which has source data present in a different cloud project B from a python API.

i can create the above said process manually from the BQ UI, but i want to automate the process of creating around 800 auth. views from a python process.

the issue i am facing is at a given time , i can only call a single bigquery service credentials, so whenever i trigger my process/code, i am getting the source dataset or view dataset credentials missing

from google.cloud import bigquery
import os

from config_files import config
from config_files import db2_table_config


os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = config.bq_service_account #service credentials of project B
client = bigquery.Client()
project = 'project_A'
source_dataset_id = 'dataset_b' # which is in different project
source_table_id = 'table'  # which is in different project
shared_dataset_ref = client.dataset('dataset_a') #target dataset for project B


view_ref = shared_dataset_ref.table("my_shared_view") #need to be created in project B
view = bigquery.Table(view_ref)
view.view_query =  "select query"

view = client.create_table(view)  # API request

print("Successfully created view at {}".format(view.full_table_id))

Paddy Popeye Paddy Popeye · Accepted Answer · 2019-12-12T11:12:06

If your use case is to “share views” with different groups with different needs. I would recommend that you review this article.The subtle art of sharing “views” in BigQuery, this explains exactly what is needed to achieve your goal. Although this walk-through deals specifically with setting it up via the Console UI. I think this is a very good post for understanding the permissions required to share views across different projects for users with differing needs.

“We share access to BigQuery tables and views using project- level IAM roles or dataset-level access controls. Currently, access controls are not directly applicable to tables or views.”

In order to be consistent with the principle of least privilege, you should use dataset-level access controls rather than project-level access controls. Essentially this is a question of setting the appropriate permissions.In particualr the control of access to Datasets and also views

As per the official documentation you cannot share access to tables or views directly.in particular you control the access to datasets, also the access to views

Your particular case might be best served by the use of Custom roles.These enable you to enforce the principle of least privilege, making certain that user/service accounts have only the minimum permissions needed to performing their tasks. Understanding IAM custom roles, Understanding IAM custom roles

As for the implementation using the Python Client library. I would advise reviewing the listed documentation all of which contains code snippets in Python showing how each of the steps are implemented in code.

creating bigquery auth. views in a different cloud project where source dataset is present using python

1 Answers