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;