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;
DROP MATERIALIZED VIEW IF EXISTS cycling.cyclist_by_age;
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;
DROP MATERIALIZED VIEW IF EXISTS cycling.cyclist_by_birthday_Netherlands;
// END-dropOtherMVs
// ORIGINAL MV
// START-age
CREATE MATERIALIZED VIEW IF NOT EXISTS 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
CAPTURE 'select_from_cyclist_by_age_18.results';
// START-select_from_cyclist_by_age_18
SELECT age, name, birthday
FROM cycling.cyclist_by_age
WHERE age = 18;
// END-select_from_cyclist_by_age_18
CAPTURE OFF;
// START-cyclist_by_birthday
CREATE MATERIALIZED VIEW IF NOT EXISTS cycling.cyclist_by_birthday
AS SELECT age, birthday, name, country
FROM cycling.cyclist_base
WHERE birthday IS NOT NULL
AND cid IS NOT NULL
PRIMARY KEY (birthday, cid);
// END-cyclist_by_birthday
// START-cyclist_by_country
CREATE MATERIALIZED VIEW IF NOT EXISTS cycling.cyclist_by_country
AS SELECT age, birthday, name, country
FROM cycling.cyclist_base
WHERE country IS NOT NULL
AND cid IS NOT NULL
PRIMARY KEY (country, cid);
// END-cyclist_by_country
// ADD 2 non-PK columns
// START-plainMVtwoNonPKcols
CREATE MATERIALIZED VIEW IF NOT EXISTS 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 IF NOT EXISTS 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 IF NOT EXISTS 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 IF NOT EXISTS 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
// START-MVNetherlands
CREATE MATERIALIZED VIEW IF NOT EXISTS cycling.cyclist_by_birthday_Netherlands
AS SELECT age, birthday, name, country
FROM cycling.cyclist_base
WHERE birthday IS NOT NULL
AND cid IS NOT NULL
AND country = 'Netherlands'
PRIMARY KEY (birthday, cid);
// END-MVNetherlands
// 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
CAPTURE 'select_cyclist_by_age.results';
// START-selectMVage
SELECT *
FROM cycling.cyclist_by_age;
// END-selectMVage
CAPTURE OFF;
CAPTURE 'select_cyclist_by_birthday_and_age.results';
// START-selectMVBdayAge
SELECT *
FROM cycling.cyclist_by_birthday_and_age;
// END-selectMVBdayAge
CAPTURE OFF;
CAPTURE 'select_cyclist_by_country_and_birthday.results';
// START-selectMVCountryBday
SELECT *
FROM cycling.cyclist_by_country_and_birthday;
// END-selectMVCountryBday
CAPTURE OFF;
// 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 QUERY PRODUCES AN INTENTIONAL ERROR
// START-MVAgeBday19981223CID
SELECT *
FROM cycling.cyclist_by_age_birthday_cid
WHERE birthday = '1998-12-23';
// END-MVAgeBday19981223CID
CAPTURE 'select_from_cyclist_by_birthday_where_birthday_1987.results';
// START-select_from_cyclist_by_birthday_where_birthday_1987
SELECT age, name, birthday
FROM cycling.cyclist_by_birthday
WHERE birthday = '1987-09-04';
// END-select_from_cyclist_by_birthday_where_birthday_1987
CAPTURE OFF;
CAPTURE 'select_from_cyclist_by_country_where_country_netherlands.results';
// START-select_from_cyclist_by_country_where_country_netherlands
SELECT age, name, birthday
FROM cycling.cyclist_by_country
WHERE country = 'Netherlands';
// END-select_from_cyclist_by_country_where_country_netherlands
CAPTURE OFF;
CAPTURE 'select_from_cyclist_by_birthday_Netherlands_where_birthday_1997.results';
// START-select_from_cyclist_by_birthday_Netherlands_where_birthday_1997
SELECT age, name, birthday
FROM cycling.cyclist_by_birthday_Netherlands
WHERE birthday = '1997-02-08';
// END-select_from_cyclist_by_birthday_Netherlands_where_birthday_1997
CAPTURE OFF;