String to Date while loading data from csv file to elasticsearch using logstash


(Teddy) #1

I'm using the apple stock as sample data.
Here I have Date field which is being changed to string type when loading the data.

the format of date in csv file is "dd-MM-YYY"

I want to load this Date as a 'date type' to my index.

please help!!

This is my config file

input{

file{

	path => ["/home/apple.csv"]

	start_position => "beginning"

}

}

filter {

date {
 match => ["Date", "dd-MM-YYYY"]

}

  csv {

  separator => ","

  columns => ["Date","Open","High","Low","Close","Volume","Adj Close"]

}

mutate {convert => ["High", "float"]}

mutate {convert => ["Open", "float"]}

mutate {convert => ["Low", "float"]}

mutate {convert => ["Close", "float"]}

mutate {convert => ["Volume", "float"]}

}

output {

elasticsearch {

    action => "index"

    hosts => "localhost"

    index => "stock"

    workers => 1

}

stdout {}

}


#2

@devil_srj7: is this the complete logstash config? Can you please add how you are getting the date as string in Date field?

Irrespective of that you need to mind that elastic search works on timestamp fields to build time series data so your date should preferably also have a time part.

Can you provide a sample of date & data you are dealing with.


(Christian Dahlqvist) #3

You must have the date filter after the csv filter as the field otherwise does not exist when the date filter tries to access it.


(Teddy) #4

The data that I have!!

Date,Open,High,Low,Close,Adj Close,Volume
05-09-2000,4.475443,4.580357,4.446429,4.459821,4.012305,74660600
06-09-2000,4.383929,4.455357,4.125,4.174107,3.75526,88851000
07-09-2000,4.223214,4.46875,4.160714,4.428571,3.98419,54366200
08-09-2000,4.401786,4.401786,4.178571,4.205357,3.783375,48879600
11-09-2000,4.191964,4.3125,4.151786,4.174107,3.75526,46845400
12-09-2000,4.095979,4.290179,4.071429,4.125,3.711082,46999400
13-09-2000,4.053571,4.25,4.053571,4.142857,3.727146,76496000
14-09-2000,4.183036,4.258929,4.058036,4.061378,3.653844,106638000
15-09-2000,4.125,4.15625,3.875,3.945307,3.54942,98628600
18-09-2000,3.946429,4.339286,3.933036,4.332586,3.897837,106134000
19-09-2000,4.267857,4.321429,4.183036,4.28125,3.851651,67877600
20-09-2000,4.2433,4.388393,4.183036,4.360486,3.922938,56847000
21-09-2000,4.178571,4.258929,3.946429,4.049107,3.642803,127622600
22-09-2000,3.59375,3.745536,3.571429,3.727679,3.353629,181675200
25-09-2000,3.767857,3.964286,3.71875,3.821429,3.437971,108887800
26-09-2000,3.808036,3.910714,3.669643,3.674107,3.305432,72734200
27-09-2000,3.696429,3.767857,3.446429,3.495536,3.14478,100564800
28-09-2000,3.522321,3.84375,3.4375,3.821429,3.437971,244896400
29-09-2000,2.013393,2.071429,1.8125,1.839286,1.654725,1855410200
02-10-2000,1.90625,1.910714,1.678571,1.732143,1.558333,606197200
03-10-2000,1.78125,1.785714,1.584821,1.59375,1.433827,509530000
04-10-2000,1.598214,1.696429,1.5625,1.6875,1.518169,366506000
05-10-2000,1.678571,1.75,1.571429,1.575893,1.417761,218251600
06-10-2000,1.620536,1.638393,1.5,1.584821,1.425794,153164200
09-10-2000,1.616071,1.633929,1.508929,1.553571,1.39768,149391200
10-10-2000,1.544643,1.602679,1.464286,1.491071,1.341451,172775400
11-10-2000,1.4375,1.5,1.366071,1.401786,1.261125,299605600
12-10-2000,1.450893,1.486607,1.392857,1.428571,1.285223,297766000


(Teddy) #5

Thanks it worked !!:+1::+1::+1:


(system) #6

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