如何在BigQuery中对比硬编码值与表数据、表元数据？

嘿，这两个BigQuery里的对比问题我太熟了，分情况给你讲明白：

具体实现要看你是想检查表中是否存在某个硬编码值，还是找出硬编码集合和表数据的差异，两种场景的写法不同：

• 单值对比（检查表中是否有匹配的记录）：
  直接用WHERE子句过滤就行，非常直观：

SELECT *
FROM `your-project.your-dataset.your-table`
WHERE target_column = '你的硬编码值';

• 多值集合对比（找出两边的差异）：
  如果是一组硬编码值，推荐用CTE（WITH子句）先构造硬编码数据集，再用EXCEPT或者JOIN来对比：

-- 示例：找出硬编码值里有，但表中没有的条目
WITH hardcoded_values AS (
  SELECT '值A' AS target_value UNION ALL
  SELECT '值B' UNION ALL
  SELECT '值C'
)
SELECT target_value
FROM hardcoded_values
EXCEPT DISTINCT
SELECT target_column
FROM `your-project.your-dataset.your-table`;

要是想反过来，找表中有但硬编码里没有的内容，只要调换EXCEPT两边的查询顺序就行。

你给出的逻辑完全可行，我帮你优化一下写法，再补充点细节：

首先，我们可以用CTE把硬编码的预期列定义得更清晰，然后和BigQuery的元数据表对比：

WITH expected_schema AS (
  SELECT 'complaint_type' AS column_name, 'STRING' AS data_type UNION ALL
  SELECT 'complaint_description', 'STRING'
)
SELECT es.column_name, es.data_type
FROM expected_schema es
EXCEPT DISTINCT
SELECT column_name, data_type
FROM `bigquery-public-data.austin_311.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS`

关键细节说明： #

• EXCEPT DISTINCT会返回预期中有，但实际表元数据里不存在的（列名+数据类型）组合，帮你快速发现缺失的列或者类型不匹配的情况。
• 如果你只需要检查顶层列（没有嵌套字段的情况），更推荐用INFORMATION_SCHEMA.COLUMNS代替COLUMN_FIELD_PATHS，因为后者会包含所有嵌套层级的字段路径（比如user.address.street这种），而前者只返回表的顶层列，结果更精准。

扩展：找出实际表中有但预期没有的列 #

要是想反过来排查表中多出来的列，只要调换EXCEPT两边的查询即可：

WITH expected_schema AS (
  SELECT 'complaint_type' AS column_name, 'STRING' AS data_type UNION ALL
  SELECT 'complaint_description', 'STRING'
)
SELECT column_name, data_type
FROM `bigquery-public-data.austin_311.INFORMATION_SCHEMA.COLUMNS`
EXCEPT DISTINCT
SELECT es.column_name, es.data_type
FROM expected_schema es;

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