BigQuery can't create view with union tables containing TIMESTAMP fields

Question

I want to create a view grouping several daily tables with same schema. The view creation fails on all TIMESTAMP fields with the following error:

Invalid field name "the_field.usec". Fields must contain only letters, numbers, and underscores, start with a letter or underscore, and be at most 128 characters long

Note that the original field doesn't contain the .usec suffix.

A workaround is to wrap all TIMESTAMP fields with the FORMAT_UTC_USEC() function, but the field is then no longer a TIMESTAMP, it becomes an INTEGER.

Answer 1

You need to write as:

select FORMAT_UTC_USEC(timestamp) as timestamp ...

Also you need to de-reference nested record type fields for views:

SELECT utm.campaign as utm_campaign ...