BigQuery中字段jsonPayload.page.result_count.total类型变更后跨时段查询问题

核心原因 #

你的问题出在同一字段在不同时间分区存储了不同数据类型——2022/09/09前是STRING，之后改为FLOAT，BigQuery查询时无法自动兼容两种类型，导致报错。下面是三种可行的解决方法：

方法1：查询时按时段转换类型（临时解决） #

直接在查询语句里用CASE结合时间分区区分处理，用SAFE_CAST避免转换失败中断查询：

SELECT
  -- 保留你需要的其他字段
  CASE
    WHEN DATE(_PARTITIONTIME) < '2022-09-09' THEN SAFE_CAST(jsonPayload.page.result_count.total AS FLOAT64)
    ELSE jsonPayload.page.result_count.total
  END AS result_count_total
FROM
  `你的项目ID.你的数据集ID.你的表名`
-- 可按需添加WHERE过滤条件

SAFE_CAST会把无法转换的STRING值转为NULL，不会导致整个查询失败。

方法2：创建视图封装转换逻辑（永久解决） #

如果需要长期用这个字段，创建一个视图来统一处理类型，之后直接查视图即可：

CREATE OR REPLACE VIEW `你的项目ID.你的数据集ID.统一类型视图` AS
SELECT
  *,
  CASE
    WHEN DATE(_PARTITIONTIME) < '2022-09-09' THEN SAFE_CAST(jsonPayload.page.result_count.total AS FLOAT64)
    ELSE jsonPayload.page.result_count.total
  END AS result_count_total
FROM
  `你的项目ID.你的数据集ID.你的表名`

后续查询视图时，直接调用result_count_total就不用再写转换逻辑。

方法3：处理格式不规范的STRING数据（进阶场景） #

如果历史STRING字段里有非数字字符（比如逗号、单位符号），可以先清理再转换：

SELECT
  CASE
    WHEN DATE(_PARTITIONTIME) < '2022-09-09' THEN PARSE_FLOAT(REGEXP_REPLACE(jsonPayload.page.result_count.total, r'[^0-9.]', ''))
    ELSE jsonPayload.page.result_count.total
  END AS result_count_total
FROM
  `你的项目ID.你的数据集ID.你的表名`

REGEXP_REPLACE会去掉所有非数字和小数点的字符，确保转换成功率。

内容的提问来源于stack exchange，提问作者nextdoordoc