Company•June 29, 2015
New in Cassandra 3.0: Materialized Views
CREATE TABLE scores
(
user TEXT,
game TEXT,
year INT,
month INT,
day INT,
score INT,
PRIMARY KEY (user, game, year, month, day)
)
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)
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)
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)
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
SELECT user, score FROM dailyhigh WHERE game = 'Coup' AND year = 2015 AND month = 06 AND day = 01 LIMIT 1
user | score
-----------+-------
iamaleksey | 2500
SELECT user, score FROM alltimehigh WHERE game = 'Coup'
user | score
-----------+-------
pcmanus | 4000
iamaleksey | 2500
yukim | 2250
jmckenzie | 2000
pcmanus | 2000
jbellis | 1750
tjake | 1000
tjake | 500
DELETE FROM scores WHERE user = 'tjake'
SELECT user, score FROM alltimehigh WHERE game = 'Coup'
user | score
-----------+-------
pcmanus | 4000
iamaleksey | 2500
yukim | 2250
jmckenzie | 2000
pcmanus | 2000
jbellis | 1750