BigQuery处理impression_share列<0.1特殊值转float/decimal问题咨询

问题根本原因 #

你遇到的报错发生在BigQuery读取表数据的阶段，早于你写的SAFE_CAST函数执行时机：daily_transfer表的schema声明impression_share为FLOAT类型，但底层从Google Sheet同步来的数据里存在< 0.1这类字符串值，BigQuery在读取数据时会先尝试将原始值按照表声明的FLOAT类型转换，转换失败直接抛出错误，根本不会执行你查询语句里的自定义逻辑。

最优解决方案 #

优先选择修改daily_transfer表schema的方案，长期兼容性最高，操作步骤如下：

1. 重建/修改daily_transfer表结构，将impression_share字段的类型调整为STRING。后续从Google Sheet同步数据时，所有值包括< 0.1都会以字符串形式正常存储，不会触发读取阶段的类型错误。
2. 同步数据到master表时，用以下逻辑完成字符串清洗和类型转换即可：

SELECT
  -- 其余字段直接正常读取即可
  SAFE_CAST(
    CASE
      WHEN TRIM(impression_share) = '< 0.1' THEN '0.1'
      -- 如果后续出现其他类似阈值字符串，可在此处新增分支处理
      ELSE impression_share
    END AS FLOAT64
  ) AS impression_share
FROM `data-studio-reporting.analytics.daily_transfer`

处理后的FLOAT类型值和master表原有字段完全兼容，可以直接写入。

可选替代方案（无需修改现有表结构） #

如果不想调整daily_transfer的现有schema，可以修改Google Sheet到BigQuery的同步加载配置：

• 在加载作业的高级设置中，将impression_share列的加载类型指定为STRING，同时开启不良数据的跳过/兼容模式，加载阶段就完成类型兼容处理，后续查询逻辑和上面一致即可。

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