Materialized Views and Secondary Indexes Hands-On

13 min to complete

Materialized Views and Secondary Indexes Hands-On
# Pick a ScyllaDB version: https://hub.docker.com/r/scylladb/scylla/tags
# This tutorial was created with 3.1.0.rc9

docker run --name scylla-si -d scylladb/scylla:3.1.0.rc9 --smp 2 --memory 4G

Open 3 terminals (#1 for base table, #2 for global index, #3 for local index

BASE TABLE – On terminal #1

docker exec -ti scylla-si /bin/bash
cqlsh
CREATE KEYSPACE restaurant_chain
WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };

Keep in mind that SimpleStrategy should not be used in production.

USE restaurant_chain;
CREATE TABLE restaurant_chain.menus (
location text,
name text,
dish_type text,
price float,
PRIMARY KEY (location, name));
INSERT INTO menus (location, name, price, dish_type) VALUES ('Reykjavik', 'hakarl', 16, 'cold Icelandic starter');
INSERT INTO menus (location, name, price, dish_type) VALUES ('Reykjavik', 'svid', 21, 'hot Icelandic main dish');
INSERT INTO menus (location, name, price, dish_type) VALUES ('Warsaw', 'sour rye soup', 7, 'Polish soup');
INSERT INTO menus (location, name, price, dish_type) VALUES ('Warsaw', 'sorrel soup', 5, 'Polish soup');
INSERT INTO menus (location, name, price, dish_type) VALUES ('Cracow', 'beef tripe soup', 6, 'Polish soup');
INSERT INTO menus (location, name, price, dish_type) VALUES ('Warsaw', 'pork jelly', 8, 'cold Polish starter');
INSERT INTO menus (location, name, price, dish_type) VALUES ('Ho Chi Minh', 'bun mam', 8, 'Vietnamese soup');
INSERT INTO menus (location, name, price, dish_type) VALUES ('Da Lat', 'banh mi', 5, 'Vietnamese breakfast');
INSERT INTO menus (location, name, price, dish_type) VALUES ('Ho Chi Minh', 'goi cuon', 6, 'Vietnamese hot starter');
DESC SCHEMA;
SELECT * from restaurant_chain.menus;
exit
nodetool flush
cd /var/lib/scylla/data/restaurant_chain/
ls -l */*

GLOBAL INDEX – On terminal #2

docker exec -ti scylla-si /bin/bash
cqlsh
USE restaurant_chain;
CREATE INDEX ON menus(dish_type);
DESC SCHEMA;
SELECT * from restaurant_chain.menus_dish_type_idx_index;
exit
nodetool flush
cd /var/lib/scylla/data/restaurant_chain/
ls -l */*

LOCAL INDEX – On terminal #3

docker exec -ti scylla-si /bin/bash
cqlsh
USE restaurant_chain;
CREATE INDEX ON menus((location),dish_type);
DESC SCHEMA;
SELECT * from restaurant_chain.menus_dish_type_idx_1_index;
exit
nodetool flush
cd /var/lib/scylla/data/restaurant_chain/
ls -l */*

Inspecting sstables for a better understanding

NOTE: The directories and files below are hypothetical. You should get the correct names from the ls -l */* (the last command ran in each terminal).

# BASE TABLE – On terminal #1

sstabledump menus-76bce980e9e711e9b4a6000000000001/mc-2-big-Data.db

# GLOBAL INDEX – On terminal #2

sstabledump menus_dish_type_idx_index-11248001e9e811e9b4a6000000000001/mc-2-big-Data.db

# LOCAL INDEX – On terminal #3

sstabledump menus_dish_type_idx_1_index-bac41d51e9e811e9b4a6000000000001/mc-2-big-Data.db
cqlsh
USE restaurant_chain;
SELECT token(location), location, name, dish_type, price
FROM restaurant_chain.menus;
SELECT dish_type, blobAsBigint(idx_token), location, name
FROM restaurant_chain.menus_dish_type_idx_index;
SELECT token(location), location, dish_type, name
FROM restaurant_chain.menus_dish_type_idx_1_index;
Looking at traces in CQLSH:
cqlsh
TRACING ON
select * from restaurant_chain.menus where location = 'Warsaw';
select * from restaurant_chain.menus where dish_type = 'Polish soup';
select * from restaurant_chain.menus where dish_type = 'Polish soup' AND location = 'Warsaw';
select * from restaurant_chain.menus where location = 'Warsaw' AND dish_type = 'Polish soup';
fa-angle-up