CRUD Operations in Cassandra

Cassandra is a powerful NoSQL database designed for high availability and scalability. In this article, we will dive right into performing the basic CRUD operations—Create, Read, Update, and Delete—using Cassandra Query Language (CQL). Our goal is to provide a hands-on guide that you can refer to while working with Cassandra.

Setting Up Your Cassandra Environment

Before we jump into the CRUD operations, make sure you have a working instance of Apache Cassandra running. If you haven’t done this yet, you can follow these simple steps:

Download and Install Cassandra: Visit the Apache Cassandra official website and download the latest version for your operating system.
Start Cassandra: After installation, you can start Cassandra using the command-line interface:
```
cassandra -f
```
CQLSH: Cassandra provides a command-line shell (CQLSH) for executing CQL commands. To open it, simply run:
```
cqlsh
```

Once your environment is ready, we can start performing CRUD operations.

1. Create Operation

The Create operation in Cassandra is about inserting new data into a table. Here’s how you can create a table and insert data into it.

Creating a Keyspace

First, define a keyspace, which is similar to a database in relational systems:

CREATE KEYSPACE IF NOT EXISTS my_keyspace
WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };

Creating a Table

Next, create a table to store our data. Let’s create a simple users table that includes user ID, name, and email:

USE my_keyspace;

CREATE TABLE IF NOT EXISTS users (
    user_id UUID PRIMARY KEY,
    name TEXT,
    email TEXT
);

Inserting Data

Now that we have our table, we can insert some data into it. Here’s how to perform an insert operation:

INSERT INTO users (user_id, name, email) 
VALUES (uuid(), 'John Doe', 'john.doe@example.com');

INSERT INTO users (user_id, name, email) 
VALUES (uuid(), 'Jane Smith', 'jane.smith@example.com');

Batch Inserts

If you want to insert multiple rows in one go, you can use a batch operation:

BEGIN BATCH 
INSERT INTO users (user_id, name, email) VALUES (uuid(), 'Alice Johnson', 'alice.johnson@example.com');
INSERT INTO users (user_id, name, email) VALUES (uuid(), 'Bob Brown', 'bob.brown@example.com');
APPLY BATCH;

Now that you've learned how to create and insert data, let’s move on to the Read operation.

2. Read Operation

Reading data from Cassandra involves querying the database using CQL. Here’s how to retrieve data from the users table.

Selecting All Records

To fetch all users, you can use the following query:

SELECT * FROM users;

Selecting Specific Columns

If you are only interested in specific columns, you can specify them in your SELECT statement:

SELECT name, email FROM users;

Using WHERE Clause

To fetch specific records based on certain conditions, you can utilize the WHERE clause. Here’s an example of how to retrieve a user by their user_id:

SELECT * FROM users WHERE user_id = 1234; 
-- Replace 1234 with an actual UUID from your records

Using ALLOW FILTERING

Cassandra doesn't allow filtering on non-primary key columns by default for performance reasons. However, you can enable filtering by using the ALLOW FILTERING clause:

SELECT * FROM users WHERE name = 'John Doe' ALLOW FILTERING;

Paging Through Results

When dealing with large datasets, you might want to paginate results. This can be achieved using the LIMIT clause:

SELECT * FROM users LIMIT 10;

3. Update Operation

The Update operation allows you to modify existing records in the table. Here's how it's done in Cassandra.

Updating a Record

To update a user's email address, you would use the following statement:

UPDATE users SET email = 'john.newemail@example.com' WHERE user_id = 1234; 
-- Replace 1234 with an actual UUID

Updating Multiple Columns

You can also update multiple columns at once:

UPDATE users SET name = 'Johnathan Doe', email = 'johnathan.doe@example.com' WHERE user_id = 1234;

If Not Exists

If you want to ensure that a column is only updated if it contains a specific value, use the IF clause:

UPDATE users SET email = 'john.doe@newexample.com' IF email = 'john.doe@example.com';

4. Delete Operation

The Delete operation is used to remove data from Cassandra tables. Here’s how to delete records safely.

Deleting a Specific Record

To delete a user by user_id, use the following CQL command:

DELETE FROM users WHERE user_id = 1234; 
-- Replace 1234 with an actual UUID

Deleting Specific Columns

If you want to delete a particular column from a row, you can do so:

DELETE email FROM users WHERE user_id = 1234;

Truncate Table

If you need to clear all records in a table, you can use the TRUNCATE command. Be cautious; this will remove all data without the ability to restore it easily:

TRUNCATE users;

Conclusion

In this article, we've covered the fundamental CRUD operations—Create, Read, Update, and Delete—using Cassandra Query Language (CQL). By utilizing these operations, you can efficiently manage your data in a Cassandra environment.

As you continue to explore and work with Cassandra, remember that its design encourages denormalization and a different approach to data modeling compared to traditional relational databases. Happy querying!

Databases - Cassandra