Periodically indexing SQL data to ES using Logstash

[Mohammed_Sanaulla]

We want to index data from SQL into ES using Logstash. The data indexed in ES will be a nested object and the nested object has to be constructed by combining data from multiple rows i.e grouping based on some common value.

Can someone guide us in this direction?

[A_B]

Hi,

this seems more like a Logstash question than an Elasticsearch one...

I have not done anything like what you are looking to do but I'm going to reply anyway

There does not seem to be an sql input plugin for Logstash https://www.elastic.co/guide/en/logstash/current/input-plugins.html

With my limited knowledge I would write a script that would pull out the data you want from SQL and export as JSON. Filebeat or Logstash could then take that JSON and index it in ES. Or ship directly to ES... I would suggest using Logstash or Filebeat as they handle some errors and log quite well if there are issues.

-AB

[Mohammed_Sanaulla]

Thanks @A_B for the reply. My use case is a bit different:

• multiple rows from SQL have to be grouped into one JSON object before indexing to ES i.e as the SQL data is normalized with one to many mappings, I would want to denormalize and group all the one to many mappings or similar mappings into a parent object, where anything having one to many mappings would be come a JSON list in the parent object

If it was the case where each row from SQL was to be indexed as a JSON object into ES, then its straight forward.

[A_B]

I'm not suggesting the script would just take one row and export that... You could have a python (well choose what ever language) srcipt select the SQL data you want and construct JSON output that you can ship directly to a Logstash JSON input or have Filebeat ship if you export it as one JSON blob or document per line.

[Mohammed_Sanaulla]

I found an approach mentioned in logstash filter aggregate plugin here.

This exactly what I was looking for. And I tried it on a small set of rows and it works!!!