How to synchronize with oracle when data updated?


(Tao.Z) #1

I'm trying to synchronize oracle to ElasticSearch by logstash-input-jdbc .But I failed.
Could you tell me how to do it?

Env:
windows 7 64bit (※Japanese)
oracleDB 11g
ElasticSearch 5.1.1
Logstash 5.1.1

Ex: syntest.conf
input {  
  jdbc {
    jdbc_driver_library => "ojdbc6.jar"
    jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
    jdbc_connection_string => "jdbc:oracle:thin:@//localhost:1521/XE"
    jdbc_user => userid
    jdbc_password => userpass
    schedule => "*/2 * * * *"
    statement => "select * from TEST where TEST.modified > :sql_last_value"
    last_run_metadata_path => "./barbar.dat"
  }
} 

output { 
  elasticsearch { 
    hosts => ["localhost:9200"] 
    index => "test"
    document_type => "TEST"
    document_id => "%{id}"
  } 
}

Ex: TEST table
id,name,modified
1111,testdata,2016/12/20 20:28:08

Ex: SQL
update TEST set MODIFIED = TO_DATE('2016/12/21 20:28:08','yyyy/mm/dd hh24:mi:ss') where id > 1000;
update TEST set name = 'testdata02' where id = '1111';

The result in below show logstash runed successfully but ES's result is old.

  {
    "_index" : "test",
    "_type" : "TEST",
    "_id" : "1111",
    "_score" : 0.826844,
    "_source" : {
      "tags" : [ ],
      "@timestamp" : "2016-12-19T02:50:00.278Z",
      "classname" : null,
      "name" : "testdata",
      "@version" : "1",
      "modified" : "2016-12-17T11:28:08.000Z",
      "id" : 1111,
    }

Could you tell me how to synchronize ?
And is it possible to check the version of ES's data. (Does @version 's value will change when data updated? )


(Tao.Z) #2

I find I can synchronize data with same config. Maybe just my env had some problem.

So my remaining question is fellowing:
Is it possible to check the version of ES's data. (Does @version 's value will change when data updated? )


(Magnus Bäck) #3

Is it possible to check the version of ES's data.

What do you mean?

Does @version 's value will change when data updated?

No, @version is the version of Logstash's event schema. Elasticsearch documents have a _version metadata field that increments by one for each update, but I don't see how you could use that here.


(Tao.Z) #4

Thank you very much.

> Is it possible to check the version of ES's data.
What do you mean?

I mean is there a field which incremented when data updated. If there is ,I think I can see it to check update worked successfully or not.


(Magnus Bäck) #5

Yes, the _version field is incremented each time the documented is updated.


(Tao.Z) #6

Thank you very much. It's clear.


(system) #7

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.