How does sql_last_value parameter work in jdbc input plugin?


(Amruth) #1

Hi,

I am using JDBC input plugin with

use_column_value => true
tracking_column => "id".

But the problem is that my id column is not an incremental all the time. For example it may be as 1,2,3,5,6,7,10,9,8. So what does the file for last_run_metadata_path contain? 8 or 10?

Can someone please help me understand this?


(Magnus Bäck) #2

It'll contain 8 since it's the last value seens that's recorded. Can't you just sort on the id column to make sure the values are delivered in order?


(Amruth) #3

Hi Magnus,

Coming to the real scenario,

Total number of rows = 3294
Last value I see in the row = 3178
Value in last_run_metadat_path file = --- 3294
Count I see in Elasticsearch(from Kibana) = 3541

I don't quite understand the logic here. Can you please explain?

Thanks


(Magnus Bäck) #4

Total number of rows = 3294
Last value I see in the row = 3178
Value in last_run_metadat_path file = --- 3294

And what query did you end up using?

Count I see in Elasticsearch(from Kibana) = 3541

Well, I can't tell you what those extra 250 documents come from. Are you sure you started with an empty index? What does your elasticsearch output configuration look like?


(Amruth) #5

statement => "SELECT * from table_name WHERE id > :sql_last_value"

Yes, I started with an empty index.

elasticsearch {  
	hosts => "******"
	index => "twiiter" 
}

(Magnus Bäck) #6

statement => "SELECT * from table_name WHERE id > :sql_last_value"

Where's the ORDER BY clause?


(Amruth) #7

statement => "SELECT * from table_name WHERE id > :sql_last_value ORDER by id"

If I use this statement, will it work correctly? I am assuming it picks the id which is greater than sql_last_value and then perform "ORDER by".


(Magnus Bäck) #8

Yes. The highest value will then be processed last, so the next time you run the query it'll ignore all previously seen values.


(Amruth) #9

Okay, will try it. Thank you :slight_smile:


(system) #10

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