# JDBC River - Which approach **URL:** \ **Category:** Elasticsearch\ **Created:** [September 13, 2013, 3:43am UTC](https://discuss.elastic.co/t/jdbc-river-which-approach/13583 "2013-09-13T03:43:41Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Prasanth\_Nair](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/prasanth_nair/32/2102_2.png) [@Prasanth\_Nair](https://discuss.elastic.co/u/Prasanth_Nair)\ **Post date:** [September 13, 2013, 3:43am UTC](https://discuss.elastic.co/t/jdbc-river-which-approach/13583/1 "2013-09-13T03:43:41Z")
All, I'm working on creating a jdbc river which essentially connects to a Mysql table (millions of rows, where updates / addition of rows will be very common). Having said that, I would like to get suggestion on what would be an ideal mechanism for search index and mysql to be in sync (if possible, near real time). I tried versioning and update table approach but would like to know are there some best practices on above requirement. thanks for helping prash -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [September 13, 2013, 5:16am UTC](https://discuss.elastic.co/t/jdbc-river-which-approach/13583/2 "2013-09-13T05:16:05Z")
I would not recommend here to use a river as you need "real time" but I would push directly from the source (service layer) as soon as I update something in the database. Makes sense? What techno stack do you have? -- David 😉 Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs Le 13 sept. 2013 à 05:43, Prasanth Nair [pn@leapcourse.com](mailto:pn@leapcourse.com) a écrit : All, I'm working on creating a jdbc river which essentially connects to a Mysql table (millions of rows, where updates / addition of rows will be very common). Having said that, I would like to get suggestion on what would be an ideal mechanism for search index and mysql to be in sync (if possible, near real time). I tried versioning and update table approach but would like to know are there some best practices on above requirement. thanks for helping ## prash You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [September 13, 2013, 7:16am UTC](https://discuss.elastic.co/t/jdbc-river-which-approach/13583/3 "2013-09-13T07:16:04Z")
JDBC river is for never changing or slow changing data, main purpose is demo mode (moving data from RDBMS to ES). If you want sync, you could use MySQL triggers. Simply said, if you have few changes and don"t want to spend much effort, use sys\_exec in a MySQL trigger to push the change with curl into the ES REST API. If you want a more sophisticated method or if you have lots of thousands of changes, a sys\_exec from a trigger obviously wouldn't scale. In such case, I would try writing a binlog based pusher in Java using [https://code.google.com/p/open-replicator/](https://code.google.com/p/open-replicator/) But, the pusher must know from the MySQL operation stream what data to push to ES and how, which strongly depends on the application. Jörg -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Prasanth\_Nair](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/prasanth_nair/32/2102_2.png) [@Prasanth\_Nair](https://discuss.elastic.co/u/Prasanth_Nair)\ **Post date:** [September 13, 2013, 11:42am UTC](https://discuss.elastic.co/t/jdbc-river-which-approach/13583/4 "2013-09-13T11:42:26Z")
Thanks David for the thoughts, I'm using spring / jaxrs service layer and PHP frontend. I see where you are coming from and our original thought was to use an event queue (MQ) to propagate DB writes to ES, it looks like I got overexcited by River possibility 🙂 -- prasanth nair On 13 September 2013 at 10:46:13 AM, David Pilato (david@pilato.fr) wrote: I would not recommend here to use a river as you need "real time" but I would push directly from the source (service layer) as soon as I update something in the database. Makes sense? What techno stack do you have? -- David 😉 Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs Le 13 sept. 2013 à 05:43, Prasanth Nair [pn@leapcourse.com](mailto:pn@leapcourse.com) a écrit : All, I'm working on creating a jdbc river which essentially connects to a Mysql table (millions of rows, where updates / addition of rows will be very common). Having said that, I would like to get suggestion on what would be an ideal mechanism for search index and mysql to be in sync (if possible, near real time). I tried versioning and update table approach but would like to know are there some best practices on above requirement. thanks for helping prash -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- You received this message because you are subscribed to a topic in the Google Groups "elasticsearch" group. To unsubscribe from this topic, visit [https://groups.google.com/d/topic/elasticsearch/yZYBz2rIBxw/unsubscribe](https://groups.google.com/d/topic/elasticsearch/yZYBz2rIBxw/unsubscribe). To unsubscribe from this group and all its topics, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [September 13, 2013, 12:13pm UTC](https://discuss.elastic.co/t/jdbc-river-which-approach/13583/5 "2013-09-13T12:13:14Z")
I ended up pushing my index/update requests to ActiveMQ 😉 -- David 😉 Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs Le 13 sept. 2013 à 13:42, prasanth nair [pn@leapcourse.com](mailto:pn@leapcourse.com) a écrit : > Thanks David for the thoughts, > > I'm using spring / jaxrs service layer and PHP frontend. I see where you are coming from and our original thought was to use an event queue (MQ) to propagate DB writes to ES, it looks like I got overexcited by River possibility 🙂 > > -- > prasanth nair > On 13 September 2013 at 10:46:13 AM, David Pilato ([david@pilato.fr](mailto:david@pilato.fr)) wrote: > > > I would not recommend here to use a river as you need "real time" but I would push directly from the source (service layer) as soon as I update something in the database. > > > > Makes sense? > > > > What techno stack do you have? > > > > -- > > David 😉 > > Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs > > > > Le 13 sept. 2013 à 05:43, Prasanth Nair [pn@leapcourse.com](mailto:pn@leapcourse.com) a écrit : > > > > All, > > > > I'm working on creating a jdbc river which essentially connects to a Mysql table (millions of rows, where updates / addition of rows will be very common). Having said that, I would like to get suggestion on what would be an ideal mechanism for search index and mysql to be in sync (if possible, near real time). I tried versioning and update table approach but would like to know are there some best practices on above requirement. > > > > thanks for helping > > > > ## prash > > > > ## You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). > > > > You received this message because you are subscribed to a topic in the Google Groups "elasticsearch" group. > > To unsubscribe from this topic, visit [https://groups.google.com/d/topic/elasticsearch/yZYBz2rIBxw/unsubscribe](https://groups.google.com/d/topic/elasticsearch/yZYBz2rIBxw/unsubscribe). > > To unsubscribe from this group and all its topics, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). > > -- > > You received this message because you are subscribed to the Google Groups "elasticsearch" group. > > To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Prasanth\_Nair](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/prasanth_nair/32/2102_2.png) [@Prasanth\_Nair](https://discuss.elastic.co/u/Prasanth_Nair)\ **Post date:** [September 13, 2013, 12:53pm UTC](https://discuss.elastic.co/t/jdbc-river-which-approach/13583/6 "2013-09-13T12:53:05Z")
Thanks Joerg, I think I got a fair idea about DOs and DONTS of river. I will stick with managing this through app layer -- prasanth nair On 13 September 2013 at 12:46:10 PM, [joergprante@gmail.com](mailto:joergprante@gmail.com) ([joergprante@gmail.com](mailto:joergprante@gmail.com)) wrote: JDBC river is for never changing or slow changing data, main purpose is demo mode (moving data from RDBMS to ES). If you want sync, you could use MySQL triggers. Simply said, if you have few changes and don"t want to spend much effort, use sys\_exec in a MySQL trigger to push the change with curl into the ES REST API. If you want a more sophisticated method or if you have lots of thousands of changes, a sys\_exec from a trigger obviously wouldn't scale. In such case, I would try writing a binlog based pusher in Java using [https://code.google.com/p/open-replicator/](https://code.google.com/p/open-replicator/) But, the pusher must know from the MySQL operation stream what data to push to ES and how, which strongly depends on the application. Jörg -- You received this message because you are subscribed to a topic in the Google Groups "elasticsearch" group. To unsubscribe from this topic, visit [https://groups.google.com/d/topic/elasticsearch/yZYBz2rIBxw/unsubscribe](https://groups.google.com/d/topic/elasticsearch/yZYBz2rIBxw/unsubscribe). To unsubscribe from this group and all its topics, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Umit\_Seren](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/umit_seren/32/44933_2.png) [@Umit\_Seren](https://discuss.elastic.co/u/Umit_Seren)\ **Post date:** [September 16, 2013, 8:37am UTC](https://discuss.elastic.co/t/jdbc-river-which-approach/13583/7 "2013-09-16T08:37:00Z")
I did create a river with "oneshot" strategy to initially sync the data between my PostgreSQL db and ES. For changes afterwards I sync them using the service layer (so whenever an entity is changed, it will be changed in the DB and in ES). On Friday, September 13, 2013 5:43:41 AM UTC+2, Prasanth Nair wrote: > All, > > I'm working on creating a jdbc river which essentially connects to a Mysql > table (millions of rows, where updates / addition of rows will be very > common). Having said that, I would like to get suggestion on what would be > an ideal mechanism for search index and mysql to be in sync (if possible, > near real time). I tried versioning and update table approach but would > like to know are there some best practices on above requirement. > > thanks for helping > > prash -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 2:16am UTC](https://discuss.elastic.co/t/jdbc-river-which-approach/13583/8 "2017-07-06T02:16:24Z")