BigQuery自动检测与手动定义Schema的行为差异及解决方案

差异原因 #

自动检测Schema时，BigQuery会扫描数据样本，不仅将字段类型识别为TIMESTAMP，还会自动匹配时间字符串的格式，在外部表的CSV读取配置中添加了对应的timeFormat参数（比如适配"2022-05-14 08:00:00 Africa/Accra"的格式规则）。但手动定义Schema时，你只指定了字段类型为TIMESTAMP，没有配置这个关键的时间格式参数，BigQuery默认的TIMESTAMP解析逻辑不支持直接识别Africa/Accra这类时区标识符，因此触发报错。

手动定义Schema的解决方法 #

方法1：创建表时明确指定时间格式参数 #

在创建外部表的DDL中，除了手动编写Schema，还要在OPTIONS里添加timeFormat配置，匹配你的时间字符串格式：

CREATE OR REPLACE EXTERNAL TABLE `你的项目ID.你的数据集ID.目标表名`
(
  SMS_DT TIMESTAMP,
  -- 其他字段按实际需求定义
)
OPTIONS (
  format = 'CSV',
  uris = ['gs://你的存储桶路径/目标文件.csv'],
  timeFormat = '%Y-%m-%d %H:%M:%E3S %Z'
);

其中%Z用于匹配时区标识符，%E3S兼容带毫秒或不带毫秒的秒数格式。

方法2：先定义为STRING类型，查询时再转换 #

如果不想在表层面配置参数，可以先把SMS_DT设为STRING类型，查询时用PARSE_TIMESTAMP函数手动解析：

CREATE OR REPLACE EXTERNAL TABLE `你的项目ID.你的数据集ID.目标表名`
(
  SMS_DT STRING,
  -- 其他字段按实际需求定义
)
OPTIONS (
  format = 'CSV',
  uris = ['gs://你的存储桶路径/目标文件.csv']
);

-- 查询时转换为TIMESTAMP
SELECT PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%E3S %Z', SMS_DT) AS SMS_DT
FROM `你的项目ID.你的数据集ID.目标表名`;

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