Operations on Table in Cassandra

Tables in Cassandra

For performing Operations on table in Cassandra, we need to create tables first. Below are some commands and statements for tables in Cassandra.

Creating Tables

The first thing to do while doing operations on tables in Cassandra is to create tables. For creating a table, one needs to specify the keyspace, which is the namespace that defines the replication strategy and the table name. One also has to specify the columns and their data types. 
Let us create a table in Cassandra with the following example.

CREATE TABLE sample_table (
  id INT PRIMARY KEY,
  name TEXT,
  age INT,
  date INT
);

The above statement creates a table with ‘name’, ‘age’, and ‘date’ columns.

Altering Tables  

The alter table statement is used to modify already existing structures present in Cassandra. Let us see how.

ALTER TABLE users
ADD gender TEXT;

Using this one can add a new column, ‘gender’ to the table.

Dropping Tables 

The drop table statement is used to discard an existing table in Cassandra. Let us see how.

DROP TABLE date;

The above statement deletes the ‘date’ column from the table.

Truncating Tables

The truncate statement is used to truncate a table where all the rows and columns of the table are deleted immediately and irreversibly.

TRUNCATE <tablename>

Operations on Table in Cassandra

In this section of the article, we will do operations on table in Cassandra.

Defining a Column

In columns, a primary key is a column which is declared mandatorily while creating a table. It is used to identify a row uniquely.

The primary keys are of two types:

• Single Primary Key
   

Primary key (ColumnName)   

• Compound Primary Key
   

Primary key(ColumnName1,ColumnName2 . . .)    

Example :

CREATE TABLE sample_table (
  id INT PRIMARY KEY,
);

Inserting Data

For inserting data into the tables, firstly, the user must specify the keyspace, table name, and values for each column. If the column has no value, then the NULL keyword is put to use.
Let us see with an example.

INSERT INTO table1 (id,name, age, phone no.) VALUES (1, 'ABCD', 19, ‘9876543210’);

And for multiple entries,

INSERT INTO table2 (name, age, phone no) VALUES (1, ‘DEF’, 21, ‘123456789’);
INSERT INTO table2 (name, age, phone no) VALUES(2, 'GHJ', 33, ‘988756632’);
INSERT INTO table2 (name, age, phone no) VALUES (3, ‘NIK’, 26, ‘255843210’);

Updating Data

The update statement is used for updating the content of the tables. For updating the table, the keyspace, table name, columns to update, and new values are to be specified by the user. Let us see how:

UPDATE table3 SET age = 16 WHERE id = 1;

Deleting Data

The delete statement is used for deleting the data from the table. For this, the keyspace, table name, and row to delete have to be specified by the user.

DELETE FROM table3 WHERE id = 1;

Selecting Data

The select statement is used to select the data from the table. The keyspace, table name, and columns to retrieve have to be specified by the user. 

SELECT name, age FROM table2 WHERE age > 16 ORDER BY age DESC LIMIT 10;

Here, ‘order by’ is used to sort the rows and the ‘limit’ is used to set a limit to the number of rows.

Advantages of using Cassandra

Here are some advantages of using Cassandra for data manipulation.

• It is an open-source application.
   
• There is no point in failure as it follows peer-to-peer architecture rather than master-slave architecture.
   
• Cassandra supports hybrid cloud environments since it was designed as a distributed system to deploy many nodes across many data centres.
   
• Cassandra application is a schema-free or a schema-optional application. Hence the creation of columns within rows 

Disadvantages of using Cassandra

• Latency issues occur in Cassandra as it handles large amounts of data.
   
• Here the data is modelled around queries and not on structures  Hence there may be chances that the same information gets stored multiple times.
   
• JVM memory management issues may occur as Cassandra stores vast amounts of data. 

Also see,  Checkpoint in DBMS And  Recursive Relationship in DBMS

Frequently Asked Questions

What is the difference between Cassandra and Traditional Databases?

In a Cassandra database, data is written in many locations and data processing volumes are high. The Cassandra database supports simple transactions and can handle high incoming data volumes. On the contrary, in Traditional databases, data volumes are moderate and are written primarily in one location. Traditional databases support complex transactions and can handle only moderate incoming data.

What is a MemTable?

Memtable is a storage engine in Cassandra. The location where data is written and stored temporarily in a MemTable. It stores the current mutations applied to Column families. Generally, there is one active MemTable per table. And by default, Cassandra is configured to store Memtable data in heap space. 

What is SSTable?

SSTable or Sorted Strings Table in Cassandra is a file format used when MemTables are flushed to durable storage from memory. SSTables in Cassandra function as the building blocks of the total data stored in the database. SSTable is created by a MemTable flush and is deleted by reconciling. These are saved as a persistent, ordered, immutable set of files on disk.

Conclusion 

In this article, we studied the operations on table in Cassandra, along with some examples. Hope this article helped you in your writing journey.

For more information, you can refer to our Guided Path on Coding Ninjas Studio to upskill yourself in Python, Data Structures and Algorithms, Competitive Programming, System Design, and many more!

You can also look at the Python Library for more Python-related content.

Head over to our practice platform Coding Ninjas Studio to practise top problems, attempt mock tests, read interview experiences and interview bundles, follow guided paths for placement preparations, and much more!!

Happy Learning Ninja!!