[
https://issues.apache.org/jira/browse/CASSANDRA-8141?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14527150#comment-14527150
]
Willem-Paul Stuurman edited comment on CASSANDRA-8141 at 5/4/15 8:23 PM:
-------------------------------------------------------------------------
Cassandra like lots of data and therefore has all the makings to create a
system where previous versions of data are not deleted but marked with 'valid
from' and 'valid to' dates (our very own time machine).
Problem is to get all records valid at a certain date 'd':
'... WHERE validFrom < d AND validTo > d;' won't work.
We solved this in our environment by taking a field that can hold two values:
in our case a decimal field where the integer part is the 'validTo' and the
remainder is the 'validFrom'.
Example with dates only to get the idea:
CREATE TABLE users (
userId text,
validFor 'com.knollenstein.apps.cassandra.custom.KnPeriodType',
lastName text,
PRIMARY KEY (userId, validFor)
) WITH CLUSTERING ORDER BY (validFor DESC);
// Add some data
UPDATE users SET lastName='Stuurma' WHERE userId='w...@test.com' AND
validFor=20150401.20150301;
UPDATE users SET lastName='Stuurman' WHERE userId='w...@test.com' AND
validFor=99999999.20150401;
To get the latest version:
SELECT lastName FROM users WHERE userId='w...@test.com' AND validFor=99999999;
To get the version valid on 2015-03-23:
SELECT lastName FROM users WHERE userId='w...@test.com' AND validFor=20150323;
Our custom comparator does the normal decimal comparison if it is comparing two
date ranges (when data is inserted/updated). So indexing/sorting is the same as
any other decimal.
But the magic happens when a select is done: the decimal column containing a
date range is compared with a single date value from the where clause (a
decimal without a remainder). The custom comparator returns '0' (=equal) if
this single date is in range of the date range value (the decimal column value
containing two dates).
We can now select all the records valid at a certain date/time or just the
latest version.
Now, I'm sure there is a much more elegant way to solve this (without a custom
comparator), but maybe this can help to getting version functionality in
Cassandra.
was (Author: wpstuurman):
Cassandra like lots of data and therefor has all the makings to create a system
where previous versions of data are not deleted but marked with 'valid from'
and 'valid to' dates (our very own time machine).
Problem is to get all records valid at a certain date 'd':
'... WHERE validFrom < d AND validTo > d;' won't work.
We solved this in our environment by taking a field that can hold two values:
in our case a decimal field where the integer part is the 'validTo' and the
remainder is the 'validFrom'.
Example with dates only to get the idea:
CREATE TABLE users (
userId text,
validFor 'com.knollenstein.apps.cassandra.custom.KnPeriodType',
lastName text,
PRIMARY KEY (userId, validFor)
) WITH CLUSTERING ORDER BY (validFor DESC);
// Add some data
UPDATE users SET lastName='Stuurma' WHERE userId='w...@test.com' AND
validFor=20150401.20150301;
UPDATE users SET lastName='Stuurman' WHERE userId='w...@test.com' AND
validFor=99999999.20150401;
To get the latest version:
SELECT lastName FROM users WHERE userId='w...@test.com' AND validFor=99999999;
To get the version valid on 2015-03-23:
SELECT lastName FROM users WHERE userId='w...@test.com' AND validFor=20150323;
Our custom comparator does the normal decimal comparison if it is comparing two
date ranges (when data is inserted/updated). So indexing/sorting is the same as
any other decimal.
But the magic happens when a select is done: the decimal column containing a
date range is compared with a single date value from the where clause (a
decimal without a remainder). The custom comparator returns '0' (=equal) if
this single date is in range of the date range value (the decimal column value
containing two dates).
We can now select all the records valid at a certain date/time or just the
latest version.
Now, I'm sure there is a much more elegant way to solve this (without a custom
comparator), but maybe this can help to getting version functionality in
Cassandra.
> Versioned rows
> --------------
>
> Key: CASSANDRA-8141
> URL: https://issues.apache.org/jira/browse/CASSANDRA-8141
> Project: Cassandra
> Issue Type: New Feature
> Reporter: Robert Stupp
>
> People still talk about "global locks" and "distributed transactions". I
> think that introducing such things is both painful to implement and dangerous
> for a distributed application.
> But it could be manageable to introduce "versioned rows".
> By "versioned rows" I mean to issue a SELECT against data that was valid at a
> specified timestamp - something like {{SELECT ... WITH
> READTIME=1413724696473}}.
> In combination with something like {{UPDATE ... IF NOT MODIFIED SINCE
> 1413724696473}} it could be powerful. (Sure, this one could be already be
> achieved by the application today.)
> It's just an idea I'd like to discuss.
> We already have such a thing like "versioned rows" implicitly since we have
> the "old" data in the SSTables. Beside that it could be necessary to:
> * don't throw away old columns/rows for some configurable timespan
> * extend the row cache to optionally maintain "old" data
> * (surely something more)
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
