No traking sql_last_value value in jdbc plugin


(Jalen Wang) #1

I'm going to use jdbc plugin to get data from oracle database,below is my sample data:

OP_TYPE    OP_DATE      OBJ_NAME          LAG HOSTNAME   GROUP_NAME RECORDLENGTH TIMESTAMP     FILESEQNO    FILERBA         ID
---------- ------------ ---------- ---------- ---------- ---------- ------------ ------------ ---------- ---------- ----------
INSERT     23-4-16   SOURCE.T1           4 oracle02   REPTGT               22 23-4-16   ########## ##########        101
INSERT     23-4-16   SOURCE.T1           4 oracle02   REPTGT               23 23-4-16   ########## ##########        102

Column ID is an auto-increment column and this value will be saved to determine where to start.but if i set use_column_value to true,sql_last_value will be 0, set use_column_column_value to false,sql_last_value will be saved as a timestamp.
Anyone could help me to save my actual ID value to sql_last_value?
below is my currently configuration

input {
    jdbc {
        jdbc_validate_connection => true
        jdbc_connection_string => "jdbc:oracle:thin:@114.94.154.172:1521/orcl"
        jdbc_user => "ogg"
        jdbc_password => "ogg"
        jdbc_driver_library => "/home/logstash/ojdbc7.jar"
        jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
        clean_run => false
        record_last_run => true
        statement => "select ID,OP_TYPE,OP_DATE,OBJ_NAME,LAG,HOSTNAME,GROUP_NAME,RECORDLENGTH,TIMESTAMP,FILESEQNO,FILERBA from ogg.ogg_history where ID>:sql_last_value"
        use_column_value => true
        tracking_column => ID
        last_run_metadata_path => "/tmp/logstash-oradb.lastrun"
        schedule => "* * * * *"
       }
}
#mutate { remove_field => [ "force_matching_signature" ] }
filter {
    # Set the timestamp to that of the ASH sample, not current time.
    mutate { convert => [ "sample_time" , "string" ]}
    date { match => ["sample_time", "ISO8601"]}
}
output {
    stdout { codec => rubydebug }
    elasticsearch {hosts=>"xx.xx.xx.xx"}
}

sql_last_value:

[logstash@iZ23qrdz9ieZ tmp]$ cat logstash-oradb.lastrun 
--- 0
[logstash@iZ23qrdz9ieZ tmp]$ cat logstash-oradb.lastrun 
--- 2016-04-23 07:08:00.012000000 Z

(Oculushut) #2

Hi there - just out of interest, but was this issue resolved in the end? Thanks!


(system) #3