February 16, 2017 | Cassandra
One of the default Cassandra strategies to deal with more sophisticated queries is to create CQL tables that contain the data in a structure that matches the query itself (denormalization). Cassandra 3.0 introduces a new CQL feature, Materialized Views which captures this concept as a first-class construct.
We decided to take a closer look.
Materialized Views are essentially standard CQL tables that are maintained automatically by the Cassandra server – as opposed to needing to manually write to many denormalized tables containing the same data, like in previous releases of Cassandra. At glance, this looks like a great feature: automating a process that was previously done by hand, and the server taking the responsibility for maintaining the various data structures.
For example, let’s suppose that we want to capture payment transaction information for a set of users. You can have the following structure as your base table which you would write the transactions to:
CREATE TABLE cc_transactions (
userid text,
year int,
month int,
day int,
id int,
amount int,
card text,
status text,
PRIMARY KEY ((userid, year), month, day, id)
);
This table can be used to record transactions of users for each year, and is suitable for querying the transaction log of each of our users.
Let’s suppose there is a requirement for an administrative function allowing to see all the transactions for a given day.
CQL has been extended by the CREATE MATERIALIZED VIEW command, which can be used in the following manner:
CREATE MATERIALIZED VIEW transactions_by_day AS
SELECT year, month, day, userid, id, amount, card, status
FROM mvdemo.cc_transactions
WHERE userid IS NOT NULL AND year IS NOT NULL AND month IS NOT NULL AND day IS NOT NULL AND id IS NOT NULL AND card IS NOT NULL
PRIMARY KEY ((year, month, day), userid, id);
Let’s insert some data:
insert into cc_transactions (userid, year, month, day, id, card, amount, status) values ('John', 2017, 2, 6, 1, '1111-1111-1111-1111', -10, 'COMPLETED');
insert into cc_transactions (userid, year, month, day, id, card, amount, status) values ('John', 2017, 2, 6, 2, '1111-1111-1111-1111', 20, 'PENDING');
insert into cc_transactions (userid, year, month, day, id, card, amount, status) values ('Bob', 2017, 2, 6, 3, '2222-2222-2222-2222', -17, 'COMPLETED');
insert into cc_transactions (userid, year, month, day, id, card, amount, status) values ('Bob', 2017, 2, 7, 4, '2222-2222-2222-2222', -32, 'COMPLETED');
As you would expect, you can then execute the following queries:
select * from cc_transactions where userid = 'John' and year = 2017;
userid | year | month | day | id | amount | card | status
--------+------+-------+-----+----+--------+---------------------+-----------
John | 2017 | 2 | 6 | 1 | -10 | 1111-1111-1111-1111 | COMPLETED
John | 2017 | 2 | 6 | 2 | 20 | 1111-1111-1111-1111 | PENDING
And:
select * from transactions_by_day where year = 2017 and month = 2 and day = 6;
year | month | day | userid | id | amount | card | status
------+-------+-----+--------+----+--------+---------------------+-----------
2017 | 2 | 6 | Bob | 3 | -17 | 2222-2222-2222-2222 | COMPLETED
2017 | 2 | 6 | John | 1 | -10 | 1111-1111-1111-1111 | COMPLETED
2017 | 2 | 6 | John | 2 | 20 | 1111-1111-1111-1111 | PENDING
The Materialized View is not a fundamentally special construct. Behind the scene, Cassandra will create “standard” table, and any mutation / access will go through the usual write and read paths.
If we look into the data directory for this keyspace, we should expect to find two separate subdirectories, containing SSTables for the base table and the Materialized View:
$ ls -la
total 16
drwxrwxr-x 4 davibo davibo 4096 Feb 9 10:32 .
drwxrwxr-x 10 davibo davibo 4096 Feb 8 12:11 ..
drwxrwxr-x 4 davibo davibo 4096 Feb 9 10:34 cc_transactions-14b32420eeb311e6b4a3754b64ff1113
drwxrwxr-x 3 davibo davibo 4096 Feb 9 10:34 transactions_by_day-1f36a390eeb311e6b4a3754b64ff1113
Let’s investigate the declaration of the Materialized View in a bit more detail:
CREATE MATERIALIZED VIEW transactions_by_day AS
SELECT year, month, day, userid, id, amount, card, status
FROM cc_transactions
WHERE userid IS NOT NULL AND year IS NOT NULL AND month IS NOT NULL AND day IS NOT NULL AND id IS NOT NULL AND card IS NOT NULL
PRIMARY KEY ((year, month, day), userid, id);
Note the PRIMARY KEY clause at the end of this statement. This is much what you would expect from Cassandra data modeling: defining the partition key and clustering columns for the Materialized View’s backing table. As such it should always be chosen carefully and the usual best practices apply to it:
Also note the NOT NULL restrictions on all the columns declared as primary key. This is to ensure that no records in the Materialized View can exist with an incomplete primary key. This is currently a strict requirement when creating Materialized Views and trying to omit these checks will result in an error: Primary key column 'year' is required to be filtered by 'IS NOT NULL'
In the current versions of Cassandra there are a number of limitations on the definition of Materialized Views.
Any materialized view must map one CQL row from the base table to precisely one other row in the materialized view. This in practice means that all columns of the original primary key (partition key and clustering columns) must be represented in the materialized view, however they can appear in any order, and can define different partitioning compared to the base table.
Accustomed to relational database systems, this may feel like an odd restriction. It actually makes sense if you consider how Cassandra manages the data in the Materialized View. Since the View is nothing more under the hood than another Cassandra table, and is being updated via the usual mechanisms, when the base table is updated; an appropriate mutation is automatically generated and applied to the View.
In case a single CQL row in the Materialized View would be a result of potentially collapsing multiple base table rows, Cassandra would have no way of tracking the changes from all these base rows and appropriately represent them in the Materialized View (this is especially problematic on deletions of base rows).
As a result you are not allowed to define a Materialized View like this:
CREATE MATERIALIZED VIEW transactions_by_card AS
SELECT userid, card, year, month, day, id, amount, status
FROM cc_transactions
WHERE year IS NOT NULL AND id IS NOT NULL AND card IS NOT NULL
PRIMARY KEY ((card, year), id);
This attempt will result in the following error: Cannot create Materialized View transactions_by_card without primary key columns from base cc_transactions (day,month,userid)
This may be somewhat surprising – the ID column is a unique transaction identifier after all. However this is additional knowledge that is due to the semantics of the data model, and Cassandra has no way of understanding (or verifying and enforcing) that it is actually true or not. As a developer you have additional knowledge of the data being manipulated than what is possible to declare in the CQL models.
As established already, the full base primary key must be part of the primary key of the Materialized View. It is possible to add another column from the original base table that was not part of the original primary key, but this is restricted in only a single additional column.
Again, this restriction feels rather odd. In this case the explanation is much more subtle: in certain concurrent update cases when both columns of the base table are manipulated at the same time; it is technically difficult to implement a solution on Cassandra’s side that guarantees no data (or deletions) are lost and the Materialized Views are consistent with the base table.
This restriction may be lifted in later releases, once the following tickets are resolved:
https://issues.apache.org/jira/browse/CASSANDRA-9928
https://issues.apache.org/jira/browse/CASSANDRA-10226
Let’s suppose you want to create a View for “suspicious” transactions – those have too large of an amount associated with them. A possible way of implementing this is via a Materialized View with a more complex filter criteria:
CREATE MATERIALIZED VIEW suspicious_transactions AS
SELECT userid, year, month, day, id, amount, card, status
FROM cc_transactions
WHERE userid IS NOT NULL AND year IS NOT NULL AND month IS NOT NULL AND day IS NOT NULL AND id IS NOT NULL
AND amount > 1000
PRIMARY KEY ((userid, year), month, day, id);
This works on Cassandra 3.10 (the latest release at the time of writing this blog), and produces the results you would expect:
After executing:
insert into cc_transactions (userid, year, month, day, id, card, amount, status) values ('Bob', 2017, 2, 7, 5, '2222-2222-2222-2222', 1200, 'COMPLETED');
When we query:
> select * from cc_transactions where userid = 'Bob' and year = 2017;
userid | year | month | day | id | amount | card | status
--------+------+-------+-----+----+--------+---------------------+-----------
Bob | 2017 | 2 | 6 | 3 | -17 | 2222-2222-2222-2222 | COMPLETED
Bob | 2017 | 2 | 7 | 4 | -32 | 2222-2222-2222-2222 | COMPLETED
Bob | 2017 | 2 | 7 | 5 | 1200 | 2222-2222-2222-2222 | COMPLETED
> select * from suspicious_transactions where userid = 'Bob' and year = 2017;
userid | year | month | day | id | amount | card | status
--------+------+-------+-----+----+--------+---------------------+-----------
Bob | 2017 | 2 | 7 | 5 | 1200 | 2222-2222-2222-2222 | COMPLETED
However on Cassandra 3.9 we get the error: Non-primary key columns cannot be restricted in the SELECT statement used for materialized view creation (got restrictions on: amount)
Maintaining the consistency between the base table and the associated Materialized Views comes with a cost. Since a Materialized View is effectively a Cassandra table, there is the obvious cost of writing to these tables. There is more to it though. Writing to any base table that has associated Materialized Views will result in the following:
The first two steps are to ensure that a consistent state of the data is persisted across all Materialized Views – no two updates on the based table are allowed to interleave, therefore we are certain to read a consistent state of the full row and generate any Materialized View updates based on it.
Creating a batch of the mutations is for atomicity – using Cassandra’s batching capabilities ensures that if the base table mutation is successful, all the views will eventually represent the correct state. In practice this adds a significant overhead to write operations. Especially considering a read operation is executed before the write this transforms the expected characteristics quite dramatically (writes in Cassandra normally don’t require random disk I/O but in this case they will).
A tracing session with on a standard write with Consistency Level ONE would look like this:
activity | timestamp | source | source_elapsed | client
--------------------------------------------------------------------------------------------+----------------------------+-----------+----------------+-----------
Execute CQL3 query | 2017-02-09 16:55:30.467000 | 127.0.0.1 | 0 | 127.0.0.1
Parsing insert into cc_transactions (...) values (...); [Native-Transport-Requests-1] | 2017-02-09 16:55:30.467000 | 127.0.0.1 | 234 | 127.0.0.1
Preparing statement [Native-Transport-Requests-1] | 2017-02-09 16:55:30.467000 | 127.0.0.1 | 460 | 127.0.0.1
Determining replicas for mutation [Native-Transport-Requests-1] | 2017-02-09 16:55:30.468000 | 127.0.0.1 | 945 | 127.0.0.1
MUTATION message received from /127.0.0.1 [MessagingService-Incoming-/127.0.0.1] | 2017-02-09 16:55:30.468000 | 127.0.0.3 | 47 | 127.0.0.1
Appending to commitlog [MutationStage-2] | 2017-02-09 16:55:30.468000 | 127.0.0.1 | 1154 | 127.0.0.1
Adding to cc_transactions memtable [MutationStage-2] | 2017-02-09 16:55:30.468000 | 127.0.0.1 | 1319 | 127.0.0.1
Sending MUTATION message to /127.0.0.3 [MessagingService-Outgoing-/127.0.0.3-Small] | 2017-02-09 16:55:30.468000 | 127.0.0.1 | 1359 | 127.0.0.1
Sending MUTATION message to /127.0.0.2 [MessagingService-Outgoing-/127.0.0.2-Small] | 2017-02-09 16:55:30.468000 | 127.0.0.1 | 1446 | 127.0.0.1
Appending to commitlog [MutationStage-2] | 2017-02-09 16:55:30.469000 | 127.0.0.3 | 474 | 127.0.0.1
MUTATION message received from /127.0.0.1 [MessagingService-Incoming-/127.0.0.1] | 2017-02-09 16:55:30.469000 | 127.0.0.2 | 26 | 127.0.0.1
Adding to cc_transactions memtable [MutationStage-2] | 2017-02-09 16:55:30.469000 | 127.0.0.3 | 643 | 127.0.0.1
Enqueuing response to /127.0.0.1 [MutationStage-2] | 2017-02-09 16:55:30.469000 | 127.0.0.3 | 819 | 127.0.0.1
REQUEST_RESPONSE message received from /127.0.0.3 [MessagingService-Incoming-/127.0.0.1] | 2017-02-09 16:55:30.470000 | 127.0.0.1 | 27 | 127.0.0.1
Sending REQUEST_RESPONSE message to /127.0.0.1 [MessagingService-Outgoing-/127.0.0.1-Small] | 2017-02-09 16:55:30.470000 | 127.0.0.3 | 1381 | 127.0.0.1
Appending to commitlog [MutationStage-1] | 2017-02-09 16:55:30.470000 | 127.0.0.2 | 1065 | 127.0.0.1
Adding to cc_transactions memtable [MutationStage-1] | 2017-02-09 16:55:30.470000 | 127.0.0.2 | 1431 | 127.0.0.1
Enqueuing response to /127.0.0.1 [MutationStage-1] | 2017-02-09 16:55:30.470000 | 127.0.0.2 | 1723 | 127.0.0.1
Sending REQUEST_RESPONSE message to /127.0.0.1 [MessagingService-Outgoing-/127.0.0.1-Small] | 2017-02-09 16:55:30.470001 | 127.0.0.2 | 1983 | 127.0.0.1
Processing response from /127.0.0.3 [RequestResponseStage-2] | 2017-02-09 16:55:30.471000 | 127.0.0.1 | 531 | 127.0.0.1
REQUEST_RESPONSE message received from /127.0.0.2 [MessagingService-Incoming-/127.0.0.1] | 2017-02-09 16:55:30.471000 | 127.0.0.1 | 24 | 127.0.0.1
Processing response from /127.0.0.2 [RequestResponseStage-1] | 2017-02-09 16:55:30.472000 | 127.0.0.1 | 225 | 127.0.0.1
Request complete | 2017-02-09 16:55:30.468692 | 127.0.0.1 | 1692 | 127.0.0.1
Executing the same insert with one Materialized View on the table results in the following trace:
activity | timestamp | source | source_elapsed | client
--------------------------------------------------------------------------------------------+----------------------------+-----------+----------------+-----------
Execute CQL3 query | 2017-02-09 17:03:15.651000 | 127.0.0.1 | 0 | 127.0.0.1
Parsing insert into cc_transactions (...) values (...); [Native-Transport-Requests-1] | 2017-02-09 17:03:15.651000 | 127.0.0.1 | 183 | 127.0.0.1
Preparing statement [Native-Transport-Requests-1] | 2017-02-09 17:03:15.652000 | 127.0.0.1 | 416 | 127.0.0.1
Determining replicas for mutation [Native-Transport-Requests-1] | 2017-02-09 17:03:15.652000 | 127.0.0.1 | 835 | 127.0.0.1
Appending to commitlog [Native-Transport-Requests-1] | 2017-02-09 17:03:15.652000 | 127.0.0.1 | 1047 | 127.0.0.1
Creating materialized view mutations from base table replica [Native-Transport-Requests-1] | 2017-02-09 17:03:15.652000 | 127.0.0.1 | 1139 | 127.0.0.1
Executing single-partition query on cc_transactions [Native-Transport-Requests-1] | 2017-02-09 17:03:15.652000 | 127.0.0.1 | 1231 | 127.0.0.1
Acquiring sstable references [Native-Transport-Requests-1] | 2017-02-09 17:03:15.653000 | 127.0.0.1 | 1303 | 127.0.0.1
Merging memtable contents [Native-Transport-Requests-1] | 2017-02-09 17:03:15.653000 | 127.0.0.1 | 1346 | 127.0.0.1
Read 1 live and 0 tombstone cells [Native-Transport-Requests-1] | 2017-02-09 17:03:15.653000 | 127.0.0.1 | 1789 | 127.0.0.1
Determining replicas for mutation [Native-Transport-Requests-1] | 2017-02-09 17:03:15.653000 | 127.0.0.1 | 1889 | 127.0.0.1
Appending to commitlog [Native-Transport-Requests-1] | 2017-02-09 17:03:15.653000 | 127.0.0.1 | 1985 | 127.0.0.1
Adding to transactions_by_day memtable [Native-Transport-Requests-1] | 2017-02-09 17:03:15.653001 | 127.0.0.1 | 2118 | 127.0.0.1
Adding to cc_transactions memtable [Native-Transport-Requests-1] | 2017-02-09 17:03:15.653001 | 127.0.0.1 | 2270 | 127.0.0.1
Sending MUTATION message to /127.0.0.2 [MessagingService-Outgoing-/127.0.0.2-Small] | 2017-02-09 17:03:15.654000 | 127.0.0.1 | 2744 | 127.0.0.1
MUTATION message received from /127.0.0.1 [MessagingService-Incoming-/127.0.0.1] | 2017-02-09 17:03:15.654000 | 127.0.0.2 | 69 | 127.0.0.1
Sending MUTATION message to /127.0.0.3 [MessagingService-Outgoing-/127.0.0.3-Small] | 2017-02-09 17:03:15.654000 | 127.0.0.1 | 2773 | 127.0.0.1
MUTATION message received from /127.0.0.1 [MessagingService-Incoming-/127.0.0.1] | 2017-02-09 17:03:15.655000 | 127.0.0.3 | 42 | 127.0.0.1
Appending to commitlog [MutationStage-1] | 2017-02-09 17:03:15.655000 | 127.0.0.2 | 719 | 127.0.0.1
Creating materialized view mutations from base table replica [MutationStage-1] | 2017-02-09 17:03:15.655000 | 127.0.0.2 | 952 | 127.0.0.1
Appending to commitlog [MutationStage-3] | 2017-02-09 17:03:15.656000 | 127.0.0.3 | 873 | 127.0.0.1
Executing single-partition query on cc_transactions [MutationStage-1] | 2017-02-09 17:03:15.656000 | 127.0.0.2 | 1125 | 127.0.0.1
Creating materialized view mutations from base table replica [MutationStage-3] | 2017-02-09 17:03:15.656000 | 127.0.0.3 | 1168 | 127.0.0.1
Acquiring sstable references [MutationStage-1] | 2017-02-09 17:03:15.656000 | 127.0.0.2 | 1327 | 127.0.0.1
Executing single-partition query on cc_transactions [MutationStage-3] | 2017-02-09 17:03:15.656000 | 127.0.0.3 | 1364 | 127.0.0.1
Merging memtable contents [MutationStage-1] | 2017-02-09 17:03:15.656000 | 127.0.0.2 | 1565 | 127.0.0.1
Acquiring sstable references [MutationStage-3] | 2017-02-09 17:03:15.656000 | 127.0.0.3 | 1491 | 127.0.0.1
Merging memtable contents [MutationStage-3] | 2017-02-09 17:03:15.657000 | 127.0.0.3 | 1625 | 127.0.0.1
Read 1 live and 0 tombstone cells [MutationStage-1] | 2017-02-09 17:03:15.657000 | 127.0.0.2 | 2194 | 127.0.0.1
Read 1 live and 0 tombstone cells [MutationStage-3] | 2017-02-09 17:03:15.657000 | 127.0.0.3 | 2274 | 127.0.0.1
Determining replicas for mutation [MutationStage-1] | 2017-02-09 17:03:15.657000 | 127.0.0.2 | 2403 | 127.0.0.1
Determining replicas for mutation [MutationStage-3] | 2017-02-09 17:03:15.657000 | 127.0.0.3 | 2454 | 127.0.0.1
Appending to commitlog [MutationStage-1] | 2017-02-09 17:03:15.657000 | 127.0.0.2 | 2523 | 127.0.0.1
Adding to transactions_by_day memtable [MutationStage-1] | 2017-02-09 17:03:15.657000 | 127.0.0.2 | 2675 | 127.0.0.1
Adding to cc_transactions memtable [MutationStage-1] | 2017-02-09 17:03:15.657000 | 127.0.0.2 | 2866 | 127.0.0.1
Enqueuing response to /127.0.0.1 [MutationStage-1] | 2017-02-09 17:03:15.657001 | 127.0.0.2 | 3054 | 127.0.0.1
REQUEST_RESPONSE message received from /127.0.0.2 [MessagingService-Incoming-/127.0.0.1] | 2017-02-09 17:03:15.658000 | 127.0.0.1 | 73 | 127.0.0.1
Sending REQUEST_RESPONSE message to /127.0.0.1 [MessagingService-Outgoing-/127.0.0.1-Small] | 2017-02-09 17:03:15.658000 | 127.0.0.2 | 3318 | 127.0.0.1
Processing response from /127.0.0.2 [RequestResponseStage-5] | 2017-02-09 17:03:15.658000 | 127.0.0.1 | 265 | 127.0.0.1
Appending to commitlog [MutationStage-3] | 2017-02-09 17:03:15.658000 | 127.0.0.3 | 2610 | 127.0.0.1
Adding to transactions_by_day memtable [MutationStage-3] | 2017-02-09 17:03:15.658000 | 127.0.0.3 | 2884 | 127.0.0.1
Adding to cc_transactions memtable [MutationStage-3] | 2017-02-09 17:03:15.658000 | 127.0.0.3 | 3116 | 127.0.0.1
Enqueuing response to /127.0.0.1 [MutationStage-3] | 2017-02-09 17:03:15.658000 | 127.0.0.3 | 3339 | 127.0.0.1
REQUEST_RESPONSE message received from /127.0.0.3 [MessagingService-Incoming-/127.0.0.1] | 2017-02-09 17:03:15.661000 | 127.0.0.1 | 44 | 127.0.0.1
Sending REQUEST_RESPONSE message to /127.0.0.1 [MessagingService-Outgoing-/127.0.0.1-Small] | 2017-02-09 17:03:15.661000 | 127.0.0.3 | 5864 | 127.0.0.1
Processing response from /127.0.0.3 [RequestResponseStage-4] | 2017-02-09 17:03:15.662000 | 127.0.0.1 | 302 | 127.0.0.1
Request complete | 2017-02-09 17:03:15.653748 | 127.0.0.1 | 2748 | 127.0.0.1
As you can see from the traces, the additional cost on the writes is significant.
Bear in mind that this is not a fair comparison – we are comparing a single-table write with another one that is effectively writing to two tables. The reason for including is to demonstrate the the difference in executing the same CQL write with or without a Materialized View.
In a realistic situation you would execute two writes on the client side, one to the base table and another to the Materialized View, or more likely a batch of two writes to ensure atomicity. According to DataStax performance tests, in such cases the built-in Materialized Views perform better than the manual denormalization (with batching), especially for single-row partitions.
Deletes and updates generally work the way you would expect. Given the following state:
> select * from cc_transactions where userid = 'Bob' and year = 2017;
userid | year | month | day | id | amount | card | status
--------+------+-------+-----+----+--------+---------------------+-----------
Bob | 2017 | 2 | 6 | 3 | -17 | 2222-2222-2222-2222 | COMPLETED
Bob | 2017 | 2 | 7 | 4 | -32 | 2222-2222-2222-2222 | COMPLETED
Bob | 2017 | 2 | 7 | 5 | 1200 | 2222-2222-2222-2222 | COMPLETED
> select * from transactions_by_day where year = 2017 and month = 2 and day = 7;
year | month | day | userid | id | amount | card | status
------+-------+-----+--------+----+--------+---------------------+-----------
2017 | 2 | 7 | Bob | 4 | -32 | 2222-2222-2222-2222 | COMPLETED
2017 | 2 | 7 | Bob | 5 | 1200 | 2222-2222-2222-2222 | COMPLETED
If we execute
update cc_transactions set status = 'PENDING' where userid = 'Bob' and year = 2017 and month = 2 and day = 7 and id = 5;
delete from cc_transactions where userid = 'Bob' and year = 2017 and month = 2 and day = 7 and id = 4;
Then
> select * from cc_transactions where userid = 'Bob' and year = 2017;
userid | year | month | day | id | amount | card | status
--------+------+-------+-----+----+--------+---------------------+-----------
Bob | 2017 | 2 | 6 | 3 | -17 | 2222-2222-2222-2222 | COMPLETED
Bob | 2017 | 2 | 7 | 5 | 1200 | 2222-2222-2222-2222 | PENDING
> select * from transactions_by_day where year = 2017 and month = 2 and day = 7;
year | month | day | userid | id | amount | card | status
------+-------+-----+--------+----+--------+---------------------+---------
2017 | 2 | 7 | Bob | 5 | 1200 | 2222-2222-2222-2222 | PENDING
There are some unexpected cases worth keeping in mind. When updating a column that is made part of a Materialized View’s primary key, Cassandra will execute a DELETE and an INSERT statement to get the View into the correct state – thus resulting in a tombstone.
To demonstrate this, let’s suppose we want to be able to query transactions for a user by status:
CREATE MATERIALIZED VIEW transactions_by_status AS
SELECT year, month, day, userid, id, amount, card, status
FROM cc_transactions
WHERE userid IS NOT NULL AND year IS NOT NULL AND month IS NOT NULL AND day IS NOT NULL AND id IS NOT NULL AND status IS NOT NULL
PRIMARY KEY ((userid, year, status), month, day, id);
Truncating the base table and executing:
insert into cc_transactions (userid, year, month, day, id, card, amount, status) values ('Bob', 2017, 2, 6, 3, '2222-2222-2222-2222', -17, 'PENDING');
update cc_transactions set status = 'COMPLETED' where userid = 'Bob' and year = 2017 and month = 2 and day = 6 and id = 3;
After nodetool flush and taking a look at the SSTable of transactions_by_status:
[
{
"partition" : {
"key" : [ "Bob", "2017", "COMPLETED" ],
"position" : 0
},
"rows" : [
{
"type" : "row",
"position" : 39,
"clustering" : [ "2", "6", "3" ],
"liveness_info" : { "tstamp" : "2017-02-10T10:04:33.387990Z" },
"cells" : [
{ "name" : "amount", "value" : "-17", "tstamp" : "2017-02-10T10:04:06.195953Z" },
{ "name" : "card", "value" : "2222-2222-2222-2222", "tstamp" : "2017-02-10T10:04:06.195953Z" }
]
}
]
},
{
"partition" : {
"key" : [ "Bob", "2017", "PENDING" ],
"position" : 88
},
"rows" : [
{
"type" : "row",
"position" : 125,
"clustering" : [ "2", "6", "3" ],
"deletion_info" : { "marked_deleted" : "2017-02-10T10:04:06.195953Z", "local_delete_time" : "2017-02-10T10:04:33Z" },
"cells" : [ ]
}
]
}
]
Notice the tombstoned row for partition (“Bob”, “2017”, “PENDING”) – this is a result of the initial insert and subsequent update. This is because by updating status in the base table, we have effectively created a new row in the Materialized View, deleting the old one.
This particular data structure is strongly discouraged: it will result in having a lot of tombstones in the (“Bob”, “2017”, “PENDING”) partition and is prone to hitting the tombstone warning and failure thresholds. Even worse – it is not immediately obvious that you are generating tombstones.
Instead of using a Materialized View, a SASI index is a much better choice for this particular case.
It is also possible to create a Materialized View over a table that already has data. In such cases Cassandra will create a View that has all the necessary data. As this might take a significant amount of time depending on the amount of data held in the base table, it is possible to track status via the system.built_views metadata table.
Materialized Views sounds like a great feature. Pushing the responsibility to maintain denormalizations for queries to the database is highly desirable and reduces the complexity of applications using Cassandra.
However the current implementation has many shortcomings that make it difficult to use in most cases. Most importantly the serious restrictions on the possible primary keys of the Materialized Views limit their usefulness a great deal. In addition any Views will have to have a well-chosen partition key and extra consideration needs to be given to unexpected tombstone generation in the Materialized Views.
And, there is a definite performance hit compared to simple writes. If an application is sensitive to write latency and throughput, consider the options carefully (Materialized Views, manual denormalisation) and do a proper performance testing exercise before making a choice.
To summarise – Materialized Views is an addition to CQL that is, in its current form suitable in a few use-cases: when write throughput is not a concern and the data model can be created within the functional limitations.
This blog is written exclusively by the OpenCredo team. We do not accept external contributions.
SHARE
