Introduction to CQL / cqlsh

cqlsh_intro.cql

#
# cqlsh_intro.cql
#
# Copyright (C) 2017 Jeff Carpenter
# Execute the commands in this file for a short guided tour of the CQL Shell (cqlsh)
#
# For more description, see Cassandra, The Definitive Guide 2nd Ed., Chapter 3: Installing
# http://shop.oreilly.com/product/0636920043041.do
#

# To get help for cqlsh, type HELP or ? to see the list of available commands:
HELP

# To learn about the current cluster you’re working in, type:
DESCRIBE CLUSTER;

# To see which keyspaces are available in the cluster, issue the command below.
# What are these keyspaces for?
DESCRIBE KEYSPACES;

# Learn the client, server, and protocol versions in use
SHOW VERSION;

# View the default paging settings that will be used on reads
PAGING;

# View the default consistency level that will be used on all queries
CONSISTENCY;

# View the default tracing options
TRACING;

# Create your own keyspace. Try using tab completion as you enter this command
CREATE KEYSPACE my_keyspace WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 1};

# Describe the keyspace you just created.
# What additional information do you notice?
DESCRIBE KEYSPACE my_keyspace;

# Use the keyspace so you don't have to enter it on every data manipulation
# Note how the prompt changes after you do this
USE my_keyspace;

# Create a simple table
# What other syntax could you use to designate a single column primary key?
CREATE TABLE user ( first_name text, last_name text, PRIMARY KEY (first_name));

# Describe the table you just created
# What additional information do you notice?
DESCRIBE TABLE user;

# Write some data
INSERT INTO user (first_name, last_name) VALUES ('Bill', 'Nguyen');

# See how many rows have been written into this table
# Warning - row scans are expensive operations on large tables
SELECT COUNT (*) FROM user;

# Read the data we just wrote
SELECT * FROM user WHERE first_name='Bill';

# Remove a non-primary key column
DELETE last_name FROM USER WHERE first_name='Bill';

# Check to see the value was removed
SELECT * FROM user WHERE first_name='Bill';

# Delete an entire row
DELETE FROM USER WHERE first_name='Bill';

# Check to make sure it was removed
SELECT * FROM user WHERE first_name='Bill';

# Add a column to the table
ALTER TABLE user ADD title text;

# Check to see that the column was added
DESCRIBE TABLE user;

# Write a couple of rows, populate different columns for each, and view the results:
INSERT INTO user (first_name, last_name, title) VALUES ('Bill', 'Nguyen', 'Mr.');
INSERT INTO user (first_name, last_name) VALUES ('Mary', 'Rodriguez');
SELECT * FROM user;

# View the timestamps generated for previous writes
SELECT first_name, last_name, writetime(last_name) FROM user;

# Note that we’re not allowed to ask for the timestamp on primary key columns:
SELECT WRITETIME(first_name) FROM user;

# Set the timestamp on a write
# Note, you will probably want to change this value to be closer to your current time
#   (similar to timestamp from previous set)
UPDATE user USING TIMESTAMP 1434373756626000 SET last_name = 'Boateng' WHERE first_name = 'Mary' ;

# Verify the timestamp used
SELECT first_name, last_name, WRITETIME(last_name) FROM user WHERE first_name = 'Mary';

# View the time to live value for a column
SELECT first_name, last_name, TTL(last_name) FROM user WHERE first_name = 'Mary';

# Set the TTL on the  last name column to one hour
UPDATE user USING TTL 3600 SET last_name = 'McDonald' WHERE first_name = 'Mary' ;

# View the resulting TTL
# Note that it will already be counting down
SELECT first_name, last_name, TTL(last_name) FROM user WHERE first_name = 'Mary';

# Empty the contents of the table
TRUNCATE user;

# Show that the table is empty
SELECT * FROM user;

# Remove the entire table
DROP TABLE user;

# Clear the screen of output from previous commands
CLEAR

# Exit cqlsh
EXIT

hotel.cql

#
# hotel.cql
#
# Copyright (C) 2017-2019 Jeff Carpenter
# This file contains the "hotel" keyspace and table definitions for the example
# defined in Chapter 5 of Cassandra: The Definitive Guide, 2nd Edition
#

CREATE KEYSPACE hotel
    WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 3};

CREATE TYPE hotel.address (
    street text,
    city text,
    state_or_province text,
    postal_code text,
    country text
);

