0

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.

  • OS : windows 10
  • Logstash version : 6.3.1
  • HSQLDB driver version : 2.5.0 (LINK)
LudoZik
  • 917
  • 1
  • 8
  • 20
  • 1
    Isn't possible for you to transform the array into a comma-separated string field (possibly in an auxiliary column), select it with the JDBC input plugin, and deal with it in a ruby filter, using .split? – Lodi Oct 21 '19 at 19:23
  • Thanks for the tip.This is what I managed to do ; I posted my solution below. – LudoZik Oct 22 '19 at 09:39

1 Answers1

0

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])"
  }
LudoZik
  • 917
  • 1
  • 8
  • 20
  • HSQLDB will now support CAST (arrayValue AS VARCHAR(N)) to convert the array to the same format. You can then use the SQL SUBSTR function to strip the unwanted characters at source. – fredt Oct 25 '19 at 20:17