Fetching changes from table with ElasticSearch JDBC river

Question

I'm configuring JDBC river for ElasticSearch but I can't find any good config example. I've read all pages on elasticsearch-river-jdbc GitHub.

I have a SQL query and I need to fetch changes from all table columns every X seconds. How can I tell JDBC river that some row is changed and should be reindexed?

Data are fetched during ES server start, polling is happening, but changes are not fetched from DB to ES.

My configuration:

curl -XPUT 'localhost:9200/_river/itemsi/_meta' -d '{
"type" : "jdbc",
"jdbc" : {
    "driver" : "com.mysql.jdbc.Driver",
    "url" : "jdbc:mysql://mydb.com:3306/dbname",
    "user" : "yyy",
    "password" : "xxx",
    "sql" : "SELECT ii.id AS _id, ii.id AS myid, ... FROM ... LEFT JOIN .. ON...",
    "poll" : "6s",
    "strategy" : "simple"
    },
"index" : {
    "index" : "invoiceitems",
    "bulk_size" : 600,
    "max_bulk_requests" : 10,
    "bulk_timeout" : "5s",
    }
}'

Thank you.

Answer 1

You can use schedule parameter which enables repetitive runs of jdbc river plugin.

Example of a schedule parameter:

"schedule" : "0 0-59 0-23 ? * *"

This executes JDBC river every minute, every hour, all the days in the week/month/year.

For more details about schedule parameter read documentation, https://github.com/jprante/elasticsearch-river-jdbc