logstash transfer data to elasticsearch Parsing date error

Question

this is my Mysql.config

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

    }
}

this is my template.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"
}

this is my elasticSearch index_mapping

    {
        "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"
            }
        }
    }

but when it start

Could not index event to Elasticsearch. {:status=>400, :action=>["index", {:_id=>"4028e58161bd3b380161bd3bcd2f0000", :_index=>"xc_course", :_type=>"doc", :_routing=>nil}, #LogStash::Event:0x62e0a8f9], :response=>{"index"=>{"_index"=>"xc_course", "_type"=>"doc", "_id"=>"4028e58161bd3b380161bd3bcd2f0000", "status"=>400, "error"=>{"type"=>"mapper_parsing_exception", "reason"=>"failed to parse [start_time]", "caused_by"=>{"type"=>"illegal_argument_exception", "reason"=>"Invalid format: \"2019-12-20T15:18:13.000Z\" is malformed at \"-12-20T15:18:13.000Z\""}}}}}

failed to parse [start_time]", "caused_by"=>{"type"=>"illegal_argument_exception"

** "Invalid format: \"2019-12-20T15:18:13.000Z\" is malformed at \"-12-20T15:18:13.000Z\""**

but inmy data base my data type is this 2019-12-28 19:24:41

when i make date to null ,its worke ,but like 2019-12-28 19:24:41 would Parsing error

so how to deal it

I see you are not filtering anything instead you are pulling data and pushing it up to ES. Do you mind some how printing one document before you try to put it in ES and paste here? that would help — JBone

Badger Badger · Accepted Answer · 2019-12-29T14:24:48

The jdbc input will automatically change date columns to LogStash::Timestamp type, but your index template expects them to be text. Remove the "format" from the date fields in your index template.

logstash transfer data to elasticsearch Parsing date error

1 Answers