Trying to import a stock price file into Elastic

[sharon1]

Goodday,

I am a newbie and still learning.
Am trying to import a stock price file into Elastic.

Spent some time to setup a Logstash config file, but didn't succeed.

This is the format of the stock price file:
01/08/2001,15:45,1255.50,1257.00,1251.50,1255.25,2099,0
01/08/2001,16:00,1254.25,1256.50,1248.25,1253.25,2227,0
01/08/2001,16:15,1253.25,1259.00,1248.25,1250.00,2642,0
01/08/2001,16:30,1249.75,1253.25,1248.25,1251.25,1791,0
01/08/2001,16:45,1251.50,1258.75,1251.25,1255.50,1726,0

Date format: mm/dd/yyyy
Time format: HH:mm
The other fields are: opening price, highest price of the day, lowest price of the day, closing price, volume, open interest

Please help me out. Should I use a Logstash config file or can I use some plugin to make it easier for me?

Thanks very much for your help.

Kind regards,
Sharon

[ericohtake]

Hi Sharon,

I'd approach the grok plugin in the filter. A quick look into the pattern, you could start with something like this:

%{DATE_US},%{HOUR}:%{MINUTE},%{NUMBER:price1},%{NUMBER:price2},%{NUMBER:price3},%{NUMBER:price4},%{NUMBER:price5}

There are many awesome examples here: https://www.elastic.co/guide/en/logstash/current/plugins-filters-grok.html

You create a config file as you already did, and pass your data through the filter using the great GROK plugin to parse it before sending them to ES.

Lastly you can use this nice web app to test out your dataset against a grok pattern: https://grokdebug.herokuapp.com/

[guyboertje]

The CSV filter will be as good, see https://www.elastic.co/guide/en/logstash/current/plugins-filters-csv.html#plugins-filters-csv-columns

If the format of the file is exactly the same for every line and you want to be a bit adventurous you can try the dissect filter, https://www.elastic.co/guide/en/logstash/current/plugins-filters-dissect.html
Here is a starter example.

input {
  generator {
    lines => [
      "01/08/2001,15:45,1255.50,1257.00,1251.50,1255.25,2099,0",
      "01/08/2001,16:00,1254.25,1256.50,1248.25,1253.25,2227,0",
      "01/08/2001,16:15,1253.25,1259.00,1248.25,1250.00,2642,0"
    ]
    count => 3
  }
}

filter {
  dissect {
    mapping => {
      message => "%{date},%{time},%{opening_price},%{highest_price_of_the_day},%{lowest_price_of_the_day},%{closing_price},%{volume},%{open_interest}"
    }
    add_field => { datetime => "%{[date]} %{[time]}" }
    convert_datatype => {
      opening_price => "float"
      highest_price_of_the_day => "float"
      lowest_price_of_the_day => "float"
      closing_price => "float"
      volume => "int"
      open_interest => "int"
    }
  }
}

output {
  stdout {
    codec => rubydebug
  }
}

After you are happy with the shape of the events that you see in the terminal, you can replace the generator input with the file input and the stdout output (+ codec) with the elasticsearch output.

[sharon1]

Thanks for your help ericohtake and guyboertje!