Grabbing mysql query with JDBC and sending to elasticsearch

Question

Good Moring, I'm trying to grab data from MySQL database and send it to elasticsearch, so I could create graph's in Kibana and display it.

I'm using the elasticsearch-jdbc plugin with this config:

input {
  jdbc {
    jdbc_driver_library => "/home/USERNAME/mysql-connector-java-5.1.40/mysql-connector-java-5.1.40-bin.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://localhost:3306/DB_NAME"
    jdbc_user => "USER"
    jdbc_password => "PASSWORD"
    schedule => "* * * * *"
    statement => "SELECT * from eps"
  }
}

output {
  elasticsearch {
       hosts => ["localhost:9200"]
       sniffing => false
       index => "eps"
       document_type => "eps"
       document_id => "%{uid}"
       }
}

And without success. When I'm looking at the /var/log/logstash/logstash-plain.log I get this error:

[2016-11-17T11:54:17,592][ERROR][logstash.agent           ] fetched an invalid config {:config=>"input {\n  beats {\n    port => 5044\n   y\n }\n}\n\noutput {\n  elasticsearch {\n    hosts => [\"localhost:9200\"]\n    manage_template => false\n    index => \"%{[@metadata][beat]}-%{+YYYY.MM.dd}\"\n    document_type => \"%{[@metadata][type]}\"\n  }\n}\n\ninput {\n  jdbc {\n    jdbc_driver_library => \"/home/USERNAME/mysql-connector-java-5.1.40/mysql-connector-java-5.1.40-bin.jar\"\n    jdbc_driver_class => \"com.mysql.jdbc.Driver\"\n    jdbc_connection_string => \"jdbc:mysql://localhost:3306/DB_NAME\"\n    jdbc_user => \"USER\"\n    jdbc_password => \"PASSWORD\"\n    schedule => \"* * * * *\"\n    statement => \"SELECT * from eps\"\n  }\n}\n\noutput {\n  elasticsearch {\n       hosts => [\"localhost:9200\"]\n       sniffing => false\n       index => \"eps\"\n       document_type => \"eps\"\n       document_id => \"%{uid}\"\n       }\n}\n\n", :reason=>"Expected one of #, => at line 5, column 2 (byte 42) after input {\n  beats {\n    port => 5044\n   y\n "}

I have created new id name using:

curl -XPUT 'localhost:9200/river/my_jdbc_river/_meta' -d '{
>     "type" : "jdbc",
>     "jdbc" : {
>         "url" : "jdbc:mysql://localhost:3306/MY_DB",
>         "user" : "USER",
>         "password" : "PASSWORD",
>         "sql" : "select * from eps"
>     }
> }'

I have changed for this post the USERNAME and PASSWORD, but I made sure - and they work. it's not that.

Any idea what I'm doing wrong? Thanks Idan

baudsp · Accepted Answer · 2016-11-17 12:58:52Z

1

The error message says your configuration is invalid.

From the error message, we have this:

input {
  beats {
    port => 5044
    y
  }
}

The y has nothing to do here and the problem is coming from it. It was also at the end of the error message.

answered Nov 17, 2016 at 12:58

baudsp

4,1091 gold badge20 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Val Over a year ago

Agreed, it doesn't seem that logstash takes the same configuration file as the one that is in the question.

Idan-Red Over a year ago

Thanks, I fixed that. when I'm trying to fetch information from the server I get this error: { "error" : { "root_cause" : [ { "type" : "index_not_found_exception", "reason" : "no such index", "resource.type" : "index_or_alias", "resource.id" : "jdbc", "index_uuid" : "na", "index" : "jdbc" } ], "type" : "index_not_found_exception", "reason" : "no such index", "resource.type" : "index_or_alias", "resource.id" : "jdbc", "index_uuid" : "na", "index" : "jdbc" }, "status" :

Idan-Red Over a year ago

When I fetch for river: curl -XGET 'localhost:9200/river/_search?pretty&q=*' { "took" : 54, "timed_out" : false, "_shards" : { "total" : 5, "successful" : 5, "failed" : 0 }, "hits" : { "total" : 1, "max_score" : 1.0, But when trying to view in Kibana, there is noting

baudsp Over a year ago

@Idan-Red I don't know why you get such result. And for Kibana, you might not see existing documents, depending on how the index_pattern is configured, which timestamp is on the document and which interval is used in visualisation

Idan-Red Over a year ago

@baudsp thanks for all the help. I see in the log file that it's correct now: [2016-11-17T13:24:00,018][INFO ][logstash.inputs.jdbc ] (0.001000s) SELECT * from eps Where else can I check for information and data?

|

Collectives™ on Stack Overflow

Grabbing mysql query with JDBC and sending to elasticsearch

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related