Jdbc Oracle - only one row returned before closing

c95mbq · July 7, 2017, 12:29am

Hi,

I'm an absolute noob so apologizing in advance.

I'm using version 5.4.0 of ELK and have a working Logstash configuration for indexing a csv file. This works ok but I wanted to try and query directly for data and ultimately setup a schedule using jdbc and Logstash. However, when I run the conf file, only one row/record seems to be returned before shutting down. A cut-down version of the conf file as per below:

input {

	jdbc {
		  jdbc_driver_library => "E:\Oracle\client64\product\12.1.0\client_1\jdbc\lib\ojdbc7.jar"
			   jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
			   jdbc_connection_string => "jdbc:oracle:thin:@fqn:port/servicename"
			   jdbc_user => "user"
			   jdbc_password => "password"
	   
			statement => "select c.id as ED_ID,to_char(c.updt_dt_tm, 'dd/mm/yyyy hh24:mi') as UPDT_DT_TM,regexp_substr(c.trigger2,'[^|]+', 1, 2) as RESUS_PATIENTS from cust_ed_esc c order by c.id"
	}
}
filter {
	date {
target => "@timestamp"
match => ["UPDT_DT_TM", 'dd/MM/yyyy HH:mm', "dd/mm/yyyy HH:mm"]
	}
	mutate {
		convert => ["ED_ID","integer"]
		convert => ["RESUS_PATIENTS","integer"]
	}
}

output {
	stdout { codec => rubydebug }
}

I can't see anything that stands out in the output either, looks like it fetches a single record:

09:40:18.081 [[main]>worker10] DEBUG logstash.pipeline - output received

 {
                        "ed_id" => 14.0,
                   "updt_dt_tm" => "05/05/2017 14:56",
               "resus_patients" => "2"
}

09:40:18.097 [Ruby-0-Thread-22: C:/logstash-5.4.0/logstash-core/lib/logstash/pipeline.rb:532] DEBUG logstash.pipeline - Pushing flush onto pipeline
09:40:18.098 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=>"#<Thread:0x39d84788 sleep>"}
09:40:18.099 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=>"#<Thread:0x20ab08ea sleep>"}
09:40:18.099 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=>"#<Thread:0x5da8c9c2 sleep>"}
09:40:18.099 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=>"#<Thread:0x3392047e sleep>"}
09:40:18.100 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=>"#<Thread:0x37339df3 sleep>"}
09:40:18.101 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=>"#<Thread:0x6a94c55a sleep>"}
09:40:18.101 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=>"#<Thread:0x18cf4dd7 sleep>"}
09:40:18.102 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=>"#<Thread:0x290929d7 sleep>"}
09:40:18.103 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=>"#<Thread:0x1d472a0d sleep>"}
09:40:18.103 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=>"#<Thread:0x438978c9 sleep>"}
09:40:18.103 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=>"#<Thread:0x3fbacca9 sleep>"}
09:40:18.104 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=>"#<Thread:0x312ac07c sleep>"}
09:40:18.104 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=>"#<Thread:0xf16ee85 sleep>"}
09:40:18.241 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x20ab08ea
09:40:18.241 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x5da8c9c2
09:40:18.241 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x3392047e
09:40:18.242 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x37339df3
09:40:18.242 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x6a94c55a
09:40:18.242 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x18cf4dd7
09:40:18.243 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x290929d7
09:40:18.243 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x3c976bfa
09:40:18.243 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x6503161b
09:40:18.244 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x1d472a0d
09:40:18.244 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x438978c9
09:40:18.244 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x3fbacca9
09:40:18.244 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x312ac07c
09:40:18.245 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0xf16ee85
09:40:18.245 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x3ca4bd16
09:40:18.245 [[main]-pipeline-manager] DEBUG logstash.filters.date - closing {:plugin=>"LogStash::Filters::Date"}
09:40:18.246 [[main]-pipeline-manager] DEBUG logstash.filters.mutate - closing {:plugin=>"LogStash::Filters::Mutate"}
09:40:18.246 [[main]-pipeline-manager] DEBUG logstash.outputs.stdout - closing {:plugin=>"LogStash::Outputs::Stdout"}
09:40:18.247 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pipeline main has been shutdown
09:40:20.701 [LogStash::Runner] DEBUG logstash.instrument.periodicpoller.os - PeriodicPoller: Stopping
09:40:20.702 [LogStash::Runner] DEBUG logstash.instrument.periodicpoller.jvm - PeriodicPoller: Stopping
09:40:20.703 [LogStash::Runner] DEBUG logstash.instrument.periodicpoller.persistentqueue - PeriodicPoller: Stopping
09:40:20.714 [LogStash::Runner] WARN logstash.agent - stopping pipeline {:id=>"main"}
09:40:20.715 [LogStash::Runner] DEBUG logstash.pipeline - Closing inputs
09:40:20.716 [LogStash::Runner] DEBUG logstash.inputs.jdbc - stopping {:plugin=>"LogStash::Inputs::Jdbc"}
09:40:20.718 [LogStash::Runner] DEBUG logstash.pipeline - Closed inputs

