CODEWITHSUNDEEP
google-bigquery
cryanbhu
cryanbhu

Reputation: 5254

Use BigQuery Python API to create view in one project referencing tables in another project

I am trying to do the above, but I am getting the following error:

ERROR - exception: 404 POST https://bigquery.googleapis.com/bigquery/v2/projects/sandbox/datasets/my_dataset_long/tables?prettyPrint=false: Not found: Dataset prod:my_dataset_short

I am using end user credentials, and I have BQ access to both projects.

How do I create the View in sandbox project that queries prod project's datasets?

I followed the example here to the tee, except for my own query, which references the other project's table, and I still got the error.

Upvotes: 1

Views: 99

Answers (2)

Sergey Geron
Sergey Geron

Reputation: 10172

View must be in the same location as the referenced tables.

The location mentioned here is a region.

Most likely the issue here is with access rights. Try to authorize your view with view access to dataset from another project:

  1. Select the github_source_data dataset from Resources and click Share dataset.
  2. In the Dataset permissions panel, click the Authorized views tab.
  3. Under Share authorized view:
    • For Select project, verify your project is selected.
    • For Select dataset, choose shared_views.
    • For Select view, type the view name: github_analyst_view.
    • Click OK.
  4. Click Add and then click Done.

In Python:

access_entries = source_dataset.access_entries
access_entries.append(
    bigquery.AccessEntry(None, "view", view.reference.to_api_repr())
)
source_dataset.access_entries = access_entries
source_dataset = client.update_dataset(
    source_dataset, ["access_entries"]
)  # API request

Upvotes: 1

cryanbhu
cryanbhu

Reputation: 5254

I tried to repeat the steps manually in BigQuery Console UI, by pasting the query into the editor and saving the View in my sandbox project.
I got the same error Not found: Dataset ...

Then I noticed the tooltip in the Save View dialog which said the View must be in the same location as the referenced tables.

Upvotes: 1

Related Questions