4

I am newbie with elasticsearch(ES) and logstash. I have successfully setup elasticsearch and kibana using this link. After this I am trying to upload one table from sql server into ES using logstash. My logstash conf file is given below:

input {
  jdbc {
  clean_run => true
    jdbc_driver_library => "/home/myusr/Downloads/sqljdbc_6.0/enu/jre8/sqljdbc42.jar"
    jdbc_connection_string => "jdbc:sqlserver://xyz;databaseName=test;user=name;password=pass@123;"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_validate_connection => true
    jdbc_user => "name"
    jdbc_password => "pass@123"
    statement => "SELECT top 10 * FROM mytable"
  }
}

output {
  elasticsearch {
    hosts => ["localhost:9200"]
    index => "my_table"
 }

}

On running this file I get:

[ERROR] 2021-07-16 18:13:55.567 [[main]<jdbc] jdbc - Unable to connect to database. Tried 1 times {:message=>"Java::JavaLang::NoClassDefFoundError: javax/xml/bind/DatatypeConverter", :exception=>Sequel::DatabaseConnectionError, :cause=>java.lang.NoClassDefFoundError: javax/xml/bind/DatatypeConverter, :backtrace=>["com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(com/microsoft/sqlserver/jdbc/SQLServerConnection.java:4098)"

I have tried reinstallaing java8 after removing java11 version as suggested here. But it didn't work. I am not using any gradle or IDE to do --add-modules to fix the issue. I'm following up this link but there's no solution as of yet. I have hit a roadblock with this. I have also checked this link. The only step that I didn't do here is the "Create Elasticsearch index with mapping". Is that the cause of the problem? How do I fix this issue?

Any help is appreciated.

Eswar
  • 1,201
  • 19
  • 45

1 Answers1

2

I found the solution to the question.

If you see the above links, they show sqljdbc42.jar as the JDBC SQL Server driver whose path must be kept in the jdbc_driver_library. This is the driver available on the Microsoft website as tar.gz. However, when I changed the driver to 'mssql-jdbc-9.2.1.jre11.jar' in this link(the driver in the zip file), it worked. I also changed the jdbc_driver_library in the Logstash file as given in this link.

The updated Logstash conf file looked like:

input {
  jdbc {
  clean_run => true
    jdbc_driver_library => ""
    jdbc_connection_string => "jdbc:sqlserver://xyz;databaseName=test;user=name;password=pass@123;"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_validate_connection => true
    jdbc_user => "name"
    jdbc_password => "pass@123"
    statement => "SELECT top 10 * FROM mytable"
  }
}

output {
  elasticsearch {
    hosts => ["localhost:9200"]
    index => "my_table"
 }
}

Please note that the jdbc_driver_library is assigned to an empty string. This can be done because we've set the path for the JDBC driver file such that it can be located by the Logstash. Apart from setting the path as given here, I have also copied my jar file to this location: /usr/share/logstash/logstash-core/lib/jars/ for Logstash to find the driver easily.

Using the above, I was able to connect to migrate data from SQL server to elastic search successfully.

Eswar
  • 1,201
  • 19
  • 45