search

jprante / elasticsearch-jdbc

JDBC importer for Elasticsearch
Apache License 2.0
2.84k stars 709 forks source link

cannot load from oracle #336

Open sdba2 opened 10 years ago

sdba2 commented 10 years ago

i followed some links and didn't succeed to load from oracle. here is what i did :

  1. install the plugin under : plugins/river-jdbc
  2. copy ojdbc6.jar into 2 places : the 1st is under plugins/river-jdbc and 2nd under : elasticsearch/lib (ls /use/share/elasticsearc/plugins/river-jdbc returns 4 files elasticsearch-river-jdbc-1.3.0.4.jar ojdbc6.jar log4j2.xml log4j.properties
  3. try to load using : curl -XPUT 'localhost:9200/_river/my_oracle_driver/_meta -d '{ "type" : "jdbc", "jdbc" : { "url" : "jdbc.oracle.thin:@//myhost1:1521/sid1", "user" : "user1", "password" : "p", "sql" : "select * from a"" } }'

the output is : {"_index":"_river","_type":"my_oracle_driver","_id":"_meta","_version":1,"created":true}

  1. what i end up with is 2 indexes _river (with 2 docs) jdbc (with 0 docs)

what am i missing? please advise

clever-trevor commented 10 years ago

What does the elasticsearch logfile show when you add it?

sdba2 commented 10 years ago

this is what i see:

[2014-09-28 09:13:49,162][INFO][index.shard.service ][node1][jdbc][2] updating refresh_interval from [1s] to [-1] [2014-09-28 09:13:49,162][INFO][index.shard.service ][node1][jdbc][0] updating refresh_interval from [1s] to [-1] [2014-09-28 09:13:49,163][INFO][index.shard.service ][node1][jdbc][3] updating refresh_interval from [1s] to [-1]

[2014-09-28 09:15:23,309][INFO][index.shard.service ][node1][jdbc][2] updating refresh_interval from [-1] to [1s] [2014-09-28 09:15:23,309][INFO][index.shard.service ][node1][jdbc][0] updating refresh_interval from [-1] to [1s] [2014-09-28 09:15:23,310][INFO][index.shard.service ][node1][jdbc][3] updating refresh_interval from [-1] to [1s]

jprante commented 10 years ago

Please try latest release. You can also switch Elasticsearch to DEBUG log level in $ES_HOME/config/logging.yml for more river messages.

mohameden commented 10 years ago

I'am trying to use this plugin with oracle (ES version = 1.4.0.Beta1, river version =1.4.0.3.Beta1.jar, oracle driver = ojdbc7) I tried both river and feeder and I'm still not able to get data from my oracle DB. Here my conf and errors. Thanks for your help.

River

My install :

I just copied elasticsearch-river-jdbc-1.4.0.3.Beta1.jar the jar file in my $ES_HOME/lib and restarted my ES.

My command :
curl -XPUT 'localhost:9200/_river/my_jdbc_river/_meta' -d '{
    "type" : "jdbc",
    "jdbc" : {
        "url" : "jdbc:oracle:thin:@myHost:1535:TNSNAME",
        "user" : "user",
        "password" : "passwd",
        "query_timeout" : 1800,
        "sql" : "select filed from test where  rownum < 15 "
        "index" : "dbTest"
      }
}'
The result :

I have only one index _river with no data from my DB.

Feeder

My script feeder.sh :
#!/bin/sh

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

# ES_HOME required to detect elasticsearch jars
export ES_HOME=$HOME/ELK/elasticsearch-1.4.0.Beta1

echo '
{
    "elasticsearch" : {
         "cluster" : "elasticsearch",
         "host" : "localhost",
         "port" : 9300
    },
    "type" : "jdbc",
    "jdbc" : {
        "url" : "jdbc:oracle:thin:@myHost:1535:TNSNAME",
        "user" : "user",
        "password" : "passwd",
        "query_timeout" : 1800,
        "sql" : "select filed from test where  rownum < 15 "
        "index" : "dbTest"
      }
}
' | java \
    -cp "${DIR}/*" \
    org.xbib.elasticsearch.plugin.jdbc.feeder.Runner \
    org.xbib.elasticsearch.plugin.jdbc.feeder.JDBCFeeder
stacktrace :
-bash-4.1$ ./feeder.sh
Classpath: [file:/home/webdev04/ELK/dbFeeder/, file:/home/webdev04/ELK/dbFeeder/ojdbc7.jar, file:/home/webdev04/ELK/dbFeeder/elasticsearch-river-jdbc-1.4.0.3.Beta1.jar, file:/home/webdev04/ELK/elasticsearch-1.4.0.Beta1/lib/lucene-core-4.10.1.jar, file:/home/webdev04/ELK/elasticsearch-1.4.0.Beta1/lib/jts-1.13.jar, file:/home/webdev04/ELK/elasticsearch-1.4.0.Beta1/lib/lucene-suggest-4.10.1.jar, file:/home/webdev04/ELK/elasticsearch-1.4.0.Beta1/lib/lucene-spatial-4.10.1.jar, file:/home/webdev04/ELK/elasticsearch-1.4.0.Beta1/lib/lucene-queryparser-4.10.1.jar, file:/home/webdev04/ELK/elasticsearch-1.4.0.Beta1/lib/antlr-runtime-3.5.jar, file:/home/webdev04/ELK/elasticsearch-1.4.0.Beta1/lib/lucene-join-4.10.1.jar, file:/home/webdev04/ELK/elasticsearch-1.4.0.Beta1/lib/lucene-queries-4.10.1.jar, file:/home/webdev04/ELK/elasticsearch-1.4.0.Beta1/lib/lucene-grouping-4.10.1.jar, file:/home/webdev04/ELK/elasticsearch-1.4.0.Beta1/lib/lucene-highlighter-4.10.1.jar, file:/home/webdev04/ELK/elasticsearch-1.4.0.Beta1/lib/lucene-memory-4.10.1.jar, file:/home/webdev04/ELK/elasticsearch-1.4.0.Beta1/lib/asm-commons-4.1.jar, file:/home/webdev04/ELK/elasticsearch-1.4.0.Beta1/lib/elasticsearch-1.4.0.Beta1.jar, file:/home/webdev04/ELK/elasticsearch-1.4.0.Beta1/lib/groovy-all-2.3.2.jar, file:/home/webdev04/ELK/elasticsearch-1.4.0.Beta1/lib/lucene-misc-4.10.1.jar, file:/home/webdev04/ELK/elasticsearch-1.4.0.Beta1/lib/log4j-1.2.17.jar, file:/home/webdev04/ELK/elasticsearch-1.4.0.Beta1/lib/spatial4j-0.4.1.jar, file:/home/webdev04/ELK/elasticsearch-1.4.0.Beta1/lib/lucene-expressions-4.10.1.jar, file:/home/webdev04/ELK/elasticsearch-1.4.0.Beta1/lib/lucene-analyzers-common-4.10.1.jar, file:/home/webdev04/ELK/elasticsearch-1.4.0.Beta1/lib/asm-4.1.jar, file:/home/webdev04/ELK/elasticsearch-1.4.0.Beta1/lib/lucene-sandbox-4.10.1.jar, file:/home/webdev04/ELK/elasticsearch-1.4.0.Beta1/lib/jna-4.1.0.jar]
log4j:WARN No appenders could be found for logger (JDBCFeeder).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.xbib.elasticsearch.plugin.jdbc.feeder.Runner.main(Runner.java:45)
Caused by: java.lang.NullPointerException
        at org.xbib.elasticsearch.plugin.jdbc.feeder.JDBCFeeder.start(JDBCFeeder.java:169)
        at org.xbib.elasticsearch.plugin.jdbc.feeder.JDBCFeeder.exec(JDBCFeeder.java:110)
        ... 5 more
java.lang.NullPointerException
        at org.xbib.elasticsearch.plugin.jdbc.feeder.JDBCFeeder.shutdown(JDBCFeeder.java:239)
        at org.xbib.elasticsearch.plugin.jdbc.feeder.JDBCFeeder$1.run(JDBCFeeder.java:219)
-bash-4.1$
clever-trevor commented 10 years ago

@mohameden I've not tried the feeder method, but this version works for me as a river.

Did you install the river plugin prior to creating the river definition?

e.g. `./bin/plugin --install jdbc --url http://xbib.org/repository/org/xbib/elasticsearch/plugin/elasticsearch-river-jdbc/1.4.0.3.Beta1/elasticsearch-river-jdbc-1.4.0.3.Beta1-plugin.zip

Also, did you copy the ojdbc jar file into the plugin directory?

mohameden commented 10 years ago

@schmorgs Yes, I installed the river plugin before creating the river definition. To install the river plugin, I just did the following :

  1. $cp elasticsearch-river-jdbc-1.4.0.3.Beta1.jar $ES_HOME/lib/
  2. restart ES Is it ok?
clever-trevor commented 10 years ago

No, that's not right - at least I've never seen it done that way.

To install the plugin, you can either download the zip file (see URL above) and install as a local file, or install directly from the internet using the command I gave above.

Example:

More detailed instructions are on the github project home page

mohameden commented 10 years ago

Thanks a lot. I'll review my install and keep you informed. (I'll do it later. I'm not able to download the zip file because of an internal internet proxy ....). Thanks

otchi commented 9 years ago

i have a same problem :+1: log4j:WARN No appenders could be found for logger (JDBCFeeder). log4j:WARN Please initialize the log4j system properly. log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info. java.lang.reflect.InvocationTargetException at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.xbib.elasticsearch.plugin.jdbc.feeder.Runner.main(Runner.java:45) Caused by: java.lang.NullPointerException at org.xbib.elasticsearch.plugin.jdbc.feeder.JDBCFeeder.start(JDBCFeeder.java:169) at org.xbib.elasticsearch.plugin.jdbc.feeder.JDBCFeeder.exec(JDBCFeeder.java:110) ... 5 more java.lang.NullPointerException at org.xbib.elasticsearch.plugin.jdbc.feeder.JDBCFeeder.shutdown(JDBCFeeder.java:239) at org.xbib.elasticsearch.plugin.jdbc.feeder.JDBCFeeder$1.run(JDBCFeeder.java:219)

my feeder.bat is in %ES_HOME%

@echo off

SETLOCAL

if NOT DEFINED ES_HOME goto err

set DIR=%~dp0

set FEEDER_CLASSPATH=%DIR%lib;%DIR%plugins\jdbc ECHO ............................. ECHO %FEEDER_CLASSPATH%

echo {^ "elasticsearch" : {^ "cluster" : "elasticsearch",^ "host" : "localhost",^ "port" : 9300,^ "type" : "jdbc",^ "schedule" : "0 0 * * * "^ },^ "jdbc" : {^ "url": "jdbc:mysql://localhost:3306/test",^ "user" : "root",^ "password" : "KatiaAmine9294",^ "sql":"select \ from amine",^ "treat_binary_as_string" : true,^ "index" : "metawiki",^ }^ }^ | "%JAVA_HOME%\bin\java" -cp "%FEEDER_CLASSPATH%" "org.xbib.elasticsearch.plugin.jdbc.feeder.Runner" "org.xbib.elasticsearch.plugin.jdbc.feeder.JDBCFeeder" goto finally

:err echo JAVA_HOME and ES_HOME environment variable must be set! pause

:finally

ENDLOCAL

I installed before jdbc plugin : plugin --install jdbc --url http://xbib.org/xbib/elasticsearch/plugin/elasticsearch-river-jdbc/1.4.4.0/elasticsearch-river-jdbc-1.4.4.0-plugin.zip

and i put JDBC driver in the same directory

Thank you in advance for your help . :+1: