ElasticSearch 5.3.2 data import from SQL Server 2012

Elastic Stack Elasticsearch
1

Directly from the example given on the site I am attempting to connect to a local default instance of SQL server 2012 on my development box and import data from an adventureworks2012 database and it fails. I tried port 9200 and then read somewhere that 9300 should be used. Any help is appreciated.

[15:00:44,875][INFO ][importer.jdbc ][pool-2-thread-1] strategy standard: settings = {elasticsearch.cluster=elasticsearch, elasticsearch.host=localhost, elasticsearch.port=9300, index=person, password=hKgWdopLnF&2, sql=SELECT BusinessEntityID as _id, BusinessEntityID, Title, FirstName, MiddleName, LastName FROM Person.Person, treat_binary_as_string=true, type=person, url=jdbc:sqlserver://localhost:1433;databaseName=AdventureWorks2012, user=ElasticSearch}, context = org.xbib.elasticsearch.jdbc.strategy.standard.StandardContext@66ff24cb
[15:00:44,890][INFO ][importer.jdbc.context.standard][pool-2-thread-1] found sink class org.xbib.elasticsearch.jdbc.strategy.standard.StandardSink@69eeee1f
[15:00:44,906][INFO ][importer.jdbc.context.standard][pool-2-thread-1] found source class org.xbib.elasticsearch.jdbc.strategy.standard.StandardSource@316c64c7
[15:00:44,968][INFO ][org.xbib.elasticsearch.helper.client.BaseTransportClient][pool-2-thread-1] creating transport client on Windows Server 2008 R2 Java HotSpot(TM) 64-Bit Server VM Oracle Corporation 1.8.0_131-b11 25.131-b11 with effective settings {autodiscover=false, client.transport.ignore_cluster_name=false, client.transport.nodes_sampler_interval=5s, client.transport.ping_timeout=5s, cluster.name=elasticsearch, flush_interval=5s, host.0=localhost, max_actions_per_request=10000, max_concurrent_requests=32, max_volume_per_request=10mb, name=importer, port=9300, sniff=false}
[15:00:45,000][INFO ][org.elasticsearch.plugins][pool-2-thread-1] [importer] modules [], plugins [helper], sites []
[15:00:45,826][INFO ][org.xbib.elasticsearch.helper.client.BaseTransportClient][pool-2-thread-1] trying to connect to [localhost/127.0.0.1:9300]
[15:00:45,936][INFO ][org.elasticsearch.org.xbib.elasticsearch.helper.client.TransportClient][pool-2-thread-1] [importer] failed to get node info for {#transport#-1}{127.0.0.1}{localhost/127.0.0.1:9300}, disconnecting...
org.elasticsearch.transport.NodeDisconnectedException: [][localhost/127.0.0.1:9300][cluster:monitor/nodes/liveness] disconnected
[15:00:45,936][ERROR][importer.jdbc ][pool-2-thread-1] error while processing request: no cluster nodes available, check settings {autodiscover=false, client.transport.ignore_cluster_name=false, client.transport.nodes_sampler_interval=5s, client.transport.ping_timeout=5s, cluster.name=elasticsearch, flush_interval=5s, host.0=localhost, max_actions_per_request=10000, max_concurrent_requests=32, max_volume_per_request=10mb, name=importer, port=9300, sniff=false}
org.elasticsearch.client.transport.NoNodeAvailableException: no cluster nodes available, check settings {autodiscover=false, client.transport.ignore_cluster_name=false, client.transport.nodes_sampler_interval=5s, client.transport.ping_timeout=5s, cluster.name=elasticsearch, flush_interval=5s, host.0=localhost, max_actions_per_request=10000, max_concurrent_requests=32, max_volume_per_request=10mb, name=importer, port=9300, sniff=false}
at org.xbib.elasticsearch.helper.client.BulkTransportClient.init(BulkTransportClient.java:164) ~[elasticsearch-helper-2.3.4.0.jar:?]
at org.xbib.elasticsearch.helper.client.ClientBuilder.toBulkTransportClient(ClientBuilder.java:113) ~[elasticsearch-helper-2.3.4.0.jar:?]
at org.xbib.elasticsearch.jdbc.strategy.standard.StandardSink.createClient(StandardSink.java:348) ~[elasticsearch-jdbc-2.3.4.1.jar:?]
at org.xbib.elasticsearch.jdbc.strategy.standard.StandardSink.beforeFetch(StandardSink.java:100) ~[elasticsearch-jdbc-2.3.4.1.jar:?]
at org.xbib.elasticsearch.jdbc.strategy.standard.StandardContext.beforeFetch(StandardContext.java:183) ~[elasticsearch-jdbc-2.3.4.1.jar:?]
at org.xbib.elasticsearch.jdbc.strategy.standard.StandardContext.execute(StandardContext.java:164) ~[elasticsearch-jdbc-2.3.4.1.jar:?]
at org.xbib.tools.JDBCImporter.process(JDBCImporter.java:203) ~[elasticsearch-jdbc-2.3.4.1.jar:?]
at org.xbib.tools.JDBCImporter.newRequest(JDBCImporter.java:189) [elasticsearch-jdbc-2.3.4.1.jar:?]
at org.xbib.tools.JDBCImporter.newRequest(JDBCImporter.java:53) [elasticsearch-jdbc-2.3.4.1.jar:?]
at org.xbib.pipeline.AbstractPipeline.call(AbstractPipeline.java:50) [elasticsearch-jdbc-2.3.4.1.jar:?]
at org.xbib.pipeline.AbstractPipeline.call(AbstractPipeline.java:16) [elasticsearch-jdbc-2.3.4.1.jar:?]
at java.util.concurrent.FutureTask.run(Unknown Source) [?:1.8.0_131]
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [?:1.8.0_131]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [?:1.8.0_131]
at java.lang.Thread.run(Unknown Source) [?:1.8.0_131]

2

This is a version mismatch, my JDBC importer 2.3.4.1 does not work with Elasticsearch 5.x

3

Thank you. Has something replaced it? Everywhere I look it points me to your jdbc importer to get data from a Microsoft sql server.

4

The JDBC importer is a spare time project, unfortunately it is lagging behind for over half a year now. I have started an adaption to 5.x a while ago but it got stuck in the middle of it because I'm too busy with other projects. I hope I can find time to continue.

5

Could you point me to an alternative method of getting data out of a Microsoft SQL Server. I am working with logstash but it too appears to be stalling a bit when pulling from SQL Server. Thank you

6

Hi,
I have the exact same issue. I am on Windows 7 and I am using elasticsearch 5.4.0 and JDBC Importer 2.3.4.1. Is there any alternative to get data from SQL Server?

7

@faamir I hope they eventually respond with an alternative for now I setup logstash which needs a bit of tweaking if you are going to import large amounts of data but its working so far.

8

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.