Day 9: Cassandra Lab Practice – Advanced Queries & Aggregations

byMy Scribble Pads -

✅ Day 9: Advanced Queries & Aggregations

🎯 Objective:

Learn how to perform advanced queries, use aggregation functions, and understand ALLOW FILTERING in Cassandra.

📘 1. Aggregation Functions in Cassandra

Cassandra supports limited aggregation functions:

Function Description
COUNT(*) Count rows
MAX(column) Maximum value in a column
MIN(column) Minimum value in a column
AVG() ❌ Not supported natively
SUM() ❌ Not supported
WRITETIME() Shows write timestamp of a column
TTL() Shows remaining TTL of a column

🧪 2. Example: Count Records

SELECT COUNT(*) FROM test_lab.employee;

📊 3. Example: MIN / MAX

SELECT MIN(joined_on), MAX(joined_on) FROM test_lab.employee;

⏰ 4. WRITETIME and TTL

SELECT WRITETIME(department), TTL(department)
FROM test_lab.employee
WHERE emp_id = <UUID>;

⚠️ 5. Using ALLOW FILTERING

Cassandra requires partition key for most queries. If not used, you must add ALLOW FILTERING.

➤ Example:

SELECT * FROM test_lab.employee
WHERE department = 'Finance' ALLOW FILTERING;

⚠️ Use ALLOW FILTERING only for testing or small datasets. It can hurt performance.

🔍 6. Pagination with LIMIT and OFFSET (Simulated)

Cassandra doesn’t support OFFSET, but you can simulate paging:

SELECT * FROM test_lab.employee LIMIT 5;
  • For real pagination, use token(partition_key) with a cursor-like method in application logic.

🧪 Day 9 Lab Tasks

  1. Use COUNT, MIN, MAX on employee and exam_scores tables
  2. Check WRITETIME and TTL on any column
  3. Run a query using ALLOW FILTERING on a non-key column
  4. Try paginating with LIMIT

✅ Checklist

Task Done
Used COUNT, MIN, MAX functions ✅
Fetched WRITETIME and TTL for a column ✅
Executed query with ALLOW FILTERING ✅
Tested result limiting with LIMIT ✅


Tags