Keeping jdbc river in sync with DB

Elastic Stack Elasticsearch
1

Hi all,

I'm trying to figure out how to configure jdbc river in production
environment.

We want to index a DB with appr. 10% write queries and 90% read queries and
need to keep ES index in sync with DB.

So I wonder how two configure river to keep Index in near-realtime Sync
with DB.

I'm thinking about pulling the DB changes every minute. Is jdbc-river
capable of updating it's already indexed data and is this a good strategy?

Regards,

Abid

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/99857c16-1ba3-4267-94c8-7538971f366b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

2

Yes, you can set up the JDBC plugin with a 'schedule' parameter so that it
is started every minute.

See the doc at

Note, if you delete rows in DB, this is not per se detected by JDBC plugin.
There is no synchronization between DB rows and JSON docs. You can only
work with documents from the rows that your SQL statement discovers. JSON
doc deletions require some additional effort.

Jörg

On Wed, Jan 21, 2015 at 4:56 PM, Abid Hussain hussain@novacom.mygbiz.com
wrote:

Hi all,

I'm trying to figure out how to configure jdbc river in production
environment.

We want to index a DB with appr. 10% write queries and 90% read queries
and need to keep ES index in sync with DB.

So I wonder how two configure river to keep Index in near-realtime Sync
with DB.

I'm thinking about pulling the DB changes every minute. Is jdbc-river
capable of updating it's already indexed data and is this a good strategy?

Regards,

Abid

--
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/99857c16-1ba3-4267-94c8-7538971f366b%40googlegroups.com
https://groups.google.com/d/msgid/elasticsearch/99857c16-1ba3-4267-94c8-7538971f366b%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CAKdsXoGXpv-28GuNLR86YW79rC%2BUjqx%3DwYsCGEpEbn8ZOgAFsA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

3

Thanks for you quick reply.

You say There is no synchronization between DB rows and JSON docs.

So how is the index being updated? I guess that new entries are being added
to index. Does the river also recognize differences between single columns
and their corresponding field in index and merge them into index?

Regards,

Abid

Am Mittwoch, 21. Januar 2015 17:02:27 UTC+1 schrieb Jörg Prante:

Yes, you can set up the JDBC plugin with a 'schedule' parameter so that it
is started every minute.

See the doc at

GitHub - jprante/elasticsearch-jdbc: JDBC importer for Elasticsearch

Note, if you delete rows in DB, this is not per se detected by JDBC
plugin. There is no synchronization between DB rows and JSON docs. You can
only work with documents from the rows that your SQL statement discovers.
JSON doc deletions require some additional effort.

Jörg

On Wed, Jan 21, 2015 at 4:56 PM, Abid Hussain <hus...@novacom.mygbiz.com
<javascript:>> wrote:

Hi all,

I'm trying to figure out how to configure jdbc river in production
environment.

We want to index a DB with appr. 10% write queries and 90% read queries
and need to keep ES index in sync with DB.

So I wonder how two configure river to keep Index in near-realtime Sync
with DB.

I'm thinking about pulling the DB changes every minute. Is jdbc-river
capable of updating it's already indexed data and is this a good strategy?

Regards,

Abid

--
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 elasticsearc...@googlegroups.com <javascript:>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/99857c16-1ba3-4267-94c8-7538971f366b%40googlegroups.com
https://groups.google.com/d/msgid/elasticsearch/99857c16-1ba3-4267-94c8-7538971f366b%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/227dada3-0ea0-442a-80c5-e8a59b0462fa%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

4

All you can do is setting up an SQL command, the result rows are fetched,
and converted to JSON docs, which are indexed.

Jörg

On Thu, Jan 22, 2015 at 6:44 PM, Abid Hussain hussain@novacom.mygbiz.com
wrote:

Thanks for you quick reply.

You say There is no synchronization between DB rows and JSON docs.

So how is the index being updated? I guess that new entries are being
added to index. Does the river also recognize differences between single
columns and their corresponding field in index and merge them into index?

Regards,

Abid

Am Mittwoch, 21. Januar 2015 17:02:27 UTC+1 schrieb Jörg Prante:

Yes, you can set up the JDBC plugin with a 'schedule' parameter so that
it is started every minute.

See the doc at

GitHub - jprante/elasticsearch-jdbc: JDBC importer for Elasticsearch

Note, if you delete rows in DB, this is not per se detected by JDBC
plugin. There is no synchronization between DB rows and JSON docs. You can
only work with documents from the rows that your SQL statement discovers.
JSON doc deletions require some additional effort.

Jörg

On Wed, Jan 21, 2015 at 4:56 PM, Abid Hussain hus...@novacom.mygbiz.com
wrote:

Hi all,

I'm trying to figure out how to configure jdbc river in production
environment.

We want to index a DB with appr. 10% write queries and 90% read queries
and need to keep ES index in sync with DB.

So I wonder how two configure river to keep Index in near-realtime Sync
with DB.

I'm thinking about pulling the DB changes every minute. Is jdbc-river
capable of updating it's already indexed data and is this a good strategy?

Regards,

Abid

--
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 elasticsearc...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/
msgid/elasticsearch/99857c16-1ba3-4267-94c8-7538971f366b%
40googlegroups.com
https://groups.google.com/d/msgid/elasticsearch/99857c16-1ba3-4267-94c8-7538971f366b%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

--
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/227dada3-0ea0-442a-80c5-e8a59b0462fa%40googlegroups.com
https://groups.google.com/d/msgid/elasticsearch/227dada3-0ea0-442a-80c5-e8a59b0462fa%40googlegroups.com?utm_medium=email&utm_source=footer
.

For more options, visit https://groups.google.com/d/optout.

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CAKdsXoFWp2RXAzM3HGCt88wGAhFfEKnogv%3D8oFjr3YNMCKWLww%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

5

Is there also a way to update ES through a commit event from the SQL database?
Something like an Active MQ trigger.