Filter jdbc_static mongodb error

Elastic Stack Logstash
1

Hello, I'm trying to enrich data with mongodb objects, here's the related part in my pipeline:

            jdbc_static {
                loaders => [ {
                    id => "circuitosOLT"
                    table => "ftthLeases"
                    query => 'db.ftthLeases.find({},{"_id": false});'
                    local_table => "dataOLT"
                }
                ]
                local_db_objects => [ {
                    name => "dataOLT"
                    index_columns => ["SERIAL"]
                    columns => [
                    ["SERIAL",   "varchar(64)"],
                    ["CLIENTE", "varchar(64)"],
                    ["BANDA",   "varchar(64)"],
                    ["CIRCUITO",      "varchar(64)"],
                    ["POSICAO",      "varchar(64)"]
                    ]
                }
                ]
                local_lookups => [ {
                    query => "select * from dataOLT WHERE POSICAO = :porta"
                    parameters => {
                        porta => "[posicao]"
                    }
                    target => "dataOLT"
                }
                ]
                loader_schedule => "30 */4 * * *"
                    jdbc_driver_class => "com.dbschema.MongoJdbcDriver"
                    jdbc_driver_library => "/usr/share/logstash/logstash-core/lib/jars/mongojdbc2.1.jar"
                    jdbc_connection_string => "jdbc:mongodb://api.test.com:27017/test"
                    jdbc_user => "root"
                    jdbc_password => "qwerty12345"

            }

But I'm getting this error:

    elk_logstash.0.wmnjztsgrhi6@elk1.localdomain    | [2020-06-29T18:37:12,640][ERROR][logstash.filters.jdbc.readonlydatabase][main] Exception occurred when executing loader Jdbc query count {:exception=>"Java::JavaSql::SQLException: <eval>:1:7 Expected ; but found count\nSELECT count(*) AS \"COUNT\" FROM (db.ftthLeases.find({},{\"_id\": false});) AS \"T1\" LIMIT 1\n       ^ in <eval> at line number 1 at column number 7", :backtrace=>["com.dbschema.MongoPreparedStatement.executeQuery(com/dbschema/MongoPreparedStatement.java:197)", "jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)", "jdk.internal.reflect.NativeMethodAccessorImpl.invoke(jdk/internal/reflect/NativeMethodAccessorImpl.java:62)", "jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(jdk/internal/reflect/DelegatingMethodAccessorImpl.java:43)", "java.lang.reflect.Method.invoke(java/lang/reflect/Method.java:566)", "org.jruby.javasupport.JavaMethod.invokeDirectWithExceptionHandling(org/jruby/javasupport/JavaMethod.java:426)", "org.jruby.javasupport.JavaMethod.invokeDirect(org/jruby/javasupport/JavaMethod.java:293)", "usr.share.logstash.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_28_dot_0.lib.sequel.adapters.jdbc.execute(/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.28.0/lib/sequel/adapters/jdbc.rb:256)"]}
    elk_logstash.0.wmnjztsgrhi6@elk1.localdomain    | [2020-06-29T18:37:12,651][ERROR][logstash.filters.jdbc.readwritedatabase][main] Exception when filling lookup db from loader circuitosOLT, query results, original exception: LogStash::Filters::Jdbc::LookupJdbcException, original message: Exception occurred when executing loader Jdbc query count {:backtrace=>["com.dbschema.MongoPreparedStatement.executeQuery(com/dbschema/MongoPreparedStatement.java:197)", "jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)", "jdk.internal.reflect.NativeMethodAccessorImpl.invoke(jdk/internal/reflect/NativeMethodAccessorImpl.java:62)", "jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(jdk/internal/reflect/DelegatingMethodAccessorImpl.java:43)", "java.lang.reflect.Method.invoke(java/lang/reflect/Method.java:566)", "org.jruby.javasupport.JavaMethod.invokeDirectWithExceptionHandling(org/jruby/javasupport/JavaMethod.java:426)", "org.jruby.javasupport.JavaMethod.invokeDirect(org/jruby/javasupport/JavaMethod.java:293)", "usr.share.logstash.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_28_dot_0.lib.sequel.adapters.jdbc.execute(/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.28.0/lib/sequel/adapters/jdbc.rb:256)", "usr.share.logstash.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_28_dot_0.lib.sequel.database.logging.log_connection_yield(/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.28.0/lib/sequel/database/logging.rb:38)", "usr.share.logstash.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_28_dot_0.lib.sequel.adapters.jdbc.execute(/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.28.0/lib/sequel/adapters/jdbc.rb:256)", "usr.share.logstash.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_28_dot_0.lib.sequel.adapters.jdbc.statement(/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.28.0/lib/sequel/adapters/jdbc.rb:692)", "usr.share.logstash.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_28_dot_0.lib.sequel.adapters.jdbc.RUBY$method$statement$0$__VARARGS__(usr/share/logstash/vendor/bundle/jruby/$2_dot_5_dot_0/gems/sequel_minus_5_dot_28_dot_0/lib/sequel/adapters//usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.28.0/lib/sequel/adapters/jdbc.rb)", "usr.share.logstash.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_28_dot_0.lib.sequel.adapters.jdbc.execute(/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.28.0/lib/sequel/adapters/jdbc.rb:251)", "usr.share.logstash.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_28_dot_0.lib.sequel.connection_pool.threaded.hold(/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.28.0/lib/sequel/connection_pool/threaded.rb:92)", "usr.share.logstash.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_28_dot_0.lib.sequel.database.connecting.synchronize(/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.28.0/lib/sequel/database/connecting.rb:270)", "usr.share.logstash.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_28_dot_0.lib.sequel.adapters.jdbc.execute(/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.28.0/lib/sequel/adapters/jdbc.rb:250)"]}

I have searched and found this related but I'm querying mongo so dont know exactly how to proceed.

Thanks in advance.

2

The problem is that the jdbc_static filter insists on knowing the number of records (the count is done by calling the sequel library that the filter is built upon). The sequel library implements the count by wrapping the SQL statement in

SELECT count(*) AS \"COUNT\" FROM (...) AS \"T1\" LIMIT 1

and that only works in databases that support that syntax. If your DB uses a different syntax then you cannot use a jdbc_static filter.

The jdbc input also counts records, but if that gets an exception like this it simply logs a message saying it will no longer try to count records.

3

That's bad, so I think I need to figure out another way. Ok, thanks!

4

I see that there has been an issue open for a couple of years for this.

1 Like
5

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.