BigQuery中DATE类型列含非常规格式值的查询报错求助

问题根源 #

你的表schema将date列定义为DATE类型，但实际存储的数据格式是yyyy-mm-dd-??（带额外后缀），不符合DATE类型的标准格式（yyyy-mm-dd）。BigQuery在执行查询前会先按照schema解析数据，所以无论你用CAST还是字符串截取，都会在数据解析阶段触发报错。

可行解决方案 #

方案1：修改外部表的schema（适用于外部表） #

如果你的表是基于GCS/S3等存储的外部表，直接修改外部表的schema，将date列类型改为STRING：

ALTER TABLE `project.dataset.your_table`
ALTER COLUMN date SET DATA TYPE STRING;

修改完成后，即可正常查询并处理该列：

-- 转为标准DATE类型
SELECT SAFE.PARSE_DATE('%Y-%m-%d', LEFT(date, 10)) AS valid_date,
       date AS original_date_str
FROM `project.dataset.your_table`;

方案2：导出数据后重新导入（适用于原生表） #

如果是BigQuery原生表，需要先导出数据，再以STRING类型重新导入：

1. 导出原表数据到云存储：

bq extract --destination_format CSV `project.dataset.original_table` gs://your-bucket/exported_data.csv

2. 创建新表，指定date列为STRING类型：

# 手动指定schema，替换为你的实际列定义
bq load --source_format CSV `project.dataset.new_table` gs://your-bucket/exported_data.csv date:STRING,col1:INT64,col2:STRING,...

3. 查询新表并处理日期：

SELECT SAFE.PARSE_DATE('%Y-%m-%d', LEFT(date, 10)) AS valid_date,
       date AS original_date_str
FROM `project.dataset.new_table`;

方案3：创建临时外部表读取原始数据（通用方法） #

无论原表类型如何，都可以创建临时外部表指向原始数据存储，以STRING类型读取date列：

-- 创建临时外部表，根据你的原始数据格式调整OPTIONS（比如JSON/CSV）
CREATE OR REPLACE TEMP EXTERNAL TABLE temp_date_fix_table (
  date STRING,
  -- 替换为你的其他列定义
  col1 INT64,
  col2 STRING
)
OPTIONS (
  format = 'CSV',
  uris = ['gs://your-bucket/path/to/original_data.csv'],
  skip_leading_rows = 1 -- 如果有表头的话
);

-- 查询并处理日期
SELECT SAFE.PARSE_DATE('%Y-%m-%d', LEFT(date, 10)) AS valid_date,
       date AS original_date_str,
       col1, col2
FROM temp_date_fix_table;

注意事项 #

• SAFE.PARSE_DATE会自动忽略解析失败的行，避免查询中断；如果需要排查错误数据，可以用PARSE_DATE结合WHERE子句筛选出格式异常的记录。
• 如果原表是分区表，重新导入时需要注意分区字段的设置，避免数据分区异常。

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