How to update Elasticsearch data from mysql?

Question

I havee 3 tables on mysql.

And indexed the data to elasticsearch.

It worked with below.(logstash config)

input {

                 jdbc {
                jdbc_driver_library => "lib/mysql-connector-java-5.1.33.jar"
                jdbc_driver_class => "com.mysql.jdbc.Driver"
                jdbc_connection_string => "jdbc:mysql://localhost:3306/test"
                jdbc_user => "test"
                jdbc_password => "test"
                statement => "SELECT * FROM TABLE1"
                schedule => "* * * * *"
                                        type => "table1"


                 }
    //  
    // two more inputs
    //

}

output {
                stdout {codec => rubydebug}
                if [type] == "table1" {
                        elasticsearch {
                                hosts => ["localhost:9200"]
                                                 index => "test"
                                                document_type => "%{type}"
                                                document_id => "%{id}"
                                                template => "./template"
                                                template_overwrite => true
                                 }
                }
             //
             // two more outputs
             //

}

I deleted other two inputs and outputs, some rows from TABLE1 on mysql.

And started logstash.

It was not updated.

So, typed

curl -XDELETE 'http://localhost:9200/*'

and restared logstash again.

But three tables were added again with same rows on elasticsearch.

How to update elasticsearch data automatically?

Kulasangar · Accepted Answer · 2017-02-24 10:48:24Z

2

I guess you're missing out use column value, tracking column and it's type in your jdbc input:

jdbc {
    jdbc_driver_library => "lib/mysql-connector-java-5.1.33.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://localhost:3306/test"
    jdbc_user => "test"
    jdbc_password => "test"
    statement => "SELECT * FROM TABLE1"
    schedule => "* * * * *"
    type => "table1"
    use_column_value => true
    tracking_column => "id"  <-- this should be the incrementing value in your table
    tracking_column_type => "numeric"    
}

tracking_column is used in order to trace which column to map when updating values in your table. This should do the trick for you. Hope it helps!

answered Feb 24, 2017 at 10:48

Kulasangar

9,5326 gold badges65 silver badges97 bronze badges

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

8 Comments

USER Over a year ago

Omg. I am using logstash version 2.1.1 and it doesn't support use_column_value, tracking_column, tracking_column_type. Is there other way to solve this problem on version 2.1.1?

USER Over a year ago

I have updated 2.1.1 to 2.4.1. And Added three line of code. use_column_value => true tracking_column => "id" lowercase_column_names => false. it returned same value

Kulasangar Over a year ago

I'm not sure whether it would work without tracking_column_type => "numeric", since I've tried with that only. I'll get back to you after checking if there's any workaround for this.

USER Over a year ago

I deleted a row and ran curl -XDELETE 'localhost:9200*' again and ran logstash again. then the row was deleted. But garbage rows were still remain(I deleted tables and rows last month). Also it wasn't automatically updated. Should I reinstall elasticsearch?

Kulasangar Over a year ago

Well if you're to make the changes from the place where you left off, (like having the last updated id) then yes you may have to delete the indice since the changes will be applied on the new rows, but if you're still reading it from the first row itself then you don't have to. Reinstall in the sense? You mean to restart the service?

|

Collectives™ on Stack Overflow

How to update Elasticsearch data from mysql?

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related