How to connect ElasticSearch to MS SQL server?

As part of my training with ElasticSearch, I would like to connect ElasticSearch to MS SQL server to pre-fill in data for searching. The installation and configuration are not complex but I would like to write down all steps here so that I can look up later. Maybe this post is also interesting and helpful for you. I’m using Windows 10 so all steps written here, are for Windows.

0. Book

For who starts to learn Elasticsearch, I warmly recommend the book Elasticsearch: The Definitive Guide. It’s very good book for learning Elasticsearch.

1. Java JDK

If you have already installed Java JDK 8 then you can skip this step. Otherwise, install the latest Java JDK from the following link

http://www.oracle.com/technetwork/java/javase/downloads/index.html

Elasticsearch based on Java, be sure that you have Java SDK on your computer before going to next step.

2. Elasticsearch

Installation of Elasticsearch is pretty simple. It’s just download and extract. Download the latest version of ElasticSearch at the following link.

Elasticsearch download

Unzip the file and run bin/elasticsearch.bat to start up the server.

ElasticSearch StartUp

After the server gets started, browse to http://localhost:9200/ to check if the server works. If you see a similar as the image below that means your server works.

ElasticSearch StartUp

3. MS SQL Server

I’m using MS SQL Express 2016 and AdventureWorks2014 as the sample database. Be sure that your MS SQL Server allows SQL authentication.

SQL Authentication

Create a new SQL Server authentication. For example, I use login name hintdesk and password 123456

SQL Authentication

And I give the account the sysadmin role. DON’T do this like I do, give the account only the permission to the required database. I give my test account the highest role only to make a demo simpler.

SQL Authentication

Start SQL Server Configuration Manager (for MS SQL Express 2016, the app is found at following path C:\Windows\SysWOW64\SQLServerManager13.msc), enable TCP/IP protocol.

TCP/IP Enabled

Write down the port which your SQL Express uses for accepting incoming connections (just double click on the setting of TCP/IP and the go to tab IP Addresses), you’ll need it for connection string later.

SQL Server Port

4. Elasticsearch – Plugin JDBC

We have now Elasticsearch and MS SQL Server installed. We’ll connect them together through plugin JDBC for Elasticsearch. Go to JDBC repository and get the latest version (elasticsearch-jdbc-x.x.x.x-dist.zip)

http://xbib.org/repository/org/xbib/elasticsearch/importer/elasticsearch-jdbc/

Unpack the .zip file to bin folder of Elasticsearch

JDBC Plugin

To make JDBC work with MS SQL server, you’ll need MS SQL Driver for it. You can the driver at the following link

Microsoft JDBC Drivers 6.0, 4.2, 4.1, and 4.0 for SQL Server

Copy files of the driver to lib folder of JDBC plugin.

Microsoft JDBC Drivers

5. Index data from MS SQL to Elasticsearch

Now you have installed everything you need. The last thing is the script to index data from MS SQL to Elasticsearch.
Under bin folder of JDBC plugin, create a new mssql-simple-example.bat script with the following content

@echo off

set DIR=%~dp0
set LIB=%DIR%..\lib\*
set BIN=%DIR%..\bin

REM ???
echo {^
"type" : "jdbc",^
"jdbc" : {^
"url" : "jdbc:sqlserver://localhost:25488;instanceName=SQLEXPRESS;databaseName=AdventureWorks2014",^
"user" : "hintdesk",^
"password" : "123456",^
"sql" : "SELECT BusinessEntityID as _id, BusinessEntityID, Title, FirstName, MiddleName, LastName FROM Person.Person",^
"treat_binary_as_string" : true,^
"elasticsearch" : {^
"cluster" : "elasticsearch",^
"host" : "localhost",^
"port" : 9200^
},^
"index" : "person",^
"type" : "person"^
}^
}^ | "%JAVA_HOME%\bin\java" -cp "%LIB%" -Dlog4j.configurationFile="%BIN%\log4j2.xml" "org.xbib.tools.Runner" "org.xbib.tools.JDBCImporter"

6. Enterprise search

Now start Elasticsearch by running elasticsearch.bat and then run mssql-simple-example.bat script above. After everything finishes (I mean the Elasticsearch server starts and the index script finishes). Launch your browser, try to make a simple search http://localhost:9200/_search?q=bruce

Microsoft JDBC Drivers
If you have something similar to the image above, Elasticsearch is now “connecting” with your MS SQL server and enables you to search through your database with his power.

