Logstash creating more rows than the source table


(sami) #1

I have only 24062 rows in my RDBMS table but logstash jdbc connection (conf file shown below) keep adding documents to the elastic search in endless loop .

[root@hadoop1 kibana]# curl 'hadoop5:9200/_cat/indices?v' | grep pa_lane_txn
health status index uuid pri rep docs.count docs.deleted store.size pri.store.size
green open pa_lane_txn _aCoJU2MRQOnW5uneK2V1w 5 1 129271 0 91.7mb 41.6mb

below is the source rdbms table details

SQL> show user
USER is "PATRON"
SQL> select count(*) from tab1;

COUNT(*)

 24062

the logstash config file is

input {
jdbc {
jdbc_validate_connection => true
jdbc_connection_string => "jdbc:oracle:thin:@patronQA:1526/patron"
jdbc_user => "patron"
jdbc_password => "xxxxx"
jdbc_driver_library => "/home/admin/ojdbc6.jar"
jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
statement => "SELECT TXN_ID,TXN_PROCESS_DATE,TRANSP_ID,TRANSP_CLASS,EXT_PLAZA_ID,EXT_LANE_ID,EXT_LANE_TYPE_CODE,EXT_DATE_TIME,TRANSP_CUR_
BAL,AVC_CLASS,TOLL_AMT_CHARGED,TOLL_AMT_COLLECTED,TOLL_AMT_FULL,CREDIT_LIST_UPDATE_FLAG,TRANS_SOURCE,REVCLASS_REV_CLASS_CODE,PAYMENT_METHOD_CODE,
ENT_PLAZA_ID,ENT_DATE_TIME,ENT_LANE_ID,ENT_LANE_TYPE_CODE,AGENCY_REJECT_CODE,MSG_ID,TRANSP_INTERNAL_NUM,UFM_PAYMENT_CODE,VEH_LIC_NUM,STATE_ID_COD
E,ORIG_TXN_ID from TAB1 WHERE TXN_PROCESS_DATE >:sql_last_value "
schedule => "*/2 * * * * *" ## every 2 secs
use_column_value => "true"
tracking_column => "TXN_PROCESS_DATE"
}
}
output {
elasticsearch {
action => "index"
hosts => ["hadoop5:9200"]
index => "pa_lane_txn"
document_type => "record"
workers => 1
}
}


(Magnus Bäck) #2

So it seems the WHERE TXN_PROCESS_DATE >:sql_last_value part of the query isn't working. What do the queries actually look like (parameters and all)? Is it asking the same query over and over? You may have to bump Logstash's log level to get useful logs (or check the query logs on the Oracle side).


(system) #3

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