How do i import data from mysql to elastic search 1.6

Elastic Stack Elasticsearch
1

How do i import data from mysql to elastic search 1.6 as river is depriciated.
and also suggest method to update elastic search real time

2

Have a look in Is jdbc river is going to expire? If yes then what is the alternative to connect the database

3

hey mark thanks for reply, but that thread is not solving the problem for es 1.6

4

It's the exact same problem.

5

Have you tried https://github.com/jprante/elasticsearch-jdbc ?

6

hey jprante,
I tried JDBC importer its giving me error ,
getting Error: Could not find or load main class org.xbib.tools.Runner.

actually documentation for this importer is difficult to understand for newbie

7

Hi Deepak/jprante
I am also facing same issue. Have found any solution for that.

8

Hi @Mohit_Kumar_Yadav ,

I have tried the steps mentioned by @jprante and it's working fine.

9

Hi @Mohit_Kumar_Yadav,

If you are using https://github.com/jprante/elasticsearch-jdbc link then follow below steps
Step 1 > Say you download elasticsearch-jdbc in /opt/elasticsearch-jdbc-1.6.0.0
Step 2 > set lib and bin variables
e.g
[root@L123 ]# lib=/opt/elasticsearch-jdbc-1.6.0.0/lib
[root@L123 ]# bin=/opt/elasticsearch-jdbc-1.6.0.0/bin

Step 3 > Now fetch data from mysql and insert into ES.
for e.g

[root@L123 ]#echo '{
"type" : "jdbc",
"jdbc": {
"url":"jdbc:mysql://localhost:3306/ABC",
"user":"XXX",
"password":"XXXXXX",
"sql":"select id as "_id", p_id as "pid" ,s_id as "sid" from tmp",
"index" : "myindex",
"type" : "mytype"
}
}' | java -cp "${lib}/*" -Dlog4j.configurationFile=${bin}/log4j2.xml org.xbib.tools.Runner org.xbib.tools.JDBCImporter

I hope this will help you.

10

Hi sohil,
Thanks for replying..
I am also following the steps of the jprante. But i am facing this issue
"Error: Could not find or load main class org.xbib.tools.Runner" whenever I
start the batch script.

System details:-
Elastic- 1.6
Database- oracle db
OS- windows seven

Thanks in advance

Regards
Mohit
On 24-Jun-2015 11:13 pm, "sohilelasticsearch" noreply@discuss.elastic.co
wrote:

11

Does anybody able to resolve "Error: Could not find or load main class org.xbib.tools.Runner" issue on windows machine?
I am facing the same issue.
Thanks in advance!

12

Hey, I can not test JDBC importer on Windows.

Community support for Windows is very welcome!

13

It's giving the same error on Linux.
I'm stucked since 3 days with this issue, I am on a Debian distribution.

14

Hi Edouard_Kombo,

Before executing below command
java -cp "${lib}/*" -Dlog4j.configurationFile=${bin}/log4j2.xml org.xbib.tools.Runner org.xbib.tools.JDBCImporter

first print the value of lib and bin variables.
e.g
[root@L123 ]# echo $bin
[root@L123 ]# echo $lib
If both variables pointing to correct path then it will work.

Thanks,
Sohil Raghwani

16

I found out the solution ,for windows users please follow the same steps i did to make it work

I'm using elastic search 1.6.0

ES jdbc importer C:\elasticsearch-jdbc-1.6.0.0\ .Tip avoid spaces in the path

download cygwin for windows 7

Mysql database which has data to be imported inside Elastic Search

Once cygwin has been installed change the working directory to :

/cygdrive/c/elasticsearch-jdbc-1.6.0.0/lib

After that ,configure the following script to import the data from mysql to ES like the following :

DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"

bin=${DIR}/bin

lib=${DIR}/lib

echo $lib

echo $bin

echo '{

"type" : "jdbc",

"jdbc" : {

"driver": "com.mysql.jdbc.Driver",

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

"user" : "root",

"password" : "root",

"sql" : "select * from city",

"treat_binary_as_string" : true,

    "elasticsearch" : {

        "cluster" : "elasticsearch",

        "host" : "localhost",

        "port" : 9300

    },

    "max_bulk_actions" : 20000,

    "max_concurrent_bulk_requests" : 10,

    "index" : "my_index",

"type":"my_type"

}

}

' | java \

-cp "elasticsearch-jdbc-1.6.0.0-uberjar.jar;mysql-connector-java-5.1.33.jar" \

-Dlog4j.configurationFile="file://localhost/C:/elasticsearch-jdbc-1.6.0.0/bin/log4j2.xml" \

org.xbib.tools.Runner \

org.xbib.tools.JDBCImporter

Press enter to run and here you go all is done .Check now the data has
been imported successfully inside elastic search within your index.

Thanks

MrReda

17

@MrReda Can you make the steps clear right from the following step ?
Or send your blog link where you might have written clearly..
After that ,configure the following script to import the data from mysql to ES like the following :

18

Hi MrReda,

Can you represent step by step about "how to import data from mysql to ES"on windows? I think it's difficult problems!

Thank in advance!

19

Hi ramaaRamadhevi Rk ,please tell what issue are you facing with ,cause i have already detailed the full required steps to make it happen.

Thanks
MrReda

20

Hi vankimchau have you checked above my comments cause i have already detailed the full required steps.If you are still stuck on it let me know the issue and i will help u with pleasure.

Thanks
MrReda

1 Like
21

Good day, MrReda!
Yes, I looking for two problem,
1, how to change the working directory. for my data on disk:

Pasted image706×405 107 KB

2, where is script file that import data from mysql to ES.

Thank in advance!