CQL (Cassandra Query Language)

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.