Hi All, Thanks for the replies. These detailed explanations are extremely useful. The are much appreciated!
One other question that came to mind with this. Is there any way I can use a TTLs to keep the old account entries around for a fixed length of time rather than deleting them immediately? That way if I had an issue I could replay the process form one of these earlier points. For the regular reads of the account table I can use a limit of 1, with the reverse comparator set I should always get the newest record without hitting an older record first. I can't do the initial account insert with a TTL as I can't guarantee when a new value would come along and so replace this account record. However when I insert the new account record, instead of deleting the old one could I reinsert it with a TTL of say 1 month. How would compaction handle this. Would the original record get compacted away after 1 month + the GC Grace period or would it hang around still? Thanks, Charlie M On Thu, Jun 5, 2014 at 5:32 PM, James Campbell <ja...@breachintelligence.com > wrote: > Thanks for creating and opening the discussion on this use case. I have > been evaluating Cassandra for a very similar problem, but with the > small twist that I'd like to roll up the ledger entries into the > aggregated 'account' information on a regular basis to ensure that even > rarely-read (but often written) data will be periodically rolled up and > ready for quick reads. There is also a larger twist that for me 'account' > is a compound key and the typical read pattern will include reading from > multiple > rows within the same partition (each of which would require separate > merging from the ledger). > > > This response prompts three questions for me about that: > > > 1. Just to clarify the requirement that only one client thread does this > at a time, I assume you mean only one thread should handle any given > 'account' at a time, right? There wouldn't be problems having multiple > clients do this data maintenance on different accounts at the same time, I > hope. > > > 2. For my use case, hadoop integration seems the natural fit, because > I'd like to batch these updates to run at night during query down-time, but > the current hadoop tools don't really support reading from or writing to > multiple tables, as is required for this implementation. I've seen a few > posts from people who have written homegrown input/output formats for > hadoop, but I haven't tried to use them or evaluate their stability. Is > there another mechanism I should be thinking about for that sort of batch > updating? > > > James Campbell > ------------------------------ > *From:* Aaron Morton <aa...@thelastpickle.com> > *Sent:* Thursday, June 5, 2014 5:26 AM > *To:* Cassandra User > *Cc:* charlie....@gmail.com > *Subject:* Re: Consolidating records and TTL > > As Tyler says, with atomic batches which are enabled by default the > cluster will keep trying to replay the insert / deletes. > > Nodes check their local batch log for failed batches, ones where the > coordinator did not acknowledge it had successfully completed, every 60 > seconds. So there is a window where it’s possible for not all mutations in > the batch to be completed. This could happen when a write timeout occurs > when processing a batch of 2 rows; the request CL will not have been > achieved on one or more of the rows. The coordinator will leave it up to > the batch log to replay the request, and the client driver will (by default > config) not retry. > > You can use a model like this. > > create table ledger ( > account int, > tx_id timeuuid, > sub_total int, > primary key (account, tx_id) > ); > > create table account ( > account int, > total int, > last_tx_id timeuuid, > primary key (account) > ); > > To get the total: > > select * from account where account = X; > > Then get the ledger entries you need > > select * from ledger where account = X and tx_id > last_tx_id; > > This query will degrade when the partition size in the ledger table gets > bigger, as it will need to read the column index > (see column_index_size_in_kb in yaml). It will use that to find the first > page that contains the rows we are interested in and then read forwards to > the end of the row. It’s not the most efficient type of read but if you are > going to delete ledger entries this *should* be able to skip over the > tombstones without reading them. > > When you want to update the total in the account write to the account > table and update both the total and the last_tx_id. You can then delete > ledger entries if needed. Don’t forget to ensure that only one client > thread is doing this at a time. > > Hope that helps. > Aaron > > > ----------------- > Aaron Morton > New Zealand > @aaronmorton > > Co-Founder & Principal Consultant > Apache Cassandra Consulting > http://www.thelastpickle.com > > On 5/06/2014, at 10:37 am, Tyler Hobbs <ty...@datastax.com> wrote: > > Just use an atomic batch that holds both the insert and deletes: > http://www.datastax.com/dev/blog/atomic-batches-in-cassandra-1-2 > > > On Tue, Jun 3, 2014 at 2:13 PM, Charlie Mason <charlie....@gmail.com> > wrote: > >> Hi All. >> >> I have a system thats going to make possibly several concurrent changes >> to a running total. I know I could use a counter for this. However I have >> extra meta data I can store with the changes which would allow me to reply >> the changes. If I use a counter and it looses some writes I can't recover >> it as I will only have its current total not the extra meta data to know >> where to replay from. >> >> What I was planning to do was write each change of the value to a CQL >> table with a Time UUID as a row level primary key as well as a partition >> key. Then when I need to read the running total back I will do a query for >> all the changes and add them up to get the total. >> >> As there could be tens of thousands of these I want to have a period >> after which these are consolidated. Most won't be any where near that but a >> few will which I need to be able to support. So I was also going to have a >> consolidated total table which holds the UUID of the values consolidated up >> to. Since I can bound the query for the recent updates by the UUID I should >> be able to avoid all the tombstones. So if the read encounters any changes >> that can be consolidated it inserts a new consolidated value and deletes >> the newly consolidated changes. >> >> What I am slightly worried about is what happens if the consolidated >> value insert fails but the deletes to the change records succeed. I would >> be left with an inconsistent total indefinitely. I have come up with a >> couple of ideas: >> >> >> 1, I could make it require all nodes to acknowledge it before deleting >> the difference records. >> >> 2, May be I could have another period after its consolidated but before >> its deleted? >> >> 3, Is there anyway I could use the TTL to allow to it to be deleted >> after a period of time? Chances are another read would come in and fix the >> value. >> >> >> Anyone got any other suggestions on how I could implement this? >> >> >> Thanks, >> >> Charlie M >> > > > > -- > Tyler Hobbs > DataStax <http://datastax.com/> > > >
