如何让BigQuery在JSON缺失属性时抛出错误？

问题场景 #

测试文件内容 #

Cloud Storage上存在以下两个测试文件：

• CSV文件 data-with-missing-column.csv：

first_column
A
B

• JSONL文件 data-with-missing-column.jsonl：

{"first_column":"A"}
{"first_column":"B"}

CSV加载行为（符合预期） #

执行以下SQL会抛出预期的列缺失错误：

CREATE OR REPLACE TEMPORARY TABLE test_table (
  first_column STRING,
  second_column STRING
);

LOAD DATA INTO TEMPORARY TABLE test_table (
  first_column STRING,
  second_column STRING
)
FROM FILES(
  format='CSV',
  uris = ['gs://bucket-name/data-with-missing-column.csv'],
  skip_leading_rows=1
);

错误信息：Invalid value: Error while reading data, error message: CSV table references column position 1, but line contains only 1 columns

JSON加载行为（不符合预期） #

但执行以下JSON加载SQL却能正常完成，不符合预期：

CREATE OR REPLACE TEMPORARY TABLE test_table (
  first_column STRING,
  second_column STRING
);

LOAD DATA INTO TEMPORARY TABLE test_table (
  first_column STRING,
  second_column STRING
)
FROM FILES(
  format='JSON',
  uris = ['gs://bucket-name/data-with-missing-column.jsonl']
);

预期只有当JSONL文件中**显式指定second_column: null**时才允许加载，例如：

{"first_column":"A","second_column":null}
{"first_column":"B","second_column":null}

限制条件 #

• 不能将second_column设置为非nullable类型
• 可接受用CSV加载数组/结构体类型列替代JSON的方案

解决方案 #

方案1：加载后校验数据，不符合则主动抛出错误 #

利用BigQuery的OPTIONS(skip_field=false)捕获原始JSON行，加载后校验每行是否显式包含目标字段：

CREATE OR REPLACE TEMPORARY TABLE test_table (
  first_column STRING,
  second_column STRING,
  raw_json STRING -- 保留原始JSON用于校验
);

LOAD DATA INTO TEMPORARY TABLE test_table (
  first_column STRING,
  second_column STRING,
  raw_json STRING OPTIONS(skip_field=false) -- 强制保留原始JSON行
)
FROM FILES(
  format='JSON',
  uris = ['gs://bucket-name/data-with-missing-column.jsonl']
);

-- 校验：统计未显式包含second_column的行数
DECLARE invalid_row_count INT64;
SET invalid_row_count = (
  SELECT COUNT(*)
  FROM test_table
  WHERE JSON_EXTRACT(raw_json, '$.second_column') IS NULL
);

-- 存在无效行则抛出错误
IF invalid_row_count > 0 THEN
  SIGNAL SQLSTATE '45000'
  SET MESSAGE = CONCAT('检测到 ', invalid_row_count, ' 行未显式指定second_column: null');
END IF;

-- 可选：校验完成后删除临时的raw_json列
ALTER TABLE test_table DROP COLUMN raw_json;

方案2：用CSV替代JSON，利用CSV的严格列校验 #

如果业务允许用CSV存储结构化数据，可以借助CSV的allow_jagged_rows=false参数实现严格列校验：

示例CSV文件（data-struct.csv）

first_column,second_column
A,null
B,null

加载脚本

CREATE OR REPLACE TEMPORARY TABLE test_table (
  first_column STRING,
  second_column STRING -- 若为结构体类型，可定义为second_column STRUCT<value STRING>
);

LOAD DATA INTO TEMPORARY TABLE test_table (
  first_column STRING,
  second_column STRING
)
FROM FILES(
  format='CSV',
  uris = ['gs://bucket-name/data-struct.csv'],
  skip_leading_rows=1,
  allow_jagged_rows=false -- 开启严格列数校验，缺失列直接报错
);

如果需要存储数组类型，可通过指定ARRAY_DELIMITER参数实现嵌套结构的严格校验，同样会在列缺失时触发错误。

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