Any help or pointers as to why only a single record is returned would be much appreciated - maybe that's not even the case and multiple records are returned but regardless if I send output to console or into Elastic, I only ever see one record.

Editing this post to add that it seems it is always the same record that is being returned. The query is on a table with a unique index on a field called ED_ID and it's always the same record that's returned despite the query containing an order by clause on the ed_id field and it's not the first record that's returned.

Thank you for your kind help
/Magnus

c95mbq · July 11, 2017, 10:40pm

Also posted this same question on SO with slightly altered config, still hoping someone will see something obviously wrong. https://stackoverflow.com/questions/45002704/logstash-jdbc-connection-only-returns-one-row

I see the below output in the console, which probably explains why it's only returning one record but can't for the life of me understand why it would insert a where clause on rownum, see below:

11:26:30.665 [[main]<jdbc] INFO logstash.inputs.jdbc - (0.084000s) SELECT * FROM (SELECT count() "COUNT" FROM (select c.id as ED_ID,to_char(c.updt_dt_tm, 'dd/mm/yyyy hh24:mi') as UPDT_DT_TM,c.esc_status as ED_STATUS,c.ed_score as ED_SCORE
,regexp_substr(c.trigger2,'[^|]+', 1, 2) as RESUS_PATIENTS,regexp_substr(c.trigger3,'[^|]+', 1, 2) AS ACUTE_PATIENTS,regexp_substr(c.trigger4,'[^|]+', 1, 2) AS
RAFT_PATIENTS,regexp_substr(c.trigger5,'[^|]+', 1, 2) AS ISO_PATIENTS,regexp_substr(c.trigger6,'[^|]+', 1, 2) AS TOC_PATIENTS,regexp_substr(c.trigger7,'[^|]+',1, 2) AS ETA_PATIENTS,regexp_substr(c.trigger8,'[^|]+', 1, 2) AS PAED_AM_PATIENT
S,regexp_substr(c.trigger9,'[^|]+', 1, 2) AS PAED_PM_PATIENTS,regexp_substr(c.trigger10,'[^|]+', 1, 2) AS TO_BE_SEEN_BY_DR_PATIENTS,regexp_substr(c.trigger11,'[^|]+',1,2) AS WITHOUT_BED_PATIENTS,regexp_substr(c.trigger12,'[^|]+', 1, 2) AS TOTAL_PATIENTS,regexp_substr(c.trigger13,'[^|]+', 1, 2) AS EMU_PATIENTS from cust_ed_esc c order by c.id) "T1") "T1" WHERE (ROWNUM <= 1)
11:26:30.670 [[main]<jdbc] DEBUG logstash.inputs.jdbc - Executing JDBC query {:statement=>"select c.id as ED_ID,to_char(c.updt_dt_tm, 'dd/mm/yyyy hh24:mi') as UPDT_DT_TM,c.esc_status as ED_STATUS,c.ed_score as ED_SCORE,regexp_substr(c.trigger2,'[^|]+', 1, 2) as RESUS_PATIENTS,regexp_substr(c.trigger3,'[^|]+', 1, 2) AS ACUTE_PATIENTS,regexp_substr(c.trigger4,'[^|]+', 1, 2) AS RAFT_PATIENTS,regexp_substr(c.trigger5,'[^|]+', 1, 2) AS ISO_PATIENTS,regexp_substr(c.trigger6,'[^|]+', 1, 2) AS TOC_PATIENTS,regexp_substr(c.trigger7,'[^|]+', 1, 2) AS ETA_PATIENTS,
regexp_substr(c.trigger8,'[^|]+', 1, 2) AS PAED_AM_PATIENTS,regexp_substr(c.trigger9,'[^|]+', 1, 2) AS PAED_PM_PATIENTS,regexp_substr(c.trigger10,'[^|]+', 1, 2)
AS TO_BE_SEEN_BY_DR_PATIENTS,regexp_substr(c.trigger11,'[^|]+',1,2) AS WITHOUT_BED_PATIENTS,regexp_substr(c.trigger12,'[^|]+', 1, 2) AS TOTAL_PATIENTS,regexp_substr(c.trigger13,'[^|]+', 1, 2) AS EMU_PATIENTS from cust_ed_esc c order by c.id", :parameters=>{:sql_last_value=>1970-01-01 00:00:00 UTC}, :count=>1}
11:26:30.674 [[main]<jdbc] INFO logstash.inputs.jdbc - (0.001000s) SELECT * FROM (SELECT count() "COUNT" FROM (select c.id as ED_ID,to_char(c.updt_dt_tm, 'dd/mm/yyyy hh24:mi') as UPDT_DT_TM,c.esc_status as ED_STATUS,c.ed_score as ED_SCORE
,regexp_substr(c.trigger2,'[^|]+', 1, 2) as RESUS_PATIENTS,regexp_substr(c.trigger3,'[^|]+', 1, 2) AS ACUTE_PATIENTS,regexp_substr(c.trigger4,'[^|]+', 1, 2) AS
RAFT_PATIENTS,regexp_substr(c.trigger5,'[^|]+', 1, 2) AS ISO_PATIENTS,regexp_substr(c.trigger6,'[^|]+', 1, 2) AS TOC_PATIENTS,regexp_substr(c.trigger7,'[^|]+',1, 2) AS ETA_PATIENTS,regexp_substr(c.trigger8,'[^|]+', 1, 2) AS PAED_AM_PATIENT
S,regexp_substr(c.trigger9,'[^|]+', 1, 2) AS PAED_PM_PATIENTS,regexp_substr(c.trigger10,'[^|]+', 1, 2) AS TO_BE_SEEN_BY_DR_PATIENTS,regexp_substr(c.trigger11,'[^|]+',1,2) AS WITHOUT_BED_PATIENTS,regexp_substr(c.trigger12,'[^|]+', 1, 2) AS TOTAL_PATIENTS,regexp_substr(c.trigger13,'[^|]+', 1, 2) AS EMU_PATIENTS from cust_ed_esc c order by c.id) "T1") "T1" WHERE (ROWNUM <= 1)
11:26:30.682 [[main]<jdbc] INFO logstash.inputs.jdbc - (0.005000s) SELECT * FROM (SELECT * FROM (select c.id as ED_ID,to_char(c.updt_dt_tm, 'dd/mm/yyyy hh24:mi') as UPDT_DT_TM,c.esc_status as ED_STATUS,c.ed_score as ED_SCORE,regexp_substr(
c.trigger2,'[^|]+', 1, 2) as RESUS_PATIENTS,regexp_substr(c.trigger3,'[^|]+', 1, 2) AS ACUTE_PATIENTS,regexp_substr(c.trigger4,'[^|]+', 1, 2) AS RAFT_PATIENTS,regexp_substr(c.trigger5,'[^|]+', 1, 2) AS ISO_PATIENTS,regexp_substr(c.trigger6,
'[^|]+', 1, 2) AS TOC_PATIENTS,regexp_substr(c.trigger7,'[^|]+', 1, 2) AS ETA_PATIENTS,regexp_substr(c.trigger8,'[^|]+', 1, 2) AS PAED_AM_PATIENTS,regexp_substr(c.trigger9,'[^|]+', 1, 2) AS PAED_PM_PATIENTS,regexp_substr(c.trigger10,'[^|]+'
, 1, 2) AS TO_BE_SEEN_BY_DR_PATIENTS,regexp_substr(c.trigger11,'[^|]+',1,2) AS WITHOUT_BED_PATIENTS,regexp_substr(c.trigger12,'[^|]+', 1, 2) AS TOTAL_PATIENTS,regexp_substr(c.trigger13,'[^|]+', 1, 2) AS EMU_PATIENTS from cust_ed_esc c order
by c.id) "T1") "T1" WHERE (ROWNUM <= 1)

editing post to add that if I manually run the queries output in the above log in something like SQL developer, they do return the right data, all of the data, and so the question remains why only one row seems to return when run from logstash.

system · August 8, 2017, 10:40pm

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

Topic		Replies	Views
Jdbc and consistence of data Logstash	5	2055	October 8, 2018
Logstash input-jdbc work logic and last run sql_last_value Logstash	1	888	November 13, 2018
Logstash deleting records from Db Table after running logstash.conf file Logstash	3	559	November 26, 2018
SQL execute for full table Logstash	3	356	November 7, 2018
Loading Oracle into Elastic with Logstash Logstash	5	1952	August 26, 2020

Jdbc Oracle - only one row returned before closing

Related topics