Logstash date conversion from string to date not working

Elastic Stack Logstash
1

Hi,
I am a beginner in the elastic stack. I have a CSV file, contents of which I want to load into elastic search. In the CSV there is a "date" column which has dates in the following format:

2/27/2018
M/d/yyyy

I am trying to convert the string date values to date type but failed. I have searched on the net a lot, but still having the same problem going on.

Here is my config file:-

        input {
      file {
        path => "/Users/ahir.chattopadhyay/AAPL.csv"
        start_position => "beginning"
    	sincedb_path => "nul"
    	type => "csv"
      }
    }
    filter {  
      if ([message] =~ "\bDate\b") {
            drop { }
        } else {
    		csv {
    		  separator => ","
    		  columns => ["Date","Open","High","Low","Close","Volume","Adj Close"]
    		}
    		date {
    			match => [ "date", "M/d/yyyy" ]
    			target => "date"
    			locale => "en"
    		}
    	  mutate {
    		convert => {
    		  "High" => "float"
    		  "Open" => "float"
    		  "Low" => "float"
    		  "Close" => "float"
    		  "Volume" => "float"
    		}
    	  }
    	}
    }
    output {  
        elasticsearch {
            action => "index"
            hosts => "localhost:9200"
            index => "stock_data"
            workers => 1
        }
        stdout {}
    }

While running it from cmd, I am getting following error:

[logstash.outputs.elasticsearch] Could not index event to Elasticsearch. {:status=>400, :action=>["index", {:_id=>nil, :_index=>"stock_data", :_type=>"doc", :_routing=>nil}, #<LogStash::Event:0x201f7c23>], :response=>{"index"=>{"_index"=>"stock_data", "_type"=>"doc", "_id"=>"9hquHmIBX6AI3pdfu73f", "status"=>400, "error"=>{"type"=>"mapper_parsing_exception", "reason"=>"failed to parse [Date]", "caused_by"=>{"type"=>"illegal_argument_exception", "reason"=>"Invalid format: \"3/5/2018\" is malformed at \"/5/2018\""}}}}}

I think the error is related to date format.
Any help would be appreciated.

2

27 is two digits so you need "dd" rather than "d" in your date pattern.

If you also have single-digits day-of-month numbers (i.e. leading zeroes aren't used) you need to specify multiple date patterns:

match => ["date", "M/d/yyyy", "M/dd/yyyy", "MM/d/yyyy", "MM/dd/yyyy"]
1 Like
3

I've modified the config file as per your suggestions. Now the error is not coming but the date type is still getting stored as string in elastic

4

It's working. I had mistakenly written "date" - the column name - in lower case, but actually, the 'D' is in upper case. Thanks a lot.

1 Like
5

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.