Cassandra-Similarities and Differences between the SQL and CQL languages

Dear Readers,

In this article we will see Similarities and Differences between the SQL and CQL languages.

Every database must provide appropriate languages and interfaces to communicate with the database. Database Languages are used to create and maintain database.

There are several databases like Oracle, MySql, SQL Server, DB2, Cassandra, mongodb,   Hbase etc…

Each and every database having the appropriate database language, those are used for read, update and storing data in a database.

As per database languages and interfaces, databases are divided in two types

• SQL ( Relational Databases )
• NoSQL (Non-Relational Databases).

 

SQL (Structured Query Language).

All Relational databases are using the SQL as Standard database language.

Relational databases:

Oracle, MySql, SQL Server, DB2 etc…

 

NoSQL (Not Only Structured Query Language).

Non-Relational databases are using the NoSQL languages.

There are several Non-Relational databases, different types of Non-Relational databases using the different Language.

Non-Relational databases:

Cassandra, mongodb, Hbase, Riak, couchdb etc…

 

Cassandra is a massively scalable open source NoSQL database. Cassandra provide its own database language CQL (Cassandra Query Language).

Similarities and Differences

SQL

All Relational databases Manages structured data.

Data arrive from one or few locations, Relational databases Handles data coming in low velocity and moderate volume.

CQL

Cassandra Database manages structured and semi-structured data.

Data arrive from many locations and Handles data coming in high velocity and very high volume.

SQL and CQL both are having the database sub-languages.

Data Definition Language:

Data Definition Language statements are used to define the structure of database, schema and database objects.

SQL and CQL

• CREATE – used to create objects in the database.
• ALTER – used to change the structure of the database.
• DROP – used to delete objects from the database.
• TRUNCATE – used to remove all records from a table.
• RENAME – used to rename an object.

SQL

You can create the table with and without primary key.

CQL

You cannot create the table without primary key.

Primary key (simple/composite/compound) column will act as a partition key in Cassandra.

SQL

If you add any column for a table, it will appear right side of the table.

CQL

If you add any column for a table, it will arrange the column position in an alphabetical order.

Data Manipulation Language:

Data Manipulation Language statements are used to store and modify the data.

SQL

• INSERT – It inserts data into a table.
• UPDATE – It updates existing data within a table.
• DELETE – It deletes records from a table.

 

CQL

• INSERT – It inserts data into a table when row does not exist in a table, if row exists it will update the existing row values (works as an update).
• UPDATE – It updates existing data within a table if row exists, if row not exists it will it inserts data into a table (works as an insert).
• DELETE – It deletes records from a table.

 

UPDATE / DELETE:

In order to update/delete particular row values you can use WHERE clause, But only primary key column can be used for where clause condition.

Cassandra providing the Batch operations, using the Batch you can perform bulk operations.

Ex:  begin batch

Insert…..

Update…..

Delete…..

Update…..

Insert…..

   Apply batch;

 

Data Retrieval/Query Language (DRL/DQL):

SQL

• Supports complex transactions (Relation between database objects is possible).
• You can choose particular columns data.
• You can select the required data using WHERE clause, on any of the column presents in the table even those columns does not having the indexes.
• You can use logical operators to query the data like AND, OR, NOT…

 

CQL

• Supports Simple transactions (Relation between database objects is not possible).
• You can choose particular columns data.
• You can select the required data using where clause, on only primary key column presents in the table.
• If you want to apply WHERE condition on non primary key column you need secondary indexes on those columns.
• You can use only AND operator to query the data, There are no OR and NOT logical operators.

 

Data Control Language (DCL):

Data Control Language statements are used to control the users access on the database.

SQL

• Grant – It gives permissions/privileges to users on a database and objects.
• Revoke – It takes back permissions from the user.

 

Transaction Control Language (TCL):

Transactional Control Language statements are used to manage the data modifications on the database.

SQL

• COMMIT – It saves the work done
• SAVEPOINT – It identifies a point in a transaction to which you can later roll back
• ROLLBACK – It restores database to original since the last COMMIT

 

There are lot of Similarities and Differences, you will come to know once you start using CQL.

 

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTEXPERTS is always active on below social media platforms.

Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts