I am creating an river plugin that synchronizes ES and a postgresql
database. I work with an timestamp to check if my document in the database
has changed, if it has changed it is re-indexed in ES if it has not
changed, my plugin skips the document. My timestamp is a column in my
database and it is also indexed with my document. My problem is, that I
don't know how I can synchronize documents that are deleted in the
database? So my id of my document is no longer available in my database,
but how can I tell ES that it has to delete this document, if I do no
longer have the id to delete it. Does anyone has an idea how I could solve
my problem?
I connect to my database, select all documents out of my database and
iterate through my result set. Then I search for each id in ES and check
timestamp from database with timestamp of indexed document in ES. But if my
document is no longer available in my database, I don't know how to inform
ES that a document has been deleted in my database.
I have looked at the jdbc river plugin, and there is used a version for
each move of the river and if the document is deleted in the database, the
version is lower than in ES, so all documents are deleted. But in my case I
only update documents if they have changed, so only those documents that
are updated are getting a higher version, and those documents that are not
changed in the database, have the same version as before. So if I would
also delete documents like it is done in the jdbc river plugin, I also
would delete documents that are still available in my database, but not
updated because they have not changed in the database. I don't think that
this solution is appropriate for my problem.
Does anyone knows if other river plugins are available, that could help me
with my problem? Or does anyone know a solution, to synchronize deleted
documents in ES?
Thanks for any help.
Hopefully I described my problem in a understandable way.
I am creating an river plugin that synchronizes ES and a postgresql database. I work with an timestamp to check if my document in the database has changed, if it has changed it is re-indexed in ES if it has not changed, my plugin skips the document. My timestamp is a column in my database and it is also indexed with my document. My problem is, that I don't know how I can synchronize documents that are deleted in the database? So my id of my document is no longer available in my database, but how can I tell ES that it has to delete this document, if I do no longer have the id to delete it. Does anyone has an idea how I could solve my problem?
I connect to my database, select all documents out of my database and iterate through my result set. Then I search for each id in ES and check timestamp from database with timestamp of indexed document in ES. But if my document is no longer available in my database, I don't know how to inform ES that a document has been deleted in my database.
I have looked at the jdbc river plugin, and there is used a version for each move of the river and if the document is deleted in the database, the version is lower than in ES, so all documents are deleted. But in my case I only update documents if they have changed, so only those documents that are updated are getting a higher version, and those documents that are not changed in the database, have the same version as before. So if I would also delete documents like it is done in the jdbc river plugin, I also would delete documents that are still available in my database, but not updated because they have not changed in the database. I don't think that this solution is appropriate for my problem.
Does anyone knows if other river plugins are available, that could help me with my problem? Or does anyone know a solution, to synchronize deleted documents in ES?
Thanks for any help.
Hopefully I described my problem in a understandable way.
What you describe about the JDBC river is correct. However, this will
change in one of the next versions, there will be no more
modification/deletion control by ES external versioning feature.
Instead, I will implement an index alias based time-based indexing scheme,
similar to logstash.
This generational indexing procedure works very simple. At each generation,
the whole index is built again. Not each SQL row ID or doc ID will be
deleted any more because this is too costly. Instead, every once in a
while, like log rotating, an index that is becoming too old can be dropped
automatically by the river switching the alias. The side effect is that
deletions are not visible before a certain delay, presumably hours or days.
From my experience, this is acceptable in most cases.
Another method would be to ingest doc ids of docs to be deleted in a
separate thread. Another SQL statement in the river setup could be executed
to obtain a list of IDs that must be deleted on a regular basis. This could
be run in an extra thread, with the risk of conflicts with the doc ingest
by the river. The list of IDs to be deleted must be managed by the RDBMS.
This is also a disadvantage.
What you describe about the JDBC river is correct. However, this will change in one of the next versions, there will be no more modification/deletion control by ES external versioning feature.
Instead, I will implement an index alias based time-based indexing scheme, similar to logstash.
This generational indexing procedure works very simple. At each generation, the whole index is built again. Not each SQL row ID or doc ID will be deleted any more because this is too costly. Instead, every once in a while, like log rotating, an index that is becoming too old can be dropped automatically by the river switching the alias. The side effect is that deletions are not visible before a certain delay, presumably hours or days. From my experience, this is acceptable in most cases.
Another method would be to ingest doc ids of docs to be deleted in a separate thread. Another SQL statement in the river setup could be executed to obtain a list of IDs that must be deleted on a regular basis. This could be run in an extra thread, with the risk of conflicts with the doc ingest by the river. The list of IDs to be deleted must be managed by the RDBMS. This is also a disadvantage.
The idea is a sliding window over indexes, I think this is how logstash
setup works.
New documents (and overwritten documents) can be generated at each river
cycle, they appear immediately, in the current index.
If a day is passed, a new index is created for the next day, and started to
get filled by the river. If an index is too old, it is deleted (by
adjusting the index alias).
So the current index filled by the river always grows and never shrinks.
Deletions only take place by dropping a whole index.
The idea is a sliding window over indexes, I think this is how logstash setup works.
New documents (and overwritten documents) can be generated at each river cycle, they appear immediately, in the current index.
If a day is passed, a new index is created for the next day, and started to get filled by the river. If an index is too old, it is deleted (by adjusting the index alias).
So the current index filled by the river always grows and never shrinks. Deletions only take place by dropping a whole index.
I highly recommend the found.no blog post on this subject [1]. It gives a
very good overview of your options for keeping elasticsearch in sync with a
datastore of record. Handling deletions are also addressed.
Since you're using postgres, you can also use LISTEN/NOTIFY [2]. Combined
with triggers, it gives you a very easy way to signal that your data has
changed.
On Thursday, December 5, 2013 3:22:22 AM UTC-5, Isabella wrote:
Hello everyone!
I am creating an river plugin that synchronizes ES and a postgresql
database. I work with an timestamp to check if my document in the database
has changed, if it has changed it is re-indexed in ES if it has not
changed, my plugin skips the document. My timestamp is a column in my
database and it is also indexed with my document. My problem is, that I
don't know how I can synchronize documents that are deleted in the
database? So my id of my document is no longer available in my database,
but how can I tell ES that it has to delete this document, if I do no
longer have the id to delete it. Does anyone has an idea how I could solve
my problem?
I connect to my database, select all documents out of my database and
iterate through my result set. Then I search for each id in ES and check
timestamp from database with timestamp of indexed document in ES. But if my
document is no longer available in my database, I don't know how to inform
ES that a document has been deleted in my database.
I have looked at the jdbc river plugin, and there is used a version for
each move of the river and if the document is deleted in the database, the
version is lower than in ES, so all documents are deleted. But in my case I
only update documents if they have changed, so only those documents that
are updated are getting a higher version, and those documents that are not
changed in the database, have the same version as before. So if I would
also delete documents like it is done in the jdbc river plugin, I also
would delete documents that are still available in my database, but not
updated because they have not changed in the database. I don't think that
this solution is appropriate for my problem.
Does anyone knows if other river plugins are available, that could help me
with my problem? Or does anyone know a solution, to synchronize deleted
documents in ES?
Thanks for any help.
Hopefully I described my problem in a understandable way.
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant
logo are trademarks of the
Apache Software Foundation
in the United States and/or other countries.
