BigQuery:无法读取类型为TIMESTAMP_MICROS的字段“戳记"为DATETIME [英] BigQuery: Cannot read field 'stamp' of type TIMESTAMP_MICROS as DATETIME

问题描述

我正在使用BigQuery的标准SQL 通配符表功能.

I'm playing with BigQuery's Standard SQL Wildcard Table functionality.

我有一个具有以下架构的表:

I have a table with the following schema:

_PARTITIONTIME  TIMESTAMP   NULLABLE
stamp           TIMESTAMP   NULLABLE
value           FLOAT       NULLABLE
source          STRING      NULLABLE
...

因为我的数据集中的表具有混合的模式，并且它们的名称通常不带有前缀，所以我打算进行宽通配符匹配-然后在WHERE中将_TABLE_SUFFIX缩小:

Because the tables in my dataset have mixed schemas and their names are not commonly prefixed, I intend on making a wide Wildcard match - then, narrowing _TABLE_SUFFIX in the WHERE:

SELECT
*,
_TABLE_SUFFIX AS table_name
FROM `my-project.my-dataset.*`
WHERE REGEXP_CONTAINS(_TABLE_SUFFIX, r"cars")

这是我能做到的基本知识. cars仅匹配单个表. 我打算在上面的REGEX中添加其他表.例如:cars|trucks|...

This is as-basic as I can make it. cars only matches a single table. I intend on adding additional tables to the REGEX above. e.g: cars|trucks|...

执行上述查询时，出现以下错误:

When I execute the above query, I get the following error:

Cannot read field 'stamp' of type TIMESTAMP_MICROS as DATETIME

此外，当我尝试从SELECT中删除stamp并在其位置使用source列时:

Furthermore, when I attempt to remove the stamp from my SELECT and use the source column in it's place:

SELECT
source,
_TABLE_SUFFIX AS table_name
FROM `my-project.my-dataset.*`
WHERE REGEXP_CONTAINS(_TABLE_SUFFIX, r"cars")

BigQuery现在告诉我:Unrecognized name: source at [2:1]

BigQuery now tells me: Unrecognized name: source at [2:1]

这两种情况都在发生什么? BigQuery为什么要转换我的stamp列，为什么找不到source?

What's happening here in both cases? Why does BigQuery want to cast my stamp column, and why is it unable to find source ?

推荐答案

这两种情况都在发生什么? BigQuery为什么要转换我的标记列，为什么找不到源?

What's happening here in both cases? Why does BigQuery want to cast my stamp column, and why is it unable to find source ?

为了执行使用通配符表的标准SQL查询，BigQuery会自动推断该表的架构. BigQuery使用与通配符匹配的最新创建表的模式作为通配符表的模式.如果在与通配符表匹配的表之间架构不一致，则BigQuery返回错误.

In order to execute a standard SQL query that uses a wildcard table, BigQuery automatically infers the schema for that table. BigQuery uses the schema for the most recently created table that matches the wildcard as the schema for the wildcard table. If the schema is inconsistent across tables matched by the wildcard table, BigQuery returns an error.

这说明了您看到的两种情况！

This explains both case you see!

在第一种情况下-最新的表具有_PARTITIONTIME作为DATETIME数据类型
在第二种情况下-最近的同一张表只是其中没有source字段

In first case - the most recent table has _PARTITIONTIME as DATETIME data type
In second case - same most recent table just simply does not have source field in it

这篇关于BigQuery:无法读取类型为TIMESTAMP_MICROS的字段“戳记"为DATETIME的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！