Sylvain Lebresne

<h1>On the evolutions of CQL3</h1>

Two previous blog posts (&nbsp;<a href="https://www.datastax.com/dev/blog/schema-in-cassandra-1-1">the evolution of schema in Cassandra</a>&nbsp;and&nbsp;<a href="https://www.datastax.com/dev/blog/whats-new-in-cql-3-0">what's New in CQL 3.0</a>) have explained the main changes in CQL3: the support of composites and the more natural support for wide rows (e.g. using transparent transposition). However, while those are the primary changes, they are by no mean the only ones that CQL3 brings to the table and this post will describe a number of those smaller changes.

But before starting, let me add that a good part of the features we will describe here have been finalized after the 1.1.0 release and so will only be present in the upcoming Cassandra 1.1.1 minor release. And in particular Cassandra 1.1.1 will include numerous bug fixes for CQL3.

This is also a good time for a reminder that currently, and for the remaining of Cassandra 1.1, CQL3 is considered beta. By that we mean that while most of the language is settled, we want to use the time until Cassandra 1.2 to finalize and improve it. So while we will not break CQL3 in any major way, we reserve the right to make small breaking changes if this will significantly improve the language.

<h2>Case sensitivity</h2>

The CQL2 story with respect to case sensitivity was a bit fuzzy. In CQL2, the&nbsp;<tt>PRIMARY KEY</tt>&nbsp;was case insensitive, but all other column identifiers where case sensitive. While not a big concern, this was inconsistent. So we have cleaned that up in CQL3. In CQL3, identifiers (keyspace/table/columns names) are always case insensitive by default. However, one can force the case by using double-quotes. In other words, if you define: 
 
<code>&nbsp;&nbsp;&nbsp;&nbsp;CREATE TABLE test ( 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Foo int PRIMARY KEY, 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"Bar" int 
&nbsp;&nbsp;&nbsp;&nbsp;)</code> 
 
then the following queries reference the defined columns: 
 
<code>&nbsp;&nbsp;&nbsp;&nbsp;SELECT foo FROM ... 
&nbsp;&nbsp;&nbsp;&nbsp;SELECT Foo FROM ... 
&nbsp;&nbsp;&nbsp;&nbsp;SELECT FOO FROM ... 
&nbsp;&nbsp;&nbsp;&nbsp;SELECT "foo" FROM ... // we lowercase everything internally 
&nbsp;&nbsp;&nbsp;&nbsp;SELECT "Bar" FROM ...</code> 
 
but the following ones do not: 
 
<code>&nbsp;&nbsp;&nbsp;&nbsp;SELECT "Foo" FROM ... 
&nbsp;&nbsp;&nbsp;&nbsp;SELECT "BAR" FROM ... 
&nbsp;&nbsp;&nbsp;&nbsp;SELECT bar FROM ...</code>

<h2>Exclusive bounds</h2>

CQL2 had no support for query with exclusive bounds. Quoting the CQL2 documentation:

<blockquote>
The greater-than and less-than operators (&gt; and &lt;) result in key ranges that are inclusive of the terms. There is no supported notion of "strictly" greater-than or less-than; these operators are merely supported as aliases to &gt;= and &lt;=.
</blockquote>

This is not the case anymore in CQL3, where strict bounds are respected. So say you have: 
 
<code>&nbsp;&nbsp;&nbsp;&nbsp;cqlsh:ks&gt;&nbsp;CREATE&nbsp;TABLE&nbsp;scores&nbsp;( 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;...&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;name&nbsp;text, 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;...&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;score&nbsp;int, 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;...&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;date&nbsp;timestamp, 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;...&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PRIMARY&nbsp;KEY&nbsp;(name,&nbsp;score) 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;...&nbsp;); 
&nbsp;&nbsp;&nbsp;&nbsp;ks&gt;&nbsp;INSERT&nbsp;INTO&nbsp;scores&nbsp;(name,&nbsp;score,&nbsp;date)&nbsp;VALUES&nbsp;('bob',&nbsp;42,&nbsp;'2011-06-24'); 
&nbsp;&nbsp;&nbsp;&nbsp;ks&gt;&nbsp;INSERT&nbsp;INTO&nbsp;scores&nbsp;(name,&nbsp;score,&nbsp;date)&nbsp;VALUES&nbsp;('bob',&nbsp;47,&nbsp;'2011-06-25'); 
&nbsp;&nbsp;&nbsp;&nbsp;ks&gt;&nbsp;INSERT&nbsp;INTO&nbsp;scores&nbsp;(name,&nbsp;score,&nbsp;date)&nbsp;VALUES&nbsp;('bob',&nbsp;33,&nbsp;'2011-06-26'); 
&nbsp;&nbsp;&nbsp;&nbsp;ks&gt;&nbsp;INSERT&nbsp;INTO&nbsp;scores&nbsp;(name,&nbsp;score,&nbsp;date)&nbsp;VALUES&nbsp;('bob',&nbsp;40,&nbsp;'2011-06-27');</code> 
 
then you will get: 
 
<code>&nbsp;&nbsp;&nbsp;&nbsp;cqlsh:ks&gt;&nbsp;SELECT&nbsp;date,&nbsp;score&nbsp;FROM&nbsp;scores&nbsp;WHERE&nbsp;name='bob'&nbsp;AND&nbsp;score&nbsp;&gt;=&nbsp;40; 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;score 
&nbsp;&nbsp;&nbsp;&nbsp;--------------------------+------- 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2011-06-27&nbsp;00:00:00+0000&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;40 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2011-06-24&nbsp;00:00:00+0000&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;42 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2011-06-25&nbsp;00:00:00+0000&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;47</code> 
 
but as expected: 
 
<code>&nbsp;&nbsp;&nbsp;&nbsp;cqlsh:ks&gt;&nbsp;SELECT&nbsp;date,&nbsp;score&nbsp;FROM&nbsp;scores&nbsp;WHERE&nbsp;name='bob'&nbsp;AND&nbsp;score&nbsp;&gt;&nbsp;40; 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;score 
&nbsp;&nbsp;&nbsp;&nbsp;--------------------------+------- 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2011-06-24&nbsp;00:00:00+0000&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;42 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2011-06-25&nbsp;00:00:00+0000&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;47</code>

<h2><tt>timeuuid</tt>&nbsp;support</h2>

<a href="http://en.wikipedia.org/wiki/Universally_unique_identifier">UUID</a>&nbsp;are an often used tool in Cassandra. Indeed, in a fully distributed environment, the cost of having an&nbsp;<a href="http://www.w3schools.com/sql/sql_autoincrement.asp">auto-increment field</a>&nbsp;to achieve unique identifiers would be prohibitive and UUID are a good replacement.

But another common use case is for time series. If you insert data keyed by their insertion time, you usually want to avoid collision if two events have the exact same time (for your time precision). An easy way to ensure this is to use&nbsp;<a href="http://en.wikipedia.org/wiki/Universally_unique_identifier#Version_1_.28MAC_address.29">Type 1 UUID</a>.

All this was already possible in CQL2 through the&nbsp;<tt>uuid</tt>&nbsp;type, that accepts any type of UUID and sorts type 1 UUID according to their embedded timestamp. However, when you use a type 1 UUID in a time series, the important information is that this is a collision-free timestamp. And using&nbsp;<tt>uuid</tt>&nbsp;doesn't carry this intention. For that reason, we've introduced in CQL3 the&nbsp;<tt>timeuuid</tt>&nbsp;type (that uses the existing thrift comparator&nbsp;<tt>TimeUUIDType</tt>&nbsp;underneath). This type only accepts type 1 UUID (to avoid mistakenly inserting UUID that don't represent a time) and allows using the CQL date syntax (e.g. '2011-06-24 11:05:42') to input them.

<h2>Direct query of system tables</h2>

The thrift API has the&nbsp;<tt>describe_keyspaces</tt>&nbsp;function to describe the cluster schema. However, so far CQL had no such facilities (<tt>cqlsh</tt>&nbsp;circumvents that by using the thrift call underneath). However, thanks to the&nbsp;<a href="https://www.datastax.com/dev/blog/the-schema-management-renaissance">ameliorations made to internal schema management</a>&nbsp;in Cassandra 1.1 and the support of composites, it is now possible to query the system tables directly in CQL3. For instance, one can query the defined keyspaces using: 
 
<code>&nbsp;&nbsp;&nbsp;&nbsp;cqlsh:ks&gt;&nbsp;SELECT&nbsp;*&nbsp;FROM&nbsp;system.schema_keyspaces; 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;keyspace&nbsp;|&nbsp;durable_writes&nbsp;|&nbsp;name&nbsp;|&nbsp;strategy_class&nbsp;|&nbsp;strategy_options 
&nbsp;&nbsp;&nbsp;&nbsp;----------+----------------+------+----------------+---------------------------- 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ks&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;True&nbsp;|&nbsp;&nbsp;&nbsp;ks&nbsp;|&nbsp;SimpleStrategy&nbsp;|&nbsp;{"replication_factor":"1"}</code> 
 
Columns families information can as easily be retrieved by querying&nbsp;<tt>system.schema_columnfamilies</tt>&nbsp;and columns metadata through&nbsp;<tt>system.schema_columns</tt>.

<h2>Paging through non-ordered partitioner results</h2>

When using the&nbsp;<tt>RandomPartitioner</tt>, Cassandra rows are ordered by the md5 of their value and hence the order of rows is not meaningful. Despite that fact, given the following definition: 
 
<code>&nbsp;&nbsp;&nbsp;&nbsp;CREATE TABLE test ( 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;k int PRIMARY KEY, 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;v1 int, 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;v2 int 
&nbsp;&nbsp;&nbsp;&nbsp;)</code> 
 
and assumming&nbsp;<tt>RandomPartitioner</tt>, CQL2 allowed queries like: 
 
<code>&nbsp;&nbsp;&nbsp;&nbsp;SELECT * FROM test WHERE k &gt; 42;</code> 
 
But the semantics of such queries was to query all rows for which the md5 of their key was bigger than the md5 of&nbsp;<tt>42</tt>. And so this query would return results where&nbsp;<tt>k ≤ 42</tt>, which is unintuitive. To avoid that, CQL3 now forbids such query (though of course the query is allowed if the partitioner in use is ordered).

That being said, even with the random partitioner, it can sometimes be of use to page through all rows. In other words, it can be useful to "query all rows for which the md5 of their key is bigger than the md5 of&nbsp;<tt>42</tt>". It is simply that the syntax "<tt>WHERE k &gt; 42</tt>" is not appropriate. So we have added a new function&nbsp;<tt>token</tt>&nbsp;to allow doing just that and the following is allowed: 
 
<code>&nbsp;&nbsp;&nbsp;&nbsp;SELECT * FROM test WHERE token(k) &gt; token(42);</code>

<h2>The end of "range ghosts"</h2>

With the thrift API,&nbsp;<tt>get_range_slices</tt>&nbsp;queries may sometimes return empty rows, a phenomenon known as&nbsp;range ghosts(see&nbsp;<a href="http://wiki.apache.org/cassandra/FAQ#range_ghosts">this FAQ entry</a>&nbsp;for more details). This is often confusing for new users and range ghosts are rarely useful since one cannot tell (without doing an additional query) whether getting a range ghost means that the row exists but had no columns matching the query, or does not exist at all. Furthermore, CQL2 perpetuates that range ghost problem.

But the abstraction of transposing wide rows that CQL3 provides makes this problem much easier to tackle. By definition, a CQL3 row (i.e. a row in the SQL sense) exists only if it has at least one column set outside the&nbsp;<tt>PRIMARY KEY</tt>&nbsp;ones. And a query will return only those existing rows that match the&nbsp;<tt>WHERE</tt>&nbsp;clause and no ghost will be returned anymore.

<h2><tt>CLUSTERING ORDER</tt></h2>

As explained in&nbsp;<a href="https://www.datastax.com/dev/blog/whats-new-in-cql-3-0">this previous post</a>, CQL3 allows to order query results in some cases, making use of the on-disk sorting of columns. When allowed, you can order results in ascending or descending order. By default, the ascending ordering will be slightly more efficient that the descending order. However some model have that property that most of the queries will be interested by the descending order. An example is a time series where the main query is to return the 10 more recent elements, which amounts do something along the line of: 
 
<code>&nbsp;&nbsp;&nbsp;&nbsp;SELECT * FROM timeseries WHERE event_type='...' 
&nbsp;&nbsp;&nbsp;&nbsp;ORDER BY insertion_time DESC LIMIT 10;</code> 
 
In those cases, CQL3 allows you to specify a&nbsp;<tt>CLUSTERING ORDER</tt>&nbsp;options at table creation. This is a performance hint for Cassandra: it won't change in any way the table definition, but underneath columns will be stored on disk in the reverse order of what they would be if the default&nbsp;<tt>CLUSTERING ORDER</tt>&nbsp;was used. As a consequence, descending queries will be faster than the ascending ones. For instance, for a table where the more performance sensive query is the one above, the definition would look like: 
 
<code>&nbsp;&nbsp;&nbsp;&nbsp;CREATE TABLE timeseries ( 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;event_type text, 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;insertion_time timestamp, 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;event blob, 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PRIMARY KEY (event_type, insertion_time) 
&nbsp;&nbsp;&nbsp;&nbsp;) WITH CLUSTERING ORDER BY (insertion_time DESC);</code>

<h2>Conclusion</h2>

On top of the "big" feature like composite types, CQL3 brings a lot of language cleanups and additional features, which we really think make the language more useful and more easy to apprehend, especially for beginners. Yet that is not the end of the road and there is a lot of features in the making, including amongst other:

<ul>
	<li>Binary protocol for CQL (<a href="https://issues.apache.org/jira/browse/CASSANDRA-2478">CASSANDRA-2478</a>): currently CQL still uses thrift as a transport. This is not as efficient as can be however and this limit a number of functionality that we would like to add, so we will add a custom made binary protocol (More on that to come soon).</li>
	<li>Access to the column timestamp and ttl (<a href="https://issues.apache.org/jira/browse/CASSANDRA-4217">CASSANDRA-4217</a>): while the timestamp and ttl of columns is currently returned in the result set, SQL-oriented drivers have not way to expose it. So we plan on adding new CQL functions to retrieve those values.</li>
	<li>Filtering null values (<a href="https://issues.apache.org/jira/browse/CASSANDRA-3783">CASSANDRA-3783</a>)</li>
	<li>Support for composite secondary indexes (<a href="https://issues.apache.org/jira/browse/CASSANDRA-3680">CASSANDRA-3680</a>)</li>
	<li>And much more ...</li>
</ul>

Lastly, note that a draft for the documentation of CQL3 has been posted on&nbsp;<a href="https://issues.apache.org/jira/browse/CASSANDRA-3779">CASSANDRA-3779</a>&nbsp;and should be publicly available soon (I'll update this post when that is the case.).

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

On the evolutions of CQL3

Sign up for our Developer Newsletter