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.
Unzip the file and run bin/elasticsearch.bat to start up the server.
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.
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.
Create a new SQL Server authentication. For example, I use login name hintdesk and password 123456
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.
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.
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.
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
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.
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
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
7.2 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();
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 😉
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
Did you have solution? And can you support me.
Thanks
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) 🙁
Thanks for sharing your step by step aprroach. I will try and connect to elastic search with this as start.Great article . Well done
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.
https://www.elastic.co/products/logstash
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.
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}}]}}
how to pass stored procedure name in above batch file
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”:[]}}
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 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
@bharath: You have to wait until mssql-simple-example.bat finishes (the console disappears). Then the search will work.
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
@smruti: Just use the .bat file above and use another SQL query to create new index and type.
im using windows shal i use this bat file to configure
{“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”
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”:[]}}
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