LogStash 从mysql向es导数据 date_time_parse_exception could not be parsed at index

1、Es 索引类型中有个字段 permissiondate  date类型 format :yyyy-mm-dd

2、mysql 中permission_date 字段为 date类型 ，例：2021-05-02

3、通过 LogStash 从mysql 向 Es 中导数据时，报如下错误

[2022-09-14T14:15:04,827][WARN ][logstash.outputs.elasticsearch][main][90de522c9959b03fa6af43a808fd4e974b06a7c80c9f43b252e9b57c7d9959cf] Could not index event to Elasticsearch. {:status=>400, :action=>[“index”, {:_id=>nil, :_index=>”patentinfo”, :routing=>nil}, {“patenteecity”=>”赖旁”, “@version”=>”1”, “abstracts”=>”Multi-layered demand-driven complexity”, “publicno”=>”CNutilize visionary vortals”, “permissiondate”=>1982-12-19T16:00:00.000Z, “patenteeprovince”=>”马鞍山”, “applicationdate”=>2002-12-18T16:00:00.000Z, “applicationno”=>”CN1104460”, “publicdate”=>1964-07-22T16:00:00.000Z, “locarno”=>”94-64”, “title”=>”Clouds of Witness”, “@timestamp”=>2022-09-14T06:15:03.108Z, “agency”=>”朱, 方 and 金”, “patentee”=>”曾峻熙”, “locarnosubdivision”=>”33-46-65”, “permissionno”=>”CN699037.0”, “imgpath”=>nil, “agent”=>”建辉.潘”, “patenteeaddress”=>”Apt. 850 曹街1号, 上海, 豫 723706”, “designer”=>”苏浩然”, “pdfpath”=>nil}], :response=>{“index”=>{“_index”=>”patentinfo”, “_type”=>”_doc”, “_id”=>”Iz-jOoMBQcCVuGudAPtx”, “status”=>400, “error”=>{“type”=>”mapper_parsing_exception”, “reason”=>”failed to parse field [permissiondate] of type [date] in document with id ‘Iz-jOoMBQcCVuGudAPtx’. Preview of field’s value: ‘1982-12-19T16:00:00.000Z'”, “caused_by”=>{“type”=>”illegal_argument_exception”, “reason”=>”failed to parse date field [1982-12-19T16:00:00.000Z] with format [yyyy-MM-dd]”, “caused_by”=>{“type”=>”date_time_parse_exception“, “reason”=>”date_time_parse_exception: Text ‘1982-12-19T16:00:00.000Z’ could not be parsed, unparsed text found at index 10”}}}}}}

4、原因：mysql 中的数据格式为  1982-12-19

               通过LogStash导数据时 格式变为 1982-12-19T16:00:00.000Z

               而Es索引类型要求的格式为 ：yyyy-MM-dd

               所以报错

5、处理方法：在LogStash jdbc.sql 中 写的查询sql时，增加 Data_Format 操作

     如：SELECT
DATE_FORMAT(publicdate,’%Y-%m-%d %H:%i:%S’) AS publicdate,
DATE_FORMAT(applicationdate,’%Y-%m-%d %H:%i:%S’) AS applicationdate,
DATE_FORMAT(permissiondate,’%Y-%m-%d %H:%i:%S’) AS permissiondate
FROM
table

处理好后，重新运行LogStash脚本，一切正常