Materialized Views
Materialized views handle automated server-side denormalization, removing the need for client side handling of denormalization and ensuring eventual consistency between the base and view data. This denormalization allows for very fast lookups of data in each view using the normal Cassandra read path.
Example
Queries
- Given a game, who has the highest score, and what is it?
- Given a game and a day, who had the highest score, and what was it?
- Given a game and a month, who had the highest score, and what was it?
CREATE KEYSPACE IF NOT EXISTS cycling WITH replication = {'class':'SimpleStrategy', 'replication_factor' : 1};
USE cycling;
CREATE TABLE scores
(
user TEXT,
game TEXT,
year INT,
month INT,
day INT,
score INT,
PRIMARY KEY (user, game, year, month, day)
);
To create the materialized view, we provide a simple select statement and the primary key to use for this view. Specifying the CLUSTERING ORDER BY allows us to reverse sort the high score so we can get the highest score by simply selecting the first item in the partition.
CREATE MATERIALIZED VIEW alltimehigh AS
SELECT user FROM scores
WHERE game IS NOT NULL AND score IS NOT NULL AND user IS NOT NULL AND year IS NOT NULL AND month IS NOT NULL AND day IS NOT NULL
PRIMARY KEY (game, score, user, year, month, day)
WITH CLUSTERING ORDER BY (score desc);
Warnings :
Materialized views are experimental and are not recommended for production use.
To query the daily high scores, we create a materialized view that groups the game title and date together so a single partition contains the values for that date. We do the same for the monthly high scores.
CREATE MATERIALIZED VIEW dailyhigh AS
SELECT user FROM scores
WHERE game IS NOT NULL AND year IS NOT NULL AND month IS NOT NULL AND day IS NOT NULL AND score IS NOT NULL AND user IS NOT NULL
PRIMARY KEY ((game, year, month, day), score, user)
WITH CLUSTERING ORDER BY (score DESC);
Warnings :
Materialized views are experimental and are not recommended for production use.
CREATE MATERIALIZED VIEW monthlyhigh AS
SELECT user FROM scores
WHERE game IS NOT NULL AND year IS NOT NULL AND month IS NOT NULL AND score IS NOT NULL AND user IS NOT NULL AND day IS NOT NULL
PRIMARY KEY ((game, year, month), score, user, day)
WITH CLUSTERING ORDER BY (score DESC);
Warnings :
Materialized views are experimental and are not recommended for production use.
INSERT INTO scores (user, game, year, month, day, score) VALUES ('pcmanus', 'Coup', 2015, 05, 01, 4000);
INSERT INTO scores (user, game, year, month, day, score) VALUES ('jbellis', 'Coup', 2015, 05, 03, 1750);
INSERT INTO scores (user, game, year, month, day, score) VALUES ('yukim', 'Coup', 2015, 05, 03, 2250);
INSERT INTO scores (user, game, year, month, day, score) VALUES ('tjake', 'Coup', 2015, 05, 03, 500);
INSERT INTO scores (user, game, year, month, day, score) VALUES ('jmckenzie', 'Coup', 2015, 06, 01, 2000);
INSERT INTO scores (user, game, year, month, day, score) VALUES ('iamaleksey', 'Coup', 2015, 06, 01, 2500);
INSERT INTO scores (user, game, year, month, day, score) VALUES ('tjake', 'Coup', 2015, 06, 02, 1000);
INSERT INTO scores (user, game, year, month, day, score) VALUES ('pcmanus', 'Coup', 2015, 06, 02, 2000);
SELECT user, score FROM alltimehigh WHERE game = 'Coup' LIMIT 1;
user | score
---------+-------
pcmanus | 4000
(1 rows)
SELECT user, score FROM dailyhigh WHERE game = 'Coup' AND year = 2015 AND month = 06 AND day = 01 LIMIT 1;
user | score
------------+-------
iamaleksey | 2500
(1 rows)
SELECT user, score FROM dailyhigh WHERE year = 2015 AND month = 06 AND day = 01 LIMIT 1;
InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING"
SELECT * FROM alltimehigh;
game | score | user | year | month | day
------+-------+------------+------+-------+-----
Coup | 4000 | pcmanus | 2015 | 5 | 1
Coup | 2500 | iamaleksey | 2015 | 6 | 1
Coup | 2250 | yukim | 2015 | 5 | 3
Coup | 2000 | jmckenzie | 2015 | 6 | 1
Coup | 2000 | pcmanus | 2015 | 6 | 2
Coup | 1750 | jbellis | 2015 | 5 | 3
Coup | 1000 | tjake | 2015 | 6 | 2
Coup | 500 | tjake | 2015 | 5 | 3
(8 rows)
We can also delete rows from the base table and the materialized view's records will be deleted. We'll delete the tjake rows from the scores table.
DELETE FROM scores WHERE user = 'tjake';
SELECT * FROM alltimehigh;
game | score | user | year | month | day
------+-------+------------+------+-------+-----
Coup | 4000 | pcmanus | 2015 | 5 | 1
Coup | 2500 | iamaleksey | 2015 | 6 | 1
Coup | 2250 | yukim | 2015 | 5 | 3
Coup | 2000 | jmckenzie | 2015 | 6 | 1
Coup | 2000 | pcmanus | 2015 | 6 | 2
Coup | 1750 | jbellis | 2015 | 5 | 3
(6 rows)
How it works
Creation
When a materialized view is created against a table which has data already, a building process will be kicked off to populate the materialized view. As such, materialized views can be created on existing tables, but there will be a period during which queries against the materialized view may not return all results.
Altering the Base Table
When a base view is altered, the materialized view is updated as well.
When not to use Materialized Views
- Materialized views do not have the same write performance characteristics that normal table writes have
- If the rows are to be combined before placed in the view, materialized views will not work. Materialized views will create a CQL Row in the view for each CQL Row in the base
- Low cardinality data will create hotspots around the ring
- Currently, only simple SELECT statements are supported
- If there will be a large number of partition tombstones, the performance may suffer
- Materialized views are not supported through Thrift