How do i import data from mysql to elastic search 1.6

Elastic Stack Elasticsearch
22

Good Morning too

1-You can change the working directory from the command line by typing cd c: or cd d: ,once inside it you can choose the working directory you want to work with.
2-Copy the following script and paste it :smile:
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" : "gemalto",
"type":"gem"
}
}
' | 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

Here you go don't forget to change the values for your database user and files required for logging plus the putting the jars inside the lib directory of elastic search jdbc 1.6

Thanks
MrReda

23

I followed your steps MrReda but still getting same issue.I am using ES 1.5.2 and importer 1.5.2 with SQL.

24

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter
$ DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
bin=${DIR}/bin

lib=${DIR}/lib

echo $lib

echo $bin

echo '{

"type" : "jdbc",

"jdbc" : {

"driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver",

"url" : "jdbc:sqlserver://usalvwaldbqa1:1433;databaseName=es"",

"user" : "es",

"password" : "Answ3r21",

"sql" : "SELECT * FROM es.es.g_fast_documentation",

"treat_binary_as_string" : true,

"elasticsearch" : {

    "cluster" : "elasticsearch_dev",

    "host" : "localhost",

    "port" : 9200

},

"max_bulk_actions" : 20000,

"max_concurrent_bulk_requests" : 10,

"index" : "my_index",

"type":"my_type"
}

}

' | java -cp "elasticsearch-jdbc-1.5.2.0-uberjar.jar;hsqldb-2.3.2.jar" \

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

org.xbib.tools.Runner \

org.xbib.tools.JDBCImporter
svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter
$

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter
$ bin=${DIR}/bin

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter
$

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter
$ lib=${DIR}/lib

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter
$

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter
$ echo $lib
/cygdrive/d/elastic/jdbcimporter/lib

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter
$

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter
$ echo $bin
/cygdrive/d/elastic/jdbcimporter/bin

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter
$

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter
$ echo '{

"type" : "jdbc",

"jdbc" : {

"driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver",

"url" : "jdbc:sqlserver://usalvwaldbqa1:1433;databaseName=es"",

"user" : "es",

"password" : "Answ3r21",

"sql" : "SELECT * FROM es.es.g_fast_documentation",

"treat_binary_as_string" : true,

"elasticsearch" : {

    "cluster" : "elasticsearch_dev",

    "host" : "localhost",

    "port" : 9200

},

"max_bulk_actions" : 20000,

"max_concurrent_bulk_requests" : 10,

"index" : "my_index",

"type":"my_type"
}

}

' | java -cp "elasticsearch-jdbc-1.5.2.0-uberjar.jar;hsqldb-2.3.2.jar" \

Usage: java [-options] class [args...]
(to execute a class)
or java [-options] -jar jarfile [args...]
(to execute a jar file)
where options include:
-d32 use a 32-bit data model if available
-d64 use a 64-bit data model if available
-server to select the "server" VM
The default VM is server.

-cp <class search path of directories and zip/jar files>
-classpath <class search path of directories and zip/jar files>
              A ; separated list of directories, JAR archives,
              and ZIP archives to search for class files.
-D<name>=<value>
              set a system property
-verbose:[class|gc|jni]
              enable verbose output
-version      print product version and exit
-version:<value>
              require the specified version to run
-showversion  print product version and continue
-jre-restrict-search | -no-jre-restrict-search
              include/exclude user private JREs in the version search
-? -help      print this help message
-X            print help on non-standard options
-ea[:<packagename>...|:<classname>]
-enableassertions[:<packagename>...|:<classname>]
              enable assertions with specified granularity
-da[:<packagename>...|:<classname>]
-disableassertions[:<packagename>...|:<classname>]
              disable assertions with specified granularity
-esa | -enablesystemassertions
              enable system assertions
-dsa | -disablesystemassertions
              disable system assertions
-agentlib:<libname>[=<options>]
              load native agent library <libname>, e.g. -agentlib:hprof
              see also, -agentlib:jdwp=help and -agentlib:hprof=help

See Java SE - Documentation for more details.

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter
$ -Dlog4j.configurationFile=${bin}/log4j2.xml \

-bash: -Dlog4j.configurationFile=/cygdrive/d/elastic/jdbcimporter/bin/log4j2.xml: No such file or directory

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter
$ org.xbib.tools.Runner \

-bash: org.xbib.tools.Runner: command not found

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter
$ org.xbib.tools.JDBCImporter
-bash: org.xbib.tools.JDBCImporter: command not found

25

Hi @Deepak_Kumar,

you may try Pentaho PDI (Link). We use it to extract and convert data, as its necessary. Unfortunately its Elasticsearch Bulk Insert-function is outdated and not working, but it also provides a REST client, which we use to directly POST data to our cluster.

Cheers, Dominik

26

Hi @MrReda,

It is working fine after downloading Cygwin. But I want to schedule the script through service. Can u please let me know how can I schedule it?

Thanks,
Vani Aravinda

27

Please check out and verify the url for the db connection and pulling the data.It seems to be wrong (may be).
Thanks

28

Hi, maybe late ... but why you don't try to use logstash with jdbc input plugin and elasticsearch output?
It worked for me (about 1 million rows) on a local laptop test environment (WIN10-elasticsearch5-logstash5-mysql5.4).

Example mysqlimport.cfg

input {
  jdbc {
    jdbc_driver_library => "your_path_to/mysql-connector-java-5.1.40-bin.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://localhost:3306/your_database"
    jdbc_user => "user"
    jdbc_password => "password"
    statement_filepath  => "your_sql_query_file" //you can create your query to extract data in a separated file
  }
}
output {
  elasticsearch {
    action => "index"
    hosts => ["your_host"]
    index => "your_index"
    document_type => "your_type"
   }
}

After created mysqlimport.cfg (you can name as you want) run :
logstash -f mysqlimport.cfg

Antonio

1 Like