jdbclogstashhsqldblogstash-jdbc

LOGSTASH - Issue with JDBC input connected to HSQL DB database when selecting ARRAY columns

I'm having troubles to successfully import HSQL DB database content using Logstash's JDBC input plugin.

The problem occurs when I try to fetch a column that is of type ARRAY.

Please note that if I try to fetch non-array columns, it works just fine.

I get the following error message from Logstash :

[WARN ][logstash.inputs.jdbc     ] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::OrgLogstash::MissingConverterException: Missing Converter handling for full class name=org.hsqldb.jdbc.JDBCArray, simple name=JDBCArray>}
[INFO ][logstash.pipeline        ] Pipeline has terminated {:pipeline_id=>"hsql", :thread=>"#<Thread:0x7b626752 run>"}

Please find below the input part of the Logstash conf file (PLATFORM_DESTINATION_CANDIDATES is the name of a column in a table.)

input {
  jdbc {
    jdbc_driver_library => "hsqldb_2.5.0.jar"
    jdbc_driver_class => "org.hsqldb.jdbc.JDBCDriver"
    jdbc_connection_string => "jdbc:hsqldb:hsql://localhost/probe"
    jdbc_user => "SA"
    statement => "SELECT PLATFORM_DESTINATION_CANDIDATES FROM PUBLIC.MESSAGES_SENT"
    connection_retry_attempts => 10
  }
}

Did any of you encounter this kind of problem, and how did you solve it ? Thanks.

Solution

  • I do not know if it is the best solution, but I managed to solve my issue. Here is how.

    I replaced the line :

    statement => "SELECT PLATFORM_DESTINATION_CANDIDATES FROM PUBLIC.MESSAGES_SENT"

    with :

     statement => SELECT concat_ws('', PLATFORM_DESTINATION_CANDIDATES , '') AS str_platforms

    It has the consequence to put in the field str_platforms of type string data that looks like : ARRAY[1,2,3,4]

    With the following ruby line, I then remove unwanted characters ( ARRAY[ and ] ) from the field :

    ruby {
    code => "event.set('listRxUnits',event.get('str_platforms').split('ARRAY[')[1].split(']')[0])"
  }