Test driving the new CQL utility

One very nice feature Cassandra offers over other NoSQL databases is the Cassandra Query Language (CQL). In fact, I’ll go so far as to say CQL is an unsung hero of Cassandra and deserves a lot more accolades and attention than it gets. Everyone focuses on Cassandra’s Big Data scalability, screaming performance, high availability, and superb data distribution capabilities, and hey, those are definitely head-turning features for sure.

But CQL is something that deserves to turn a few heads as well. About 99.5% of people coming to a NoSQL database like Cassandra are either former or current RDBMS developers and administrators who are well versed in SQL.

Whereas some NoSQL databases require these folks learn different syntax for object management, data manipulation, and retrieving data, such is not necessarily the case with Cassandra because CQL resembles SQL. This helps greatly reduce the learning curve for Cassandra and ensures newbies are productive in short order.

CQL is what helps make Cassandra a truly “Not-only SQL” type database.

Be aware that in Cassandra, you have the CQL language, CQL application drivers, and the CQL command line shell/utility. At the moment, the CQL language and utility shell are both at version 2.0, although they likely won’t stay in sync version-wise in the future.

For Cassandra versions prior to 1.0, the CQL utility and language were pretty raw. With 1.0, things considerably improved, and with the most recent releases, the CQL 2.0 language and shell are even better.

I thought I’d point out a few of the recent additions in the latest CQL shell utility that the talented Cassandra developers have made, which you might find interesting.

And Away We Go

First, the CQL shell is now bundled into DataStax distributions/downloads; no separate download is required as was the case previously. You’ll find it in the core bin directories of both the community Apache and DataStax Community Edition download. For DataStax Enterprise, it’ll be in the <install directory>/resources/cassandra/bin directory.

If you’re downloading Apache drops of Cassandra, the CQL shell is in the tarball build, but not the Debian or RPM builds at the current time. And note that if you do get the Apache tarball, you’ll still need to install the Python CQL driver for it to work. But again, if you get the DataStax builds, you won’t have any hoops to jump through to use the CQL utility.

When you log into the CQL shell, you’ll now get nice info up front on the versions you’re using and some cluster info:

robinmac:dse-1.0.1 robin$ ./resources/cassandra/bin/cqlsh

Connected to Test Cluster at localhost:9160.

[cqlsh 2.0.0 | Cassandra 1.0.5 | CQL spec 2.0.0 | Thrift protocol 19.19.0]

Use HELP for help.

cqlsh>

Next, as the last line in the output above says, you now have built-in help for the CQL shell, which is a very welcome addition!

cqlsh> help

Documented commands (type help <topic>):

========================================

ASSUME  DESCRIBE  EXIT  HELP  SELECT  SHOW  USE

Miscellaneous help topics:

==========================

DROP_INDEX                 DROP_KEYSPACE        UPDATE_USING

ALTER_DROP                 TYPES                UPDATE_WHERE

SELECT_EXPR                SELECT_COLUMNFAMILY  CONSISTENCYLEVEL

DELETE_COLUMNS             DROP_COLUMNFAMILY    CREATE_INDEX

ALTER_ADD                  BEGIN                CREATE_KEYSPACE

APPLY                      UPDATE_SET           DROP

DELETE_USING               UPDATE_COUNTERS      ALTER

CREATE_COLUMNFAMILY_TYPES  TRUNCATE             CREATE_COLUMNFAMILY_OPTIONS

SELECT_WHERE               UPDATE               CREATE_COLUMNFAMILY

CREATE                     SELECT_LIMIT         DELETE

ALTER_ALTER                DELETE_WHERE

The help contains the necessary syntax diagrams as well as examples of the commands in action. Having help in the shell itself is a nice productivity aid for sure.

If you have multiple keyspaces in a Cassandra cluster, then you’ll likely appreciate how the CQL utility now changes the prompt for you when you switch to a keyspace so you won’t have to wonder which one you’re connected to:

cqlsh> use hr;

cqlsh:hr>

You’ll notice some new supported commands in the help text above. The first is ASSUME, and it works the same way in the CQL shell as it does in the CLI utility. It allows the CQL shell to deserialize data from a given column family (e.g. column names, column values, or column values for a particular column) as though it was a certain type, even if the Cassandra metadata doesn't agree.

The DESCRIBE command works for an entire cluster’s schema, a particular keyspace, or a particular column family, and outputs the syntax for the particular object in question:

cqlsh:hr> describe columnfamily emp;

CREATE COLUMNFAMILY emp (

empid int PRIMARY KEY,

empname text

) WITH

comment='' AND

comparator=text AND

row_cache_provider='ConcurrentLinkedHashCacheProvider' AND

key_cache_size=200000.000000 AND

row_cache_size=0.000000 AND

read_repair_chance=0.100000 AND

gc_grace_seconds=864000 AND

default_validation=text AND

min_compaction_threshold=4 AND

max_compaction_threshold=32 AND

row_cache_save_period_in_seconds=0 AND

key_cache_save_period_in_seconds=14400;

The SHOW command provides metadata feedback on things like cluster name, version, (which is shown when you first log into CQL):

cqlsh:hr> show version

[cqlsh 2.0.0 | Cassandra 1.0.5 | CQL spec 2.0.0 | Thrift protocol 19.19.0]

cqlsh:hr> show host

Connected to Test Cluster at localhost:9160.

Outside of the newly supported commands, the new CQL utility also has a first step toward auto-completion for keywords and commands. Much in the same way as you can tab in a Linux command prompt and it will auto-fill in what it believes you want, the CQL utility will do the same. Note that on some platforms (e.g. Mac) auto-complete may not work as it relies on a Python lib that may not be present by default.

One last minor thing to call out is that the CQL utility offers some syntax highlighting, with query results (column names and data) having different colors than various CQL commands.

Conclusions

The direction in which the Cassandra community seems to be heading is one where CQL will be the primary interface into the database. That being the case, it’s good to see the new enhancements in the CQL utility that makes working with CQL even easier than it was before.

Download an updated copy of Cassandra in either DataStax Community or Enterprise and let us know what you think. And thanks for your continued support of Apache Cassandra and DataStax.