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

2
votes

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

1
google-bigquery
as a workaround for painful process of specifying all fields - you can use Add Fields Button in BigQuery Mate. I introduced it for such cases. Navigate to respective table's schema and Add Fields button will appear cloase to Query Table Button. Tables Alias is also supported here – Mikhail Berlyant
I managed to get something pretty close to what I want with the help of your extension but this won't help me in the end as I want to be able to create my view from the python API client library. I could probably find a similar workaround though. Meh... – Mouz
having at least your schema would help to help you. having simplified example (of edit1) would help too :o) – Mikhail Berlyant
I edited my post, hope it can make the situation clearer. – Mouz
looks like view refuses repeated field in the root - do you care about preserving repeated in schema or you ok with flattening it? – Mikhail Berlyant

1 Answers

0
votes

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