将Google Sheets导入BigQuery时的TIMESTAMP字段问题

问题详情 #

尝试将大型Google Sheets文件导入BigQuery时，TIMESTAMP字段始终报错。试过转换多种格式、导入时排除该列，甚至手动定义Schema跳过自动检测，都无法解决。

错误提示如下：

while reading table: kostore-402516.KO.Order_Details, error message: Could not convert value to timestamp. Error: Could not parse '9/20/2023 15:38:17' as a timestamp. Required format is YYYY-MM-DD HH:MM[:SS[.SSSSSS]] or YYYY/MM/DD HH:MM[:SS[.SSSSSS]]. Row 11513; Col 43. File: 1vU1jivBNm0WFz0TMKDmCo3LONp2itNlmj-cWn8ZSFXc

相关截图说明：

• 错误截图：显示BigQuery导入时的时间戳解析错误弹窗
• 问题列截图：展示Google Sheets中格式为MM/DD/YYYY HH:MM:SS的目标时间列
• Schema截图：显示BigQuery中手动定义的Schema结构，目标字段类型设为TIMESTAMP

解决方案 #

方法1：在Google Sheets中直接转换格式 #

直接把Sheets里的时间列改成BigQuery兼容的格式：

1. 在原时间列右侧插入新列
2. 在新列首行输入公式：=TEXT(原时间单元格, "yyyy-mm-dd hh:mm:ss")（比如原列是A列，就写=TEXT(A1, "yyyy-mm-dd hh:mm:ss")），下拉填充整列
3. 删除原时间列，把新列重命名为原列名，再重新导入BigQuery

方法2：加载时指定时间解析格式（推荐） #

不用修改Sheets，直接在BigQuery加载阶段自定义解析规则：

1. 加载数据时选择「自定义Schema」，将目标字段类型设为TIMESTAMP
2. 展开该字段的高级选项，在时间格式中输入%m/%d/%Y %H:%M:%S（对应你数据里的9/20/2023 15:38:17格式）
3. 确认Schema后继续加载，BigQuery会自动按指定格式解析时间戳

方法3：先导入为字符串，再SQL转换 #

如果前两种方法行不通，先绕开时间解析，导入后再转换：

1. 加载数据时，把目标字段类型设为STRING，成功导入后执行以下SQL：

-- 新增TIMESTAMP类型列
ALTER TABLE `kostore-402516.KO.Order_Details`
ADD COLUMN new_timestamp TIMESTAMP;

-- 转换字符串为时间戳
UPDATE `kostore-402516.KO.Order_Details`
SET new_timestamp = PARSE_TIMESTAMP("%m/%d/%Y %H:%M:%S", 原时间列名)
WHERE 原时间列名 IS NOT NULL;

-- 验证后删除原字符串列
ALTER TABLE `kostore-402516.KO.Order_Details`
DROP COLUMN 原时间列名;

-- 重命名新列为原列名
ALTER TABLE `kostore-402516.KO.Order_Details`
RENAME COLUMN new_timestamp TO 原时间列名;

关于「排除列仍报错」的处理 #

如果排除列后还是报错，大概率是BigQuery缓存了之前的Schema或数据检测结果，解决方法：

• 新建一个全新的BigQuery表，不要复用之前的表
• 加载时彻底关闭「自动检测Schema」，手动勾选需要导入的列

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