ELASTICSEARCH JDBC CONNECTOR

ELASTICSEARCH JDBC CONNECTOR

1) Check the dependency and download appropriate JDBC connector version code as per your elasticsearch version.

https://github.com/jprante/elasticsearch-jdbc#recent-versions

2) Download the code from below location:

https://github.com/jprante/elasticsearch-jdbc

3) Compile the code using “mvn clean install”. It will create a jar file in target folder.

4) Copy the jar file into elasticsearch lib folder.

5) Download the JDBC importer distribution from below location

http://xbib.org/repository/org/xbib/elasticsearch/importer/elasticsearch-jdbc/<version>/elasticsearch-jdbc-<version>-dist.zip

6) unpack

unzip elasticsearch-jdbc-<version>-dist.zip

7) This will be our $JDBC_IMPORTER_HOME

8) Set the below path 

#Elastic Search

$> ES_HOME=/Users/jayeshbhoyar/Desktop/Programs/elasticsearchinstance/

$> export ES_HOME

# JDBC_IMPORTER_HOME

$> JDBC_IMPORTER_HOME=/Users/jayeshbhoyar/Desktop /Programs/elasticsearchinstance/elasticsearchjdbc1.5.2.0/

$> export JDBC_IMPORTER_HOME

9) Download the JDBC driver jar file from vendor site and copy it in “ES_HOME/lib” and “JDBC_IMPORTER_HOME/lib” folders

10) Go to “JDBC_IMPORTER_HOME” Folder and execute the below command:

bin=$JDBC_IMPORTER_HOME/bin 

lib=$JDBC_IMPORTER_HOME/lib 

echo '{     

"type" : "jdbc",     

"jdbc" : {         

"url" : "jdbc:mysql://localhost:3306/test",         

"user" : "",         "

password" : "",         

"sql" : "select *, id as _id from orders"     

} 

}' | java \        

-cp "${lib}/*" \        

-Dlog4j.configurationFile=${bin}/log4j2.xml \        

org.xbib.tools.Runner \        

org.xbib.tools.JDBCImporter

It will start pulling the records from orders table and insert it into elasticsearch.

Sample File:

{

    "type" : "jdbc",

    "bulk_size" : 1000,

"max_bulk_requests" : 50,

"bulk_flush_interval" : "10s",

"max_concurrent_bulk_requests" : 16,

"max_bulk_actions" : 1000,

    "jdbc" : {

        "url" : "jdbc:oracle:thin:@hostname:port:SID",

        "driver": "oracle.jdbc.OracleDriver",

        "user" : "username",

        "fetchsize" : 1000,

        "password" : "password",

        "sql" : "SELECT * FROM ABC”,

        "index" : "INDEXNAME",

        "type" : "INDEXTYPE",

        "index_settings": {

            "index": {

                "refresh_interval": "5s"

            }

        } ,

        "max_bulk_actions" : 1000,

    "metrics" : {

        "enabled" : true,

        "interval" : "1m",

        "logger" : {

            "plain" : true,

            "json" : false

        }

    },

        "max_concurrent_bulk_requests" : 16,

        "connection_properties" : {

            "oracle.jdbc.TcpNoDelay" : false,

            "useFetchSizeWithLongColumn" : false,

            "oracle.net.CONNECT_TIMEOUT" : 10000,

            "oracle.jdbc.ReadTimeout" : 50000

        },

        "elasticsearch" : {

         "cluster" : "CLUSTERNAME",

         "host" : "ELASTICSEARCHHOSTNAME",

         "port" : 9300

    }

    }

}

For more settings and more information visit below link.

https://github.com/jprante/elasticsearch-jdbc

Hope this Helps!!!!

Jayesh Bhoyar

Reference:

https://github.com/jprante/elasticsearch-jdbc

Check More Articles on ElasticSearch:
http://technical-fundas.blogspot.com/2015/07/elasticsearch-bigdesk-plugin.html