Command Line Interface

Master CQLSH

The Cassandra Query Language Shell - Your primary interface to Cassandra!

💻 What is CQLSH?

CQLSH = Cassandra Query Language Shell

It's your primary command-line interface for interacting with Cassandra! Think of it like:

💾 MySQL: mysql client → Cassandra: cqlsh
🐘 PostgreSQL: psql → Cassandra: cqlsh
🗄️ MongoDB: mongo shell → Cassandra: cqlsh

✨ What You Can Do with CQLSH

✅ Run CQL queries: SELECT, INSERT, UPDATE, DELETE
✅ Create schemas: Keyspaces, tables, indexes
✅ Import/Export data: COPY commands, CSV files
✅ View cluster info: DESCRIBE commands
✅ Execute scripts: Run .cql files
✅ Configure settings: Consistency levels, paging

🎯 Why Learn CQLSH?

📚 Learning: Best way to learn CQL
🐛 Debugging: Quick queries to investigate issues
🔧 Administration: Schema changes, maintenance
📊 Development: Prototype queries, test data
🚀 Scripts: Automate tasks with .cql files

🎓 Master cqlsh = Master Cassandra!

⚙️ Installing CQLSH

Multiple ways to get cqlsh!

🐳

With Docker

Easiest - Already included!

# Run Cassandra
docker run -d --name cassandra \
  -p 9042:9042 cassandra:4.1

# Use cqlsh
docker exec -it cassandra cqlsh
                                

📦

Standalone (pip)

Install separately

# Install via pip
pip install cqlsh

# Or with Python 3
pip3 install cqlsh

# Use it
cqlsh localhost 9042
                                

📥

With Cassandra

Included with installation

# Linux/Mac
/opt/cassandra/bin/cqlsh

# After install
cqlsh
                                

✓

Verify Installation

# Check version
cqlsh --version

# Expected output:
cqlsh 6.1.0

# Test connection
cqlsh localhost

# Should connect successfully!
Connected to Test Cluster at localhost:9042
[cqlsh 6.1.0 | Cassandra 4.1.x | CQL spec 3.4.6]
cqlsh>
                                

Python Version Required

CQLSH needs Python!

✅ Python 3.6+ recommended
⚠️ Python 2.7 still works but deprecated
📦 Pip install handles dependencies automatically

🔌 Connecting to Cassandra

Different connection scenarios!

Local Connection (Default)

# Connect to localhost:9042
cqlsh

# Same as:
cqlsh localhost 9042

# Expected:
Connected to Test Cluster at localhost:9042
cqlsh>
                                

Remote Connection

# Connect to remote host
cqlsh 192.168.1.100 9042

# With hostname
cqlsh cassandra-prod.example.com

# Specify datacenter
cqlsh 192.168.1.100 --cqlversion="3.4.6"
                                

With Authentication

# Username and password
cqlsh -u cassandra -p cassandra

# Will prompt for password
cqlsh -u admin
Password:

# In Docker
docker exec -it cassandra cqlsh -u cassandra -p cassandra
                                

Execute Commands Directly

# Run single command
cqlsh -e "SELECT release_version FROM system.local;"

# Run script file
cqlsh -f schema.cql

# Multiple commands
cqlsh -e "USE my_keyspace; SELECT * FROM users LIMIT 5;"
                                

CQLSH Command Options

# View all options
cqlsh --help

# Common options:
-u USERNAME              # Username
-p PASSWORD              # Password
-k KEYSPACE              # Initial keyspace
-f FILE                  # Execute CQL file
-e STATEMENT             # Execute statement
--cqlversion=VERSION     # CQL version
--connect-timeout=SEC    # Connection timeout
--request-timeout=SEC    # Request timeout
                            

💻 Essential CQLSH Commands

Commands you'll use every day!

DESCRIBE - View Schema

-- List all keyspaces
DESCRIBE KEYSPACES;

-- Describe specific keyspace
DESCRIBE KEYSPACE my_keyspace;

-- List all tables in current keyspace
DESCRIBE TABLES;

-- Describe specific table
DESCRIBE TABLE users;

-- Show full schema
DESCRIBE FULL SCHEMA;

-- Describe cluster
DESCRIBE CLUSTER;
                                    

USE - Switch Keyspace

-- Switch to keyspace
USE my_keyspace;

-- Now all queries run in this keyspace
SELECT * FROM users;

-- Prompt shows current keyspace:
cqlsh:my_keyspace>
                                    

HELP - Get Help

-- Show all commands
HELP;

-- Help for specific command
HELP SELECT;
HELP CREATE;
HELP COPY;
                                    

SOURCE - Execute Script

-- Run CQL script file
SOURCE '/path/to/schema.cql';

-- Useful for:
-- - Creating schemas
-- - Loading test data
-- - Batch operations
                                    

CONSISTENCY - Set Level

-- Check current consistency level
CONSISTENCY;

-- Set to QUORUM
CONSISTENCY QUORUM;

-- Set to ONE
CONSISTENCY ONE;

-- All subsequent queries use this level
                                    

PAGING - Control Results

-- Enable paging (100 rows at a time)
PAGING 100;

-- Disable paging (show all)
PAGING OFF;

-- Check status
PAGING;
                                    

TRACING - Debug Queries

-- Enable query tracing
TRACING ON;

-- Run query (shows execution details)
SELECT * FROM users WHERE id = 'abc123';

-- Disable tracing
TRACING OFF;
                                    

EXIT - Close Shell

-- Exit cqlsh
EXIT;

-- Or
QUIT;

-- Or Ctrl+D
                                    