CREATE TABLE hotel.hotels_by_poi (
    poi_name text,
    hotel_id text,
    name text,
    phone text,
    address frozen<address>,
    PRIMARY KEY ((poi_name), hotel_id)
) WITH comment = 'Q1. Find hotels near given poi'
AND CLUSTERING ORDER BY (hotel_id ASC) ;

CREATE TABLE hotel.hotels (
    id text PRIMARY KEY,
    name text,
    phone text,
    address frozen<address>,
    pois set<text>
) WITH comment = 'Q2. Find information about a hotel';

CREATE TABLE hotel.pois_by_hotel (
    poi_name text,
    hotel_id text,
    description text,
    PRIMARY KEY ((hotel_id), poi_name)
) WITH comment = 'Q3. Find pois near a hotel';

CREATE TABLE hotel.available_rooms_by_hotel_date (
    hotel_id text,
    date date,
    room_number smallint,
    is_available boolean,
    PRIMARY KEY ((hotel_id), date, room_number)
) WITH comment = 'Q4. Find available rooms by hotel / date';

CREATE TABLE hotel.amenities_by_room (
    hotel_id text,
    room_number smallint,
    amenity_name text,
    description text,
    PRIMARY KEY ((hotel_id, room_number), amenity_name)

learning_cql_data_types.cql

#
# learning_cql_data_types.cql
#
# Copyright (C) 2017-2019 Jeff Carpenter
# Execute the commands in this file for a short guided tour of the data types supported in CQL
#
# For more description, see Cassandra, The Definitive Guide 2nd Ed., Chapter 4: The Cassandra Query Language
# http://shop.oreilly.com/product/0636920043041.do
#

# Continue using the keyspace and table from cqlsh_intro.cql
# Note use of IF NOT EXISTS syntax to avoid errors if already present
CREATE KEYSPACE IF NOT EXISTS my_keyspace WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 1} ;
USE my_keyspace;
CREATE TABLE IF NOT EXISTS user ( first_name text, last_name text, title text, PRIMARY KEY (first_name));

# Write a couple of rows, but only if they don't exist already
INSERT INTO user (first_name, last_name, title) VALUES ('Bill', 'Nguyen', 'Mr.') IF NOT EXISTS;
INSERT INTO user (first_name, last_name) VALUES ('Mary', 'Rodriguez') IF NOT EXISTS;

#
# UUID examples
#

# Add a unique identifier using a uuid
ALTER TABLE user ADD id uuid;

# Allow Cassandra to auto-assign a uuid
UPDATE user SET id = uuid() WHERE first_name = 'Mary';

# View the id that was set
SELECT first_name, id FROM user WHERE first_name = 'Mary';

#
# Set examples
#

# Add a set to contain email addresses
ALTER TABLE user ADD emails set<text>;

# Add an email address and check that it was added successfully
UPDATE user SET emails = { 'mary@example.com' } WHERE first_name = 'Mary';
SELECT emails FROM user WHERE first_name = 'Mary';

# Add another email address using concatenation
UPDATE user SET emails = emails + {'mary.mcdonald.AZ@gmail.com' } WHERE first_name = 'Mary';
SELECT emails FROM user WHERE first_name = 'Mary';

#
# List examples
#

# Modify the user table to add a list of phone numbers
ALTER TABLE user ADD phone_numbers list<text>;

# Add a phone number for Mary and check that it was added successfully
UPDATE user SET phone_numbers = [ '1-800-999-9999' ] WHERE first_name = 'Mary';
SELECT phone_numbers FROM user WHERE first_name = 'Mary';

# Add a second number by appending it:
UPDATE user SET phone_numbers = phone_numbers + [ '480-111-1111' ] WHERE first_name = 'Mary';
SELECT phone_numbers FROM user WHERE first_name = 'Mary';

# Replace an individual item in the list referenced by its index
UPDATE user SET phone_numbers[1] = '480-111-1111' WHERE first_name = 'Mary';

# Use the subtraction operator to remove a list item matching a specified value
UPDATE user SET phone_numbers = phone_numbers - [ '480-111-1111' ] WHERE first_name = 'Mary';

# Delete a specific item directly using its index
DELETE phone_numbers[0] from user WHERE first_name = 'Mary';

#
# Map examples
#

# Add a map attribute to store information about user logins (timed in seconds) keyed by a timestamp (timeuuid)
ALTER TABLE user ADD login_sessions map<timeuuid, int>;

