google-bigquery - BigQuery - UNION ALL 的类型不兼容？ - 使我抓狂

Question

以下是我的查询的简化版本。 Debug 表中的 DebugReason 属于 INTEGER 类型，DebugData 属于 STRING 类型。 GPS 表中没有这两个字段，所以我用所有 NULL 伪造了它。为什么我需要这样做的原因与这个问题无关，长话短说，我在以下过程中需要它

 WITH RawDebug as 
(
  SELECT 
  STRUCT(DebugReason,DebugData) as Debug
  FROM `devicedata.Debug.T*`
  WHERE _TABLE_SUFFIX="20180624"

),
RawGPS AS (        
       SELECT
          STRUCT(null as DebugReason,null as DebugData) as Debug
        FROM
          `devicedata.Gps.T*` AS g
         WHERE _TABLE_SUFFIX="20180624"

)
select Debug
from RawDebug
UNION ALL
select Debug
from RawGPS

BigQuery 说:

Error: Column 1 in UNION ALL has incompatible types: STRUCT<DebugReason INT64, DebugData STRING>, STRUCT<DebugReason INT64, DebugData INT64> at [18:1]

我不知道出了什么问题...以及如何改正？

Answer 1

在联合中的一个查询中，DebugData 字段的类型为 STRING，而在另一个查询中，它的类型为 INT64。如果你想要一个一致的类型，你需要强制转换:

WITH RawDebug as 
(
  SELECT 
  STRUCT(DebugReason,DebugData) as Debug
  FROM `devicedata.Debug.T*`

),
RawGPS AS (        
       SELECT
          STRUCT(
            null as DebugReason,
            CAST(DebugData AS STRING) AS DebugData -- note the cast
          ) as Debug
        FROM
          `devicedata.Gps.T*` AS g

)
select Debug
from RawDebug
UNION ALL
select Debug
from RawGPS

这是因为 NULL 的默认类型是 INT64。