⚙️ Configuring CQLSH (.cqlshrc)

Customize your cqlsh experience!

Create Config File

Create ~/.cqlshrc (Linux/Mac) or %USERPROFILE%\.cqlshrc (Windows)

# Create file
nano ~/.cqlshrc

# Or
vim ~/.cqlshrc
                                

Sample Configuration

# Connection settings
[connection]
hostname = localhost
port = 9042
# factory = cqlshlib.ssl.ssl_transport_factory

# Authentication
[authentication]
username = cassandra
password = cassandra

# Display settings
[ui]
color = on
datetimeformat = %Y-%m-%d %H:%M:%S%z
float_precision = 3
encoding = utf8
timezone = UTC

# Query settings
[cql]
version = 3.4.6
consistency = QUORUM

# Copy settings
[copy]
chunksize = 5000
maxattempts = 3
pagetimeout = 10
                                

Useful Config Options

color: Syntax highlighting (on/off)
timezone: Display timestamps in your timezone
float_precision: Decimal places for floats
consistency: Default consistency level
username/password: Auto-login credentials

📦 Importing and Exporting Data

Move data in and out of Cassandra!

📤

Export Data to CSV

-- Export entire table
COPY users TO '/tmp/users.csv';

-- Export specific columns
COPY users (id, name, email) TO '/tmp/users_subset.csv';

-- With custom delimiter
COPY users TO '/tmp/users.tsv' 
WITH DELIMITER='\t';

-- Without header
COPY users TO '/tmp/users.csv' 
WITH HEADER=false;

-- Export query results
COPY (
  SELECT name, email 
  FROM users 
  WHERE country = 'USA'
) TO '/tmp/usa_users.csv';
                                

📥

Import Data from CSV

-- Import from CSV
COPY users FROM '/tmp/users.csv';

-- Specify columns
COPY users (id, name, email) 
FROM '/tmp/users_subset.csv';

-- With custom delimiter
COPY users FROM '/tmp/users.tsv' 
WITH DELIMITER='\t';

-- Skip header row
COPY users FROM '/tmp/users.csv' 
WITH HEADER=true;

-- Set chunk size (performance)
COPY users FROM '/tmp/users.csv' 
WITH CHUNKSIZE=10000;
                                

COPY Command Tips

⚠️ Not for large datasets: Use sstableloader for GB+ data
⚠️ Performance: Increase CHUNKSIZE for faster imports
⚠️ NULL values: Empty fields = NULL by default
⚠️ Data types: CSV must match table schema
✅ Good for: < 1GB, testing, migrations

🔧 Common Issues & Solutions

Fix problems quickly!

❌ "Connection refused"

Problem: Can't connect to Cassandra

Solutions:

Check Cassandra is running: nodetool status
Verify port 9042 is listening: netstat -an | grep 9042
Check firewall rules
Try explicit IP: cqlsh 127.0.0.1 9042

❌ "Authentication failed"

Problem: Wrong username/password

Solutions:

Default credentials: cassandra / cassandra
Check authentication enabled in cassandra.yaml
Reset password in system_auth.roles table

❌ "command not found: cqlsh"

Problem: CQLSH not in PATH

Solutions:

# Add to PATH (Linux/Mac)
export PATH=$PATH:/opt/cassandra/bin

# Add to ~/.bashrc for persistence
echo 'export PATH=$PATH:/opt/cassandra/bin' >> ~/.bashrc

# Or use full path
/opt/cassandra/bin/cqlsh
                                    

❌ "No module named 'cassandra'"

Problem: Python driver not installed

Solution:

# Install Python driver
pip install cassandra-driver

# Or reinstall cqlsh
pip install --force-reinstall cqlsh
                                    

❌ Slow queries / Timeout

Problem: Query taking too long

Solutions:

Add LIMIT to queries: SELECT * FROM users LIMIT 100
Increase timeout: cqlsh --request-timeout=60
Enable paging: PAGING 100
Check query uses partition key

💡 Pro Tips & Tricks

Work smarter with cqlsh!

⌨️ Command History

# Use arrow keys
↑ ↓  # Navigate command history

# Search history (Ctrl+R)
Ctrl+R  # Then type to search

# History file location
~/.cassandra/cqlsh_history
                                    

✏️ Tab Completion

Press TAB to auto-complete:

Keyspace names
Table names
Column names
CQL keywords

📋 Format Output

-- Vertical output (easier to read)
EXPAND ON;
SELECT * FROM users LIMIT 1;

-- Back to normal
EXPAND OFF;

-- Capture output to file
CAPTURE '/tmp/output.txt';
SELECT * FROM users;
CAPTURE OFF;
                                    

🔢 Timing Queries

-- Show query execution time
TIMING ON;

SELECT COUNT(*) FROM users;

-- Output includes: (123 rows returned, 0.234 seconds)

TIMING OFF;

🎨 Color Output

Edit ~/.cqlshrc:

[ui]
color = on

# Customize colors
[colors]
error = red bold
output_header = cyan
output_text = green
                                    

📝 Multi-Line Statements

-- Write query across multiple lines
SELECT id, 
       name, 
       email
FROM users
WHERE country = 'USA'
LIMIT 10;

-- Semicolon executes the statement
                                    

🔐 Secure Password Storage

Never put passwords in scripts!

# Instead of:
cqlsh -u admin -p MyPassword123  # ❌ Visible in history!

# Use prompt:
cqlsh -u admin  # ✅ Prompts for password
Password:

# Or use .cqlshrc (secure file permissions!)
chmod 600 ~/.cqlshrc
                                    

Responsive Ad