Apache Cassandra 1.2 Documentation

Working with pre-CQL 3 applications

Internally, CQL 3 does not change the row and column mapping from the Thift API mapping. CQL 3 and Thrift use the same storage engine. CQL 3 supports the same query-driven, denormalized data modeling principles as Thrift. Existing applications do not have to be upgraded to CQL 3. The CQL 3 abstraction layer makes CQL 3 easier to use for new applications. For an in-depth comparison of Thrift and CQL 3, see A Thrift to CQL 3 Upgrade Guide and CQL 3 for Cassandra experts.

Creating legacy tables

You can create Thrift/CLI-compatible tables in CQL 3 using the COMPACT STORAGE directive. The compact storage directive used with the CREATE TABLE command provides backward compatibility with older Cassandra applications; new applications should generally avoid it.

Compact storage stores an entire row in a single column on disk instead of storing each non-primary key column in a column that corresponds to one column on disk. Using compact storage prevents you from adding new columns that are not part of the PRIMARY KEY.

Querying a legacy table

You can use the CLI GET command to query tables created with or without the COMPACT STORAGE directive in CQL 3 or created with the Thrift API, CLI, or early version of CQL.

Using CQL 3, you can query a legacy table. A legacy table managed in CQL 3 includes an implicit WITH COMPACT STORAGE directive.

Using the CLI GET command

Continuing with the music service example, select all the columns in the playlists table that was created in CQL 3. This output appears:

[default@music] GET playlists [62c36092-82a1-3a00-93d1-46196ee77204];
  => (column=7db1a490-5878-11e2-bcfd-0800200c9a66:,
    value=, timestamp=1357602286168000)
  => (column=7db1a490-5878-11e2-bcfd-0800200c9a66:album,
    value=4e6f204f6e6520526964657320666f722046726565, timestamp=1357602286168000)
.
.
.
  => (column=a3e64f8f-bd44-4f28-b8d9-6938726e34d4:title,
    value=4c61204772616e6765, timestamp=1357599350478000)
Returned 16 results.

The output of cell values is unreadable because GET returns the values in byte format.

Using a CQL 3 query

A CQL 3 query resembles a SQL query to a greater extent than a CQL 2 query. CQL 3 no longer uses the REVERSE keyword, for example.

In the example GET command and in CLI output in general, there is a timestamp value that doesn't appear in the CQL 3 output.

This timestamp represents the date/time that a write occurred to a columns. In CQL 3, you use WRITETIME in the select expression to get this timestamp. For example, to get the date/times that a write occurred to the body column:

SELECT WRITETIME (title)
  FROM songs
  WHERE id = 8a172618-b121-4136-bb10-f665cfc469eb;

 writetime(title)
------------------
 1353890782373000

The output in microseconds shows the write time of the data in the title column of the songs table.

When you use CQL 3 to query legacy tables with no column names defined for data within a partition, CQL 3 generates the names (column1 and value1) for the data. Using the CQL RENAME clause, you can change the default name to a more meaningful name.

ALTER TABLE users RENAME key to user_id;

CQL 3 supports dynamic tables created in the Thrift API, CLI, and earlier CQL versions. For example, a dynamic table is represented and queried like this in CQL 3:

CREATE TABLE clicks (
  userid uuid,
  url text,
  timestamp date
  PRIMARY KEY (userid, url)
) WITH COMPACT STORAGE;

SELECT url, timestamp
  FROM clicks
  WHERE userid = 148e9150-1dd2-11b2-0000-242d50cf1fff;

SELECT timestamp
  FROM clicks
  WHERE userid = 148e9150-1dd2-11b2-0000-242d50cf1fff
  AND url = 'http://google.com';

In these queries, only equality conditions are valid.

Super columns not supported

CQL does not support super columns. For new projects, use compound keys and collections instead. For old projects, the Thrift supercolumn API will remain supported.

Starting CQLsh using the CQL 2 mode

CQL 2 supports dynamic columns, but not compound primary keys. To start cqlsh 2.0.0 using the legacy CQL 2 mode from the Cassandra bin directory on Linux, for example:

./cqlsh -cql2

Or on Windows, from the Cassandra bin directory in Command Prompt:

python cqlsh -cql2