March 12th, 2014

This article was created by Manisha Sethi. To view the original article, and more postings by Manisha, visit the Xebia blog.

Many of you know must be heard of or worked on Cassandra – The Columnar NoSQL Database. Most of us gets a feel from the term NoSQL as it is not much like RDBMS because of ease of use in terms of the Query Language. But with Cassandra 2.0 , It is providing a better Query Language Support using CQL3.

Here in CQL 3.0, it has borrowed many of features from SQL like Table creation, OrderBy clause and many more.

The main difference is that Cassandra does not support joins or subqueries, except for batch analysis through Hive. Instead, Cassandra emphasizes denormalization through CQL features like collections and clustering specified at the schema level.

CQL is the recommended way to interact with Cassandra. The simplicity of reading and using CQL is an advantage over older Cassandra APIs.

The main goal of this approach is to give the end user a familiar feel of working with SQL. Additionally CQL is useful in knowing the information related to cluster, Keyspaces structure. And it also provides commands to read the TTL(Time To Live) for data and many other much useful information

Launching the CQL Shell

If one has Cassandra installed and running, Open a terminal, you need to go to the your CASSANDRA_HOME directory.

Just e.g. If you have Cassandra setup in /home/user/cassandra/, then

And type Command :

This will open up the CQL Shell like:

Screenshot from 2014-02-27 15:43:22

Creating and updating a keyspace

Creating a keyspace is the CQL counterpart to creating an SQL database, but a little different. The Cassandra keyspace is a namespace that defines how data is replicated on nodes. Typically, a cluster has one keyspace per application. Replication is controlled on a per-keyspace basis, so data that has different replication requirements typically resides in different keyspaces.

1.Create a keyspace.

2. Use the KeySpace.

3. Update the replication factor

Create the Column Family

Creating the table employee with Composite Primary Key.

Note –> Use a compound primary key when you want to create columns that you can query to return sorted results.

Inserting Data into Column Family

Similar to SQL Insert Statement.

Retrieving and sorting results

To retrieve results, use the SELECT command.

Similar to a SQL query, use the WHERE clause and then the ORDER BY clause to retrieve and sort results.


1. Retrieve and sort results in descending order.

2. Retrieve and sort results in ascending order.

Using a Counter

A counter is a special kind of column used to store a number that incrementally counts the occurrences of a particular event or process. For example, you might use a counter column to count the number of times a page is viewed.

Counter column tables must use Counter data type. Counters may only be stored in dedicated tables. You cannot index a counter column.

You load data into a counter column using the UPDATE command instead of INSERT. To increase or decrease the value of the counter, you also use UPDATE.


1. Create keySpace and table for counter column.

2. Load data into the counter column.

3. Take a look at the counter value.

Output is:

url_name | page_name | counter_value

——————+———–+————— | home | 1

(1 rows)

4. Increase the value of the counter.

5. Take a look at the counter value.

Output :

url_name | page_name | counter_value

——————+———–+————— | home | 3

Collection Columns

CQL introduces these collection types:

  • set
  • list
  • map

In a relational database, to allow users to have multiple email addresses, you create an email_addresses table having a many-to-one (joined) relationship to a users table. CQL handles the classic multiple email addresses use case, and other use cases, by defining columns as collections. Using the set collection type to solve the multiple email addresses problem is convenient and intuitive.

1. Adding a collection to a table

Here we are altering an existing Table Named songs to add a collection to store tags for each Song.

2. Updating a collection

3. Querying a Collection

To query a collection, include the name of the collection column in the select expression.


Prepared statements

CQL supports prepared statements. Using a prepared statement, Cassandra parses a query only once, but executes it multiple times with different concrete values.

In a statement, each time a column value is expected in the data manipulation and query statements, a bind variable marker can be used instead. A statement with bind variables must then be prepared. During execution of the prepared statement, concrete values for bind variables are provided in the order the bind variables are defined in the query string.



A BATCH statement combines multiple data modification language (DML) statements (INSERT, UPDATE, DELETE) into a single logical operation, and sets a client-supplied timestamp for all columns written by the statements in the batch. Batching multiple statements saves network exchanges between the client/server and server coordinator/replicas.

In Cassandra 1.2 and later, batches are atomic by default. In the context of a Cassandra batch operation, atomic means that if any of the batch succeeds, all of it will. To achieve atomicity, Cassandra first writes the serialized batch to the batchlog system table that consumes the serialized batch as blob data. When the rows in the batch have been successfully written and persisted (or hinted) the batchlog data is removed.


Determining time-to-live for a column

This procedure creates a table, inserts data into two columns, and calls the TTL function to retrieve the date/time of the writes to the columns.


1. Create a users table named clicks in the excelsior keyspace.

2. Insert data into the table, including a date in yyyy-mm-dd format, and set that data to expire in a day (86400 seconds). Use the USING TTL clause to set the expiration period.

3. Wait for a while and then issue a SELECT statement to determine how much longer the data entered in step 2 has to live.

There are many more new and useful features in CQL, the Cassandra Query Language.