Introduction to Cassandra Query Language

Cassandra Query Language (CQL) is a query language for the Apache Cassandra database.

The Cassandra Query Language (CQL) is the primary language for communicating with the Apache Cassandra™ database. The most basic way to interact with Apache Cassandra is using the CQL shell, cqlsh. Using cqlsh, you can create keyspaces and tables, insert and query tables, plus much more. If you prefer a graphical tool, you can use DataStax DevCenter. For production, DataStax supplies a number drivers so that CQL statements can be passed from client to cluster and back. Other administrative tasks can be accomplished using OpsCenter.

Important: This document assumes you are familiar with either the Apache Cassandra 2.1 or Apache Cassandra 2.0 documentation.

Apache Cassandra 2.1 features

Cassandra 2.1 new CQL features include:

Nested user-defined types
Improved counter columns that maintain the correct count when Cassandra replays the commit log
Configurable counter cache
Support for indexes on collections, including using map keys to filter query results
Timestamps precise to the millisecond
The new tuple type that holds fixed-length sets of typed positional fields

The cqlsh utility also has been improved:

Capability to accept and execute a CQL statement from the operating system command line
Support for describing types using the DESCRIBE command

DataStax Java Driver 2.0.0 supports Cassandra 2.1 with limitations. This version of the driver is incompatible with the new features.

Other notable changes:

Cassandra rejects USING TIMESTAMP or USING TTL in the command to update a counter column, and now generates an error message when you attempt such an operation.
In Cassandra 2.1, the CQL table property index_interval is replaced by min_index_interval and max_index_interval. The max_index_interval is 2048 by default. The default would be reached only when SSTables are infrequently-read and the index summary memory pool is full. When upgrading from earlier releases, Cassandra uses the old index_interval value for the min_index_interval.

Cassandra 2.0.x features

Cassandra 2.0.x key features are:

Lightweight transactions using the IF keyword in INSERT and UPDATE statements.
Prevention of application errors by performing conditional tests for the existence of a table, keyspace, or index.
Simply include IF EXISTS or IF NOT EXISTS in DROP or CREATE statements, such as DROP KEYSPACE or CREATE TABLE.
Initial support for triggers that fire events executed in or out of a database cluster.
The ALTER TABLE DROP command, which had been removed in the earlier release.
Column aliases, similar to aliases in RDBMS SQL, in a SELECT statement.
Indexing of the part, partition key or clustering columns, portion of a compound primary key.

DataStax drivers support Cassandra 2.0.

CQL for Cassandra 2.0 deprecated super columns. Cassandra continues to support apps that query super columns, translating super columns on the fly into CQL constructs and results.

Some changes were made to the cqlsh commands in CQL Cassandra 2.0:

The ASSUME command has been removed.
Use the blobAsType and typeAsBlob conversion functions instead of ASSUME.
The COPY command now supports for collections.

Several CQL table attributes were added in CQL included with Cassandra 2.0:

default_time_to_live
memtable_flush_period_in_ms
populate_io_cache_on_flush
speculative_retry