Updated data from jdbc(mysql) to elasticsearch by using logstash

[songhee]

Hello,
I am also newbie in elk stack.
I just connected mysql(DBMS) and elasticsearch by using logstash
and successed to send bulk data from mysql server.
I want to manage data on mysql. like create, update, delete process(CUD)
and I want to use the elasticsearch as a search engine [I need to READ data from mysql on elasticsearch]
to achieve it, the updated, deleted data should be sent to elasticsearch from mysql perfectly. by using logstash

I searched many times, it is impossible..

really it is?>??

could you help me out to find best exemple applied these process ?
or git hub url is also ok

mysql - C(CREATE), U(UPDATE), D(DELETE)
elastiscearch - R(READ)
logstash - send updated/deleted data from mysql to elasticsearch

this is my logstash configuration

input {
jdbc {
jdbc_driver_library => "../lib/mysql-connector-java-5.1.33.jar"
jdbc_driver_class => "...r"
jdbc_connection_string => "..."
jdbc_user => "..."
jdbc_password => "..."
jdbc_validate_connection => true
schedule => "* * * * * *"
tracking_column => "id"
use_column_value => true
tracking_column_type => "numeric"
clean_run => true
statement => "SELECT id, title, content, lastUpdateDate, ip, userName FROM board where id> :sql_last_value order by id"
jdbc_pool_timeout => 10
jdbc_paging_enabled => true
jdbc_page_size => 10000
}
}

output {
elasticsearch {
hosts => ["localhost:9200"]
index => "board"
document_type => "posts"
document_id => "%{id}"
action => "update"
doc_as_upsert => true
}
stdout {
codec => rubydebug
}
}

and i heard that there is a 'upsert' option in output-elasticsearch
could you explain how to use it?? is it associated with send updated date from jdbc to elasticsearch???

[yaauie (Ry Biesemeyer)]

My suspicion is that what you are looking to do isn't quite possible with this architecture.

For example, when a row is deleted from MySQL, the next time the jdbc input runs, it simply won't find that row, and therefore, the stale row will continue to reside in Elasticsearch.

Here is a recent blog post about using Elasticsearch as an app search engine for data in MySQL. It's .NET-focused, so not everything will apply to your use-case exactly, but it should provide the general concepts.