Unable to load data from Oracle DB

Elastic Stack Logstash
1

Command

./logstash -f ../conf/jdbc.conf

File

#jdbc.conf
input {
    jdbc {
            # path of the jdbc driver
            jdbc_driver_library => "C:\Ashok\ojdbc8-full\ojdbc8.jar"

            # The name of the driver class 
            jdbc_driver_class => "oracle.jdbc.Driver"

            # Mysql jdbc connection string to company database
            jdbc_connection_string => "jdbc:oracle:thin:@new-connection:1234:dbsource"
        
            # user credentials to connect to the DB
            jdbc_user => "xyz"
            jdbc_password => "xyz"

            # when to periodically run statement, cron format (ex: every 30 minutes)
            schedule => "30 * * * *"

            # query parameters
            parameters => { "account_no" => "213072717" }

            # sql statement 
            statement => "SELECT * FROM accounts WHERE account_no= :account_no 
        }
}

output {
    elasticsearch {
      index => "bank"
      document_type => "account"
      hosts => "localhost:9200"
    }
}

Log

Sending Logstash logs to C:/Ashok/logstash-7.1.1/logs which is now configured via log4j2.properties
[2019-05-29T22:16:19,161][WARN ][logstash.config.source.multilocal] Ignoring the 'pipelines.yml' file because modules or command line options are specified
[2019-05-29T22:16:19,181][INFO ][logstash.runner          ] Starting Logstash {"logstash.version"=>"7.1.1"}
[2019-05-29T22:16:20,280][ERROR][logstash.agent           ] Failed to execute action {:action=>LogStash::PipelineAction::Create/pipeline_id:main, :exception=>"LogStash::ConfigurationError", :message=>"Expected one of #, {, } at line 30, column 17 (byte 930) after #jdbc.conf\r\ninput {\r\n    jdbc {\r\n            # path of the jdbc driver\r\n            jdbc_driver_library => \"C:\\Ashok\\ojdbc8-full\\ojdbc8.jar\"\r\n\r\n            # The name of the driver class \r\n
       jdbc_driver_class => \"oracle.jdbc.Driver\"\r\n\r\n            # odbc jdbc connection string to company database\r\n            jdbc_connection_string
=> \"jdbc:oracle:thin:@new-connection:1234:dbsource\"\r\n        \r\n            # user credentials to connect to the DB\r\n            jdbc_user => \"xyz\"\r\n            jdbc_password => \"xyz\"\r\n\r\n            # when to periodically run statement, cron format (ex: every 30 minutes)\r\n            schedule => \"30 * * * *\"\r\n\r\n            # query parameters\r\n            parameters => { \"account_no\" => \"213072717\" }\r\n\r\n            # sql statement \r\n            statement => \"SELECT * FROM accounts WHERE account_no= :account_no \r\n        }\r\n}\r\n\r\noutput {\r\n    elasticsearch {\r\n      index => \"", :backtrace=>["C:/Ashok/logstash-7.1.1/logstash-core/lib/logstash/compiler.rb:41:in `compile_imperative'", "C:/Ashok/logstash-7.1.1/logstash-core/lib/logstash/compiler.rb:49:in `compile_graph'", "C:/Ashok/logstash-7.1.1/logstash-core/lib/logstash/compiler.rb:11:in `block in compile_sources'", "org/jruby/RubyArray.java:2577:in `map'", "C:/Ashok/logstash-7.1.1/logstash-core/lib/logstash/compiler.rb:10:in `compile_sources'", "org/logstash/execution/AbstractPipelineExt.java:151:in `initialize'", "org/logstash/execution/JavaBasePipelineExt.java:47:in `initialize'", "C:/Ashok/logstash-7.1.1/logstash-core/lib/logstash/java_pipeline.rb:23:in `initialize'", "C:/Ashok/logstash-7.1.1/logstash-core/lib/logstash/pipeline_action/create.rb:36:in `execute'", "C:/Ashok/logstash-7.1.1/logstash-core/lib/logstash/agent.rb:325:in `block in converge_state'"]}
[2019-05-29T22:16:20,691][INFO ][logstash.agent           ] Successfully started Logstash API endpoint {:port=>9600}
[2019-05-29T22:16:25,419][INFO ][logstash.runner          ] Logstash shut down.
2

jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"

kindly change driver name

3

Changed it, but still getting the same error.

The error says , Expected one of #, {, } at line 30, column 17

Which at the line where I declared the output plugin [index=> "bank" is the line number 30]

output {
    elasticsearch {
      index => "bank"
      document_type => "account"
      hosts => "localhost:9200"
    }
}
4

There was a syntax error in the sql statement.

After fixing it, logstash ran successfully.

Thank you Siva for your help.

5

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