Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

Creating Materialized View using Table definition in Cassandra

  • Last Updated : 21 Jan, 2021

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-requisiteMaterialized view 

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

 

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_nameteam_match_lossesteam_match_tiesteam_match_wins
PAK8570
IN55100
AUS4580
NZ5280
ENG9560

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_winsteam_nameteam_match_lossesteam_match_ties
100IN55
My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!