CQL MV Example

CREATE KEYSPACE mykeyspace WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '1'}  AND durable_writes = true;
USE mykeyspace;
CREATE TABLE buildings (name text, city text, height int, PRIMARY KEY (name));

INSERT INTO buildings(name,city,height) VALUES ('Burj Khalifa','Dubai',828);
INSERT INTO buildings(name,city,height) VALUES ('Shanghai Tower','Shanghai',632);
INSERT INTO buildings(name,city,height) VALUES ('Abraj Al-Bait Clock Tower','Mecca',601);
INSERT INTO buildings(name,city,height) VALUES ('Ping An Finance Centre','Shenzhen',599);
INSERT INTO buildings(name,city,height) VALUES ('Lotte World Tower','Seoul',554);
INSERT INTO buildings(name,city,height) VALUES ('One World Trade Center','New York City',541);
INSERT INTO buildings(name,city,height) VALUES ('Guangzhou CTF Finance Centre','Guangzhou',530);
INSERT INTO buildings(name,city,height) VALUES ('Tianjin CTF Finance Centre','Tianjin',530);
INSERT INTO buildings(name,city,height) VALUES ('China Zun','Beijing',528);
INSERT INTO buildings(name,city,height) VALUES ('Taipei 101','Taipei',508);
INSERT INTO buildings(name,city,height) VALUES ('Shanghai World Financial Center','Shanghai',492);
INSERT INTO buildings(name,city,height) VALUES ('International Commerce Centre	Hong','Kong',484);
INSERT INTO buildings(name,city,height) VALUES ('Lakhta Center','StPetersburg',462);
INSERT INTO buildings(name,city,height) VALUES ('Landmark 81','Ho Chi Minh City',461);
INSERT INTO buildings(name,city,height) VALUES ('Changsha IFS Tower T1','Changsha',452);
INSERT INTO buildings(name,city,height) VALUES ('Petronas Tower 1','Kuala Lumpur',451);
INSERT INTO buildings(name,city,height) VALUES ('Petronas Tower 2','Kuala Lumpur',451);
INSERT INTO buildings(name,city,height) VALUES ('The Exchange 106','Kuala Lumpur',451);
INSERT INTO buildings(name,city,height) VALUES ('Zifeng Tower','Nanjing',450);
INSERT INTO buildings(name,city,height) VALUES ('Suzhou IFS','Suzhou',450);
INSERT INTO buildings(name,city,height) VALUES ('Willis Tower','Chicago',442);
INSERT INTO buildings(name,city,height) VALUES ('KK100','Shenzhen',442);
INSERT INTO buildings(name,city,height) VALUES ('Guangzhou International Finance Center','Guangzhou',440);
INSERT INTO buildings(name,city,height) VALUES ('Wuhan Center','Wuhan',438);
INSERT INTO buildings(name,city,height) VALUES ('432 Park Avenue','New York City',425);
INSERT INTO buildings(name,city,height) VALUES ('Marina 101','Dubai',425);
INSERT INTO buildings(name,city,height) VALUES ('Trump International Hotel and Tower','Chicago',423);
INSERT INTO buildings(name,city,height) VALUES ('Jin Mao Tower','Shanghai',421);
INSERT INTO buildings(name,city,height) VALUES ('Princess Tower','Dubai',414);
INSERT INTO buildings(name,city,height) VALUES ('Al Hamra Tower','	Kuwait City',413);
INSERT INTO buildings(name,city,height) VALUES ('Two International Finance Centre',' Hong Kong',412);

select * from buildings WHERE name = 'Tianjin CTF Finance Centre';

cqlsh:mykeyspace> select * from buildings WHERE city = 'Shenzhen';
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 buildings WHERE city = 'Shenzhen' ALLOW FILTERING;

CREATE MATERIALIZED VIEW building_by_city AS
 SELECT * FROM buildings
 WHERE city IS NOT NULL AND name IS NOT NULL
 PRIMARY KEY(city, name);

cqlsh:mykeyspace> select * from building_by_city WHERE city = 'Shenzhen';

 city     | name                   | height
----------+------------------------+--------
 Shenzhen |                  KK100 |    442
 Shenzhen | Ping An Finance Centre |    599

(2 rows)


## View includes Subset of the rows
CREATE MATERIALIZED VIEW building_by_city_at_Shenzhen AS SELECT * FROM buildings WHERE city IS NOT NULL AND name IS NOT NULL AND city = 'Shenzhen' PRIMARY KEY(city, name);
select * from building_by_city_at_Shenzhen;

 city     | name                   | height
----------+------------------------+--------
 Shenzhen |                  KK100 |    442
 Shenzhen | Ping An Finance Centre |    599


CREATE TABLE heartrate (
 pet_chip_id  uuid,
 time timestamp,
 heart_rate int,
PRIMARY KEY (pet_chip_id, time));

CREATE MATERIALIZED VIEW heartrate_by_rate AS SELECT * FROM heartrate WHERE pet_chip_id IS NOT NULL AND time IS NOT NULL AND heart_rate IS NOT NULL PRIMARY KEY(pet_chip_id,heart_rate,time);

SELECT * FROM heartrate_by_rate WHERE pet_chip_id = 123e4567-e89b-12d3-a456-426655440b23 LIMIT 1;

SELECT * FROM heartrate_by_rate WHERE pet_chip_id = 123e4567-e89b-12d3-a456-426655440b23 AND heart_rate >=120;

### Sec Index Examples

CREATE INDEX buildings_by_city ON buildings (city);
SELECT * FROM buildings WHERE city = 'New York City';