3

I'm using the JDBC plugin for ElasticSearch to update my MySQL database. It picks up new and changed records, but does not delete records that have been removed from MySQL. They remain in the index.

This is the code I use to create the river:

curl -XPUT 'localhost:9200/_river/account_river/_meta' -d '{
    "type" : "jdbc",
    "jdbc" : {
        "driver" : "com.mysql.jdbc.Driver",
        "url" : "jdbc:mysql://localhost:3306/test",
        "user" : "test_user",
        "password" : "test_pass",
        "sql" : "SELECT `account`.`id` as `_id`, `account`.`id`, `account`.`reference`, `account`.`company_name`, `account`.`also_known_as` from `account` WHERE NOT `account`.`deleted`",
        "strategy" : "simple",
        "poll" : "5s",
        "versioning" : true,
        "digesting" : false,
        "autocommit" : true,
        "index" : "headphones",
        "type" : "Account"
    }
}'

Installed ElasticSearch via homebrew on OSX Mountain Lion, no errors or problems and everything responds as expected. Permissions OK, no errors in logs.

I have removed, and included (and set to true and false) every combination of autocommit, versioning and digesting that I could think of. It's a dev database so I'm sure that records are deleted fully, not cached and not soft-deleted. If I delete all the records (i.e. leave the river intact and just delete what was indexed on ES), the next time the river updates it does not re-add the record, which leads me to believe I have missed something regarding versioning and deleting.

Note I've also tried various ways to specify the _id column, and I checked that it had a value via JSON on call.

Cheers.

Roopendra
  • 7,674
  • 16
  • 65
  • 92
Stephen O'Flynn
  • 2,309
  • 23
  • 34
  • 3
    An update - we could never get this working to our satisfaction and ended up taking a different approach. Our system now uses events to figure out changes to individual records and updates ElasticSearch directly. That gives us vastly finer and more granular control over what's going one and allows to reinitiate a reindex from the system. Sorry if anybody's stuck looking for the answer. – Stephen O'Flynn Jul 29 '14 at 14:18

2 Answers2

3

Since this question has been asked, the parameters have changed greatly, versioning and digesting have been deprecated, and poll has been replaced by schedule, which will take a cron expression on how often to rerun the river (below is scheduled to run every 5 mins)

    curl -XPUT 'localhost:9200/_river/account_river/_meta' -d '{
        "type" : "jdbc",
        "jdbc" : {
            "driver" : "com.mysql.jdbc.Driver",
            "url" : "jdbc:mysql://localhost:3306/test",
            "user" : "test_user",
            "password" : "test_pass",
            "sql" : "SELECT `account`.`id` as `_id`, `account`.`id`, `account`.`reference`, `account`.`company_name`, `account`.`also_known_as` from `account` WHERE NOT `account`.`deleted`",
            "strategy" : "simple",
            "schedule": "0 0/5 * * * ?" ,
            "autocommit" : true,
            "index" : "headphones",
            "type" : "Account"
        }
    }'

But for the main question, the answer i got from the developer is this https://github.com/jprante/elasticsearch-river-jdbc/issues/213

Deletion of rows is no longer detected.

I tried housekeeping with versioning, but this did not work well together with incremental updates and adding rows.

A good method would be windowed indexing. Each timeframe (maybe once per day or per week) a new index is created for the river, and added to an alias. Old indices are to be dropped after a while. This maintenance is similar to logstash indexing, but it is outside the scope of a river.

The method i am currently using as a I research aliasing is I recreate the index and river nightly, and schedule the river to run every few hours. It ensures new data being put in will be indexed that day, and deletions will reflect every 24 hrs

Jay Rizzi
  • 4,196
  • 5
  • 42
  • 71
  • On a side note, does the river simply replace the existing records using the "_id" field? In other words, if the sql statement returns a very large result set, would it also take a long time for the river to run even if very few rows are affected by the inserts/updates? Or does it somehow monitor what has changed since it last run and only insert/update those new rows. – Yamcha Jan 28 '15 at 17:32
  • I believe the answer is no, it creates duplicates of the doc with the exact same _id , you need to delete the original – Jay Rizzi Jan 28 '15 at 21:47
  • I believe you're wrong. From my experiments, it replaces it. I also read somewhere that this was the case. – Yamcha Jan 29 '15 at 20:29
  • You are correct, using _id replaces the record, it has been months and I was a little rusty, apologies – Jay Rizzi Jan 30 '15 at 22:00
0

i am still relatively new to elastic and had been using jdbc river for my project. If i understood correctly, which not necessarily could be the case, this is how it works:

  1. Fetches all rows (specified by the SQL statement in the river) from the database.
  2. calculates a digest from (id,type and index) of all the fetched rows (if new rows were added or rows were deleted this should change).
  3. for all rows re-index the documents. This will automatically increment the version of each document.
  4. increment version of the river stored in the _river index (custom)
  5. if the calculated digest in #3 is different than the one that is stored in the _river index then:
    • store it
    • run housekeeping function (deletes all docs with lower version numbers).

so considering that you would want to have a housekeeping running you need to have versioning to be set to true and subsequently this implies that digesting should be set to true as well.

So having said that your river should look like this:

curl -XPUT 'localhost:9200/_river/account_river/_meta' -d '{
    "type" : "jdbc",
    "jdbc" : {
        "driver" : "com.mysql.jdbc.Driver",
        "url" : "jdbc:mysql://localhost:3306/test",
        "user" : "test_user",
        "password" : "test_pass",
        "sql" : "SELECT `account`.`id` as `_id`, `account`.`id`, `account`.`reference`, `account`.`company_name`, `account`.`also_known_as` from `account` WHERE NOT `account`.`deleted`",
        "strategy" : "simple",
        "poll" : "5s",
        "autocommit" : true,
        "index": {
          "index" : "headphones",
          "type" : "Account",
          "versioning" : true,
          "digesting" : true
        }
    }
}'

note that versioning and digesting should be part of index definition and not jdbc definition

Алексей
  • 1,847
  • 12
  • 15
  • The above doesn't work for what I'm running. Having the index as a sub-object means it ignores index/type. I have noticed from moving a few things around that when versioning & digesting "take", there's a third document on the river, which shows a version that changes every poll. However, I have not yet seen a version on actual documents themselves, which leads me to believe that the housekeeping does not have anything to trigger on. – Stephen O'Flynn Jan 22 '14 at 09:18