Load table with jdbc_static filter with NULL integers

[Micah_Hunsberger]

I'm trying to use the jdbc_static filter in my logstash pipeline, but I am getting the following error:

[2018-12-04T10:07:38,952][ERROR][logstash.filters.jdbc.readwritedatabase] Exception when filling lookup db from loader remote-interfaces, query results, original exception: Sequel::DatabaseError, original message: Java::JavaSql::SQLException: Import error on line 1 of file C:/Windows/TEMP/logstash/jdbc_static/import_data/interfaces: Invalid character string format for type INTEGER.  {:backtrace=>["org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(org/apache/derby/impl/jdbc/SQLExceptionFactory)", "org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(org/apache/derby/impl/jdbc/SQLExceptionFactory)", "org.apache.derby.impl.jdbc.Util.seeNextException(org/apache/derby/impl/jdbc/Util)", "org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(org/apache/derby/impl/jdbc/TransactionResourceImpl)", "org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(org/apache/derby/impl/jdbc/TransactionResourceImpl)", "org.apache.derby.impl.jdbc.EmbedConnection.handleException(org/apache/derby/impl/jdbc/EmbedConnection)", "org.apache.derby.impl.jdbc.ConnectionChild.handleException(org/apache/derby/impl/jdbc/ConnectionChild)", "org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(org/apache/derby/impl/jdbc/EmbedStatement)", "org.apache.derby.impl.jdbc.EmbedStatement.execute(org/apache/derby/impl/jdbc/EmbedStatement)", "org.apache.derby.impl.jdbc.EmbedStatement.execute(org/apache/derby/impl/jdbc/EmbedStatement)", "java.lang.reflect.Method.invoke(java/lang/reflect/Method.java:498)", "org.jruby.javasupport.JavaMethod.invokeDirectWithExceptionHandling(org/jruby/javasupport/JavaMethod.java:453)", "org.jruby.javasupport.JavaMethod.invokeDirect(org/jruby/javasupport/JavaMethod.java:314)", "C_3a_.LS.logstash_minus_6_dot_3_dot_0.vendor.bundle.jruby.$2_dot_3_dot_0.gems.sequel_minus_5_dot_14_dot_0.lib.sequel.adapters.jdbc.block in execute(C:/LS/logstash-6.3.0/vendor/bundle/jruby/2.3.0/gems/sequel-5.14.0/lib/sequel/adapters/jdbc.rb:248)", "C_3a_.LS.logstash_minus_6_dot_3_dot_0.vendor.bundle.jruby.$2_dot_3_dot_0.gems.sequel_minus_5_dot_14_dot_0.lib.sequel.database.logging.log_connection_yield(C:/LS/logstash-6.3.0/vendor/bundle/jruby/2.3.0/gems/sequel-5.14.0/lib/sequel/database/logging.rb:38)", "C_3a_.LS.logstash_minus_6_dot_3_dot_0.vendor.bundle.jruby.$2_dot_3_dot_0.gems.sequel_minus_5_dot_14_dot_0.lib.sequel.adapters.jdbc.block in execute(C:/LS/logstash-6.3.0/vendor/bundle/jruby/2.3.0/gems/sequel-5.14.0/lib/sequel/adapters/jdbc.rb:248)"]}

the loader it is complaining about has this config:

            {
                id => "remote-interfaces"
                query => "select id, name, device_id, virtual_machine_id from public.dcim_interface order by id"
                local_table => "interfaces"
            }

and the local db object has this config

            {
                name => "interfaces"
                index_columns => [ "id", "device_id", "virtual_machine_id" ]
                columns => [
                    [ "id", "integer" ],
                    [ "name", "varchar(64)" ],
                    [ "device_id", "integer" ],
                    [ "virtual_machine_id", "integer" ]
                ]
            }

on the postgresql database, the same query returns this:

netbox=# select id, name, device_id, virtual_machine_id from public.dcim_interface order by id;
 id |   name   | device_id | virtual_machine_id
----+----------+-----------+--------------------
  1 | a1       |         1 |
  2 | Ethernet |           |                  1
(2 rows)

I think the issue is that the integer fields are returning NULL and so the apache derby db is complaining about it. What do I have to do to allow NULL values? Thanks

[Micah_Hunsberger]

It seems that jdbc_static uses the apache derby import csv file, which interprets blank columns as null, and indeed, the temp data staging file looked like this:

1, 'a1', 1, NULL
2, 'Ethernet', NULL, 1

which the csv importer must not know to interpret as null instead of "NULL" so I tried modifying the select statement to change null values to empty strings:

query => "select id, name, coalesce(device_id,''), coalesce(virtual_machine_id,'') from public.dcim_interface order by id"

When I do that, I get a different error:

[ERROR][logstash.filters.jdbc.readonlydatabase] Exception occurred when executing loader Jdbc query count {:exception=>"Java::OrgPostgresqlUtil::PSQLException: ERROR: invalid input syntax for integer: \"\"\n  Position: 70", :backtrace=>["org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(org/postgresql/core/v3/QueryExecutorImpl.java:2440)", "org.postgresql.core.v3.QueryExecutorImpl.processResults(org/postgresql/core/v3/QueryExecutorImpl.java:2183)", "org.postgresql.core.v3.QueryExecutorImpl.execute(org/postgresql/core/v3/QueryExecutorImpl.java:308)", "org.postgresql.jdbc.PgStatement.executeInternal(org/postgresql/jdbc/PgStatement.java:441)", "org.postgresql.jdbc.PgStatement.execute(org/postgresql/jdbc/PgStatement.java:365)", "org.postgresql.jdbc.PgStatement.executeWithFlags(org/postgresql/jdbc/PgStatement.java:307)", "org.postgresql.jdbc.PgStatement.executeCachedSql(org/postgresql/jdbc/PgStatement.java:293)", "org.postgresql.jdbc.PgStatement.executeWithFlags(org/postgresql/jdbc/PgStatement.java:270)"]}

[Micah_Hunsberger]

The previous error was a postgresql error because I was trying to mix integer and string fields. I changed the query to be

query => "select id, name, case when device_id is null then 'null' else cast(device_id as varchar(10)) end, case when virtual_machine_id is null then 'null' else cast(virtual_machine_id as varchar(10)) end from public.dcim_interface order by id"

which does successfully make NULL an empty string, but the csv file escaped the empty string:

1, 'a1', '1', ''
2, 'Ethernet', '', '1'

and so the import was still unsuccessful, so I am back where I started. Whatever code is creating the CSV is not building it out correctly for the apache derby database to accept NULL values as actually null, and instead treats it as a string: "NULL".

Instead of

1, 'a1', '1', NULL
2, 'Ethernet', NULL, '1'

The csv should be

1, 'a1', '1',
2, 'Ethernet',, '1'

based on this documentation on csv import/export: https://db.apache.org/derby/docs/10.7/tools/ctoolsimportnulls.html