materialized-view-cyclist-base

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 (
  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'
);

DESCRIBE MATERIALIZED VIEW cycling.cyclist_by_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_base';

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);

CREATE MATERIALIZED VIEW IF NOT EXISTS cycling.cyclist_by_birthday
  AS SELECT age, cid, birthday, name, country
  FROM cycling.cyclist_base
  WHERE birthday IS NOT NULL AND cid IS NOT NULL
  PRIMARY KEY (birthday, cid);

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);

CREATE MATERIALIZED VIEW IF NOT EXISTS cycling.cyclist_by_birthday_and_age_19 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 (birthday, age);

CREATE MATERIALIZED VIEW IF NOT EXISTS cycling.cyclist_by_birthday_netherlands
  AS SELECT age, cid, birthday, name, country FROM cycling.cyclist_base
  WHERE birthday IS NOT NULL AND cid IS NOT NULL AND country = 'Netherlands'
  PRIMARY KEY (birthday, cid);

CREATE MATERIALIZED VIEW IF NOT EXISTS cycling.cyclist_by_country
  AS SELECT age, cid, birthday, name, country
  FROM cycling.cyclist_base
  WHERE country IS NOT NULL AND cid IS NOT NULL
  PRIMARY KEY (country, cid);

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);

DESCRIBE MATERIALIZED VIEW cycling.cyclist_by_age;

DESCRIBE MATERIALIZED VIEW cycling.cyclist_by_age_birthday_cid;

DESCRIBE MATERIALIZED VIEW cycling.cyclist_by_birthday;

DESCRIBE MATERIALIZED VIEW cycling.cyclist_by_birthday_and_age;

DESCRIBE MATERIALIZED VIEW cycling.cyclist_by_birthday_and_age_is_19;

DESCRIBE MATERIALIZED VIEW cycling.cyclist_by_birthday_netherlands;

DESCRIBE MATERIALIZED VIEW cycling.cyclist_by_country;

DESCRIBE MATERIALIZED VIEW cycling.cyclist_by_country_and_birthday;

DROP MATERIALIZED VIEW IF EXISTS cycling.cyclist_by_age;

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;

SELECT age, name, birthday FROM cycling.cyclist_by_age WHERE age = 18;

SELECT * FROM cycling.cyclist_by_age;

SELECT * FROM cycling.cyclist_by_age_birthday_cid
  WHERE age = 19;

SELECT * FROM cycling.cyclist_by_birthday;

SELECT * FROM cycling.cyclist_by_age_birthday_cid
  WHERE age = 19 AND birthday = '1998-12-23';

SELECT * FROM cycling.cyclist_by_age_birthday_cid
  WHERE birthday = '1998-12-23';

SELECT * FROM cycling.cyclist_by_birthday_and_age;

SELECT * FROM cycling.cyclist_by_birthday_and_age_19;

SELECT * FROM cycling.cyclist_by_birthday_netherlands;

SELECT * FROM cycling.cyclist_by_country;

SELECT age, name, birthday FROM cycling.cyclist_by_birthday
  WHERE birthday = '1987-09-04';

SELECT age, name, birthday FROM cycling.cyclist_by_birthday_and_age
  WHERE birthday = '1997-02-08';

SELECT age, name, birthday FROM cycling.cyclist_by_birthday_netherlands
  WHERE birthday = '1997-02-08';

SELECT age, name, birthday FROM cycling.cyclist_by_country
  WHERE country = 'Netherlands';
// end::select_from_cyclist_by_country_where_country_netherlands[]

ALTER MATERIALIZED VIEW cycling.cyclist_by_age
WITH caching = {
  'keys' : 'NONE',
  'rows_per_partition' : '15'
};

ALTER MATERIALIZED VIEW cycling.cyclist_by_age
  WITH comment = 'A most excellent and useful view'
  AND bloom_filter_fp_chance = 0.02;