Creating materialised view using table definition in cassandra

A materialised is defined as a database object that contains the results of a particular query. It could be called as a subset of a table. Any changes in the base table would be reflected in the materialised view.

Syntax

CREATE MATERIALISED VIEW view_name
AS SELECT * FROM table_name 
WHERE column_name IS NOT NULL
PRIMARY KEY (provide_primary_key)

Here,

• view_name is the name of the materialised view,

• table_name is the base table,

• column_name are the columns

• provide_primary_key are the primary keys for the base table.

Example 1

In this example, we are gonna have a look on how to create a materialised view for a table.

Algorithm

• Step 1 − Create a keyspace

• Step 2 − Create table within the keyspace

• Step 3 − Create materialised view within the keyspace

• Step 4 − Select data from table the table

• Step 5 − Verify the values to be not null

• Step 6 − Provide primary key.

• Step 7 − Values inserted into the table

• Step 8 − Select values from table to get the desired output.

Code

CREATE KEYSPACE position WITH REPLICATION= {'class': 'simplestrategy','replication_factor':1}; #keyspace is created 
CREATE TABLE position.employee
(e_id int,e_name char(50),dept char(50),salary int)
#table created inside keyspace
CREATE MATERIALISED VIEW position.cell #materialised view named cell is created
AS SELECT e_id,e_name,dept,salary FROM employee #data is selected 
WHERE e_id IS NOT NULL AND e_name IS NOT NULL #verifying that primary  key values must not be null
PRIMARY KEY (e_id);#primary key provided
INSERT INTO employee values(1,'rahul','developer',40000),
(2,'monu','hr',50000),
(3,'aman','consulting',60000);#data is inserted
SELECT * FROM cell;

Output

e_id	e_name	dept	salary
1	rahul	developer	40000
2	monu	hr	50000
3	aman	consulting	60000

Example 2

In this example, we are going to see the effect of the primary key as to create a valid materialised view.

Here, we are going to create a table first with the primary key known.

Input TABLE

PLANT

P_name	fruit	taste
mango_tree	mango	sweet
lemon_tree	lemon	sour
apple_tree	apple	sweet

P_name = primary key

Case 1 − Materialised view allowed

Algorithm

• Step 1 − Create materialised view

• Step 2 − Select value from base table

• Step 3 − Verify the values to be not null

• Step 4 − Provide primary key

• Step 5 − Select values for materialised view

Code

CREATE MATERIALISED VIEW nature AS #materialised view created
SELECT * FROM plant #value selected
WHERE p_name IS NOT NULL #condition check for not null
PRIMARY KEY (p_name);# primary key provided
SELECT * FROM nature;

Output

Nature

P_name	fruit	taste
mango_tree	mango	sweet
lemon_tree	lemon	sour
apple_tree	apple	sweet

Case 2 − Materialised view not allowed −

Algorithm

• Step 1 − Create materialised view

• Step 2 − Select value from base table

• Step 3 − Verify the values to be not null

• Step 4 − Provide primary key

Code

CREATE MATERIALISED VIEW nature AS#materialised view created
SELECT * FROM plant#value selected 
WHERE p_name IS NOT NULL AND fruit IS NOT NULL  #condition check for not null
PRIMARY KEY (fruit);# primary key provided

Output

Invalid Primary key 

Conclusion

Creating materialised view is as making a subset of a table so as to contain the result of a particular query. In first example, we have created materialised view by creating keyspace whereas in second example, we have focused on creating a valid materialised view by having two cases in which case 1 having the materialised view allowed and case 2 having it not allowed and as a result also found the importance of primary key