7. FAQ

7.1 Delete index

Delete Index

7.2 List mapping

List mapping

7.3 Search with C#

const string ESServer = "http://localhost:9200";
ConnectionSettings settings = new ConnectionSettings(new Uri(ESServer));
settings.DefaultIndex("person");
settings.MapDefaultTypeNames(map => map.Add(typeof(Person), "person"));

ElasticClient client = new ElasticClient(settings);

var response = client.Search<Person>(s =>
	s.Query(q => q.Term(t => t.LastName, "ashe")));

var result = response.Documents.ToList();
if (result.Count == 0)
	Console.WriteLine("No person found");
foreach (var person in result)
{
	Console.WriteLine(person.FirstName + " " + person.LastName);
}
Console.ReadLine();

20 comments

s11 says:

December 4, 2016 at 6:57 pm

I had some trouble connecting to the elastic node.
So I had to change the port in the “mssql-simple-example.bat ” from 9200 to 9300 and it just works 😉

Reply
Aravind says:

January 27, 2017 at 10:11 am

I followed all steps and when I ran the BATCH file, I am getting below errors:
==========================================================
{“type” : “jdbc”,^
‘”jdbc”‘ is not recognized as an internal or external command,
operable program or batch file.
‘”user”‘ is not recognized as an internal or external command,
operable program or batch file.
‘”sql”‘ is not recognized as an internal or external command,
operable program or batch file.
‘”elasticsearch”‘ is not recognized as an internal or external command,
operable program or batch file.
‘”host”‘ is not recognized as an internal or external command,
operable program or batch file.
‘}’ is not recognized as an internal or external command,
operable program or batch file.
‘”type”‘ is not recognized as an internal or external command,
operable program or batch file.
==========================================================
Looks like someone else is also facing similar error as mentioned here: https://github.com/jprante/elasticsearch-jdbc/issues/447

Any help is highly appreciated.

Thanks

Reply
1. Hung says:
  
  March 27, 2019 at 4:48 am
  
  Did you have solution? And can you support me.
  Thanks
  
  Reply
Aravind says:

January 28, 2017 at 5:28 am

Found solution for my problem.
As mentioned here, https://discuss.elastic.co/t/moving-data-from-sql-server-2012-to-elasticsearch/70683/2

issue is related to the batch file which was written in Unix style. After making changes as mentioned in the above link, I could successfully run the batch file but I am not able to see any results (I had already started the elasticsearch service) 🙁

Reply
Michael says:

May 24, 2017 at 10:57 pm

Thanks for sharing your step by step aprroach. I will try and connect to elastic search with this as start.Great article . Well done

Reply
Koen says:

June 21, 2017 at 8:32 am

Hi there. I keep getting the result below:
{“took”:0,”timed_out”:false,”_shards”:{“total”:0,”successful”:0,”failed”:0},”hits”:{“total”:0,”max_score”:0.0,”hits”:[]}}

Does anyone have a clue what could be going wrong? Thanks in advance.

Reply
Daniel says:

June 28, 2017 at 4:22 am

https://www.elastic.co/products/logstash

Reply
Ines says:

July 5, 2017 at 10:41 am

I followed all steps but i keep getting an error when i wrote this url http://localhost:9200/Capteur
Note : (Capteur is the name of my table in SQL Server)
I have kept the user “sa” and port “1433”

root_cause
0
type “index_not_found_exception”
reason “no such index”
resource.type “index_or_alias”
resource.id “Capteur”
index_uuid “_na_”
index “Capteur”
type “index_not_found_exception”
reason “no such index”
resource.type “index_or_alias”
resource.id “Capteur”
index_uuid “_na_”
index “Capteur”
status 404
Does anyone have a clue what could be going wrong?
Thanks in advance.

Reply
shi says:

August 13, 2017 at 4:59 pm

I followed all steps but i keep getting the following everytime I Launch the browser to do a search:

{“took”:0,”timed_out”:false,”_shards”:{“total”:1,”successful”:1,”failed”:0},”hits”:{“total”:1,”max_score”:1.0,”hits”:[{“_index”:”.kibana”,”_type”:”config”,”_id”:”5.5.1″,”_score”:1.0,”_source”:{“buildNum”:15405}}]}}

Reply
Nikunj Jivani says:

August 22, 2017 at 3:43 am

how to pass stored procedure name in above batch file

Reply
Senthamizh Selvan R says:

September 13, 2017 at 3:25 am

