Hi,
I am trying to operationalize our MonetDB usage and need some advice. What is the best way to do upserts in MonetDB? (https://secure.wikimedia.org/wikipedia/en/wiki/Upsert)
Our application stores its data in a reference database (not MonetDB) and need to export some of its (derived) data in MonetDB for our real-time analytics needs. This "population" of MonetDB can happen in a couple of ways:
A) In "real-time", on a "one row at a time" basis, when the application detects that it would be valuable to update the analytics information.
B) Nightly in a batch oriented basis, to make sure that no information was lost due to a glitch/downtime during the day.
C) We also periodically "refresh" all historical data in a batch oriented mode when we find bugs in the way we are deriving the data or what to derive new analytics from the same reference data.
And whatever triggered the update, the rule is that the last update "wins" (and contains the full data).
I am explaining this to illustrate why upserts would be very convenient. They would allow me to use the same code in all situations (A, B, C) and in "row" mode as well as in "batch" mode.
If, as I suspect there is no out-of-the-box support for upserts, what is the best way to achieve them efficiently in batch mode.
I am open to any (performant) solution, it does not have to be SQL based.
Thanks in advance, - Philippe
PS: Thanks a lot for MonetDB, I am completely amazed by its performance for our analytics queries.