DESCRIBE CLUSTER;
DESCRIBE KEYSPACES;
SHOW VERSION;
USE ${keyspace};
DESCRIBE TABLES;
Creating a Keyspace and Table
A Cassandra keyspace is sort of like a relational database.
Type the following and then press Tab.
CREATE KEYSPACE ${keyspace.name} WITH
When you press Tab, cqlsh begins completing the syntax of your command.
CREATE KEYSPACE ${keyspace.name} WITH replication = {'class': '
This is informing you that in order to specify a keyspace, you also need to specify a replication strategy. Tab again to see what options you have:
CREATE KEYSPACE ${keyspace.name} WITH replication = {'class': '
NetworkTopologyStrategy OldNetworkTopologyStrategy SimpleStrategy
Type the one you select and Tab again.
CREATE KEYSPACE ${keyspace.name} WITH replication = {'class': 'SimpleStrategy', 'replication_factor':
The next option you're presented with is a replication factor. For the simple strategy, this indicates how many nodes the data in this keyspace will be written to. For a production deployment, you'll want copies of your data stored on multiple nodes, but because you're just running a single node at the moment, you'll ask for a single copy. Specify a value of 1 and a space and Tab again.
CREATE KEYSPACE ${keyspace.name} WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 1};
Check your keyspace.
DESCRIBE KEYSPACE ${keyspace.name};
Use keyspace.
USE ${keyspace.name};
Create table
CREATE TABLE user (first_name text, last_name text, title text, primary key (last_name, first_name));
The text
and varchar
types are synonymous and are used to store strings.
Check your table
DESCRIBE TABLE user;
Write data
INSERT INTO user (first_name, last_name, title) VALUES ('Alex', 'Won', 'Developer');
Read data
SELECT * FROM user WHERE first_name = 'Alex' AND last_name = 'Won';
last_name | first_name | title
-----------+------------+-----------
Won | Alex | Developer
(1 rows)
In this command, you requested to return rows matching the primary key including all columns. For this query, you specified both of the columns referenced by the primary key. What happens when you only specify one of the values?
SELECT * FROM user WHERE first_name = 'Alex';
last_name | first_name | title
-----------+------------+-----------
Won | Alex | Developer
(1 rows)
SELECT * FROM user WHERE last_name = 'Won';
InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING"
Counting data and full table scans
SELECT COUNT (*) FROM user WHERE last_name = 'Won';
Warnings :
Aggregation query used without partition key
Note that when you execute this command, cqlsh
gives you the correct count of rows, but also gives you a warning. This is because you've asked Cassandra to perform a full table scan. In a multi-node cluster with potentially large amounts of data, this COUNT
could be a very expensive operation.
Delete column
DELETE title FROM user WHERE first_name = 'Alex' AND last_name = 'Won';
SELECT * FROM user WHERE last_name = 'Won';
last_name | first_name | title
-----------+------------+-------
Won | Alex | null
(1 rows)
You can perform this query because title
column is not part of the primary key.
Delete row
DELETE FROM user WHERE first_name = 'Alex' AND last_name = 'Won';
SELECT * FROM user WHERE last_name = 'Won';
last_name | first_name | title
-----------+------------+-------
(0 rows)
Delete every row
TRUNCATE <table.name>;
Delete table
DROP TABLE <table.name>;
Add column
ALTER TABLE user ADD middle_initial text;
View timestamp → writetime()
function
SELECT first_name, last_name, title, writetime(title) FROM user;
first_name | last_name | title | writetime(title)
------------+-----------+-----------+------------------
Alex | Won | Developer | 1617085464654323
Bob | Kim | null | null
(1 rows)
There is no timestamp for a column that has not been set.
SELECT writetime(first_name) FROM user;
InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot use selection function writeTime on PRIMARY KEY part first_name"
It is not allowed to ask for the timestamp on primary key columns.
Time to live (TTL) → ttl()
function
SELECT TTL(title) FROM user;
ttl(title)
------------
null
(1 rows)
The TTL value defaults to null
, meaning that data that is written will not expire.