SQL Server stream into Elastic Search


(Wayne Taylor) #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


(Jun Wang) #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 }
}


(Wayne Taylor) #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.


(Jun Wang) #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.


(Jörg Prante) #5

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


(Wayne Taylor) #6

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


(Jörg Prante) #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.


(Endrit Bytyqi) #8

Hi @jprante,

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


(system) #9