CQL Example

cql_example.md

-- CQLSH

-- creating keyspace
CREATE KEYSPACE dev_db WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '1'} AND durable_writes = 'true';

-- consistency
CONSISTENCY; -- Current consistency level is LOCAL_QUORUM.
CONSISTENCY ONE; -- OK for one node
CONSISTENCY QUORUM; -- Best for multiple nodes at high replication factor of ~3
CONSISTENCY LOCAL_ONE;
CONSISTENCY LOCAL_QUORUM;

-- creating table
CREATE TABLE IF NOT EXISTS users (
  id INT PRIMARY KEY, -- or UUID
  name TEXT,
  address TEXT,
  username TEXT,
  email TEXT,
  country TEXT,
  city TEXT
);
// Primary key (Composite key) = Partition key + clustering columns (used to sort columns)
// Partition Key - Determines which node to store the data on
// Clustering Column - How the data is sorted within that node
// red - partition key
// blue - clustering column
// purple - regular column data

CREATE TABLE employee_by_id (id int PRIMARY KEY, name text, position text);
CREATE TABLE employee_by_car_make (car_make text, id int, car_model text, PRIMARY KEY(car_make, id)); -- id clustering col
CREATE TABLE employee_by_car_make_sorted (car_make text, age int, id int, name text, PRIMARY KEY(car_make, age, id)); -- two clustering cols
CREATE TABLE employee_by_car_make_and_model (car_make text, car_model text, id int, name text, PRIMARY KEY((car_make, car_model), id)); -- creates 2 partition keys. For when all users have 1 car_make like BMW, partition data more

-- using
USE dev_db;

-- viewing
DESCRIBE KEYSPACES;
DESC TABLES;
DESC users;

-- selecting
SELECT * FROM users;
SELECT id, address, city, WRITETIME(country) FROM users;

-- inserting
INSERT INTO users (id, name, address, username, email, country, city) VALUES (1, 'John Doe', '123 Main St', 'johndoe123', '[email protected]', 'USA', 'New York');
INSERT INTO users (id, name, address, username, email, country, city) VALUES (2, 'Jane Smith', '456 Elm St', 'janesmith456', '[email protected]', 'USA', 'Los Angeles');

-- updating
UPDATE users SET name='Sally Smith' WHERE name='John Doe' AND id=1;

-- TTL (time to live)
-- Good for validation tokens
UPDATE users USING TTL 20 SET name='Billy Bob' WHERE id=1; -- becomes null after 20 seconds, not reverted

-- removing
TRUNCATE users; - removes all data in the table

-- deleting
DROP TABLE users;
DROP KEYSPACE dev_db;

-- sets
ALTER TABLE users ADD phone set<text>;
UPDATE users SET phone = {'343', '565'} WHERE id=1;
UPDATE users SET phone = phone + {'555'} WHERE id=1; -- Adds to the front
UPDATE users SET phone = phone - {'555'} WHERE id=1; -- Removes value
UPDATE users SET phone = {} WHERE id=1; -- remove all. Sets to null
-- lists
ALTER TABLE users ADD passwords list<text>;

-- filtering
SELECT * FROM users WHERE name='Jane Smith'; -- does not work
SELECT * FROM users WHERE name='Jane Smith' ALLOW FILTERING; -- works but not recommended. Use secondary index instead
-- secondary index
CREATE INDEX ON users (name); -- not recommended but useful if schema not created well

-- uuids
CREATE TABLE employee_by_uuid (id uuid PRIMARY KEY, first_name text);
INSERT INTO employee_by_uuid (id, first_name, last_name) VALUES (uuid(), 'Tom', 'Dunne');

-- timeuuids
// Used to sort data chronologically
CREATE TABLE employee_by_timeuuid (id timeuuid PRIMARY KEY, first_name text); -- Have random+time component 
INSERT INTO employee_by_timeuuid (id, first_name, last_name) VALUES (now(), 'Tom', 'Dunne');

-- counters
CREATE TABLE purchases_by_customer_id (id uuid PRIMARY KEY, purchases counter);
UPDATE purchases_by_customer_id SET purchases = purchases+1 WHERE id=uuid(); -- Cannot insert

-- importing
// Use batch import if importing more than 2M rows
// Need to access CQLSH locally to import csv files
CREATE TABLE test_csv_import (car_make text, car_model text, start_year int, id int, first_name text, last_name text, department text, PRIMARY KEY(car_make, car_model, start_year, id));

COPY test_csv_import (car_make, car_model, start_year, id, first_name, last_name, department) FROM '/home/downloads/filename.csv' WITH DELIMITER=',' AND HEADER=TRUE;

-- exporting
COPY test_csv_import TO '/home/downloads/exported.csv' WITH DELIMITER=',';
COPY test_csv_import (car_make, department, first_name) TO '/home/downloads/exported.csv' WITH DELIMITER=',';