BigQuery无法识别CSV中成对双引号问题求助

问题原因 #

你遇到的问题确实和BigQuery的CSV解析规则直接相关，同时你的CSV格式也不符合ISO标准的完整要求：

• ISO CSV标准规定：若字段包含双引号、逗号或换行符，必须用双引号包裹整个字段，且字段内的双引号需用两个连续双引号转义。
• 你的原CSV中，第三个字段开头是""Occurence""，但整个字段并未用双引号包裹（即字段末尾没有对应的闭合双引号）。BigQuery默认的CSV解析逻辑是：看到单独的双引号就判定为字段起始，直到找到下一个双引号作为字段结束。这就导致它误将""当成一个完整字段，后面的is spelled incorrectly on the ticket summaries被视为新列，最终因列数不匹配报错。
• 你说添加一对引号后能正常解析，本质是让整个字段被双引号包裹了（比如变成"""Occurence"" is spelled..."），此时BigQuery会把内部的""解析为字面量双引号，符合转义规则。

解决方法 #

根据你对CSV格式控制权限有限的情况，提供两种可行方案：

方案1：调整BigQuery外部表解析配置 #

如果无法修改CSV文件，可通过自定义外部表的CSV参数来适配：

• 手动拆分字段（推荐）：
  1. 创建外部表时，将field_delimiter设为一个CSV中不存在的字符（比如|），让BigQuery把每一行当成单个字段读取。
  2. 使用正则表达式函数手动拆分字段，处理带双引号的场景。例如：

     SELECT
       REGEXP_EXTRACT_ALL(row, r'(?:"([^"]*)"|([^,]*))')[OFFSET(0)] AS ticket_id,
       REGEXP_EXTRACT_ALL(row, r'(?:"([^"]*)"|([^,]*))')[OFFSET(1)] AS date,
       REGEXP_REPLACE(REGEXP_EXTRACT_ALL(row, r'(?:"([^"]*)"|([^,]*))')[OFFSET(2)], r'""', '"') AS summary,
       REGEXP_EXTRACT_ALL(row, r'(?:"([^"]*)"|([^,]*))')[OFFSET(3)] AS action,
       REGEXP_EXTRACT_ALL(row, r'(?:"([^"]*)"|([^,]*))')[OFFSET(4)] AS status
     FROM `your-project.your-dataset.your-external-table`
     

     这个正则会匹配带双引号包裹的字段或普通字段，之后再把字段内的""替换为"。

方案2：修改CSV格式（若有机会） #

如果能调整CSV生成逻辑，按照标准格式修改：

• 将包含双引号的字段用双引号完整包裹，内部双引号用两个转义。例如原字段：

  ""Occurence"" is spelled incorrectly on the ticket summaries
  

  修改为：

  """Occurence"" is spelled incorrectly on the ticket summaries"
  

  （外层双引号包裹整个字段，内部的""保留作为转义的字面量双引号）

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