Indexing Data In Bulk From SQL Server

Jorg, It is Apache 2.

I'm facing a similar challenge in that I need to get a few tables from an existing relational database into Elasticsearch. Basically it's customer data (parent) and two related tables (transactions and contact information) being used in an existing application that needs a speedup when searching through those tables.

I managed to ingest the tables into Elasticsearch with logstash using the JDBC input plugin and the elasticsearch output plugin, hoping that I would be able to benefit from the parent-child feature in Elasticsearch. Unfortunately, the elasticsearch output plugin in logstash is missing the ability to pass along a "parent_id" parameter when ingesting the child tables, which would be essential to properly hook up the ingested child tables with the parent table in Elasticsearch. In other words, it's currently not possible to set the _parent property properly in Elasticsearch using logstash.

As an alternative, I'm considering just putting the related tables into the main one as nested objects. However, I am at loss how this could be done using out-of-the-box logstash features. I imagine that there should be a way to ingest the main table with logstash and maybe add the relevant data from the other two tables with some sort of lookup function that would created said nested objects.

I would be really greatful if some of the gurus in this area would kindly share some hints on how to proceed.

Please start your own thread.

@jprante We are using JDBC drivers to import data and it's awesome. Now I have 11 bat files to import data from diff. tables from diff. database and it's taking a good amount of time (10-12 seconds each bat file) executing all of them separate. However, if I am running all those scripts together in SQL management studio, it takes 2 seconds max. So wanted to merge those all scripts in one procedure, it will return me different datasets in one execution. Is there anyway, I can call single stored procedure returning multiple result sets and store each result set to diff. elastic type within the same index?

@jprante, Does this JDBC Importer work with ElastisSearch 5.2?
I am facing several issues trying to get data from MS SQL server.

TIA
JnB

@jnb JDBC importer is not ready yet for ES 5.x, I need to find some time to update (and add features).
Out of curiosity, what issues do you encounter?

Your JDBC Importer is the best feature for me. Since I need to connect to a MS SQL Server and query some data.
But now that JDBC Importer is still being updated, is there a better way to connect to any MS SQ Server to query a table and import that data in to ES?