Batch statement ordering

My use-case is similar to the example (delete and insert/update statements together in the same batch), though perhaps a bit more nuanced.

Background/Context
I have a legacy/pre-existing Java application that was implemented around a traditional RDBMS (and JPA/Hibernate). Some parts of it would be more appropriately implemented using a NoSQL store, and are bogging down the RDBMS for various reasons. So I'm migrating those parts over to use Cassandra.

Overview
At a high level, I have an 'Item', and each Item is associated with a 'Type', which is basically a tree (i.e. root node is a 'Type' instance, and each subnode is also a 'Type' instance, and so on). Many Item's may share a single Type, and the user is able to annotate the nodes in an Item's Type hierarchy with arbitrary key:value pairs of their choosing. It's the annotations which are being moved to Cassandra.

Schema
I've set up two tables/column-families, one which keeps all the key:value pairs for an Item in a map<text, text> (used in places where I need to fetch the entire set of annotations, for instance for display purposes), and a 'flat' version that keeps one key:value pair per row (used in more of a 'search' kind of context, where I want to see if a particular item possesses a particular annotation without necessarily looking at the entire set).

So essentially like:

CREATE TABLE itemProperties (
itemId bigint PRIMARY KEY,
properties map<text, text>
);
CREATE TABLE itemProperties_flat (
itemId bigint,
typeId bigint,
propertyKey text,
propertyValue text,
PRIMARY KEY (itemId, typeId, propertyKey)
) WITH COMPACT STORAGE;

Generally an Item will have on the order of a hundred or so properties against it.

The Issue
Previously each 'ItemProperty' was an entity in the ORM layer, and the 'Item' entity exposed a List of them to the rest of the world, and that how these things were managed. There's enough code that relies upon that presentation that I don't want to significantly alter it.

So what I've done is just removed 'ItemProperty's entity status, and have continued using it as a POJO to ferry data to/from Cassandra. Basically what happens is that the 'Item' entity will delegate to Cassandra 1) whenever a new List of 'ItemProperty's is set against it, and 2) whenever someone asks for the list of 'ItemProperty's.

A consequence of that is that the code which is dealing with Cassandra doesn't know exactly what changes have occurred. As in, it receives the new list that should be saved, but cannot easily tell what things (if any) have been added/removed/changed (unless it does an exhaustive comparison between the new list and the old). That means that the simplest way to ensure that Cassandra stores the correct information is to do something like:

1. "DELETE FROM itemProperties WHERE itemId = :itemId"
2. "DELETE FROM itemProperties_flat WHERE itemId = :itemId"
3. (run a bunch of insert statements to put the data back in)

And I was using a BatchStatement to do that, because that's the recommended approach in the reference docs. And it seems appropriate to use a batch there as the operation should be atomic (don't want the deletes to succeed and then the inserts to fail, for instance).

Obviously I was quite surprised when that approach resulted in all of the properties being deleted whenever any change was made (however slight). From what I can gather, I'm not the first person to be caught off-guard there.

Anyways, I hope that sheds some light on what I'm trying to do, and why. I do think there are valid use-cases for mixing delete and insert statements in a single batch (and that at least some of those cases require batched statements to be applied in-order rather than merged...or at least in-order according to statement-type; so applying all the deletes, then all the inserts, then all the updates might work). Certainly there are other approaches that I might have tried, but in terms of dealing with a preexisting codebase, the "delete everything and then re-insert" option appears to be the minimally disruptive solution.

For what it's worth, I find the batch works fine usably for my purposes if I separate the delete statements from the insert/update statements (i.e. if I session.execute() the deletes, and then batch the inserts). Though of course that misses out on the most important part of the atomicity, and could potentially lead to a situation where the deletes succeed and the inserts fail.

And attempting to parameterize the timestamp value (by adding "USING TIMESTAMP :timestamp" to my PreparedStatement's) failed miserably with a "com.datastax.driver.core.exceptions.SyntaxError: line 1:64 missing EOF at 'USING' (... WHERE itemId = :itemId [USING] TIMESTAMP...)" message. Is it not possible to parameterize the timestamps?

, can you provide some more information about your use case? In the jira description the user describes a use case explaining the following operations in the same batch:

This use case seems reasonable if your aim is to 'delete all data in the existing row' and then 'update some of the columns' (in this case all columns are updated, so the delete isn't needed). I can see this being counterintuitive to the user as the row ends up being deleted. In cassandra the 2 operations will share the same timestamp, and the row partition delete will win since nulls/tombstones take highest precedence, and then the lexicographically highest value as described in CASSANDRA-6426. However, this is not a really good way to go about things even if it did work since if you do this on every change you are going to create a lot of tombstones which could lead to TombstoneOverwhelmingExceptions.

The other use case I can think of is (which you may be describing) is where you are making multiple changes to a cell (updating the value and then updating the value again in the same batch). I'm not sure why you would want to change the same cell (column) in a batch when you could apply only what you think is the proper update (that is what C* does, using the precedence rules described in the referenced CASSANDRA-6426 ticket).

I think we could debate that when the user is using client timestamps using withTimestampGenerator that timestamps are set at the level of the statements in the BatchStatement, not on the batch itself. Using the default timestamp generator this would set up your Batch in such a way that each individual statement would have it's own timestamp in increasing order. This option was not previously available when this ticket was created, so it may be worth revisiting now.

This is beyond silly. Basically what you're saying is that if I want to have my statements executed in the order provided, I have to tack an extra 'timestamp' parameter onto every PreparedStatement that I use, and do something like:

long refTime = System.currentTimeMillis();
//[do stuff like set up a BoundStatement to add to the batch...]
statement.setLong("timestamp", refTime); //first statement
//...
statement.setLong("timestamp", refTime + 1); //second statement
//...
statement.setLong("timestamp", refTime + 2); //third statement
//...
statememt.setLong("timestamp", refTime + n); //nth statement

There should at least be the option of having the BatchStatement do this bookkeeping automatically, or a built-in 'OrderedBatchStatement' subclass, or something. The intuitive semantics are that batched operations are executed sequentially. If BatchStatement isn't going to follow the intuitive semantics then at the very least it should be renamed it to something more accurately descriptive, like 'ConcurrentFreeForAllStatement' or 'LossyDataMergingStatement'.

I've answered this in and it has indeed been discussed in CASSANDRA-6426 too. If you want to order the statements within a batch, you should provide individual timestamps for the statements.

See this : https://issues.apache.org/jira/browse/CASSANDRA-6426