BigQuery无法使用包含TIMESTAMP字段的联合表创建视图 [英] BigQuery can't create view with union tables containing TIMESTAMP fields

问题描述

我想创建一个视图，将同一个模式的几个日常表分组。视图创建在所有TIMESTAMP字段上失败，并出现以下错误：

无效的字段名称the_field.usec。字段只能包含
字母，数字和下划线，以字母或下划线开头，
且最多128个字符

请注意，原始字段不包含.usec后缀。

解决方法是用FORMAT_UTC_USEC（）函数包装所有TIMESTAMP字段，但是该字段不再是TIMESTAMP，它变成了INTEGER。

解决方案

您需要写为：

选择FORMAT_UTC_USEC（timestamp）作为时间戳...

另外您需要为视图取消引用嵌套记录类型字段：

SELECT utm.campaign as utm_campaign ...

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.

解决方案

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 ...

这篇关于BigQuery无法使用包含TIMESTAMP字段的联合表创建视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！