cyclist_by_age-mv
Supports queries by age without an index or allow filtering.
Supports queries by age without an index or allow filtering.
// Source keyspace, tables, and insert data
// SOURCE '0_create_keyspace.cql';
// SOURCE 'cyclist_base-table.cql';
// SOURCE 'cyclist_base_ext-table.cql';
// START-dropmv
DROP MATERIALIZED VIEW IF EXISTS cycling.cyclist_by_age;
// END-dropmv
// START-dropOtherMVs
DROP MATERIALIZED VIEW IF EXISTS cycling.cyclist_by_birthday_and_age;
DROP MATERIALIZED VIEW IF EXISTS cycling.cyclist_by_country_and_birthday;
DROP MATERIALIZED VIEW IF EXISTS cycling.cyclist_by_birthday_and_age19;
DROP MATERIALIZED VIEW IF EXISTS cycling.cyclist_by_age_birthday_cid;
// END-dropOtherMVs
// ORIGINAL MV
// START-age
CREATE MATERIALIZED VIEW cycling.cyclist_by_age AS
SELECT age, cid, birthday, country, name
FROM cycling.cyclist_base
WHERE age IS NOT NULL
AND cid IS NOT NULL
PRIMARY KEY (age, cid)
WITH CLUSTERING ORDER BY (cid ASC)
AND caching = { 'keys' : 'ALL', 'rows_per_partition' : '100' }
AND comment = 'Based on table cyclist';
// END-age
// ADD 2 non-PK columns
// START-plainMVtwoNonPKcols
CREATE MATERIALIZED VIEW cycling.cyclist_by_birthday_and_age AS
SELECT age, cid, birthday, country, name
FROM cycling.cyclist_base
WHERE age IS NOT NULL
AND birthday IS NOT NULL
AND cid IS NOT NULL
PRIMARY KEY (cid, birthday, age);
// END-plainMVtwoNonPKcols
// ADD 2 non-PK columns and specify one in the WHERE phrase
// START-WhereCountryMVtwoNonPKcols
CREATE MATERIALIZED VIEW cycling.cyclist_by_country_and_birthday AS
SELECT age, cid, birthday, country, name
FROM cycling.cyclist_base
WHERE birthday IS NOT NULL
AND cid IS NOT NULL
AND country = 'Australia'
PRIMARY KEY (cid, country, birthday);
// END-WhereCountryMVtwoNonPKcols
// ADD 2 non-PK columns and specify one in the WHERE phrase
// START-WhereAgeMVtwoNonPKcols
CREATE MATERIALIZED VIEW cycling.cyclist_by_birthday_and_age19 AS
SELECT age, cid, birthday, country, name
FROM cycling.cyclist_base
WHERE birthday IS NOT NULL
AND cid IS NOT NULL
AND age = 19
PRIMARY KEY (cid, birthday, age);
// END-WhereAgeMVtwoNonPKcols
// ADD multiple PK columns, but MV has different order for values
// START-MVmultPKcols
CREATE MATERIALIZED VIEW cycling.cyclist_by_age_birthday_cid AS
SELECT age, cid, birthday, country, name
FROM cycling.cyclist_base_ext
WHERE age IS NOT NULL
AND birthday IS NOT NULL
AND cid IS NOT NULL
PRIMARY KEY (age, birthday, cid);
// END-MVmultPKcols
// add a comment to describe the table
// START-comment
ALTER MATERIALIZED VIEW cycling.cyclist_by_age
WITH comment = 'A most excellent and useful view'
AND bloom_filter_fp_chance = 0.02;
// END-comment
// alter example
// START-compression
ALTER MATERIALIZED VIEW cycling.cyclist_by_age
WITH compression = {
'sstable_compression' : 'DeflateCompressor',
'chunk_length_kb' : 64 }
AND compaction = {
'class': 'SizeTieredCompactionStrategy',
'max_threshold': 64};
// END-compression
// alter example
// START-cache
ALTER MATERIALIZED VIEW cycling.cyclist_by_age
WITH caching = {
'keys' : 'NONE',
'rows_per_partition' : '15' };
// END-cache
// START-test
INSERT INTO cycling.cyclist_base (cid,name,age,birthday,country)
VALUES (
d1aad83b-be60-47a4-bd6e-069b8da0d97b,
'Johannes HEIDER',
27,
'1987-09-04',
'Germany'
);
// END-test
// Add some data to cyclist_base
// START-cyclist_baseData
INSERT INTO cycling.cyclist_base (cid,name,age,birthday,country)
VALUES (
d1aad83b-be60-47a4-bd6e-069b8da0d97b,
'Johannes HEIDER',
27,
'1987-09-04',
'Germany'
);
INSERT INTO cycling.cyclist_base (cid,name,age,birthday,country)
VALUES (
1c526849-d3a2-42a3-bcf9-7903c80b3d16,
'Kanden GROVES',
19,
'1998-12-23',
'Australia'
);
INSERT INTO cycling.cyclist_base (cid,name,age,birthday,country)
VALUES (
96c4c40d-58c8-4710-b73f-681e9b1f70ae,
'Benjamin DYBALL',
29,
'1989-04-20',
'Australia'
);
INSERT INTO cycling.cyclist_base (cid,name,age,birthday,country)
VALUES (
410919ef-bd1b-4efa-8256-b0fd8ab67029,
'Iskandarbek SHODIEV',
19,
'1999-01-04',
'Uzbekistan'
);
// END-cyclist_baseData
// Add some data to cyclist_base_ext
// START-cyclist_base_extData
INSERT INTO cycling.cyclist_base_ext (cid,name,age,birthday,country)
VALUES (
d1aad83b-be60-47a4-bd6e-069b8da0d97b,
'Johannes HEIDER',
27,
'1987-09-04',
'Germany'
);
INSERT INTO cycling.cyclist_base_ext (cid,name,age,birthday,country)
VALUES (
1c526849-d3a2-42a3-bcf9-7903c80b3d16,
'Kanden GROVES',
19,
'1998-12-23',
'Australia'
);
INSERT INTO cycling.cyclist_base_ext (cid,name,age,birthday,country)
VALUES (
96c4c40d-58c8-4710-b73f-681e9b1f70ae,
'Benjamin DYBALL',
29,
'1989-04-20',
'Australia'
);
INSERT INTO cycling.cyclist_base_ext (cid,name,age,birthday,country)
VALUES (
410919ef-bd1b-4efa-8256-b0fd8ab67029,
'Iskandarbek SHODIEV',
19,
'1999-01-04',
'Uzbekistan'
);
// END-cyclist_base_extData
// SELECTS
// START-selectMVage
SELECT *
FROM cycling.cyclist_by_age;
// END-selectMVage
// START-selectMVBdayAge
SELECT *
FROM cycling.cyclist_by_birthday_and_age;
// END-selectMVBdayAge
// START-selectMVCountryBday
SELECT *
FROM cycling.cyclist_by_country_and_birthday;
// END-selectMVCountryBday
// START-selectMV19BdayAge
SELECT *
FROM cycling.cyclist_by_birthday_and_age19;
// END-selectMV19BdayAge
// START-MVAge19BdayCID
SELECT *
FROM cycling.cyclist_by_age_birthday_cid
WHERE age = 19;
// END-MVAge19BdayCID
// START-MV19981223CIDAge19Bday
SELECT *
FROM cycling.cyclist_by_age_birthday_cid
WHERE age = 19
AND birthday = '1998-12-23';
// END-MV19981223CIDAge19Bday
// THIS ONE WILL ERROR OUT
// START-MVAgeBday19981223CID
SELECT *
FROM cycling.cyclist_by_age_birthday_cid
WHERE birthday = '1998-12-23';
// END-MVAgeBday19981223CID