使用Java Client加载GCS中CSV至BigQuery遇解析错误求助

错误信息 #

com.google.cloud.bigquery.BigQueryException: Error while reading data, error message: Error detected while parsing row starting at position: 178031. Error: Data between close double quote (") and field separator. File: gs://merch-meter/Merch-Meter-jira-issues-03-31-2023

已做处理 #

• 将所有NULL字符串替换为空字符串""
• 所有列用双引号包裹
• 处理了issueName字段中的逗号，替换为空格

CSV生成代码 #

public static String createCsvRecord(Issue issue) {
        String sprintstartdate = (issue.getSprintstartdate() == null) ? "" : DateUtil.getUtcDatetime(issue.getSprintstartdate());
        String sprintenddate = (issue.getSprintenddate() == null) ? "" : DateUtil.getUtcDatetime(issue.getSprintenddate());
        String createddate = (issue.getCreateddate() == null) ? "" : DateUtil.getUtcDatetime(issue.getCreateddate());
        String completeddate = (issue.getCompleteddate() == null) ? "" : DateUtil.getUtcDatetime(issue.getCompleteddate());
        String lastupdateddate = (issue.getLastupdateddate() == null) ? "" : DateUtil.getUtcDatetime(issue.getLastupdateddate());
        String issueName = Utils.escapeSpecialCharacters(issue.getIssueName());
        String parentId = StringUtils.isNotBlank(issue.getParentId())? issue.getParentId():"";
        String parentType = StringUtils.isNotBlank(issue.getParentType())? issue.getParentType():"";
        String lastUpdatedUser = StringUtils.isNotBlank(issue.getLastupdateduser()) &&
                !issue.getLastupdateduser().equalsIgnoreCase("null")? issue.getLastupdateduser():"";
        String labels = Utils.escapeSpecialCharacters(issue.getLabels());
        if(StringUtils.isNotBlank(issueName) && StringUtils.contains(issueName, ","))
            issueName = issueName.replace(",", " ");

        StringBuilder csvStringBuilder = new StringBuilder();
        csvStringBuilder.append("\"").append(issue.getTeamid()).append("\",");
        csvStringBuilder.append("\"").append(issue.getJiraboardid()).append("\",");
        csvStringBuilder.append("\"").append(issue.getIssueid()).append("\",");
        csvStringBuilder.append("\"").append(issue.getIssuetype()).append("\",");
        csvStringBuilder.append("\"").append(issue.getSprintId()).append("\",");
        csvStringBuilder.append("\"").append(issue.getStatus()).append("\",");
        csvStringBuilder.append("\"").append(createddate).append("\",");
        csvStringBuilder.append("\"").append(completeddate).append("\",");
        csvStringBuilder.append("\"").append(sprintstartdate).append("\",");
        csvStringBuilder.append("\"").append(sprintenddate).append("\",");
        csvStringBuilder.append("\"").append(lastUpdatedUser).append("\",");
        csvStringBuilder.append("\"").append(lastupdateddate).append("\",");
        csvStringBuilder.append("\"").append(issue.getIssuepoints().intValue()).append("\",");
        csvStringBuilder.append("\"").append(issue.getCreator_emailAddress()).append("\",");
        csvStringBuilder.append("\"").append(issue.getCreator_displayName()).append("\",");
        csvStringBuilder.append("\"").append(issue.getCreator_accountId()).append("\",");
        csvStringBuilder.append("\"").append(issue.isCreator_isActive()).append("\",");
        csvStringBuilder.append("\"").append(issue.getComponents()).append("\",");
        csvStringBuilder.append("\"").append(labels).append("\",");
        csvStringBuilder.append("\"").append(issueName).append("\",");
        csvStringBuilder.append("\"").append(parentId).append("\",");
        csvStringBuilder.append("\"").append(parentType).append("\",");
        csvStringBuilder.append("\"").append(issue.isDeleted()).append("\",");
        csvStringBuilder.append("\"").append(DateUtil.getUtcDatetime(new Date())).append("\"");
        csvStringBuilder.append("\n");
        return csvStringBuilder.toString();
    }

排查建议 #

• 检查字段内双引号转义：确认Utils.escapeSpecialCharacters方法是否将字段内的单个双引号"替换为两个双引号""——BigQuery解析CSV时，字段内的双引号必须用此方式转义，否则会导致解析器误判字段结束位置。
• 扩展特殊字符处理范围：目前仅issueName和labels调用了转义方法，其他字段（如creator_displayName、components）如果包含双引号或换行符，同样会触发解析错误，需要统一处理所有字段的特殊字符。
• 定位错误行内容：用GCS命令提取错误位置对应的行，直接分析内容：

  gsutil cat gs://merch-meter/Merch-Meter-jira-issues-03-31-2023 | head -n [错误行号] | tail -1
  

• 用BigQuery验证工具排查：执行带VALIDATE_ONLY参数的LOAD语句，获取更详细的错误细节：

  LOAD DATA INTO `your-project.your-dataset.your-table`
  FROM FILES (
    format = 'CSV',
    uris = ['gs://merch-meter/Merch-Meter-jira-issues-03-31-2023']
  )
  OPTIONS (
    skip_leading_rows = 0,
    quote = '"',
    field_delimiter = ',',
    validate_only = true
  );
  

内容的提问来源于stack exchange，提问作者gcpdev-guy