Creating Materialized View using Table definition in Cassandra

In this article, we are going to discuss how we can create a materialized view based on a particular problem and also cover the example for the same. Let’s discuss one by one.

Pre-requisite – Materialized view 

In this, you will see how you can create the materialized view based on table definition. Let’s understand this by using the following steps given below.

Creating the table definition :

Now, to understand the scenarios consider an example where cricket_team is a table name and team_name, team_match_wins, team_match_losses, and team_match_ties are the fields.

CREATE TABLE cricket_team
(
team_name text primary key,
team_match_wins int,
team_match_losses int,
team_match_ties int
);

Inserting data into the table :

Now, you will see the CQL query to insert some rows for the above-created table.

Insert into cricket_team( team_name, team_match_wins, team_match_losses, team_match_ties) 
values ('IN' , 100, 5, 5); 
Insert into cricket_team( team_name, team_match_wins, team_match_losses, team_match_ties) 
values ('NZ' , 80, 5, 2); 
Insert into cricket_team( team_name, team_match_wins, team_match_losses, team_match_ties) 
values ('AUS' , 80, 4, 5); 
Insert into cricket_team( team_name, team_match_wins, team_match_losses, team_match_ties) 
values ('PAK' , 70, 8, 5); 
Insert into cricket_team( team_name, team_match_wins, team_match_losses, team_match_ties) 
values ('ENG' , 60, 9, 5);

Verifying data :

Now, to verify the above-inserted data you can use the following CQL query.

 select * from cricket_team;

Output :

team_name	team_match_losses	team_match_ties	team_match_wins
PAK	8	5	70
IN	5	5	100
AUS	4	5	80
NZ	5	2	80
ENG	9	5	60

Example –

Now, if you want to query like as follows.

select * cricket_team_by_team_match_wins where team_match_wins = 100;

Note –

Primary key fields can not be NULL the WHERE clause must include a NULL check. Since the WHERE clause in the SELECT is based on team_match_wins, team_match_wins must be the partition key.

Creating Materialized view :

Now, if you will execute this query you will get an error because there is no table with the name cricket_team_by_team_match_wins. So, first, you can create a materialized view by using the following CQL query.

create materialized view if not exists
cricket_team_by_team_match_wins As
select * from cricket_team
where team_match_wins IS NOT NULL AND team_name IS NOT NULL
primary key((team_match_wins),team_name);

Executing the Query :

Now, if you will execute the given below query then it will be executed successfully.

select * cricket_team_by_team_match_wins where team_match_wins = 100;

Output :

team_match_wins	team_name	team_match_losses	team_match_ties
100	IN	5	5

My Personal Notes arrow_drop_up

Save