Design issues in the Cassandra caching transform

This issue is to address issues in the caching logic with respect to the new CQL3 parser. This new parser properly parses CQL3 statements and provides us with better understanding of the query. The result is a number of potential issues in the caching transform.

Definitions

For purposes of this issue the word key shall mean a fully qualified Cassandra key containing bot the Partition and Clustering segments and both being fully specified.

Overview

The caching system intercepts Cassandra query statements so that it may create a Redis cache for specified tables. The Redis cache utilizes the a Lexicographic key that is the Redis interpretation of the Cassandra key and provides the same operations that the Cassandra key does.

The cache intercepts Insert and Update statements to add values to cached results. I also intercepts Select statements to return values from the cache when appropriate.

Analysis

When looking at single query statements there are 5 CQL3 statements that we need to address: SELECT, INSERT, UPDATE, DELETE and DROP TABLE. The following analysis is with respect to a single CQL3 statement, issues with BATCHing and PREPARE/EXECUTE statements will be dealt with later.

SELECT statements

The SELECT statement has several clauses and options that are problematic for the cache these are listed below with proposed actions.

SELECT includes a function in the select_clause

An example of this statement is SELECT f(x) as foo FROM bar WHERE bax='baz'. As we don't know if f(x) is idempotent or not, as such we can not check the cache for this statement and it must be ignored and passed to the Cassandra server.

SELECT includes an indexed value in the select_clause

An example is SELECT thing[part] FROM bar WHERE bax='baz'. This statement is returning part of a structured value. As we do not know the structure of the value we should not attempt to return the sub value, This statement should be ignored and passed to the Cassandra server.

SELECT where clause includes columns not in the key.

Such columns are indicative of the use of secondary indexes or other processes that filter beyond the scope of the primary key. These should be ignored and passed to the Cassandra server.

SELECT where clause includes a function.

As with the function in the select clause, functions in the where_clause make the selection on deterministic for us. These should be ignored and passed to the Cassandra server.

SELECT where clause includes the `IN` keyword.

This indicates a set of equality comparisons that can not be performed with the standard Cassandra clustering key search. As such we can not perform these in Redis. These statements should be ignored and passed to the Cassandra server.

SELECT where clause includes the `CONTAINS` or CONTAINS KEY keyword.

Similar to the 'IN' statement this yields a query that is not easily managed by the Redis key structure and these statements should be ignored and passed to the Cassandra server.

SELECT includes the ALLOWS FILTERING key words.

These statements, by their nature do not have a fully qualified key. If we use the partial key for filtering it is probable that we will miss some data. It is best that these statements should be ignored and passed to the Cassandra server.

INSERT statements

The result of an INSERT statement is dependent upon the state of the database when the insert is executed. We must therefor prepare the cache insert but not execute it until we see that the server accepted and processed the statement without error. There are several cases where the cache insert should not be attempted regardless of the server results.

INSERT includes the JSON data format.

In these statements we do not currently parse the JSON format so we have no way of determining the values that should be placed in the cache. All JSON inserts should be ignored and simply forwarded to the Cassandra server. We will pick up the data later when a fully qualified SELECT is executed against it.

INSERT does not include a column list.

The column list is optional for the insert statement, without the column list we have to know the structure of the table. Since we do not have this data this statement should be ignored and simply forwarded to the Cassandra server. We will pick up the data later when a fully qualified SELECT is executed against it.

INSERT includes the IF NOT EXISTS clause

The result of this operation is dependent upon the state of the database. Since we do not know if the data already exists the data should not be inserted, in addition the result of the operation will be "OK" even if the insert was not performed. Since we can not tell if the data was inserted should simply forward the query to the Cassandra server.

UPDATE statements

The update statements can modify one or more keys in the table. As such in some cases we need to clear cache values for one or more keys and in other cases we need to update data in the cache itself. In all cases the query is forwarded to the Cassandra server.

UPDATE includes an operation in the assignment clause

An example of an operation in the assignment clauses is UPDATE cycling.popular_count SET popularity = popularity + 2 WHERE id = 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47; Since we may not know the value of the columns and due to the overhead involved in performing the calculations we do not know how to update the data in the cache. All matching keys should be removed from the cache.

UPDATE where_clause includes columns not in the key

This indicates a further filtering of the result potentially by secondary indexes. As such we have no visibility to what records should be updated. All matching keys should be removed from the cache.

UPDATE where clause includes a function

As with other calculated values we do not know what specific keys to update so all matching keys must be removed from the cache.

UPDATE includes an IN statement.

clear all matching keys.

UPDATE where_clause includes a CONTAINS/CONTAINS KEY statement

clear all matching keys.

UPDATE if_clause is present

In this case we don't know if the record will be updated or not. We must clear all matching keys.

DELETE

The Cassandra delete statement can set a column value to null or delete a row. In all cases the delete command is sent to the Cassandra server.

DELETE with no column list is provided

Indicates the row should be deleted so the key should be removed from Redis.

DELETE with column list is provided

Indicates the column(s) should be set to null. Cache entry should be updated. If not feasible then the key should be removed from the cache.

DELETE where_clause includes an IN, CONTAINS, or CONTAINS KEY statement

The number of keys impacted are difficult to determine. The partition key for the table should be deleted.

DELETE if_clause is present

Item should be removed from the cache as though the if_clause were not present that the delete worked.

DROP TABLE

Drop table should always result in the table being purged from the cache.

Analysis of complex statements.

CQL3 supports 2 complex statements: BATCH statements and PREPARE/EXECUTE statements.

BATCH

Batch statements queue multiple statements to be executed at one time. The Datastax documentations states

Batch operations for both single partition and multiple partitions ensure atomicity.

My reading of the protocol indicates that the BATCH statement is send as one command with multiple statements in it. The server processes each statement and provides the atomicity guarantee. It then sends a response message for each statement within the batch. The the BATCH command is a one request to many results operation. There does not appear to be a way to tie each response back to the query statement within the batch that generated it. If cache were able to insert itself into the BATCH command it would have to ensure the atomicity and would need to be able to intercept the BATCH, break it into separate query statements, send unhandled ones to the Cassandra server and then be able to inject the results in the proper sequence in the resulting message stream.

This seems like an approach that is fraught with potential pitfalls. Instead, Batch should be processed to determine what tables/columns are being updated and those cache entries removed as noted in the analysis above. However none of the Batch results should be processed.

PREPARE/EXECUTE

The PREPARE statement takes a CQL3 query, parses it and prepares it for execution. An index is created and returned to the client. When the client want to execute the statement an EXECUTE command is send with the ID from the prepared statement and any parameters. The prepared ID can also be used in the BATCH command in place of query string.

Processing the PREPARE statement is not an issue, however preserving the PREPARE ID between calls will require a session object for each client. The simplest solution here is that the key(s) for the prepared ID will be stored and when the ID is executed the keys will be removed from the cache.

shotover / shotover-proxy