将换行分隔JSON文件上传至BigQuery时遇Error Code:3求助

上传1.5GB换行分隔JSON文件至BigQuery时触发Error Code:3，错误提示JSON表遇过多错误后终止，具体指向第1127行（位置132601653）存在解析错误：解析器在字符串结束前终止。排除超时问题，同类任务可正常执行，以下是错误详情及对应行内容：

错误信息 #

jobStatus: {
errorResult: {
code: 3
message: "Error while reading data, error message: JSON table encountered too many errors, giving up. Rows: 1127; errors: 1. Please look into the errors[] collection for more details."
}
errors: [
0: {
code: 3
message: "Error while reading data, error message: JSON table encountered too many errors, giving up. Rows: 1127; errors: 1. Please look into the errors[] collection for more details."
}
1: {
code: 3
message: "Error while reading data, error message: JSON processing encountered too many errors, giving up. Rows: 1127; errors: 1; max bad: 0; error percent: 0"
}
2: {
code: 3
message: "Error while reading data, error message: JSON parsing error in row starting at position 132601653: Parser terminated before end of string"
}]
jobState: "DONE"

第1127行内容 #

{"id":"xxx","ContextId":"xxx","dueAt":"2023-01-26T05:59:59.000Z","title":"1/25 MATH: *LOCATION CHANGE* Live Session @ 9:40 -  ZEARN M4 L 20","aos":null,"aos_user":null,"aos_ass":null,"aos_work":null,"aos_assId":null,"c_id":"xxx","c_accountId":"xxx","c_enrollmentTermId":"xxx","c_name":"MATH 5 - xxx - 02","c_courseWorkflowState":"available","e_id":"xxx","e_courseId":"xxx","e_courseSectionId":"xxx","cs_sisSourceId":"xxx~xxx","u_id":"xxx","u_name":"xxx xxx","edd_assignmentId":null,"edd_userId":null,"edd_effectiveDueDate":null,"et_id":"xxx","et_name":"2022/2023 S1 S2","et_startAt":"2022-08-04T05:01:00.000Z","et_endAt":"2023-05-23T04:59:00.000Z","et_valueGradingPeriodGroupId":"xxx","acc_id":"xxx","acc_name":"Elementary Math","acc_parentAccount":"xxx","really_dueAT":"2023-01-26T05:59:59Z","YearWeek":"202304","s_id":"xxx","s_excused":"false","s_assignmentId":"xxx","s_gradingPeriodId":"xxx","s_submission_type":"basic_lti_launch","s_workflowState":"graded","s_attempt":"1"}

注：列名和数据已脱敏，结构保持不变。

排查思路 #

• 检查目标行隐藏字符：用VS Code开启显示控制字符功能，或通过cat -A 文件名命令查看原始内容，确认是否存在未转义的换行、制表符等破坏JSON结构的不可见字符，导致字符串未正常闭合。
• 验证JSON语法合法性：将该行内容复制到本地jq工具校验，执行echo '行内容' | jq .，检查是否存在未转义的引号、反斜杠等语法错误。
• 确认文件编码：用file -i 文件名查看文件编码，确保为UTF-8格式，非UTF-8编码需转码后重试。
• 调整加载容错参数：临时设置BigQuery加载任务的max_bad_records为大于0的值（如10），允许跳过少量错误行，同时收集更多错误信息，确认问题范围。
• 分段测试文件：将大文件拆分为多个小文件（比如每1000行一个文件），逐个上传测试，定位是否仅该行存在问题。
• 检查行分隔符：确认文件使用标准\n作为行分隔符，而非\r\n或其他非标准分隔符，避免解析器误判行边界。

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