BigQuery更新schema后使用_TABLE_SUFFIX查询报options字段不存在的问题

完成目标数据表的schema更新后，两类BigQuery查询的执行结果存在明显差异：

• 采用通配符表匹配、通过_TABLE_SUFFIX做表后缀过滤的查询，执行后返回报错：Field name options does not exist in STRUCT<normalisedId STRING, normalisedParentId STRING, questionType STRING, ...>，即引擎识别到的questionAnalysis结构中不存在options字段，对应查询SQL如下：

SELECT
questionAnalysisUnnest.normalisedId AS normalisedId,
FIRST_VALUE(questionAnalysisUnnest.questionText) OVER (PARTITION BY questionAnalysisUnnest.normalisedId ORDER BY formFilled.creationTime.milliSeconds DESC) AS latest_quesText,
FIRST_VALUE(quesOptionsUnnest.displayText) OVER (PARTITION BY questionAnalysisUnnest.normalisedId, quesOptionsUnnest.answerNo ORDER BY formFilled.creationTime.milliSeconds DESC) AS latest_optionText,
quesOptionsUnnest.answerNo AS ansNo
  FROM
`xxxxxxxxxxxxx.xxxxxxxxxx.1982701_*`
CROSS JOIN
UNNEST(questionAnalysis) AS questionAnalysisUnnest,
UNNEST(questionAnalysisUnnest.options) AS quesOptionsUnnest
 WHERE
_TABLE_SUFFIX IN ('2021_12')
AND formFilled.creationTime.milliSeconds BETWEEN 1616351400000
AND 1616956199999
AND formFilled.form.series.dbInfo.id IN ('HkDt!@ipfeedbackform')
AND IFNULL(questionAnalysisUnnest.questionType,
  'UNKNOWN_QUESTION_TYPE') IN ('CUSTOM_OPTIONS_TYPE'))

• 对照验证场景：移除_TABLE_SUFFIX过滤逻辑，在FROM子句中直接指定带完整后缀的目标表名xxxxxxxxxxx.xxxxxxxxxxx.1982701_2021_12，其余查询逻辑完全保持一致时，查询可正常执行无报错，对应SQL如下：

SELECT
questionAnalysisUnnest.normalisedId AS normalisedId,
FIRST_VALUE(questionAnalysisUnnest.questionText) OVER (PARTITION BY questionAnalysisUnnest.normalisedId ORDER BY formFilled.creationTime.milliSeconds DESC) AS latest_quesText,
FIRST_VALUE(quesOptionsUnnest.displayText) OVER (PARTITION BY questionAnalysisUnnest.normalisedId, quesOptionsUnnest.answerNo ORDER BY formFilled.creationTime.milliSeconds DESC) AS latest_optionText,
quesOptionsUnnest.answerNo AS ansNo
FROM
`xxxxxxxxxxx.xxxxxxxxxxx.1982701_2021_12`
CROSS JOIN
UNNEST(questionAnalysis) AS questionAnalysisUnnest,
UNNEST(questionAnalysisUnnest.options) AS quesOptionsUnnest
WHERE
formFilled.creationTime.milliSeconds BETWEEN 1616351400000
AND 1616956199999
AND formFilled.form.series.dbInfo.id IN ('HkDt!@ipfeedbackform')
AND IFNULL(questionAnalysisUnnest.questionType,
  'UNKNOWN_QUESTION_TYPE') IN ('CUSTOM_OPTIONS_TYPE'))

两类查询的结果差异本质来自BigQuery通配符表的schema解析规则，和很多开发者的直觉认知不同：

通配符表的schema推断发生在查询执行的最早期阶段，完全独立于WHERE子句中的_TABLE_SUFFIX过滤逻辑。引擎在解析FROM子句时，会先扫描所有匹配通配符前缀的表，收集这些表的结构并取公共交集，作为整个查询的基础schema；这一步完成后才会校验所有被引用的字段是否存在，最后才会应用WHERE条件做数据行过滤。

你在更新1982701_2021_12表的schema时，仅给这张表的questionAnalysis结构新增了options字段，但同通配符前缀下还存在其他未更新schema的历史表，这些表的questionAnalysis结构本身就没有options字段。引擎做通配符schema对齐时，取所有匹配表的结构交集，自然推断不出options字段的存在，直接触发字段不存在的报错。
而直接指定完整表名的查询，在schema解析阶段只加载这张更新过的目标表的结构，自然可以正常识别到options字段，查询顺利执行。

• 临时验证场景下，缩小通配符的匹配范围，确保通配符仅匹配到已经完成schema更新的表，避免扫描到未对齐的历史表。比如将宽泛的1982701_*前缀调整为仅覆盖schema更新后生成的新表的前缀，从根源上避免schema交集不包含目标字段的问题。
• 长期使用通配符匹配的场景，建议先对齐同前缀下所有历史表的schema，确保所有匹配通配符的表都补上options字段，让引擎推断出的公共结构可以覆盖所有查询需要的字段。
• 如果存在无法对齐schema的历史表，可放弃使用宽泛的大前缀通配符，采用逐表UNION ALL的方式拼接需要查询的目标表，这种写法下引擎会逐表加载对应schema，不会做跨表的结构交集，可完全规避通配符的schema推断问题，同时保留多表查询的能力。

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