Robin Schumacher

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&nbsp;CQL language,&nbsp;CQL application drivers, and the&nbsp;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.

<h3>And Away We Go</h3>

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 &lt;install directory&gt;/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:

<pre>
robinmac:dse-1.0.1 robin$ ./resources/cassandra/bin/cqlsh</pre>

<pre>
Connected to Test Cluster at localhost:9160.</pre>

<pre>
[cqlsh 2.0.0 | Cassandra 1.0.5 | CQL spec 2.0.0 | Thrift protocol 19.19.0]</pre>

<pre>
Use HELP for help.</pre>

<pre>
cqlsh&gt;</pre>

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!

<pre>
cqlsh&gt; help</pre>

<pre>
Documented commands (type help &lt;topic&gt;):</pre>

<pre>
========================================</pre>

<pre>
ASSUME&nbsp; DESCRIBE&nbsp; EXIT&nbsp; HELP&nbsp; SELECT&nbsp; SHOW&nbsp; USE</pre>

Miscellaneous help topics:

<pre>
==========================</pre>

<pre>
DROP_INDEX&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DROP_KEYSPACE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; UPDATE_USING</pre>

<pre>
ALTER_DROP&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TYPES&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; UPDATE_WHERE</pre>

<pre>
SELECT_EXPR&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT_COLUMNFAMILY&nbsp; CONSISTENCYLEVEL</pre>

<pre>
DELETE_COLUMNS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DROP_COLUMNFAMILY&nbsp;&nbsp;&nbsp; CREATE_INDEX</pre>

<pre>
ALTER_ADD&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BEGIN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CREATE_KEYSPACE</pre>

<pre>
APPLY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; UPDATE_SET&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DROP</pre>

<pre>
DELETE_USING&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; UPDATE_COUNTERS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ALTER</pre>

<pre>
CREATE_COLUMNFAMILY_TYPES&nbsp; TRUNCATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CREATE_COLUMNFAMILY_OPTIONS</pre>

<pre>
SELECT_WHERE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; UPDATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CREATE_COLUMNFAMILY</pre>

<pre>
CREATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT_LIMIT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DELETE</pre>

<pre>
ALTER_ALTER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DELETE_WHERE</pre>

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:

<pre>
cqlsh&gt; use hr;</pre>

<pre>
cqlsh:hr&gt;</pre>

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:

<pre>
cqlsh:hr&gt; describe columnfamily emp;</pre>

<pre>
CREATE COLUMNFAMILY emp (</pre>

<pre>
empid int PRIMARY KEY,</pre>

<pre>
empname text</pre>

<pre>
) WITH</pre>

<pre>
comment='' AND</pre>

<pre>
comparator=text AND</pre>

<pre>
row_cache_provider='ConcurrentLinkedHashCacheProvider' AND</pre>

<pre>
key_cache_size=200000.000000 AND</pre>

<pre>
row_cache_size=0.000000 AND</pre>

<pre>
read_repair_chance=0.100000 AND</pre>

<pre>
gc_grace_seconds=864000 AND</pre>

<pre>
default_validation=text AND</pre>

<pre>
min_compaction_threshold=4 AND</pre>

<pre>
max_compaction_threshold=32 AND</pre>

<pre>
row_cache_save_period_in_seconds=0 AND</pre>

<pre>
key_cache_save_period_in_seconds=14400;</pre>

The SHOW command provides metadata feedback on things like cluster name, version, (which is shown when you first log into CQL):

<pre>
cqlsh:hr&gt; show version</pre>

<pre>
[cqlsh 2.0.0 | Cassandra 1.0.5 | CQL spec 2.0.0 | Thrift protocol 19.19.0]</pre>

<pre>
cqlsh:hr&gt; show host</pre>

<pre>
Connected to Test Cluster at localhost:9160.</pre>

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&nbsp;an updated copy of Cassandra in either&nbsp;DataStax Community&nbsp;or&nbsp;Enterprise&nbsp;and let us know what you think. And thanks for your continued support of Apache Cassandra and DataStax.

Build and scale cloud native apps

Discover the benefits of DBaaS and why your apps deserve an upgrade.

Learn about NoSQL Databases

Learn about NoSQL databases with Apache Cassandra and Astra DB.

2022 Recap: A Year for Customers, Community, and Real-Time Data

Highlights from 2022 and a glimpse into the year ahead.

ViewingTechnical How To’s

Test driving the new CQL utility

Sign up for our Developer Newsletter