Apache Cassandra 1.2 Documentation

Getting started using CQL

Cassandra Query Language (CQL) is a SQL (Structured Query Language)-like language for querying Cassandra. Although CQL has many similarities to SQL, there are some fundamental differences. CQL doesn't support joins, which make no sense in Cassandra. Cassandra is a distributed store, so joins are too expensive to perform on distributed data over many machines. Joins require expensive random reads, which need to be merged across the network, dramatically increasing the overhead involved. Instead of joins, Cassandra promotes collocating data accessed together through denormalization and the ordering provided by the Cassandra storage engine. Using this kind of model, the Cassandra DBMS can handle up to two billion columns per row.

CQL 3 is the default and primary interface into the Cassandra DBMS. CQL 3 provides a new API to Cassandra that is simpler than the Thrift API for new applications. The Thrift API, the Cassandra Command Line Interface (CLI), and legacy versions of CQL expose the internal storage structure of Cassandra. CQL 3 adds an abstraction layer that hides implementation details and provides native syntaxes for CQL 3 collections and other common encodings. For more information about backward compatibility and working with database objects created outside of CQL 3, see Working with pre-CQL 3 applications.

Note

DataStax Enterprise 3.0.x supports CQL 3 in Beta form. Users need to refer to Cassandra 1.1 documentation for CQL information.

Activating CQL 3

You activate the CQL mode in one of these ways:

  • Use the DataStax Java Driver to activate CQL through the native/binary protocol.

  • Start cqlsh, a Python-based command-line client.

  • Use the set_sql_version Thrift method.

  • Specify the desired CQL mode in the connect() call to the Python driver:

    connection = cql.connect('localhost:9160', cql_version='3.0')

CQL 3 supports compound keys and clustering. Also, super columns are not supported by either CQL version; column_type and subcomparator arguments are not valid.

Running CQL

Developers can access CQL commands in a variety of ways. Drivers are available for Python, PHP, Ruby, Node.js, and JDBC-based client programs. For the purposes of administrators, cqlsh is the most direct way to run simple CQL commands. Using cqlsh, you can run CQL commands from the command line. The location of cqlsh is <install_location>/bin for tarball installations, or /usr/bin for packaged installations.

When you start cqlsh, you can provide the IP address of a Cassandra node to connect to. The default is localhost. You can also provide the RPC connection port (default is 9160), and the cql specification number.

Starting cqlsh using the CQL 3 mode

If you use security features, provide a user name and password to start cqlsh.

Linux

To start cqlsh using the default CQL 3 mode from the Cassandra bin directory on Linux:

./cqlsh

The default user name and password are cassandra/cassandra.

To start cqlsh on a different node, specify the IP address and port:

./cqlsh 1.2.3.4 9160

Windows

To start cqlsh using the default CQL 3 mode from the Cassandra bin directory on Windows, in Command Prompt:

python cqlsh

To start cqlsh on a different node, specify the IP address and port:

python cqlsh 1.2.3.4 9160

Linux and Windows

To exit cqlsh:

cqlsh> exit

CQL syntax is SQL-like. To query the Cassandra database, follow steps in Querying Cassandra. CQL commands are described in detail in the CQL Reference.

Using tab completion to enter commands

You can use tab completion to see hints about cqlsh commands. Some platforms, such as Mac OSX, do not ship with tab completion installed. You can use easy_install to install tab completion capabilities on Mac OSX:

easy_install readline