CQL How To Basics

Create Instance


  • Click on Open Cassandra Page in IDE
  • Open Cassandra CLI

Show Details of Server

  • show host
  • show version

Show All Keyspaces

  • describe keyspaces

Clear Screen

  • cls

Disconnect from Server

  • exit
# Show Server Details
root@cqlsh> show host
Connected to Test Cluster at 172........:xxxx

root@cqlsh> show version
[cqlsh 6.0.0 | Cassandra 5.0-beta1 | CQL spec 3.4.7 | Native protocol v5]


# Show All Keyspaces
root@cqlsh> describe keyspaces

system       system_distributed  system_traces  system_virtual_schema
system_auth  system_schema       system_views 

# Clear screen
root@cqlsh> cls

# Disconnect from server
root@cqlsh> exit

Create Keyspace


  • Create Instance
  • Open CLI
  • Create keyspace called training
  • Simple strategy
  • Replication factor of 3
  • CREATE KEYSPACE training WITH replication = {'class':'SimpleStrategy', 'replication_factor' : 3};

Show All Keyspaces

  • describe keyspaces

Describe Keyspace

  • Let’s show more details about the keyspace
  • describe training

Alter Keyspace

  • Above we created a keyspace named training using SimpleStrategy
  • Let’s change that to use NetworkTopologyStrategy
  • NetworkTopologyStrategy is used when all the nodes in your cassandra cluster are spread across multiple data centers
  • ALTER KEYSPACE training WITH replication = {'class': 'NetworkTopologyStrategy'};

Describe Kesypace

  • Let’s verify the alteration with
  • describe training

Use a Keyspace

  • use training;

List All Tables

  • describe tables

Drop Keyspace

  • drop keyspace training;

Verify Dropped Keyspace

  • Verify the system no longer contain the dropped keyspace
  • use system; describe keyspaces
# Create Keyspace
root@cqlsh> CREATE KEYSPACE training  
            WITH replication = {'class':'SimpleStrategy', 'replication_factor' : 3};

Warnings :
Your replication factor 3 for keyspace training is higher than the number of nodes 1

# Show all keyspaces
root@cqlsh> describe keyspaces

system       system_distributed  system_traces  system_virtual_schema
system_auth  system_schema       system_views   training 

# Show all the details regarding the keyspace
root@cqlsh> describe training

CREATE KEYSPACE training WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '3'}  AND durable_writes = true;

# Alter keyspace using NetworkTopologyStrategy
root@cqlsh> ALTER KEYSPACE training
            WITH replication = {'class': 'NetworkTopologyStrategy'};

Warnings :
Your replication factor 3 for keyspace training is higher than the number of nodes 1 for datacenter datacenter1

# Verify the alteration
root@cqlsh> describe training

CREATE KEYSPACE training WITH replication = {'class': 'NetworkTopologyStrategy', 'datacenter1': '3'}  AND durable_writes = true;

# Let's connect to the keyspace and list all the tables which should be none
root@cqlsh> use training;
root@cqlsh:training> describe tables

# Drop Keyspace
root@cqlsh:training> drop keyspace training;

# Verify keyspace is dropped from system
root@cqlsh:training> use system;
root@cqlsh:system> describe keyspaces

system       system_distributed  system_traces  system_virtual_schema
system_auth  system_schema       system_views

Create Table


Create Instance

  • Create Instance
  • Open CLI
  • Create keyspace called training
  • Simple strategy
  • Replication factor of 3
  • CREATE KEYSPACE training WITH replication = {'class':'SimpleStrategy', 'replication_factor' : 3};

Create Table

  • Using training
  • Create a table named movies, in the training keyspace
  • The movies table has three columns:
    • ‘movie_id’ is an integer and is the primary key.

    • ‘movie_name’ is a text column.

    • ‘year_of_release’ is an integer

Verify Table

  • describe tables;

Show Details of Table

  • describe movies

ALTER Table

  • Let’s add a column to existing movies table
  • Add a column named genre which is of type text
  • ALTER TABLE movies ADD genre text;

View Table

  • describe movies;

DROP Table

  • drop table movies;
  • verify system: describe movies;
# Create keyspace - simplestrategy - replication factor of 3
CREATE KEYSPACE training  
WITH replication = {'class':'SimpleStrategy', 'replication_factor' : 3};

# Create Table
root@cqlsh> use training;
root@cqlsh:training> CREATE TABLE movies(
            ... movie_id int PRIMARY KEY,
            ... movie_name text,
            ... year_of_release int
            ... );
            
# Verify the table was created
root@cqlsh:training> describe tables;

movies

# Show table details
root@cqlsh:training> describe movies

CREATE TABLE training.movies (
    movie_id int PRIMARY KEY,
    movie_name text,
    year_of_release int
) WITH additional_write_policy = '99p'
    AND allow_auto_snapshot = true
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND cdc = false
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '16', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND memtable = 'default'
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND extensions = {}
    AND gc_grace_seconds = 864000
    AND incremental_backups = true
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99p';
    
