CREATE MATERIALIZED VIEW

Synopsis

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] [keyspace_name.]view_name
  AS SELECT [ (column_list) ]
  FROM [keyspace_name.]table_name
  WHERE column_name IS NOT NULL [ AND column_name IS NOT NULL ... ]
    [ AND relation [ AND ... ] ] 
  PRIMARY KEY (column_list)
  [ WITH [ table_properties ]
    [ [ AND ] CLUSTERING ORDER BY (cluster_column_name order_option) ] ] ;

IF NOT EXISTS

Optional. Suppresses the error message when attempting to create a materialized view that already exists. Use to continue executing commands, such as a SOURCE command. The option only validates that a materialized view with the same name exists; columns, primary keys, properties and other settings can differ.

keyspace_name

Optional. When no keyspace is selected or to create the view in another keyspace, set the keyspace name before the materialized view name.

Note: Base tables and materialized views are always in the same keyspace.

view_name

Materialized view names can only contain alpha-numeric characters and underscores. The view name must begin with a number or letter and can be up to 49 characters long.

column_list

Comma-separated list of non-PRIMARY KEY columns from the base table to include in the materialized view. All primary key columns are automatically included.

Static columns, even when specified, are not included in the materialized view.

PK_column_name IS NOT NULL

Test all primary key columns for null values in the where clause. Separate each condition with AND. Rows with null values in the primary key are not inserted into the materialized view table.

AND relation

Other relations that target the specific data needed.

PRIMARY KEY (column_list)

Comma-separated list of columns used to partition and cluster the data. You can add a single non-primary key column from the base table. Reorder the primary keys as needed to query the table more efficiently, including changing the partitioning and clustering keys.

List the partition key first, followed by the clustering keys. Create a compound partition key by enclosing column names in parenthesis, for example:

PRIMARY KEY (
  ( PK_column1[, PK_column2 ...] ) ,
    clustering_column1[, clustering_column2 ... ] )

table_properties

Optional. Specify table properties if different than default. Separate table property definitions with an AND. See table properties.

Note: The base table properties are not copied.

Restriction: Change log, CDC, is not available for materialized views. Not all table properties are available when creating a materialized view; for example, default_time_to_live is not available.

Examples

Basic example of a materialized view

CREATE TABLE IF NOT EXISTS cycling.cyclist_base (
  cid UUID PRIMARY KEY, 
  name text, 
  age int, 
  birthday date, 
  country text
);

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

SELECT *
FROM cycling.cyclist_by_age;

 age | cid                                  | birthday   | country       | name
-----+--------------------------------------+------------+---------------+---------------------
  28 | 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 | 1987-06-07 |   Netherlands |   Steven KRUIKSWIJK
  19 | 1c526849-d3a2-42a3-bcf9-7903c80b3d16 | 1998-12-23 |     Australia |       Kanden GROVES
  19 | 410919ef-bd1b-4efa-8256-b0fd8ab67029 | 1999-01-04 |    Uzbekistan | Iskandarbek SHODIEV
  18 | 15a116fc-b833-4da6-ab9a-4a7775752836 | 1997-08-19 | United States |        Adrien COSTA
  18 | 18f471bf-f631-4bc4-a9a2-d6f6cf5ea503 | 1997-03-29 |   Netherlands |         Bram WELTEN
  18 | ffdfa2a7-5fc6-49a7-bfdc-3fcdcfdd7156 | 1997-02-08 |   Netherlands |    Pascal EENKHOORN
  22 | e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 1993-06-18 |   New Zealand |          Alex FRAME
  27 | c9c9c484-5e4a-4542-8203-8d047a01b8a8 | 1987-09-04 |        Brazil |     Cristian EGIDIO
  27 | d1aad83b-be60-47a4-bd6e-069b8da0d97b | 1987-09-04 |       Germany |     Johannes HEIDER
  20 | 862cc51f-00a1-4d5a-976b-a359cab7300e | 1994-09-04 |       Denmark |       Joakim BUKDAL
  38 | 220844bf-4860-49d6-9a4b-6b5d3a79cbfb | 1977-07-08 |         Italy |     Paolo TIRALONGO
  29 | 96c4c40d-58c8-4710-b73f-681e9b1f70ae | 1989-04-20 |     Australia |     Benjamin DYBALL

(12 rows)

Using a materialized view to perform queries that are not possible on a base table

CREATE TABLE IF NOT EXISTS cycling.mechanic (
  emp_id int,
  dept_id int,
  name text,
  age int,
  birthdate date,
  PRIMARY KEY (emp_id, dept_id)
);

 emp_id | dept_id | age | birthdate  | name
--------+---------+-----+------------+------------
      5 |       3 |  25 | 1996-10-04 | Lisa SMITH
      1 |       1 |  21 | 1992-06-18 | Fred GREEN
      2 |       1 |  22 | 1993-01-15 | John SMITH
      4 |       2 |  24 | 1995-08-19 | Jack JONES
      3 |       2 |  23 | 1994-02-07 |   Jane DOE

(5 rows)

CREATE MATERIALIZED VIEW IF NOT EXISTS cycling.mechanic_view AS
  SELECT emp_id, dept_id, name, age, birthdate
  FROM cycling.mechanic
  WHERE emp_id IS NOT NULL
    AND dept_id IS NOT NULL
    AND name IS NOT NULL
    AND age IS NOT NULL
    AND birthdate IS NOT NULL
  PRIMARY KEY (age, emp_id, dept_id);

SELECT *
FROM cycling.mechanic_view
WHERE age = 21;

 age | emp_id | dept_id | birthdate  | name
-----+--------+---------+------------+------------
  21 |      1 |       1 | 1992-06-18 | Fred GREEN

(1 rows)