# Add a couple of login sessions for Mary and see the results
# Use the now() function to allow Cassandra to set the timestamp
UPDATE user SET login_sessions = { now(): 13, now(): 18} WHERE first_name = 'Mary';
SELECT login_sessions FROM user WHERE first_name = 'Mary';

#
# User Defined Type (UDT) examples
#

# Create a UDT for address information
CREATE TYPE address (street text, city text, state text, zip_code int);

# Can we use this UDT in a map?
ALTER TABLE user ADD addresses map<text, address>;

# Freeze the UDT so we can use it in a map
# freezing means we cannot access individual fields of the UDT but must select or insert the entire object at once
ALTER TABLE user ADD addresses map<text, frozen<address>>;

# Add a home address for Mary
UPDATE user SET addresses = addresses +  {'home': { street: '7712 E. Broadway', city: 'Tucson',
  state: 'AZ', zip_code: 85715} } WHERE first_name = 'Mary';

#
# Index examples
#

# Query based on a non-primary key column
# Why doesn't this work?
SELECT * FROM user WHERE last_name = 'Nguyen';

# Create a secondary index for the last_name column.
CREATE INDEX ON user ( last_name );

# Now try the query again
SELECT * FROM user WHERE last_name = 'Nguyen';

# View the output of the describe command to see the full index definition
# We didn't name the index, so Cassandra assigned a default name
DESCRIBE KEYSPACE;

# Create indexes on other attributes if desired, even collections
# Note that queries based on indexes are typically more expensive, as they involve talking to more nodes
CREATE INDEX ON user ( addresses );
CREATE INDEX ON user ( emails );
CREATE INDEX ON user ( phone_numbers );

# Drop indexes we no longer want maintained
DROP INDEX user_last_name_idx;
DROP INDEX user_addresses_idx;
DROP INDEX user_emails_idx;
DROP INDEX user_phone_numbers_idx;

# Create a SSTable Attached Secondary Index (SASI), which is a more performant index implementation
CREATE CUSTOM INDEX user_last_name_sasi_idx ON user (last_name) USING 'org.apache.cassandra.index.sasi.SASIIndex';

# SASI indexes allow us to perform inequality and text searches such as "like" searches
SELECT * FROM user WHERE last_name LIKE 'N%';

learning_cql_query_clauses.cql

#
# learning_cql_query_clauses.cql
#
# Copyright (C) 2017-2019 Jeff Carpenter
# Execute the commands in this file for a short guided tour of the filtering options supported on CQL queries
#
# For more description, see Cassandra, The Definitive Guide 2nd Ed., Chapter 9: Reading and Writing
# http://shop.oreilly.com/product/0636920043041.do
#

# Load the hotels schema used in these examples
# This command assumes you've cloned this repository in your home directory
# Or replace with the path to the actual file
SOURCE './hotel.cql';
USE hotel;

# Load test data for the table containing available room data
COPY available_rooms_by_hotel_date FROM './z_available_rooms.csv' WITH HEADER=true;

#
# WHERE clause examples
#

# Search for hotel rooms for a specific hotel and date range:
SELECT * FROM available_rooms_by_hotel_date WHERE hotel_id='AZ123' and date>'2016-01-05' and date<'2016-01-12';

# Why doesn't this query work?
SELECT * FROM available_rooms_by_hotel_date WHERE hotel_id='AZ123' and room_number=101;

# Look at the table again
DESCRIBE TABLE available_rooms_by_hotel_date;

# We can force it to work, but why is this not a good practice?
SELECT * FROM available_rooms_by_hotel_date WHERE date='2016-01-25' ALLOW FILTERING;

#
# IN clause example
#

# Use the IN clause to test equality with multiple possible values for a column
# Find inventory on two dates a week apart
SELECT * FROM available_rooms_by_hotel_date WHERE hotel_id='AZ123' AND date IN ('2016-01-05', '2016-01-12');

#
# SORT clause example
#

# Override the default sort order on the table
SELECT * FROM available_rooms_by_hotel_date
  WHERE hotel_id='AZ123' AND date>'2016-01-05' AND date<'2016-01-12'
  ORDER BY date DESC;

# Bonus: Assuming you're running a multi-node cluster, try running some the queries above with tracing on
# to see the interactions between nodes
TRACING ON;

z_available_rooms.csv