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.
