latest max column_name instead of sql_last_start

[igiton]

At the moment, it is not possible to query database like: "SELECT * FROM TABLE_NAME where id > : last_saved_id"
and not by using built-in parameter "sql_last_start" e.g. "SELECT * FROM TABLE_NAME where timestamp > : sql_last_start".
I don't have timestamp in my table and I want to schedule logstash to have jdbc input and output in elasticsearch.
I dont want to query whole table, I want to query only new entries from database but I don't know how and where to save the "id" from previous input. This id will be used in next scheduled time.
If you found this worth to be implemented in the future please do.

[talevy]

This would be a very useful feature for some that have ordered fields representing IDs. We may add this into the next release of this plugin

[ghost]

+1 for this feature.

I've got a lot of event data that I'm importing from SQL and I can't use sql_last_start (as the query is against a subscriber DB that gets updates shipped to it on a periodic basis).

Ideally being able to specify the column that contains my ID, and have that kept between runs would be great.

[alaendle]

+1 - Will be much more useful than :sql_last_start. Even if you have a timestamp column you couldn't use it in conjunction with :sql_last start if logstash runs on a different server and you couldn't guarantee that system times are in sync.

[nitram4]

+1 exactly what I meant #46

[crazw]

+1

[untergeek]

@igiton @willhughes @alaendle @nitram4 @crazw

You've all asked for this feature. Is it preferred to be in addition to sql_last_start or should it be an either-or thing? The current push in #108 is in an either-or configuration. While I can't imagine it being a highly desirable feature to be able to use both, I am only one user whose opinion is just that.

[ghost]

@untergeek hey, thanks for working on this.

I don't use the existing sql_last_start functionality, so having it be one or the other is fine by me.

[suyograo]

/cc @acchen97 - any thoughts on keeping both the options or either of them?

[acchen97]

I like the way the current PR is (either-or). I expect most users to use this to accurately track a particular PK or UUID. Depending on how popular the "tracking by column value" option is, it may even make sense to make it default in the future.

Would love validation from others who have requested this feature though. :)

[nitram4]

exactly, this feature will solve lot of issues with incremental fetching of data...

[alaendle]

At the moment I couldn't imagine a scenario where it makes sense to use both - so I also vote for the either-or approach. Many thanks for your work!

[untergeek]

@igiton @willhughes @alaendle @nitram4 @crazw

Feel free to upgrade your jdbc plugin to 3.0.0, which now has this feature.

To make use of it, you need to add/set the following options:

  jdbc {
    # ...other config...
    use_column_value => true
    tracking_column => MY_COLUMN_NAME
    # ...other config, if any...
  }

Also changed is the built-in metadata. When using timestamps to determine which rows to get, this was called sql_last_start. In order to keep it simple, and with one metadata value, this is now sql_last_value. sql_last_value will hold the last column value from tracking_column, so you can set up a query like:

jdbc {
  statement => "SELECT id, mycolumn1, mycolumn2 FROM my_table WHERE id > :sql_last_value"
  use_column_value => true
  tracking_column => id
  # ... other configuration bits
}

Please open a new issue if you encounter any problems.

[nitram4]

awesome, can be also used for timestamp fields?

[untergeek]

@nitram4 I don't see why not. It's a stored value. If you can do a timestamp comparison in a query string, then using the stored value should just work.

[lovejeet]

@untergeek - Tanks and missiles man !!!
Way to go. I upgraded to 3.0.0 and voila ....

[nitram4]

thanks, its working !!! i needed it so much. Thanks again

[tianchao-haohan]

Would you please update the guide in elk page, where the sql_last_start was still used:
https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#plugins-inputs-jdbc-record_last_run

[acchen97]

@tianchao-haohan please see master for the latest doc version: https://www.elastic.co/guide/en/logstash/master/plugins-inputs-jdbc.html

[tianchao-haohan]

Got that, thanks!

[geeklisted]

Any chance a tracking column could be a concat or expression of multiple columns? I.e. (timestamp + some_other_column)?

[talevy]

@geeklisted feel free to open an issue around this specifically.

one way to achieve this today is to alias a concatted field as is done in these examples: http://www.sqlbook.com/SQL/SQL-CONCATENATE-24.aspx. and using that field name as the tracking column