I followed all steps but I got this output only
{“took”:56,”timed_out”:false,”_shards”:{“total”:15,”successful”:15,”failed”:0},”hits”:{“total”:0,”max_score”:null,”hits”:[]}}

Reply
bharath says:

September 27, 2017 at 10:05 pm

I followed all steps but i keep getting the following everytime I Launch the browser to do a search:
http://localhost:9200/_search?q=bruce

{“took”:0,”timed_out”:false,”_shards”:{“total”:0,”successful”:0,”skipped”:0,”failed”:0},”hits”:{“total”:0,”max_score”:0.0,”hits”:[]}}

Reply
bharath says:

September 27, 2017 at 10:06 pm

I followed all steps but i keep getting the following everytime I Launch the browser to do a search:
http://localhost:9200/_search?q=bruce

{“took”:0,”timed_out”:false,”_shards”:{“total”:0,”successful”:0,”skipped”:0,”failed”:0},”hits”:{“total”:0,”max_score”:0.0,”hits”:[]}}

i have not got the output . guide me if i did the mistakes

Reply
admin says:

October 6, 2017 at 12:39 pm

@bharath: You have to wait until mssql-simple-example.bat finishes (the console disappears). Then the search will work.

Reply
smruti says:

October 31, 2017 at 3:35 am

I want to execute different sql queries with different data to create different nodes by connecting same data base.Not able to add multiple nodes.Need help

Reply
admin says:

November 11, 2017 at 12:17 am

@smruti: Just use the .bat file above and use another SQL query to create new index and type.

Reply
Divyak says:

November 13, 2017 at 10:36 pm

im using windows shal i use this bat file to configure

Reply
Divyak says:

November 13, 2017 at 11:42 pm

{“took”:1,”timed_out”:false,”_shards”:{“total”:0,”successful”:0,”failed”:0},”hits”:{“total”:0,”max_score”:0.0,”hits”:[]}}

I got this output only please any one help me

my code:

@echo off
set DIR=%~dp0
set LIB=%DIR%..lib*
set BIN=%DIR%..bin

REM ???
echo {^
“type” : “jdbc”,^
“jdbc” : {^
“url” : “jdbc:sqlserver://192.14.541/Ssme2012;instanceName=SQLEXPRESS;databaseName=ESHUN”,^
“user” : “ghud”,^
“password” : “ghud”,^
“sql” : “select Top 10 Name, Area, City from details.Person”,^
“treat_binary_as_string” : true,^
“elasticsearch” : {^
“cluster” : “elasticsearch”,^
“host” : “localhost”,^
“port” : 9200^
},^
“index” : “person”,^
“type” : “person”^
}^
}^ | “%JAVA_HOME%binjava” -cp “%LIB%” -Dlog4j.configurationFile=”%BIN%log4j2.xml” “org.xbib.tools.Runner” “org.xbib.tools.JDBCImporter”
}^ | “%JAVA_HOME%binjava” -cp “%LIB%” -Dlog4j.configurationFile=”%BIN%log4j2.xml” “org.xbib.tools.Runner” “org.xbib.tools.JDBCImporter”

Reply
pratik says:

January 2, 2019 at 4:18 am

http://localhost:9200/_search?q=bruce

{“took”:13,”timed_out”:false,”_shards”:{“total”:0,”successful”:0,”skipped”:0,”failed”:0},”hits”:{“total”:0,”max_score”:0.0,”hits”:[]}}

Reply
pratik says:

January 2, 2019 at 4:19 am

mssql-simple-example.bat

@echo off

set DIR=%~dp0
set LIB=%DIR%..\lib\*
set BIN=%DIR%..\bin

REM ???
echo {^
“type” : “jdbc”,^
“jdbc” : {^
“url” : “jdbc:sqlserver://server;database=indianclothstore;user=sa;password=Admin@123”,^
“user” : “”,^
“password” : “”,^
“sql” : “select *, uniqueid as _id from productmaster”,^
“treat_binary_as_string” : true,^
“elasticsearch” : {^
“cluster” : “elasticsearch”,^
“host” : “localhost”,^
“port” : 9200^
},^
“index” : “metawiki”^
}^
}^ | “%JAVA_HOME%\bin\java” -cp “%LIB%” -Dlog4j.configurationFile=”%BIN%\log4j2.xml” “org.xbib.tools.Runner” “org.xbib.tools.JDBCImporter”

no out put please help me

Reply