使用Logstash解析CSV文件时出现_csvparsefailure错误的解决咨询

问题详情 #

使用Logstash解析CSV文件时触发_csvparsefailure标签，尝试删除引号、移除回车后问题仍存在。相关信息如下：

配置文件 #

input {
  file {
    path => "c:/s/test.csv"
    start_position => "beginning"
    sincedb_path => "NUL"
  }
}

filter {
  mutate {
    gsub => [ "message", "\r", "" ]
  }
  csv {
    separator => ","
    quote_char => "\""
    columns => ["t", "i", "c", "d", "u", "u2", "p", "b", "m", "m2"]
  }
}

output {
  elasticsearch {
    cloud_id => "*" 
    api_key => "*"
    index => "lea"
  }
  stdout {
    codec => rubydebug
  }
}

样本CSV数据 #

"1","2","3","4","5","6","7","8","9","10"
"a1","a2","a3","a4","a5","a6","a7","a8","a9","a10"

错误输出 #

{
  "@timestamp" => 2024-04-23T07:48:54.097522500Z,
  "log" => {
    "file" => {
      "path" => "c:/s/test.csv"
    }
  },
  "tags" => [
    [0] "_csvparsefailure"
  ],
  "message" => "\"a1\",\"a2\",\"a3\",\"a4\",\"a5\",\"a6\",\"a7\",\"a8\",\"a9\",\"a10\"",
  "event" => {
    "original" => "\"a1\",\"a2\",\"a3\",\"a4\",\"a5\",\"a6\",\"a7\",\"a8\",\"a9\",\"a10\""
  },
  "@version" => "1",
  "host" => {
    "name" => "au"
  }
}

解决步骤 #

1. 优化换行符处理逻辑 #

Windows系统换行符为\r\n，仅替换\r可能残留异常格式。调整mutate插件的gsub规则，优先替换完整的Windows换行符：

mutate {
  gsub => [
    "message", "\r\n", "\n",
    "message", "\r", ""
  ]
}

2. 增强CSV插件解析兼容性 #

添加skip_empty_columns和allow_quoted_escapes参数，提升插件对带引号CSV的解析容错性：

csv {
  separator => ","
  quote_char => "\""
  columns => ["t", "i", "c", "d", "u", "u2", "p", "b", "m", "m2"]
  skip_empty_columns => true
  allow_quoted_escapes => true
}

3. 明确文件编码 #

在file输入插件中指定UTF-8字符集，避免编码异常导致的解析失败：

file {
  path => "c:/s/test.csv"
  start_position => "beginning"
  sincedb_path => "NUL"
  codec => plain { charset => "UTF-8" }
}

4. 完整修改后的配置 #

input {
  file {
    path => "c:/s/test.csv"
    start_position => "beginning"
    sincedb_path => "NUL"
    codec => plain { charset => "UTF-8" }
  }
}

filter {
  mutate {
    gsub => [
      "message", "\r\n", "\n",
      "message", "\r", ""
    ]
  }
  csv {
    separator => ","
    quote_char => "\""
    columns => ["t", "i", "c", "d", "u", "u2", "p", "b", "m", "m2"]
    skip_empty_columns => true
    allow_quoted_escapes => true
  }
}

output {
  elasticsearch {
    cloud_id => "*" 
    api_key => "*"
    index => "lea"
  }
  stdout {
    codec => rubydebug
  }
}

额外排查点 #

• 检查CSV文件是否存在BOM头：用文本编辑器将文件保存为无BOM的UTF-8格式
• 排查隐藏特殊字符：开启文本编辑器的「显示所有字符」功能，确认无不可见控制字符
• 测试自动列检测：临时将CSV插件的autodetect_column_names设为true，验证是否能正确识别字段数量

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