Apache Cassandra – Part 04 (CQL)

From previous posts we talked about the basics ,installation and cqlsh of Apache Cassandra,

From this post I’m going to talk about Cassandra query language, CQL. It’s similar to the SQL what we already know. So let’s have a close look at a comparison of terms before going in to the queries.

Mysql Vs. Cassandra

Data Base – Key Space

Table – Column Family

So next let’s have a close look at the CQL queries one by one,

Create Key space : Create a new key space

CREATE KEYSPACE <name> WITH replication = {‘class’: ‘<type>’, ‘replication_factor’: <no-of-replications>} AND durable_writes = ‘<true/false>’

replication – Mandatory : class – type of replication

replication_factor – no of replicas

durable_writes – Optional : true – will write a commit log : default

false – will not write the commit log

We can check whether the key space has created or not using DESC. DESC KEYSPACES will list all the existing key spaces.

USE – Same as MySQL, USE will select a Key Space to work on.

Alter Key space : To change the existing key space

ALTER KEYSPACE<name> WITH replication = {‘class’: ‘<type>’, ‘replication_factor’: <no-of-replications>} AND durable_writes = ‘<true/false>’

Drop key space : Drop an existing key space

DROP KEYSPACE <name>

Create column family : Create a new column family/ table

CREATE COLUMNFAMILY<name>(

<column-one> <data-type>,

<column-two> <data-type>,

….

)

CREATE TABLE<name>(

<column-one> <data-type>,

<column-two> <data-type>,

….

)

Alter table : Change existing table

ALTER TABLE <name>

ADD <column-one> <data-type>

ALTER TABLE <name>

DROP <column-one>

Drop table : To drop the table from the key space

DROP TABLE <name>

Insert data to the table

INSERT INTO <name>(<column-one>,<column-two>,..) VALUES (‘<value-one>’,'<value-two>’,…)

Select data from a table

SELECT * FROM <name>

SELECT <column-one>,<column-two>,.. FROM <name>

Update table : update data in a table

UPDATE <name> SET <column-name-one>=<value-one>, <column-name-two>=<value-two> WHERE <column-name>=<value>

Delete data : Remove columns or data from table

DELETE <column> FROM <table-name> WHERE <column>=<value>

DELETE FROM <table-name> WHERE <column>=<value>

Truncate table : Delete data from a table

TRUNCATE TABLE <name>

Create index : Indexes can be helpful in increasing the performance. We can create indexes from a non primary key column as below,

CREATE INDEX <name> ON <table-name>(<column-name>)

Drop index : We can drop an index as well.

DROP INDEX <name>

BATCH : We can execute a set of commands at once as well.

BEGIN BATCH

<query-one>;

<query-two>;

….

APPLY BATCH;

As you can see Cassandra Query Language is similar to SQL. That’s all for this post. Hope now you have a clear idea about CQL. See you soon with another interesting topic. Thank You!