Create Keyspace

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 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)