Useful CQL query in Cassandra

Prerequisite – Cassandra
In this article, we are going to discuss CQL query which is very useful to create, insert, delete, manipulate, filtering data etc. Let’s discuss how we can write the better CQL query for these operations.

Cassandra Query Language (CQL):
CQL is used to create, insert, manipulate data in Cassandra. The main thing to note about CQL is that it has no concept of GROUP or JOIN, and a very limited implementation of ORDER BY.

Creating a keyspace

//keyspace with SimpleStrategy

create keyspace cluster1    
WITH replication = {'class': 'SimpleStrategy', 
                    'replication_factor' : 3};

//keyspace with NetworkTopologyStrategy

create keyspace cluster1
WITH replication = {'class': 'NetworkTopologyStrategy', 
                    'west' : 1, 'east' : 3}
AND durable_writes = false; 

Now, here we are going to create a table web_info in which url, user_id, net_location, visitor_info are the fields of table.
Let’s have a look.

Creating a table:

CREATE TABLE web_info (
  url text,
  user_id uuid,
  net_location inet,
  visitor_info text,
  PRIMARY KEY (url, net_location)
) ; 

Inserting rows in table:
Now, we are going to insert some data into table.

Insert into web_info(url, user_id, net_location, visitor_info)
values('https://www.google.com', uuid(), '127.0.0.1', 'Ashish');   
 
Insert into web_info(url, user_id, net_location, visitor_info)
values('https://www.google.com', uuid(), '127.0.0.4', 'shivang');    
 
Insert into web_info(url, user_id, net_location, visitor_info)
values('https://www.yahoo.com', uuid(), '127.0.0.2', 'rana');

Insert into web_info(url, user_id, net_location, visitor_info)
values('https://www.geeksforgeeks.com', uuid(), '127.0.0.3', 'Ashish_rana'); 

Now, to verify the results using LIMIT option for specific number of rows.

SELECT * 
FROM web_info LIMIT 3; 

Output:

ALLOW FILTERING:
This is option is very useful in CQL which has the ability for the server to actively filter out the results.

usage of SELECT with ALLOW FILTERING:

SELECT *
FROM web_info
WHERE url='https://www.google.com'
AND net_location='127.0.0.1' AND visitor_info = 'Ashish'
ALLOW FILTERING; 

Output:

This example shows how ALLOW FILTERING works in CQL query for non partitioning columns.
Let’s have a look.

SELECT *
FROM web_info
WHERE visitor_info = 'Ashish'
ALLOW FILTERING; 

Output:

To verify the results of inserted data into the web_info table used the following CQL given below.

SELECT * 
FROM web_info; 

Output:

Here, we are just going to show how we BATCH CQL query is useful in Cassandra. In below given example we are just showing that how we can update, delete and inserted data into the table using BATCH. Let’s have a look.

BEGIN BATCH
UPDATE web_info USING TTL 345600
SET visitor_info = 'Ashish rana'
WHERE url='https://www.google.com' and net_location = '127.0.0.1';


Delete visitor_info 
FROM web_info 
where url='https://www.google.com' and net_location = '127.0.0.1';
    
    
Insert into web_info(url, user_id, net_location, visitor_info)
values('https://www.geeksforgeeks.com', uuid(), '127.0.0.5', 'new_user');
APPLY BATCH; 

Now, to verify the BATCH results used the following CQL query given below.

select * 
from web_info; 

Output:

To set the visitor_info column used the following CQL query given below.

UPDATE web_info 
SET visitor_info = 'login at academy portal'
WHERE url='https://www.google.com' and net_location = '127.0.0.1'; 

Now, to verify the results used the following CQL query given below.

select * 
from web_info; 

Output: