2

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

Mouz
  • 273
  • 2
  • 13
  • 2
    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 Mar 17 '16 at 14:09
  • 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 Mar 17 '16 at 14:37
  • having at least your schema would help to help you. having simplified example (of edit1) would help too :o) – Mikhail Berlyant Mar 17 '16 at 17:58
  • I edited my post, hope it can make the situation clearer. – Mouz Mar 18 '16 at 08:46
  • 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 Mar 18 '16 at 11:18
  • I'm having trouble deciding whether I need repeated in the schema or not. Aren't repeated fields automatically flattened when querying (at least when there is only one)? That's what I understand from the docs. – Mouz Mar 21 '16 at 10:20
  • You only need to explicitly flatten a repeated field when you are querying across more than one repeated field. BigQuery will otherwise automatically flatten nested fields. – Adam Mar 21 '16 at 21:00
  • What happens if you have more than 1 repeated field in your record? I'm encountering a similar issue, and since I can only flatten 1 level, I cannot save a view. Query runs fine though. – Gil Adirim Apr 04 '16 at 11:29
  • And to answer my own question, for other people who might try to find a solution - use multiple sequential flattens, a la: http://stackoverflow.com/questions/17598058/querying-multiple-repeated-fields-in-bigquery – Gil Adirim Apr 04 '16 at 11:56

1 Answers1

0

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

Stephen Kennedy
  • 20,585
  • 22
  • 95
  • 108
Harm Cox
  • 1