# Alter Table by adding genre column with type text
root@cqlsh:training> ALTER TABLE movies
                     ADD genre text;
                     

# Verify alteration                     
root@cqlsh:training> describe movies;

CREATE TABLE training.movies (
    movie_id int PRIMARY KEY,
    genre text,
    movie_name text,
    year_of_release int
) WITH additional_write_policy = '99p'
    AND allow_auto_snapshot = true
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND cdc = false
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '16', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND memtable = 'default'
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND extensions = {}
    AND gc_grace_seconds = 864000
    AND incremental_backups = true
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99p';
    
    
# Drop Table & Verify 
root@cqlsh:training> drop table movies;
root@cqlsh:training> describe movies;
'movies' not found in keyspace 'training'

CRUD


Create Instance

  • Create a keyspace named training using SimpleStrategy and replication factor of 3

Create Table

  • Using training
  • Create a table named movies, in the training keyspace
  • The movies table has three columns:
    • ‘movie_id’ is an integer and is the primary key.
    • ‘movie_name’ is a text column.
    • ‘year_of_release’ is an integer

Insert Data

  • Insert a row into the table

Verify Data is saved

  • view data in table using CQL
  • select * from movies;

Insert Additional Data

movie_id movie_name year_of_release
2 Jumanji 1995
3 Heat 1995
4 Scream 1995
5 Fargo 1996

View Data

  • SELECT * FROM movies;
movie_id movie_name year_of_release
5 Fargo 1996
1 Toy Story 1995
2 Jumanji 1995
4 Scream 1995
3 Heat 1995

(5 rows)

Query Data

  • view the movie with movie_id=1
  • select movie_name from movies where movie_id = 1;

UPDATE Table

  • Let’s update movie_id for Scream to be 4
  • It was released in 1996 and not 1995
  • UPDATE movies SET year_of_release = 1996 WHERE movie_id = 4;

Verify Update

  • select * from movies where movie_id = 4;

DELETE Data

  • Let’s delete one item movie_id=5 from the table
  • DELETE from movies WHERE movie_id = 5;
# Create keyspace - simplestrategy - replication factor of 3
CREATE KEYSPACE training  
WITH replication = {'class':'SimpleStrategy', 'replication_factor' : 3};

# Create Table
root@cqlsh> use training;
root@cqlsh:training> CREATE TABLE movies(
            ... movie_id int PRIMARY KEY,
            ... movie_name text,
            ... year_of_release int
            ... );
            
# Verify the table was created
root@cqlsh:training> describe tables;

movies

# Insert a row into the table
root@cqlsh:training> INSERT into movies(
                            movie_id, movie_name, year_of_release)
                            VALUES (1,'Toy Story',1995);
                            
# View data in table
SELECT * FROM movies;

# OUTPUT
 movie_id | movie_name | year_of_release
----------+------------+-----------------
        1 |  Toy Story |            1995
        
# Insert additional Data as shown in instructions
root@cqlsh:training> INSERT into movies(
                            movie_id, movie_name, year_of_release)
                            VALUES (2,'Jumanji',1995);
root@cqlsh:training> INSERT into movies(
                            movie_id, movie_name, year_of_release)
                            VALUES (3,'Heat',1995);
root@cqlsh:training> INSERT into movies(
                            movie_id, movie_name, year_of_release)
                            VALUES (4,'Scream',1995);
root@cqlsh:training> INSERT into movies(
                            movie_id, movie_name, year_of_release)
                            VALUES (5,'Fargo',1996);
                            
# View data in table
SELECT * FROM movies;
# OUTPUT
 movie_id | movie_name | year_of_release
----------+------------+-----------------
        5 |      Fargo |            1996
        1 |  Toy Story |            1995
        2 |    Jumanji |            1995
        4 |     Scream |            1995
        3 |       Heat |            1995

(5 rows)

# Query Data
root@cqlsh:training> select movie_name from movies where movie_id = 1;

 movie_name
------------
  Toy Story

(1 rows)

# UPDATE table data
root@cqlsh:training> UPDATE movies
                     SET year_of_release = 1996
                     WHERE movie_id = 4;
                     
# Verify Update
root@cqlsh:training> select * from movies where movie_id = 4;

# OUTPUT
 movie_id | movie_name | year_of_release
----------+------------+-----------------
        4 |     Scream |            1996

(1 rows)

# Delete one movie from table
root@cqlsh:training> DELETE from movies
            ... WHERE movie_id = 5;
            
# Verify data has been removed
root@cqlsh:training> select * from movies;

# OUTPUT
 movie_id | movie_name | year_of_release
----------+------------+-----------------
        1 |  Toy Story |            1995
        2 |    Jumanji |            1995
        4 |     Scream |            1996
        3 |       Heat |            1995

(4 rows)