Moving data from SQL Server 2012 to ElasticSearch

[rrmave (Rijesh Rajan)]

Hi,

I am trying to move data from SQL Server to ElasticSearch. I am using the JDBC importer (http://xbib.org/repository/org/xbib/elasticsearch/importer/elasticsearch-jdbc/2.3.4.1/) and have written a batch script to pull the data.

My initial steps were to install java and elasticsearch and confirm it was running

I then extracted the JDBC importer to the elasticsearch\bin folder

I then downloaded the SQL Server drivers and placed them in the Importer Plugin Lib folder

I then created the following script

@echo off

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

REM ???
echo '
{
"type" : "jdbc",
"jdbc" : {
"url" : "jdbc:sqlserver://localhost:;instanceName=<instance_name>;databaseName=<db_name>",
"user" : "",
"password" : "",
"sql" : "<select_statement>",
"treat_binary_as_string" : true,
"elasticsearch" : {
"cluster" : "elasticsearch",
"host" : "localhost",
"port" : 9200
},
"index" :"record",
"type" :"record"
}
}
' | "%JAVA_HOME%\bin\java" -cp "%LIB%" -Dlog4j.configurationFile="%BIN%\log4j2.xml" "org.xbib.tools.Runner" "org.xbib.tools.JDBCImporter"

placed it in the Importer Plugin bin folder and ran the script. But I am getting the following error

C:\elasticsearch-2.4.1\bin\elasticsearch-jdbc-2.3.4.1\bin>mssql-simple-example.bat
'
'{' 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.
'"jdbc"' is not recognized as an internal or external command,
operable program or batch file.
'"url"' 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.
'"password"' 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.
'"treat_binary_as_string"' 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.
'"cluster"' 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.
'"port"' 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.
'"index"' 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.
'}' 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.
''' is not recognized as an internal or external command,
operable program or batch file.

I am new to ES so advise would be greatly appreciated.

Thanks,

[danielmitterdorfer]

Hi @rrmave,

your problem is unrelated to Elasticsearch. You use Unix features in a Windows batch script, that's why it fails.

The docs of the importer say, that you can also provide the parameters in a file and this seems to be the better (only?) option in your case:

So I guess you need to do something along these lines:

"%JAVA_HOME%\bin\java" -cp "%LIB%" -Dlog4j.configurationFile="%BIN%\log4j2.xml" "org.xbib.tools.Runner" "org.xbib.tools.JDBCImporter" "statefile.json"

where statefile.json contains your parameters:

{
  "type": "jdbc",
  "jdbc": {
    "url": "jdbc:sqlserver://localhost:;instanceName=;databaseName=",
    "user": "",
    "password": "",
    "sql": "",
    "treat_binary_as_string": true,
    "elasticsearch": {
      "cluster": "elasticsearch",
      "host": "localhost",
      "port": 9200
    },
    "index": "record",
    "type": "record"
  }
}

Daniel

[jprante (Jörg Prante)]

@danielmitterdorfer Thanks for the quick response here, even though JDBC importer is not supported by Elastic company

[danielmitterdorfer]

Hi @jprante,

you're welcome. It was quite easy to find in the docs so I figured I could write a quick response.

Daniel

[rrmave (Rijesh Rajan)]

Hi Daniel,

Thanks a lot for the update. I knew I was doing something silly.

[rrmave (Rijesh Rajan)]

I was actually following the steps in http://hintdesk.com/how-to-connect-elasticsearch-to-ms-sql-server/

[rrmave (Rijesh Rajan)]

Maybe connecting to the SQL Server db via ES and searching is not ideal from a performance perspective. Would you recommend copying the data to ES rather than connecting to the db?

Thanks

[danielmitterdorfer]

Hi @rrmave,

well, there is no "right" answer to your question, that depends on your application and how you intend to use Elasticsearch. For starters you can read https://www.elastic.co/blog/found-keeping-elasticsearch-in-sync.

Daniel