BigQuery使用_TABLE_SUFFIX查通配表嵌套bid列报错的原因及解决方法

BigQuery对通配符表的Schema校验存在固定执行逻辑：

• 执行带auction_*这类通配符的查询时，服务端不会提前遍历所有匹配到的分片表、合并生成全量统一Schema
• 服务端会选取匹配范围内的某一个分片表的Schema，作为整个通配查询的唯一参考Schema，完成所有字段名的解析、校验

你遇到的unrecognized name: bid at [2:15]报错，本质是被选做基准Schema的那份分片表，本身缺失bid这个嵌套字段：

• 单查auction_20210125时，表结构和查询要求完全匹配，因此可以正常展开bid.*嵌套字段
• 跨20210122-20210125范围做通配查询时，基准Schema来自缺bid字段的分片，服务端在解析bid.*语句时，基于基准Schema找不到这个字段，直接抛出校验错误，流程不会进入实际数据读取环节

根据你的实际表结构运维情况，可选择对应方案落地：

• 方案1：统一所有分片的Schema结构
  排查匹配范围内所有auction_YYYYMMDD分片的表结构，对缺失bid嵌套字段的历史分片，通过ALTER TABLE ADD COLUMN语句补全和其他分片完全一致的bid结构（历史无数据的字段值可默认为空），结构统一后原通配查询可直接正常执行。
• 方案2：加字段安全解析逻辑，规避Schema校验拦截
  不对历史表结构做修改，在嵌套字段展开时使用BigQuery的SAFE.前缀做兜底解析，字段不存在时自动返回空值，不会触发校验报错：

  WITH auction_union AS (
      SELECT 
        *,
        SAFE.bid.*
      FROM `auction_*`
      WHERE _TABLE_SUFFIX BETWEEN '20210122' AND '20210125'
  )
  SELECT *
  FROM auction_union
  LIMIT 1000
  

• 方案3：分片显式拼接，绕开通配符Schema校验
  如果跨分片的bid字段结构存在不兼容差异（比如部分分片bid是单结构体、部分分片是结构体数组），可通过逐表UNION ALL的方式显式拼接查询，每个子查询单独适配对应分片的Schema，避免被通配符的统一Schema规则拦截：

  WITH auction_union AS (
      SELECT * FROM `project.dataset.auction_20210122` UNION ALL
      SELECT * FROM `project.dataset.auction_20210123` UNION ALL
      -- 对缺bid字段的分片，显式补全空值bid结构匹配整体Schema
      SELECT * REPLACE(NULL AS bid) FROM `project.dataset.auction_20210124` UNION ALL
      SELECT * FROM `project.dataset.auction_20210125`
  )
  SELECT *
  FROM auction_union
  LIMIT 1000
  

注意：通配符查询不会自动兼容跨分片的Schema差异，只要被选做基准Schema的分片缺失查询字段，哪怕其他绝大多数分片都存在该字段，依然会抛出字段不存在的错误。

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