Import MySQL data into elasticsearch

There are various ways to import mysql data into elasticsearch. Couple of as follows.

Read one by one record and run curl e.g. curl -XPUT ‘http://localhost:9200/ …
By using JDBC and elasticsearch river.

Using JDBC and Elasticsearch River

Install JDBC river plugin

./bin/plugin -url http://bit.ly/10FJhEd -install river-jdbc

Download MySQL JDBC driver

http://dev.mysql.com/downloads/mirror.php?id=412177
unzip mysql-connector-java-5.1.21-bin.zip
cp mysql-connector-java-5.1.21-bin.jar $ES_HOME/plugins/river-jdbc/
./bin/elasticsearch -f

Import table from mysql

curl -XPUT 'localhost:9200/_river/jdbc/_meta' -d '{
"type" : "jdbc",
"jdbc" : {
"driver" : "com.mysql.jdbc.Driver",
"url" : "jdbc:mysql://localhost:3306/test",
"user" : "",
"password" : "",
"sql" : " select * from test;"
},
"index" : {
"index" : "jdbc",
"type" : "jdbc"
}
}'

Select table data from elasticsearch

curl -XGET 'http://localhost:9200/jdbc/_search?q=*'

Display indexed data on browser interface.

bin/plugin -install OlegKunitsyn/elasticsearch-browser

Open http://localhost:9200/_plugin/browser/?database=[index]&table=[type]

Ref. From –

https://github.com/jprante/elasticsearch-river-jdbc/wiki/Quickstart

https://github.com/OlegKunitsyn/elasticsearch-browser/wiki

Please note :- Latest elasticsearch has deprecated rivers – https://www.elastic.co/blog/deprecating-rivers

Categories: Elasticsearch, Linux, Website Peformance

About Author: