# Show Server Details
@cqlsh> show host
root172........:xxxx
Connected to Test Cluster at
@cqlsh> show version
root6.0.0 | Cassandra 5.0-beta1 | CQL spec 3.4.7 | Native protocol v5]
[cqlsh
# Show All Keyspaces
@cqlsh> describe keyspaces
root
system system_distributed system_traces system_virtual_schema
system_auth system_schema system_views
# Clear screen
@cqlsh> cls
root
# Disconnect from server
@cqlsh> exit root
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
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
@cqlsh> CREATE KEYSPACE training
root= {'class':'SimpleStrategy', 'replication_factor' : 3};
WITH replication
Warnings :3 for keyspace training is higher than the number of nodes 1
Your replication factor
# Show all keyspaces
@cqlsh> describe keyspaces
root
system system_distributed system_traces system_virtual_schema
system_auth system_schema system_views training
# Show all the details regarding the keyspace
@cqlsh> describe training
root
= {'class': 'SimpleStrategy', 'replication_factor': '3'} AND durable_writes = true;
CREATE KEYSPACE training WITH replication
# Alter keyspace using NetworkTopologyStrategy
@cqlsh> ALTER KEYSPACE training
root= {'class': 'NetworkTopologyStrategy'};
WITH replication
Warnings :3 for keyspace training is higher than the number of nodes 1 for datacenter datacenter1
Your replication factor
# Verify the alteration
@cqlsh> describe training
root
= {'class': 'NetworkTopologyStrategy', 'datacenter1': '3'} AND durable_writes = true;
CREATE KEYSPACE training WITH replication
# Let's connect to the keyspace and list all the tables which should be none
@cqlsh> use training;
root@cqlsh:training> describe tables
root
# Drop Keyspace
@cqlsh:training> drop keyspace training;
root
# Verify keyspace is dropped from system
@cqlsh:training> use system;
root@cqlsh:system> describe keyspaces
root
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 = {'class':'SimpleStrategy', 'replication_factor' : 3};
WITH replication
# Create Table
@cqlsh> use training;
root@cqlsh:training> CREATE TABLE movies(
rootint PRIMARY KEY,
... movie_id
... movie_name text,int
... year_of_release ;
... )
# Verify the table was created
@cqlsh:training> describe tables;
root
movies
# Show table details
@cqlsh:training> describe movies
root
CREATE TABLE training.movies (int PRIMARY KEY,
movie_id
movie_name text,int
year_of_release = '99p'
) WITH additional_write_policy = true
AND allow_auto_snapshot = 0.01
AND bloom_filter_fp_chance = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
AND caching = false
AND cdc = ''
AND comment = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
AND compaction = {'chunk_length_in_kb': '16', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
AND compression = 'default'
AND memtable = 1.0
AND crc_check_chance = 0
AND default_time_to_live = {}
AND extensions = 864000
AND gc_grace_seconds = true
AND incremental_backups = 2048
AND max_index_interval = 0
AND memtable_flush_period_in_ms = 128
AND min_index_interval = 'BLOCKING'
AND read_repair = '99p';
AND speculative_retry
# Alter Table by adding genre column with type text
@cqlsh:training> ALTER TABLE movies
root;
ADD genre text
# Verify alteration
@cqlsh:training> describe movies;
root
CREATE TABLE training.movies (int PRIMARY KEY,
movie_id
genre text,
movie_name text,int
year_of_release = '99p'
) WITH additional_write_policy = true
AND allow_auto_snapshot = 0.01
AND bloom_filter_fp_chance = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
AND caching = false
AND cdc = ''
AND comment = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
AND compaction = {'chunk_length_in_kb': '16', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
AND compression = 'default'
AND memtable = 1.0
AND crc_check_chance = 0
AND default_time_to_live = {}
AND extensions = 864000
AND gc_grace_seconds = true
AND incremental_backups = 2048
AND max_index_interval = 0
AND memtable_flush_period_in_ms = 128
AND min_index_interval = 'BLOCKING'
AND read_repair = '99p';
AND speculative_retry
# Drop Table & Verify
@cqlsh:training> drop table movies;
root@cqlsh:training> describe movies;
root'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 = {'class':'SimpleStrategy', 'replication_factor' : 3};
WITH replication
# Create Table
@cqlsh> use training;
root@cqlsh:training> CREATE TABLE movies(
rootint PRIMARY KEY,
... movie_id
... movie_name text,int
... year_of_release ;
... )
# Verify the table was created
@cqlsh:training> describe tables;
root
movies
# Insert a row into the table
@cqlsh:training> INSERT into movies(
root
movie_id, movie_name, year_of_release)1,'Toy Story',1995);
VALUES (
# View data in table
* FROM movies;
SELECT
# OUTPUT
| movie_name | year_of_release
movie_id ----------+------------+-----------------
1 | Toy Story | 1995
# Insert additional Data as shown in instructions
@cqlsh:training> INSERT into movies(
root
movie_id, movie_name, year_of_release)2,'Jumanji',1995);
VALUES (@cqlsh:training> INSERT into movies(
root
movie_id, movie_name, year_of_release)3,'Heat',1995);
VALUES (@cqlsh:training> INSERT into movies(
root
movie_id, movie_name, year_of_release)4,'Scream',1995);
VALUES (@cqlsh:training> INSERT into movies(
root
movie_id, movie_name, year_of_release)5,'Fargo',1996);
VALUES (
# View data in table
* FROM movies;
SELECT # OUTPUT
| movie_name | year_of_release
movie_id ----------+------------+-----------------
5 | Fargo | 1996
1 | Toy Story | 1995
2 | Jumanji | 1995
4 | Scream | 1995
3 | Heat | 1995
5 rows)
(
# Query Data
@cqlsh:training> select movie_name from movies where movie_id = 1;
root
movie_name------------
Toy Story
1 rows)
(
# UPDATE table data
@cqlsh:training> UPDATE movies
root= 1996
SET year_of_release = 4;
WHERE movie_id
# Verify Update
@cqlsh:training> select * from movies where movie_id = 4;
root
# OUTPUT
| movie_name | year_of_release
movie_id ----------+------------+-----------------
4 | Scream | 1996
1 rows)
(
# Delete one movie from table
@cqlsh:training> DELETE from movies
root= 5;
... WHERE movie_id
# Verify data has been removed
@cqlsh:training> select * from movies;
root
# OUTPUT
| movie_name | year_of_release
movie_id ----------+------------+-----------------
1 | Toy Story | 1995
2 | Jumanji | 1995
4 | Scream | 1996
3 | Heat | 1995
4 rows) (