How to sync MSSQL to Elasticsearch?

user_7199922 0
Viewed 16057

Every time I Google this, I find the "river" approach which is deprecated. I'm using Dapper if this is somehow a helpful information.

So what's the solution for this these days?

elasticsearch-net nest dapper sql-server-2008 c#
5 Answers

I've come across this post multiple times and feel it needs an updated answer.

For shipping data from a mssql instance into Elasticsearch I use Logstash which is inherent to the ELK stack. You define individual pipe lines and configurations using the jdbc input plug in.

Here is an example config file. This runs a stored procedure every 2 minutes and inserts the data into the correct index. Keep in mind to provide some method to only sync new records of data otherwise you'll have a scaling issue when data becomes large. 

input {  
    jdbc {
        jdbc_connection_string => "jdbc:sqlserver://${sql_server};database=api;user=<username>;password=<password>;applicationname=logstash"
        # The user we want to execute our statement as
        jdbc_user => nil
        id => "index_name"
        jdbc_driver_library => "/var/tmp/mssql-jdbc-6.2.2.jre8.jar"
        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
        schedule => "*/2 * * * *"
        statement => "exec stored_procedure"
        lowercase_column_names => false

    }

}


output {
    elasticsearch {
        "id" => "index_name"
        "hosts" => "elasticsearch:9200"
        "index" => "index_name"
        "document_id" => "%{primary_key}" 

    }

}

`

user_44075280
Related