SQL Server stream into Elastic Search

Elastic Stack Elasticsearch
1

Sorry if this has been mentioned before

I am looking to stream in data from Microsoft SQL Server, I looked at Rivers and then found out it was deprecated, so wasted a little time.

So then I looked at what next, so now I see, use logstash. I have a simple configuration working and its importing data and scheduled to run at set intervals. Whilst this is good, its batch in nature.

Long term goal is for me to work with the data creators to insert a document into elastic when the event happens. However, in the short terms is there any solution outside of scheduled logstash jobs?

Thanks
Wayne

2

I have been using logstash jdbc driver to run SQL query and load data into elasticsearch.
Here is my logstash config file. Ran on Windows 7 just fine.
Hope it helps.

input {
jdbc {
jdbc_driver_library => "C:\Elastic\logstash-2.2.0\sqljdbc42.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://SQLDEVL01:1433;databaseName=AnalyticDB;user=elasticsearch;password=elasticsearch;"
jdbc_user => "elasticsearch"
jdbc_password => "elasticsearch"
statement => "select * from auth"
}
}
filter {
}
output {
elasticsearch {
hosts => "sqlprod01"
index => "auths"
document_type => "auth"
document_id => "%{authref}"
}
stdout { codec => rubydebug }
}

3

Hi JunWang, I understand this bit and have it working. The question here is it just scheduling this to run or is there an option outside of the deprecated rivers to auto-run this. I guess not? Without long term having people create the documents.

4

I think I would use JDBC driver logstash for initial data conversion. For ongoing more real time data synchronization I like to use SQL Server service broker. I am currently working on this as well but not finished yet.

5

You can try JDBC importer https://github.com/jprante/elasticsearch-jdbc

6

this might seem a strange response, but how is this different than using logstash jdbc?

7

It is older, written in Java, and has advanced features like auto GeoJSON and auto JSON detection, also hints for JDBC result set streaming http://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#setFetchSize-int- and other goodies like merging rows into JSON docs or automatic JDBC to JSON type conversions.

1 Like
8

Hi @jprante,

Is there any release of ES-jdbc for ES v5.0 ? (because I coudn't find it)