CODEWITHSUNDEEP
google-bigquery
Mouz
Mouz

Reputation: 263

Saving a view fails to create a valid output schema in Google BigQuery

I'm trying to save a view of a query joining two tables in BigQuery. Here are the simplified schemas of those tables.

First table schema enter image description here

Running the following query in the BigQuery UI returns the result I expect:

SELECT * 
FROM [dataset_name.table1] as t1 
JOIN [dataset_name.table2] as t2 
ON t1.primaryEmail = t2.user_email

Then, hitting the button Save View returns the following error:

Failed to save view. Cannot create valid output schema for field organizations.description.
Try renaming organizations.description to t1.organizations.description in the outermost SELECT.

Renaming the field in the outermost select seems quite painful as I would probably have to specify all the fields (which is a lot) of my two tables in the very same select.

Did I miss something about views?

edit 1: I managed to create a view by specifying all the fields in the SELECT and adding t1.organizations.xxx AS t1.organizations.xxx (on the recommendations of the errors) only for the record sub-fields:

SELECT
t1.primaryEmail, 
t1.suspended, 
t1.name.familyName, 
t1.name.fullName, 
t1.name.givenName, 
t1.organizations.name as t1.organizations.name, 
t1.organizations.title as t1.organizations.title, 
t1.organizations.primary as t1.organizations.primary,
t2.report_date
FROM [dataset_name.table1] t1 JOIN [dataset_name.table2] t2 ON t1.primaryEmail = t2.user_email

It creates a view with an extra unwanted record field t1:

enter image description here

Upvotes: 2

Views: 978

Answers (1)

Harm Cox
Harm Cox

Reputation: 1

You could also do a group by to make each record unique which allows it to be turned into a view.

Upvotes: 0

Related Questions