CREATE MATERIALIZED VIEW
Optimizes read requests by allowing different partitioning and clustering columns than the base table and eliminates the need for individual write requests to multiple tables. When data is written to the base table, it is also automatically written to all associated materialized views.
Restriction:
-
Use all base table primary keys in the materialized view.
-
Multiple non-primary key columns from the base table are supported when the partition key is the same as in the base table, otherwise only a single non-primary key from the base table is allowed in the materialized view’s PRIMARY KEY.
-
Static columns are not supported.
-
Exclude rows with null values in the materialized view primary key column.
-
A materialized view cannot be created in a different keyspace from the base table. You also cannot create a materialized view in the system tables.
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>) ] ] ;
Syntax legend
Syntax conventions | Description |
---|---|
UPPERCASE |
Literal keyword. |
Lowercase |
Not literal. |
|
Variable value. Replace with a user-defined value. |
|
Optional.
Square brackets ( |
|
Group.
Parentheses ( |
|
Or.
A vertical bar ( |
|
Repeatable.
An ellipsis ( |
|
Single quotation ( |
|
Map collection.
Braces ( |
Set, list, map, or tuple.
Angle brackets ( |
|
|
End CQL statement.
A semicolon ( |
|
Separate the command line options from the command arguments with two hyphens ( |
|
Search CQL only: Single quotation marks ( |
|
Search CQL only: Identify the entity and literal value to overwrite the XML element in the schema and solrConfig files. |
- 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.
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 columns from the base table to include in the materialized view.
Static columns, even when specified, are not supported and not included in the materialized view.
- <column_name> IS NOT NULL
-
Test all columns for null values in the
WHERE
clause. Separate each condition withAND
. Rows with null values in any column 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 non-primary key columns 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.
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
This section shows example scenarios that illustrate the use of materialized views.
Basic examples of materialized views
These tables are used in the first example scenario:
CREATE TABLE IF NOT EXISTS cycling.cyclist_base (
cid UUID PRIMARY KEY,
name text,
age int,
birthday date,
country text
);
CREATE TABLE IF NOT EXISTS cycling.cyclist_base_ext (
cid UUID,
name text,
age int,
birthday date,
country text,
PRIMARY KEY (cid, age, birthday)
);
The following materialized view cyclist_by_age
uses the base table cyclist_base
.
The WHERE
clause ensures that only rows whose age
and cid
columns are non-NULL are added to the materialized view.
In the materialized view, age
is the partition key, and cid
is the clustering column.
In the base table, cid
is the partition key.
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';
The results of this query:
SELECT *
FROM cycling.cyclist_by_age;
are:
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)
The following materialized view cyclist_by_birthday_and_age
uses the base table cyclist_base
.
The WHERE
clause ensures that only rows whose age
, birthday
, and cid
columns are non-NULL are added to the materialized view.
In the materialized view, cid
is the partition key, birthday
is the first clustering column, and age
is the second clustering column.
In the base table, cid
is the partition key.
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);
The results of this query:
SELECT *
FROM cycling.cyclist_by_birthday_and_age;
are:
cid | birthday | age | country | name
--------------------------------------+------------+-----+---------------+---------------------
ffdfa2a7-5fc6-49a7-bfdc-3fcdcfdd7156 | 1997-02-08 | 18 | Netherlands | Pascal EENKHOORN
15a116fc-b833-4da6-ab9a-4a7775752836 | 1997-08-19 | 18 | United States | Adrien COSTA
e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 1993-06-18 | 22 | New Zealand | Alex FRAME
c9c9c484-5e4a-4542-8203-8d047a01b8a8 | 1987-09-04 | 27 | Brazil | Cristian EGIDIO
410919ef-bd1b-4efa-8256-b0fd8ab67029 | 1999-01-04 | 19 | Uzbekistan | Iskandarbek SHODIEV
d1aad83b-be60-47a4-bd6e-069b8da0d97b | 1987-09-04 | 27 | Germany | Johannes HEIDER
862cc51f-00a1-4d5a-976b-a359cab7300e | 1994-09-04 | 20 | Denmark | Joakim BUKDAL
1c526849-d3a2-42a3-bcf9-7903c80b3d16 | 1998-12-23 | 19 | Australia | Kanden GROVES
18f471bf-f631-4bc4-a9a2-d6f6cf5ea503 | 1997-03-29 | 18 | Netherlands | Bram WELTEN
220844bf-4860-49d6-9a4b-6b5d3a79cbfb | 1977-07-08 | 38 | Italy | Paolo TIRALONGO
6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 | 1987-06-07 | 28 | Netherlands | Steven KRUIKSWIJK
96c4c40d-58c8-4710-b73f-681e9b1f70ae | 1989-04-20 | 29 | Australia | Benjamin DYBALL
(12 rows)
The following materialized view cyclist_by_country_and_birthday
uses the base table cyclist_base
.
The WHERE
clause ensures that only rows whose birthday
and cid
columns are non-NULL and country
equals Australia
are added to the materialized view.
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);
The results of this query:
SELECT *
FROM cycling.cyclist_by_country_and_birthday;
are:
cid | country | birthday | age | name
--------------------------------------+-----------+------------+-----+-----------------
1c526849-d3a2-42a3-bcf9-7903c80b3d16 | Australia | 1998-12-23 | 19 | Kanden GROVES
96c4c40d-58c8-4710-b73f-681e9b1f70ae | Australia | 1989-04-20 | 29 | Benjamin DYBALL
(2 rows)
The following materialized view cyclist_by_birthday_and_age19
uses the base table cyclist_base
.
The WHERE
clause ensures that only rows whose birthday
and cid
columns are non-NULL and age
equals the value 19
are added to the materialized view.
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);
The results of this query:
SELECT *
FROM cycling.cyclist_by_birthday_and_age19;
are:
cid | birthday | age | country | name
--------------------------------------+------------+-----+------------+---------------------
410919ef-bd1b-4efa-8256-b0fd8ab67029 | 1999-01-04 | 19 | Uzbekistan | Iskandarbek SHODIEV
1c526849-d3a2-42a3-bcf9-7903c80b3d16 | 1998-12-23 | 19 | Australia | Kanden GROVE
(2 rows)
The following materialized view cyclist_by_age_birthday_cid
uses the base table cyclist_base_ext
.
The WHERE
clause ensures that only rows whose age
, birthday
, and cid
columns are non-NULL are added to the materialized view.
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);
The results of this query:
SELECT *
FROM cycling.cyclist_by_age_birthday_cid
WHERE age = 19;
are:
cid | birthday | age | country | name
--------------------------------------+------------+-----+------------+---------------------
410919ef-bd1b-4efa-8256-b0fd8ab67029 | 1999-01-04 | 19 | Uzbekistan | Iskandarbek SHODIEV
1c526849-d3a2-42a3-bcf9-7903c80b3d16 | 1998-12-23 | 19 | Australia | Kanden GROVE
(2 rows)
The results of this query using a WHERE clause for two values:
SELECT *
FROM cycling.cyclist_by_age_birthday_cid
WHERE age = 19
AND birthday = '1998-12-23';
are:
age | birthday | cid | country | name
-----+------------+--------------------------------------+-----------+---------------
19 | 1998-12-23 | 1c526849-d3a2-42a3-bcf9-7903c80b3d16 | Australia | Kanden GROVES
(1 rows)
Notice that clustering columns must still be included in order. This query violates the rule:
SELECT *
FROM cycling.cyclist_by_age_birthday_cid
WHERE birthday = '1998-12-23';
Result:
materialized-view-cyclist-by-age.cql:195:InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot
execute this query as it might involve data filtering and thus may have unpredictable performance. If you
want to execute this query despite the performance unpredictability, use ALLOW FILTERING.
Using a materialized view to perform queries that are not possible on a base table
The following scenario shows how to use a materialized view to perform queries that are not possible on a base table unless ALLOW FILTERING is used. ALLOW FILTERING is not recommended because of the performance degradation. This table stores the cycling team mechanic information:
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)
);
The table contains these rows:
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)
This materialized view selects the columns from the previous table and contains a different primary key from the table:
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);
This query retrieves the rows where the age is 21:
SELECT *
FROM cycling.mechanic_view
WHERE age = 21;
The previous query cannot be run on the base table without ALLOW FILTERING. The output from the previous query is as follows:
age | emp_id | dept_id | birthdate | name
-----+--------+---------+------------+------------
21 | 1 | 1 | 1992-06-18 | Fred GREEN
(1 rows)
-