Cassandra Delete All Rows From a Table

“When working with databases, there is a large margin for errors, either from human or application corruption. Although we, as developers, take all the measures to mitigate data loss, operations such as backups and restoring may not fit particular needs.

Therefore, you may encounter a scenario where you need to perform a reset on the database or a specific table. This post will teach you how to remove all the data from a Cassandra table without removing the table schema. This allows you to reset all the data and start from scratch.”

Cassandra Truncate Command

We can use the TRUNCATE command to remove all the data from a specific table while preserving the table layout and schema.

Once you run the TRUNCATE command, all the data is removed from the table, and it’s impossible to reverse unless you have a backup copy of the table. This operation is applied across all the nodes in your Cassandra cluster. This ensures that all the data is synchronized in all the nodes.

The command syntax is as shown:

TRUNCATE [TABLE] [keyspace_name.table_name]

If you are already in the target keyspace, you can use the syntax as shown:

TRUNCATE [TABLE] [table_name]

Before running the TRUNCATE command, ensure the following:

All the nodes in the cluster are up and running. This allows the truncate operation to be applied across the cluster.
The target table exists in the keyspace.

Step 1 – Select the Target Table

The first step is to ensure the target table exists in the keyspace.

Create Keyspace

Before setting up any sample data, we need a database to store the data. We can do this by creating a sample keyspace.

The query is as provided below:

cqlsh> CREATE KEYSPACE store WITH REPLICATION = { 'class': 'SimpleStrategy', 'replication_factor': 3 };

The query above creates a simple keyspace called store with a replication_factor of 3. If you are not familiar with Cassandra Keyspaces, check out our tutorial on the topic to learn more.

Once created, we can switch to the keyspace with the command:

cqlsh> USE store;

Create Table

The next step is to create a table holding the structure for our data. Keep in mind that Cassandra can handle unstructured data. However, for simplicity, we will attempt to use a table structure.

We can run the query as shown:

cqlsh:store> CREATE TABLE inventory ( product_id int, product_name text, quantity int, price int, availability boolean, PRIMARY KEY(product_id) );

The query above should create an inventory table with the specified columns and data types.

cqlsh:store> INSERT INTO inventory(product_id, product_name, quantity, price, availability)
... VALUES (1, 'Meta Quest', 5, 399, true);
cqlsh:store> INSERT INTO inventory(product_id, product_name, quantity, price, availability) VALUES (2, 'Echo Dot', 10, 27, true);
cqlsh:store> INSERT INTO inventory(product_id, product_name, quantity, price, availability) VALUES (3, 'Tile Pro', 10, 26, false);
cqlsh:store> INSERT INTO inventory(product_id, product_name, quantity, price, availability) VALUES (4, 'Monitor', 100, 499, false);
cqlsh:store> INSERT INTO inventory(product_id, product_name, quantity, price, availability) VALUES (5, 'Laptop', 87, 700, true);
We can then query the table as:
SELECT * FROM inventory;

The query above should return the records stored in the table as shown: