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