Materialized views in Cassandra

In this article we will discuss overview of Materialized views and its importance. In Cassandra Materialized views play an important role such that Materialized views are suited for high cardinality data. We also discuss How we can create, Alter and Drop Materialized views. let’s discuss one by one. 

Materialized Views:
Materialized view is work like a base table and it is defined as CQL query which can queried like a base table. Materialized view is very important for de-normalization of data in Cassandra Query Language is also good for high cardinality and high performance. Let’s understand with an example. 

Now, first we are going to define the base table (base table – User_information) and User1 is the keyspace name. User_name is the partitioning key by which we will get the result in sorted way. same primary key will be used for materialized view and another one more column can be add in materialized view for primary key to get the user information because of restriction to define primary key in materialized view. User_name, User_email, User_password and User_address are the column’s of base table. let’s have a look. 

To create table for user information used the following CQL query.  

CREATE TABLE User1.User_information (          
User_name text PRIMARY KEY,
User_email text,
User_password text,
User_address text
);

Now, let us consider that If we want to find the user information with the help of user email then we need to create a new table and by defining email as partition key we can get the user information but we have another better option which is called materialized view. 

so, now let’s create Materialized of the above given table which is also called the base table. To create materialized view used the following CQL query. 

Syntax: Creating Materialized view:  

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

Syntax Source

Example:

CREATE MATERIALIZED VIEW User1.Users_by_User_email AS
SELECT User_email, User_password, User_address
FROM User_information
WHERE User_name IS NOT NULL AND User_email IS NOT NULL
PRIMARY KEY (User_email, User_name ); 

To perform operation such that read data from materialized view used the following CQL query.  

SELECT * 
FROM Users_by_User_email 
WHERE User_email = ? ;

In materialized view there are following restriction that must follow.  

1. In materialized view whatever the primary column in base table must contain in materialized view table that ensure every row of MV (materialized view) is correspond to the base table.
2. In materialized view only we can add one more column that is not a primary column in base table.

Let’s take an example to understand the concept.  

CREATE TABLE base_table
(
Key1 int,
Col1 int,
Col2 int,
Col3 int,
Col4 text,
PRIMARY KEY (key1, Col1, Col2)
); 

Materialized view of above given base table is allowed. Let’s have a look. 

Case-1:
CREATE MATERIALIZED VIEW test_MV1 AS 
SELECT * 
FROM test_MV1 
WHERE key1 IS NOT NULL 
      AND Col1 IS NOT NULL 
      AND Col2 IS NOT NULL 
PRIMARY KEY (Col1, key1, Col2);

Case-2:
CREATE MATERIALIZED VIEW test_MV2 AS 
SELECT * 
FROM test_MV2 
WHERE key1 IS NOT NULL 
      AND Col1 IS NOT NULL 
      AND Col2 IS NOT NULL 
      AND Col3 IS NOT NULL 
PRIMARY KEY (Col3, key1, Col1, Col2); 

Materialized view of above given base table is NOT allowed. Let’s have a look. 

Case-1:
CREATE MATERIALIZED VIEW test_MV1 AS 
SELECT * 
FROM test_MV2 
WHERE key1 IS NOT NULL 
      AND Col1 IS NOT NULL 
      AND Col2 IS NOT NULL 
      AND Col3 IS NOT NULL 
PRIMARY KEY (Col3, Col4, key1, Col1, Col2);  
 // * ERROR : it will give an error that Col3 
         and Col4 is not primary key in base table.

Case-2:
CREATE MATERIALIZED VIEW test_MV1 AS 
SELECT * 
FROM test_MV2 
WHERE Col1 IS NOT NULL 
      AND Col2 IS NOT NULL 
PRIMARY KEY (Col1, Col2);
// * ERROR : it will give an error that key1 is primary key 
    in base table that must contain in materialized view. 

Alter Materialized View:
Given below is the CQL query to alter the basic properties of Materialized view using ALTER command. let’s have a look. 

Syntax:

ALTER MATERIALIZED VIEW [keyspace_name.] view_name 
[WITH table_options] 

Drop Materialized View:
We can remove materialized view table by using DROP command and there will be no affect on the base table. 

Syntax:

DROP MATERIALIZED VIEW [IF EXISTS] [keyspace_name.] view_name 

For example, to DROP Materialized view used the following CQL Query. 

DROP MATERIALIZED VIEW User1.Users_by_User_email ;