Hello everyone!
I'm trying to import a data set with more than 4 million rows from my database in SQL Server.
I followed the advice here Jdbc input plugin | Logstash Reference [8.4] | Elastic and set jdbc_fetch_size
with a small value of 1000 but nothing happened and the process is exiting by timeout.
Then I tried to set jdbc_paging_enabled => true
and jdbc_paging_mode => "explicit"
in order to manage limit/offset manually but after running the pipeline I'm receiving this error:
[2022-09-01T20:00:30,453][ERROR][logstash.inputs.jdbc ] Unknown setting 'jdbc_paging_mode' for jdbc
[2022-09-01T20:00:30,460][ERROR][logstash.agent ] Failed to execute action {:action=>LogStash::PipelineAction::Create/pipeline_id:my-pipeline, :exception=>"Java::JavaLang::IllegalStateException", :message=>"Unable to configure plugins: (ConfigurationError) Something is wrong with your configuration.", :backtrace=>["org.logstash.config.ir.CompiledPipeline.<init>
(CompiledPipeline.java:119)", "org.logstash.execution.JavaBasePipelineExt.initialize(JavaBasePipelineExt.java:83)", "org.logstash.execution.JavaBasePipelineExt$INVOKER$i$1$0$initialize.call(JavaBasePipelineExt$INVOKER$i$1$0$initialize.gen)", "org.jruby.internal.runtime.methods.JavaMethod$JavaMethodN.call(JavaMethod.java:837)", "org.jruby.ir.runtime.IRRuntimeHelpers.instanceSuper(IRRuntimeHelpers.java:1169)", "org.jruby.ir.instructions.InstanceSuperInstr.interpret(InstanceSuperInstr.java:84)", "org.jruby.ir.interpreter.InterpreterEngine.processCall(InterpreterEngine.java:361)", "org.jruby.ir.interpreter.StartupInterpreterEngine.interpret(StartupInterpreterEngine.java:72)", "org.jruby.internal.runtime.methods.MixedModeIRMethod.INTERPRET_METHOD(MixedModeIRMethod.java:86)", "org.jruby.internal.runtime.methods.MixedModeIRMethod.call(MixedModeIRMethod.java:73)", "org.jruby.ir.targets.InvokeSite.invoke(InvokeSite.java:207)", "usr.share.logstash.logstash_minus_core.lib.logstash.pipeline_action.create.RUBY$method$execute$0(/usr/share/logstash/logstash-core/lib/logstash/pipeline_action/create.rb:52)", "usr.share.logstash.logstash_minus_core.lib.logstash.pipeline_action.create.RUBY$method$execute$0$__VARARGS__(/usr/share/logstash/logstash-core/lib/logstash/pipeline_action/create.rb)", "org.jruby.internal.runtime.methods.CompiledIRMethod.call(CompiledIRMethod.java:80)", "org.jruby.internal.runtime.methods.MixedModeIRMethod.call(MixedModeIRMethod.java:70)", "org.jruby.ir.targets.InvokeSite.invoke(InvokeSite.java:207)", "usr.share.logstash.logstash_minus_core.lib.logstash.agent.RUBY$block$converge_state$2(/usr/share/logstash/logstash-core/lib/logstash/agent.rb:389)", "org.jruby.runtime.CompiledIRBlockBody.callDirect(CompiledIRBlockBody.java:138)", "org.jruby.runtime.IRBlockBody.call(IRBlockBody.java:58)", "org.jruby.runtime.IRBlockBody.call(IRBlockBody.java:52)", "org.jruby.runtime.Block.call(Block.java:139)", "org.jruby.RubyProc.call(RubyProc.java:318)", "org.jruby.internal.runtime.RubyRunnable.run(RubyRunnable.java:105)", "java.base/java.lang.Thread.run(Thread.java:834)"]}
warning: thread "Converge PipelineAction::Create<my-pipeline>" terminated with exception (report_on_exception is true):
LogStash::Error: Don't know how to handle `Java::JavaLang::IllegalStateException` for `PipelineAction::Create<my-pipeline>`
create at org/logstash/execution/ConvergeResultExt.java:129
add at org/logstash/execution/ConvergeResultExt.java:57
converge_state at /usr/share/logstash/logstash-core/lib/logstash/agent.rb:402
It seems jdbc_paging_mode
does not exists? Or is only valid for other drivers different to SQL Server?
So, how could I paginate results efficently using SQL Server? Can't find any example in the documentation or in the Github plugin repo neither.
Here's a copy from my conf
input {
jdbc {
jdbc_connection_string => "jdbc:sqlserver://${DB_SERVER};databaseName=${DB_NAME}"
jdbc_user => "${DB_USER}"
jdbc_password => "${DB_PASSWORD}"
jdbc_driver_library => "./logstash-core/lib/jars/mssql-jdbc-11.2.0.jre11.jar" # Driver downloaded from https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_paging_enabled => true
jdbc_paging_mode => "explicit"
jdbc_page_size => 1000
jdbc_fetch_size => 1000
lowercase_column_names => false
record_last_run => true
schedule => "*/10 * * * * *"
statement => "SELECT * FROM MyTable WHERE CreatedDate > :sql_last_value AND CreatedDate < GETUTCDATE() ORDER BY CreatedDate OFFSET :size ROWS FETCH NEXT :offset ROWS ONLY"
use_column_value => true
tracking_column => "CreatedDate"
tracking_column_type => "timestamp"
}
}
filter {
}
output {
stdout { codec => rubydebug { metadata => true } }
}
Any help would be appreciate it!
Regards,
Manuel