# Jdbc input plugin and sql\_last\_value **URL:** \ **Category:** Logstash\ **Created:** [August 24, 2018, 8:30am UTC](https://discuss.elastic.co/t/jdbc-input-plugin-and-sql-last-value/145869 "2018-08-24T08:30:40Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![rubpa](https://avatars.discourse-cdn.com/v4/letter/r/b5e925/32.png) [@rubpa](https://discuss.elastic.co/u/rubpa)\ **Post date:** [August 24, 2018, 8:30am UTC](https://discuss.elastic.co/t/jdbc-input-plugin-and-sql-last-value/145869/1 "2018-08-24T08:30:40Z")
I'm considering replicating a MySQL database into elasticsearch to enable Kibana visualizations on that data. There's another application that runs off the database and the existing data gets updated. It appears that logstash + [Jdbc input plugin](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html) is the best way for this. I found `sql_last_value` option that can be used to detect and incrementally update the elastic data. However, my database does not have any column to indicate the updated records. If the plugin query is just `SELECT * FROM my_table`, would these incremental updates work? In other words, does the plugin or MySQL have any inbuilt feature that allows the plugin to figure out updated records? ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [August 27, 2018, 8:14pm UTC](https://discuss.elastic.co/t/jdbc-input-plugin-and-sql-last-value/145869/2 "2018-08-27T20:14:41Z")
> does the plugin or MySQL have any inbuilt feature that allows the plugin to figure out updated records? No. What you might be able to do is fetch all rows and store them in ES with the same document id each time, i.e. so you'll be overwriting the same documents over and over again. It's clearly inefficient (perhaps prohibitively so), but if there's no way to figure out the modified rows it's the best you can do. ---
**Author:** ![rubpa](https://avatars.discourse-cdn.com/v4/letter/r/b5e925/32.png) [@rubpa](https://discuss.elastic.co/u/rubpa)\ **Post date:** [August 28, 2018, 7:33am UTC](https://discuss.elastic.co/t/jdbc-input-plugin-and-sql-last-value/145869/3 "2018-08-28T07:33:22Z")
So, as I understand, if I use the primary key `id` of my table as the `document_id` in the elasticsearch output, it would overwrite all documents every time it runs (as per the schedule). Anyway the table size is about 32MB as per MySQL with about 30k records. In my opinion, with a 5 min schedule, this amount of data should be tiny for my single-node elastic cluster. Of course, this solution would not scale if I also want other tables - I do have one with 3 million records. ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [August 28, 2018, 7:33am UTC](https://discuss.elastic.co/t/jdbc-input-plugin-and-sql-last-value/145869/4 "2018-08-28T07:33:57Z")
Yes, your understanding is correct. ---
**Author:** ![rubpa](https://avatars.discourse-cdn.com/v4/letter/r/b5e925/32.png) [@rubpa](https://discuss.elastic.co/u/rubpa)\ **Post date:** [August 28, 2018, 7:59am UTC](https://discuss.elastic.co/t/jdbc-input-plugin-and-sql-last-value/145869/5 "2018-08-28T07:59:48Z")
I do have the option to modify the database. I came across [this](https://stackoverflow.com/a/40747698) and [this](https://medium.com/@bengarvey/use-an-updated-at-column-in-your-mysql-table-and-make-it-update-automatically-6bf010873e6a) which suggests using an `updatedAt` field. If I do setup my table as suggested, I guess my query would look like `SELECT * FROM my_table WHERE updatedAt > :sql_last_value ORDER BY updatedAt`. Can you confirm the following? It is not very obvious in the documentation of the plugin. - that the `ORDER BY` clause is compulsory - using `sql_last_value` in the query means `use_column_value` and `tracking_column` become mandatory ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [August 28, 2018, 8:39am UTC](https://discuss.elastic.co/t/jdbc-input-plugin-and-sql-last-value/145869/6 "2018-08-28T08:39:26Z")
> that the `ORDER BY` clause is compulsory Yes. > using `sql_last_value` in the query means `use_column_value` and `tracking_column` become mandatory Yes. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [September 25, 2018, 8:39am UTC](https://discuss.elastic.co/t/jdbc-input-plugin-and-sql-last-value/145869/7 "2018-09-25T08:39:28Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.