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();

13 thoughts on “How to connect ElasticSearch to MS SQL server?”

  1. 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 😉

  2. 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

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

  4. 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.

  5. 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.

  6. 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}}]}}

  7. 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”:[]}}

  8. 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”:[]}}

  9. 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

Leave a Reply

Your email address will not be published. Required fields are marked *