Logstash从MySQL取timestamp失败转字符串正常？求原因及优化方案

问题背景 #

尝试用Logstash JDBC输入同步数据时，想将数据库的timestamp字段覆盖Elasticsearch的@timestamp，但出现_dateparsefailure标签，配置及结果如下：

失败配置 #

input {
    jdbc {
        jdbc_driver_library => "C:/path/to/mariadb-java-client.jar"
        statement => "SELECT '${FIELD}' as field, from ${TABLE_NAME}"
        tracking_column => "timestamp"
        tracking_column_type => "timestamp" 
    }
}

filter {
    grok {
        match => ["timestamp","%{TIMESTAMP_ISO8601}"]
    }
    date {
        match => ["timestamp", "ISO8601"]
    }
}

注：无论是否添加grok过滤器，结果一致。

失败结果 #

{
    "@timestamp" => 2022-12-13T09:16:10.365Z,
    "timestamp" => 2022-11-23T10:36:13.000Z,              
    "@version" => "1",
    "tags" => [
        [0] "_dateparsefailure"
    ],
    "type" => "mytype",
}

改用临时字段中转后，解析成功：

成功配置 #

input {
    *same input*
}

filter {
    grok {
        match => ["timestamp","%{TIMESTAMP_ISO8601:tmp}"]
        tag_on_failure => [ "_grokparsefailure"]
    }
    date {
        match => ["tmp", "ISO8601"]
    }
}

成功结果 #

{
    "@timestamp" => 2022-11-23T11:16:36.000Z,
    "@version" => "1",
    "timestamp" => 2022-11-23T11:16:36.000Z,
    "tmp" => "2022-11-23T11:16:36.000Z",
}

原因分析 #

1. JDBC输入插件会自动将数据库的timestamp类型字段解析为Logstash内部日期对象，而非字符串。
2. Logstash的date过滤器仅支持解析字符串类型字段，传入日期对象时会直接解析失败，触发_dateparsefailure标签。
3. 临时字段方案中，grok过滤器将日期对象转换为字符串存入tmp，date过滤器可正常处理该字符串，因此解析成功。

无临时字段解决方案 #

方案1：直接复用日期对象（推荐） #

因为JDBC已完成日期解析，直接将timestamp字段值复制到@timestamp即可，无需二次解析：

filter {
    mutate {
        copy => { "timestamp" => "@timestamp" }
    }
}

方案2：转字符串后解析 #

若需保留date过滤器的逻辑，先将日期对象转为字符串再解析：

filter {
    mutate {
        convert => { "timestamp" => "string" }
    }
    date {
        match => ["timestamp", "ISO8601"]
        target => "@timestamp"
    }
}

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