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

作者:高景洋 日期:2021-05-15 06:51:17 浏览次数:613

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


2、mysql 中EnteredDate 字段为 DateTime 类型 ,例:2021-05-02 09:06:25


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

[2021-05-13T23:35:50,539][WARN ][logstash.outputs.elasticsearch] Could not index event to Elasticsearch. {:status=>400, :action=>["index", {:_id=>"100000436463", :_index=>"product_index", :_type=>"product", :_routing=>nil}, #<LogStash::Event:0x9d81a44>], :response=>{"index"=>{"_index"=>"product_index", "_type"=>"product", "_id"=>"100000436463", "status"=>400, "error"=>{"type"=>"mapper_parsing_exception", "reason"=>"failed to parse field [PriceUpdatedDate] of type [date] in document with id '100000436463'. Preview of field's value: '2021-05-02T00:50:54.000Z'", "caused_by"=>{"type"=>"illegal_argument_exception", "reason"=>"failed to parse date field [2021-05-02T00:50:54.000Z] with format [yyyy-MM-dd HH:mm:ss]", "caused_by"=>{"type"=>"date_time_parse_exception", "reason"=>"Text '2021-05-02T00:50:54.000Z' could not be parsed at index 10"}}}}}}


4、原因:mysql 中的数据格式为  2021-05-02 09:06:25

               通过LogStash导数据时 格式变为 2021-05-02T09:06:25.000Z

               而Es索引类型要求的格式为 :yyyy-MM-dd HH:mm:ss

               所以报错


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

     如:SELECT
DATE_FORMAT(PriceUpdatedDate,'%Y-%m-%d %H:%i:%S') AS PriceUpdatedDate,
DATE_FORMAT(UpdatedDate,'%Y-%m-%d %H:%i:%S') AS UpdatedDate,
DATE_FORMAT(EnteredDate,'%Y-%m-%d %H:%i:%S') AS EnteredDate
FROM
product1


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

本文永久性链接:
<a href="http://r4.com.cn/art188.aspx">LogStash 从mysql向es导数据 date_time_parse_exception could not be parsed at index</a>
apple手机报价 石材厂家 石材