logstash将数据传输到elasticsearch解析日期错误

Question

这是我的Mysql。配置

input {
    stdin {
    }
    jdbc {

    jdbc_connection_string => "jdbc:mysql://localhost:3306/xc_course?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC"
    # the user we wish to excute our statement as
    jdbc_user => "root"
    jdbc_password => "1234"
    # the path to our downloaded jdbc driver
    jdbc_driver_library =>"/usr/local/elasticsearch/logstash/mysql-connector-java-5.1.4.jar"
    # the name of the driver class for mysql
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_paging_enabled => "true"
    jdbc_page_size => "50000"
    #statement_filepath => "/conf/course.sql"
    statement => "select * from course_pub where timestamp > date_add(:sql_last_value,INTERVAL 8 HOUR)"
    schedule => "* * * * *"

    record_last_run => true

    last_run_metadata_path => "/usr/local/elasticsearch/elasticsearch-6.2.1/config/logstash_metadata"   

    }
}
output {
    elasticsearch {
    hosts => "localhost:9200"
    #hosts => ["localhost:9200","localhost:9202","localhost:9203"]
    index => "xc_course"
    document_id => "%{id}"
    document_type => "doc"
    template =>"/usr/local/elasticsearch/logstash-6.2.1/config/xc_course_template.json"
    template_name =>"xc_course"
    template_overwrite =>"true"

    }
    stdout {

        codec => json_lines

    }
}

这是我的模板。json

{
    "mappings": {
        "doc": {
            "properties": { 
                "charge": {
                    "type": "keyword"
                },
                "description": {
                    "analyzer": "ik_max_word",
                    "search_analyzer": "ik_smart",
                    "type": "text"
                },
                "end_time": {
                    "format": "yyyy‐MM‐dd HH:mm:ss",
                    "type": "date"
                },
                "expires": {
                    "format": "yyyy‐MM‐dd HH:mm:ss",
                    "type": "date"
                },
                "grade": {
                    "type": "keyword"
                },
                "id": {
                    "type": "keyword"
                },
                "mt": {
                    "type": "keyword"
                },
                "name": {
                    "analyzer": "ik_max_word",
                    "search_analyzer": "ik_smart",
                    "type": "text"
                },
                "pic": {
                    "index": false,
                    "type": "keyword"
                },
                "price": {
                    "type": "float"
                },
                "price_old": {
                    "type": "float"
                },
                "pub_time": {
                    "format": "yyyy‐MM‐dd HH:mm:ss",
                    "type": "date"
                },
                "qq": {
                    "index": false,
                    "type": "keyword"
                },
                "st": {
                    "type": "keyword"
                },
                "start_time": {
                    "format": "yyyy‐MM‐dd HH:mm:ss",
                    "type": "date"
                },
                "status": {
                    "type": "keyword"
                },
                "studymodel": {
                    "type": "keyword"
                },
                "teachmode": {
                    "type": "keyword"
                },
                "teachplan": {
                    "analyzer": "ik_max_word",
                    "search_analyzer": "ik_smart",
                    "type": "text"
                },
                "users": {
                    "index": false,
                    "type": "text"
                },
                "valid": {
                    "type": "keyword"
                }
            }
        }
    },
    "template": "xc_course"
}

这是我的elasticSearch索引映射

    {
        "properties": {
            "description": {
                "analyzer": "ik_max_word",
                "search_analyzer": "ik_smart",
                "type": "text"
            },
            "grade": {
                "type": "keyword"
            },
            "id": {
                "type": "keyword"
            },
            "mt": {
                "type": "keyword"
            },
            "name": {
                "analyzer": "ik_max_word",
                "search_analyzer": "ik_smart",
                "type": "text"
            },
            "users": {
                "index": false,
                "type": "text"
            },
            "charge": {
                "type": "keyword"
            },
            "valid": {
                "type": "keyword"
            },
            "pic": {
                "index": false,
                "type": "keyword"
            },
            "qq": {
                "index": false,
                "type": "keyword"
            },
            "price": {
                "type": "float"
            },
            "price_old": {
                "type": "float"
            },
            "st": {
                "type": "keyword"
            },
            "status": {
                "type": "keyword"
            },
            "studymodel": {
                "type": "keyword"
            },
            "teachmode": {
                "type": "keyword"
            },
            "teachplan": {
                "analyzer": "ik_max_word",
                "search_analyzer": "ik_smart",
                "type": "text"
            },
            "expires": {
                "type": "date",
                "format": "yyyy‐MM‐dd HH:mm:ss"
            },
            "pub_time": {
                "type": "date",
                "format": "yyyy‐MM‐dd HH:mm:ss"
            },
            "start_time": {
                "type": "date",
                "format": "yyyy‐MM‐dd HH:mm:ss"
            },
            "end_time": {
                "type": "date",
                "format": "yyyy‐MM‐dd HH:mm:ss"
            }
        }
    }

但当它开始的时候

无法将事件索引到Elasticsearch.{： state=

解析[start_time]"、"caused_by"=失败

**msgstr"格式无效：\"2019-12-20T15:18:13.000Z\"在\"-12-20T15:18:13.000Z\"**

但是在我的数据库中，我的数据类型是2019-12-28 19:24:41

当我将date设置为null时，它的工作方式是，但就像2019-12-28 19:24:41一样，解析错误

那么怎么处理呢

Answer 1

jdbc输入将自动将日期列更改为LogStash::Timestamp类型，但索引模板希望它们是文本。从索引模板中的日期字段中删除“格式”。