How does sql_last_value parameter work in jdbc input plugin?

Elastic Stack Logstash
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?

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?

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

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?

5

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

Yes, I started with an empty index.

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

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

Where's the ORDER BY clause?

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".

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.

1 Like
9

Okay, will try it. Thank you :slight_smile:

10

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