Day 4: Cassandra Lab Practice – Insert & Select Queries

byMy Scribble Pads -

✅ Day 4: Insert and Select Queries in Cassandra

🎯 Objective:

Learn how to insert and retrieve data using CQL’s INSERT and SELECT statements, and understand the rules of querying in Cassandra.

🧩 1. INSERT Data into Tables

Syntax:

INSERT INTO keyspace.table_name (column1, column2, ...) 
VALUES (value1, value2, ...);

✅ Example – employee table:

INSERT INTO test_lab.employee (emp_id, name, department, joined_on)
VALUES (uuid(), 'John Doe', 'HR', toTimestamp(now()));

📝 Notes:

  • Use uuid() to auto-generate a unique ID
  • Use toTimestamp(now()) for the current timestamp

🔍 2. SELECT Data from Tables

➤ Select all rows:

SELECT * FROM test_lab.employee;

➤ Select specific columns:

SELECT name, department FROM test_lab.employee;

⚠️ 3. Query Rules in Cassandra

  • WHERE clause must use partition key
  • You can filter clustering columns after partition key
  • If filtering non-key columns, use ALLOW FILTERING (use cautiously)

📘 4. Practice: Query with WHERE

➤ Valid Query (on partition key):

SELECT * FROM test_lab.attendance WHERE class_id = '10A';

➤ Valid Query (with clustering column):

SELECT * FROM test_lab.attendance 
WHERE class_id = '10A' AND date = '2024-10-01';

➤ Invalid Without Partition Key:

-- This will throw an error
SELECT * FROM test_lab.attendance WHERE date = '2024-10-01';

➤ Use ALLOW FILTERING:

SELECT * FROM test_lab.attendance 
WHERE status = 'Present' ALLOW FILTERING;

⚠️ Use ALLOW FILTERING only for small datasets or for testing.

🔢 5. LIMIT and ORDER BY

➤ Limit results:

SELECT * FROM test_lab.employee LIMIT 3;

➤ Order by clustering key (in reverse):

SELECT * FROM test_lab.attendance 
WHERE class_id = '10A' 
ORDER BY date DESC;

🧪 Day 4 Lab Tasks

  1. Insert 3–5 rows into each table:

    • employee, attendance, exam_scores

  2. Run different SELECT queries:

    • Fetch by partition key
    • Use LIMIT and ORDER BY
    • Try ALLOW FILTERING carefully
  3. Observe errors when querying without partition key

✅ Checklist

Task Done
Inserted multiple rows into tables ✅
Queried with valid WHERE conditions ✅
Practiced LIMIT, ORDER BY clauses ✅
Observed effect of ALLOW FILTERING ✅


Tags