Logstash and Oracle – JDBC input settings

Lately, I began experimenting with elastic search, logstash and kibana. As a DBA, the first thing I wanted to try is Data base connectivity. Just like any new emerging technology, Oracle database is not the first priority on their implementation (and of course, documentation). There are much more examples for sexier (and free) databases like Postgresql or MySQL. As a result, there are only few examples and the configuaration is a real PITA.

While it is easy to find mysql database jdbc for logstash examples

input {
  jdbc {
    jdbc_driver_library => "mysql-connector-java-5.1.36-bin.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://localhost:3306/mydb"
    jdbc_user => "mysql"
    parameters => { "favorite_artist" => "Beethoven" }
    schedule => "* * * * *"
    statement => "SELECT * from songs where artist = :favorite_artist"
  }
}

It is much harder to find Oracle database jdbc logstash examples but I found something to start with at https://discuss.elastic.co/t/logstash-jdbc-input-oracle-settings/26996

jdbc {
      # The path to our downloaded jdbc driver saved mine in /bin, driver ojdbc6.jar also works. 
      #Best location can be /vendor folder
           jdbc_driver_library => "ojdbc7.jar"
      # ORACLE Driver Class
           jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
      # ORACLE jdbc connection string to our database, ORACLE  jdbc:oracle:thin:@hostname:PORT/SERVICE
           jdbc_connection_string => "jdbc:oracle:thin:@hostname:PORT/SERVICE"
      # The user and password we wish to execute our statement as
           jdbc_user => "DBUSERNAME"
           jdbc_password => "DBPASSWORD"
   
    # our SQL query
    statement => "select * from AWESOMETABLE"

    #optional extras I use 
      type => "Database"
      tags => ["awesome", "import"]
 }

Since I did not have ojdbc7.jar on my computer I chose to use ojdbc6.jar so my Oracle database jdbc logstash query came out like this:

input {
jdbc {
    jdbc_connection_string => "jdbc:oracle:thin:@\\my-host:1521:my-sid"
    jdbc_user => "db-user"
    jdbc_password => "db-pwd"
    jdbc_validate_connection => true
    jdbc_driver_library => "C:\path\to\ojdbc6.jar"
    jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
    statement => "SELECT * from my-table"
}
}
output {
elasticsearch {
index => "contacts"
document_type => "contact"
document_id => "%{uid}"
hosts => "localhost:9200"
}
}

Along the way, I encountered two error messages that are the reason for this post:
The first

” Error: java::oracle.jdbc.driver.OracleDriver not loaded. Are you sure you’ve included the correct jdbc driver in :jdbc_driver_library?
You may be interested in the ‘–configtest’ flag which you can use to validate logstash’s configuration before you choose to restart a running system. ”

Running the query with the ‘–configtest’ flag did not help much but eventually, it turned out to be a simple typo. The oracle driver was not loaded only because the call was
” java::oracle.jdbc.driver.OracleDriver ” instead of ” Java::oracle.jdbc.driver.OracleDriver “.
(this one was really annoying but I hope this will save you time I spent on it ).

Another message I got along the way was

Java::JavaSql::SQLRecoverableException: IO Error: Invalid connection string fo
rmat, a valid format is: “host:port:sid”

apparently, while all examples for the connection string are in the following format:
jdbc:mysql://localhost:3306/mydb
The valid format in my case (oracle client 11 on windows 7) was
jdbc:oracle:thin:@\\my-host:1521:my-sid

This should get you connected but if you still get

Java::JavaSql::SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
Check the following:
The database and the listener are running.
The specified port in the code is the same port that the database is listening .
There is no firewall blocking
Good luck

2 thoughts on “Logstash and Oracle – JDBC input settings

  1. Dhin

    Thanks for the great post.

    Can you also add the input configuration to add Derby Database?

    Thanks in advance

  2. P

    Almost spot on with your config and setup. It worked right away, with a minor tweak.

    TYPE should be type
    INDEX should be index

    Logstash will throw an error if those are upper case.
    (I think this might be the fault of the ‘pretty code’ block that you’re using on this blog)

    If you encounter the exception:
    [2017-10-11T02:38:08,163][ERROR][logstash.inputs.jdbc ] Unknown setting ‘TYPE’ for jdbc
    [2017-10-11T02:38:08,217][ERROR][logstash.agent ] Cannot create pipeline {:reason=>”Something is wrong with your configuration.”}

Leave a Reply

Your email address will not be published. Required fields are marked *