16/07 - A look at the CQL changes in 3.x by Summit16

1 .Benjamin Lerer A look at the CQL changes in 3.x 

2 . • Updates and Deletions • Filtering • Grouping © DataStax, All Rights Reserved. 2 

3 . Updates and Deletions (3.0) © DataStax, All Rights Reserved. 3 

4 .Updates and Deletions CREATE TABLE toys ( brand text category text, id int, name text, price decimal, PRIMARY KEY (brand, category, id) Clustering columns © DataStax, All Rights Reserved. 4 

5 .Simple updates INSERT INTO toys (brand, category, id, name, price) VALUES (‘Lego’, ‘Star Wars’, 75060, ‘Slave I’, 219.99) UPDATE toys SET name = ‘Tie Fighter’, price = 219.99 WHERE brand = ‘Lego’ AND category = ‘Star Wars’ AND id = 75095 Memtable ‘Star Wars’-75060 ts: t1 name: ‘Slave I’ ts: t1 price: 219.99 ts: t1 © DataStax, All Rights Reserved. 5 ‘Star Wars’-75095 ts: Long.MIN name: ‘Tie Fighter’ ts: t2 price: 219.99 ts: t2 

6 .Multi-updates UPDATE toys SET price = 229.99 WHERE brand = ‘Lego’ AND category = ‘Star Wars’ AND id IN (75059, 75060, 75095) Memtable ‘Star Wars’-75059 ts: Long.MIN price: 229.99 ts: t3 ‘Star Wars’-75060 ts: t1 name: ‘Slave I’ ts: t1 price: 229.99 ts: t3 ‘Star Wars’-75095 ts: Long.MIN name: ‘Tie Fighter’ ts: t2 price: 229.99 ts: t3 © DataStax, All Rights Reserved. 6 

7 .Column deletion DELETE name FROM toys WHERE brand = ‘Lego’ AND category = ‘Star Wars’ AND id IN (75059, 75060) Memtable ‘Star Wars’-75059 ts: Long.MIN name: <tombstone> ts: t4 price: 229.99 ts: t3 ‘Star Wars’-75060 ts: t1 name: <tombstone> ts: t4 price: 229.99 ts: t3 ‘Star Wars’-75095 ts: Long.MIN name: ‘Tie Fighter’ ts: t2 price: 229.99 ts: t3 © DataStax, All Rights Reserved. 7 

8 .Column deletion on empty Memtable DELETE name FROM toys WHERE brand = ‘Lego’ AND category = ‘Star Wars’ AND id IN (75059, 75060) Memtable ‘Star Wars’-75059 ts: Long.MIN name: <tombstone> ts: t4 ‘Star Wars’-75060 ts: Long.MIN name: <tombstone> ts: t4 © DataStax, All Rights Reserved. 8 

9 .Row deletion DELETE FROM toys WHERE brand = ‘Lego’ AND category = ‘Star Wars’ AND id = 75059 Memtable ‘Star Wars’-75059 ts: Long.MIN deletedAt: t5 ‘Star Wars’-75060 ts: t1 name: <tombstone> ts: t4 price: 229.99 ts: t3 ‘Star Wars’-75095 ts: Long.MIN name: ‘Tie Fighter’ ts: t2 price: 229.99 ts: t3 © DataStax, All Rights Reserved. 9 

10 .Range deletion (3.0) DELETE FROM toys WHERE brand = ‘Lego’ AND category = ‘Star Wars’ AND id <= 75060 Memtable DeletionInfo deletedAt: Long.MIN ranges: (‘Star Wars’ … ‘Start Wars’-75060] ‘Star Wars’-75059 ts: Long.MIN deletedAt: t5 ‘Star Wars’-75060 ts: t1 name: <tombstone> ts: t4 price: 229.99 ts: t3 ‘Star Wars’-75095 ts: Long.MIN name: ‘Tie Fighter’ ts: t2 price: 229.99 ts: t3 © DataStax, All Rights Reserved. 10 

11 .Partition deletion DELETE FROM toys WHERE brand = ‘Lego' Memtable DeletionInfo deletedAt: t6 ranges: (‘Star Wars’ … ‘Start Wars’-75060] ‘Star Wars’-75059 ts: Long.MIN deletedAt: t5 ‘Star Wars’-75060 ts: t1 name: <tombstone> ts: t4 price: 229.99 ts: t3 ‘Star Wars’-75095 ts: Long.MIN name: ‘Tie Fighter’ ts: t2 price: 229.99 ts: t3 © DataStax, All Rights Reserved. 11 

12 . Filtering (3.0, 3.6, 3.10) © DataStax, All Rights Reserved. 12 

13 .Filtering CREATE TABLE scores ( user text, game text, year int, month int, day int, score int, PRIMARY KEY (user, game, year, month, day)) © DataStax, All Rights Reserved. 13 

14 .Filtering In 2.2: SELECT * FROM scores WHERE user = ‘Aleksey’ AND game = ‘coup’ AND score >= 1000 InvalidRequest: Error from server: code=2200 [Invalid query] message="Predicates on non-primary-key columns (score) are not yet supported for non secondary index queries" © DataStax, All Rights Reserved. 14 

15 .Filtering In 3.0: SELECT * FROM scores WHERE user = ‘Aleksey’ AND game = ‘coup’ AND score >= 1000 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" © DataStax, All Rights Reserved. 15 

16 . Filtering = Brute Force approach © DataStax, All Rights Reserved. 16 

17 .Filtering SELECT * FROM scores WHERE user = ‘Aleksey’ AND game = ‘coup’ AND score >= 1000 ALLOW FILTERING String partitionKey = "Aleksey"; String[] clusteringPrefix = new String[]{"coup"}; List<Row> rows = loadRows(partitionKey, clusteringPrefix); List<Row> filteredRows = new ArrayList<>(); for (Row row : rows) { if (row.getInt("score") >= 1000) { filteredRows.add(row); } } return filteredRows; © DataStax, All Rights Reserved. 17 

18 .Clustering column filtering (3.6) SELECT * FROM scores WHERE user = ‘Aleksey’ AND game = ‘coup’ AND month = 9 Filtering ALLOW FILTERING SELECT * FROM scores WHERE user = ‘Aleksey’ AND game = ‘coup’ AND year >= 2014 Clustering slice AND month = 9 Filtering ALLOW FILTERING © DataStax, All Rights Reserved. 18 

19 .Filtering Filtering is performed on the replica side Filtering can return stale data (CASSANDRA-8273) © DataStax, All Rights Reserved. 19 

20 .Filtering 3 replicas: A, B, C INSERT INTO scores (user, game, year, month, day, score) VALUES (‘Aleksey’, ‘coup’, 2016, 1, 12, 1100); At QUORUM: UPDATE scores SET score = 1200 WHERE user = ‘Aleksey’ AND game = ‘coup’ AND year = 2016 AND month = 1 AND day = 12; SELECT * FROM scores WHERE user = ‘Aleksey’ AND game = ‘coup’ AND score = 1100 ALLOW FILTERING © DataStax, All Rights Reserved. 20 

21 .Filtering In 3.0 filtering is supported on: • Non primary key columns • Static columns In 3.6 filtering is also supported on clustering columns In 3.10 filtering will be supported on partition key When using filtering, be aware of: • Its performance unpredictability • The fact that it can return stale data © DataStax, All Rights Reserved. 21 

22 . Grouping (3.10) © DataStax, All Rights Reserved. 22 

23 .Grouping SELECT year, month, max(score), min(score), count(score) FROM scores WHERE user = ‘Aleksey’ AND game = ‘coup’ AND year = 2016 GROUP BY month LIMIT 2 Year Month Day Score 2016 1 12 1200 2016 1 31 800 2016 2 8 1050 2016 3 1 1400 […] 2016 6 24 800 © DataStax, All Rights Reserved. 23 

24 .Grouping SELECT score, count(*) FROM scores WHERE user = ‘Aleksey’ AND game = ‘coup’ AND year = 2016 GROUP BY score LIMIT 2 Year Month Day Score 2016 1 12 1200 2016 1 31 800 2016 2 8 1050 2016 3 1 1400 […] 2016 6 24 800 © DataStax, All Rights Reserved. 24 

25 .Grouping SELECT score, count(*) FROM scores WHERE user = ‘Aleksey’ AND game = ‘coup’ AND year = 2016 GROUP BY score LIMIT 2 InvalidRequest: Error from server: code=2200 [Invalid query] message="Group by is currently only supported on the columns of the PRIMARY KEY, got score" © DataStax, All Rights Reserved. 25 

26 .Grouping CREATE MATERIALIZED VIEW yearlyHigh AS SELECT user, game, year, score, month, day FROM scores WHERE user IS NOT NULL AND game IS NOT NULL AND year IS NOT NULL AND score IS NOT NULL AND month IS NOT NULL AND day IS NOT NULL PRIMARY KEY (user, game, year, score, month, day) WITH CLUSTERING ORDER BY (game ASC, year DESC, score DESC) © DataStax, All Rights Reserved. 26 

27 .Grouping SELECT score, count(*) FROM yearlyHigh WHERE user = ‘Aleksey’ AND game = ‘coup’ AND year = 2016 GROUP BY score LIMIT 2 Year Score Month Day 2016 1400 3 1 2016 1400 6 12 2016 1050 2 8 2016 1020 5 23 […] 2016 800 6 24 © DataStax, All Rights Reserved. 27 

28 .Grouping CREATE TABLE gameScores ( user text, game text, year int, month int, day int, score int, PRIMARY KEY ((user, game, year), month, day)) Partition key © DataStax, All Rights Reserved. 28 

29 .Grouping SELECT year, max(score), min(score), count(score) FROM gameScores GROUP BY user, game InvalidRequest: Error from server: code=2200 [Invalid query] message="Group by is not supported on only a part of the partition key" © DataStax, All Rights Reserved. 29