Elastic Search on Windows with MySQL & SQL Server 2014

Setting up Elastic Search

1. Download and install jdk
http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html

2. Set JAVA_HOME

set windows environment variable

 

3. Download Elastic Search

https://www.elastic.co/downloads/elasticsearch (choose zip file)

4. Extract zip file and into your C: drive
In my case servers directory

5. Install Elastic Search  as Windows Service

Run command:

c:\servers\elasticsearch-{version}bin>service install

 

Install Elastic Search as Windows Service

6. Start service and test it

Start Windows Service

 

and run http://localhost:9200 in your browser

Run Elastic Search

 

Connecting  Elastic Search to Database

1. Download JDBC plugin from here:

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

For this tutorial I used:
http://xbib.org/repository/org/xbib/elasticsearch/importer/elasticsearch-jdbc/1.7.0.1/elasticsearch-jdbc-1.7.0.1-dist.zip

2. Extract it in the plugins folder so you get this structure:

Elastic Search plugin directory

 

Setup Powershell for JDBC Feeder script

1. Download and Install Powershell 3.0 (within Windows Management Framework 3.0 package)
https://www.microsoft.com/en-us/download/details.aspx?id=34595 (Windows6.1-KB2506143-x64.msu)

2. Reboot

3. Check version by opening Powershell and running:

$PSVersionTable.PSVersion

4. Change execution policy (Powershell must be launched as Administrator)

Set-ExecutionPolicy RemoteSigned

 Setting up CURL for Windows

http://r2d2.cc/2015/08/05/setup-curl-windows/

 

MySQL Setup

1. Create sample table:


DROP TABLE IF EXISTS `test`;

CREATE TABLE `test` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=105 DEFAULT CHARSET=utf8;

INSERT INTO `test`(`id`,`name`,`email`) VALUES (1,'Darth Vader','darthvader@galacticempire.com');
INSERT INTO `test`(`id`,`name`,`email`) VALUES (2,'Luke Skywalker','luke@jedi.com');
INSERT INTO `test`(`id`,`name`,`email`) VALUES (3,'Han Solo','han@rebels.com');
INSERT INTO `test`(`id`,`name`,`email`) VALUES (4,'R2D2','r2d2@droidsyouarelookingfor.com');

2. Download:  https://dev.mysql.com/downloads/connector/j/5.1.html (choose Platform independent)

and copy mysql-connector-java-{version}-bin.jar to plugins/elasticsearch-jdbc-{version}/lib folder

1. In Elastic Search directory create directory elasticsearch\bin\feeder
2. Create jdbc_mysql.json file with this content:

 {
 "type":"jdbc",
 "jdbc":{
 "url":"jdbc:mysql://localhost:3306/test",
 "user":"test",
 "password":"test",
 "sql":"SELECT id as _id, id, name,email FROM test",
 "index":"users",
 "type":"user",
 "autocommit":"true"
 }
 }

2. Create jdb_mysql.ps1 file with this content:

function Get-PSVersion {
 if (test-path variable:psversiontable) {$psversiontable.psversion} else {[version]"1.0.0.0"}
}
$powershell = Get-PSVersion
if($powershell.Major -le 2 ){
 Write-Error "Oh, so sorry, this script requires Powershell 3 (due to convertto-json)"
 exit
}
if((Test-Path env:\JAVA_HOME) -eq $false){
 Write-Error "Environment variable JAVA_HOME must be set to your java home"
 exit
}
curl -XDELETE "http://localhost:9200/users"

$DIR = "D:\programs\elasticsearch\plugins\elasticsearch-jdbc-1.7.0.1\"
$FEEDER_CLASSPATH="$DIR\lib"
$FEEDER_LOGGER="file://$DIR\bin\log4j2.xml"

java -cp "$FEEDER_CLASSPATH\*" -"Dlog4j.configurationFile=$FEEDER_LOGGER" "org.xbib.tools.Runner" "org.xbib.tools.JDBCImporter" jdbc_mysql.json
}

3. Open Powershell and run Feeder from a command line:

cd c:\servers\elasticsearch-1.7.1\bin\feeder\
.\jdbc_mysql.ps1

4. Go to http://tm-allegrohr:9200/users/_search

Elastic Search Installation on Windows

 

 

Microsoft SQL Server

1. Create sample table:


USE [test]
GO

CREATE TABLE [dbo].[planets](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) NULL,
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[planets] ON

INSERT [dbo].[planets] ([id], [name]) VALUES (1, N'Mercury')
INSERT [dbo].[planets] ([id], [name]) VALUES (2, N'Venus')
INSERT [dbo].[planets] ([id], [name]) VALUES (3, N'Earh')
INSERT [dbo].[planets] ([id], [name]) VALUES (4, N'Mars')
INSERT [dbo].[planets] ([id], [name]) VALUES (5, N'Jupiter')
INSERT [dbo].[planets] ([id], [name]) VALUES (6, N'Saturn')
INSERT [dbo].[planets] ([id], [name]) VALUES (7, N'Uranus')
INSERT [dbo].[planets] ([id], [name]) VALUES (8, N'Neptune')
SET IDENTITY_INSERT [dbo].[planets] OFF

2. Download: http://www.microsoft.com/en-us/download/details.aspx?id=11774
In my case I selected: sljdbc_4.2.6225.100_enu.exeq

3. Unzip downloaded file and copy
sqljdbc4.jar
sqljdbc41.jar
sqljdbc42.jar
to plugins/elasticsearch-jdbc-{version}/lib

4. In Elastic Search directory create directory elasticsearch\bin\feeder

5. Create jdbc_sqlserver.json file with this content:

{
 "type":"jdbc",
 "jdbc":{
 "url":"jdbc:sqlserver://localhost:1433;databaseName=test",
 "user":"test",
 "password":"test",
 "sql":"SELECT id as _id, id, name,email FROM test",
 "index":"users",
 "type":"user",
 "autocommit":"true"
 }
}
 }

6. Create jdb_sqlserver.ps1 file with this content:

function Get-PSVersion {
 if (test-path variable:psversiontable) {$psversiontable.psversion} else {[version]"1.0.0.0"}
}
$powershell = Get-PSVersion
if($powershell.Major -le 2 ){
 Write-Error "Oh, so sorry, this script requires Powershell 3 (due to convertto-json)"
 exit
}
if((Test-Path env:\JAVA_HOME) -eq $false){
 Write-Error "Environment variable JAVA_HOME must be set to your java home"
 exit
}
curl -XDELETE "http://localhost:9200/planets"

$DIR = "D:\programs\elasticsearch\plugins\elasticsearch-jdbc-1.7.0.1\"
$FEEDER_CLASSPATH="$DIR\lib"
$FEEDER_LOGGER="file://$DIR\bin\log4j2.xml"

java -cp "$FEEDER_CLASSPATH\*" -"Dlog4j.configurationFile=$FEEDER_LOGGER" "org.xbib.tools.Runner" "org.xbib.tools.JDBCImporter" jdbc_sqlserver.json

7. Open Powershell and run Feeder from a command line:

cd c:\servers\elasticsearch-1.7.1\bin\feeder\
.\jdbc_sqlserver.ps1

Check logs for any erroer  c:\servers\elasticsearch-1.7.1\bin\feeder\log

If you get this error:

The tcp/ip connection to the host localhost port 1433 has failed ...

it means you haven’t setup TCP/IP connections properly. To fix it check:
http://r2d2.cc/2015/08/05/the-tcpip-connection-to-the-host-localhost-port-1433-has-failed-microsoft-sql-server-2014/ 

8. Go to http://tm-allegrohr:9200/planets/_search to test

You may also like...