Apache Cassandra 1.0 Documentation

Getting Started with CQL

This document corresponds to an earlier product version. Make sure you are using the version that corresponds to your version.

Latest Cassandra documentation | Earlier Cassandra documentation

Developers can access CQL commands in a variety of ways. Drivers are available for Python, Twisted Python, and JDBC-based client programs.

For the purposes of administrators, the most direct way to run simple CQL commands is via the Python-based cqlsh command-line client.

Starting the CQL Command-Line Program (cqlsh)

As of DataStax Community version 1.0.1, the cqlsh client is installed with Cassandra in <install_location>/bin for tarball installations, or /usr/bin for packaged installations.

When you start cqlsh, you must provide the IP of a Cassandra node to connect to (default is localhost) and the RPC connection port (default is 9160). For example:

$ cqlsh 103.263.89.126 9160
cqlsh>

To exit cqlsh type exit at the command prompt.

cqlsh> exit

Running CQL Commands with cqlsh

Commands in cqlsh combine SQL-like syntax that maps to Cassandra concepts and operations. If you are just getting started with CQL, make sure to refer to the CQL Reference.

As of CQL version 2.0, cqlsh has the following limitations in support for Cassandra operations and data objects:

  • Super Columns are not supported; column_type and subcomparator arguments are not valid
  • Composite columns are not supported
  • Only a subset of all the available column family storage properties can be set using CQL.

The rest of this section provides some guidance with simple CQL commands using cqlsh. This is a similar (but not identical) set of commands as the set described in Using the Cassandra Client.

Creating a Keyspace

You can use the cqlsh commands described in this section to create a keyspace. In creating an example keyspace for Twissandra, we will assume a desired replication factor of 1 and implementation of the SimpleStrategy replica placement strategy. For more information on these keyspace options, see About Replication in Cassandra.

Note the single quotes around the string value of strategy_class:

cqlsh> CREATE KEYSPACE twissandra WITH
     strategy_class = 'SimpleStrategy'
     AND strategy_options:replication_factor = '1';

Creating a Column Family

For this example, we use cqlsh to create a users column family in the newly created keyspace. Note the USE command to connect to the twissandra keyspace.

cqlsh> USE twissandra;

cqlsh> CREATE COLUMNFAMILY users (
 ...  KEY varchar PRIMARY KEY,
 ...  password varchar,
 ...  gender varchar,
 ...  session_token varchar,
 ...  state varchar,
 ...  birth_year bigint);

Inserting and Retrieving Columns

Though in production scenarios it is more practical to insert columns and column values programmatically, it is possible to use cqlsh for these operations. The example in this section illustrates using the INSERT and SELECT commands to insert and retrieve some columns in the users column family.

The following commands create and then get a user record for "jsmith." The record includes a value for the password column we created when we created the column family, as well as an expiration time for the password column. Note that the user name "jsmith" is the row key, or in CQL terms, the primary key.

cqlsh> INSERT INTO users (KEY, password) VALUES ('jsmith', 'ch@ngem3a') USING TTL 86400;
cqlsh> SELECT * FROM users WHERE KEY='jsmith';
u'jsmith' | u'password',u'ch@ngem3a' | u'ttl', 86400

Adding Columns with ALTER COLUMNFAMILY

The ALTER COLUMNFAMILY command lets you add new columns to a column family. For example, to add a coupon_code column with the varchar validation type to the users column family:

cqlsh> ALTER TABLE users ADD coupon_code varchar;

This creates the column metadata and adds the column to the column family schema, but does not update any existing rows.

Altering Column Metadata

With ALTER COLUMNFAMILY, you can change the type of a column any time after it is defined or added to a column family. For example, if we decided the coupon_code column should store coupon codes in the form of integers, we could change the validation type as follows:

cqlsh> ALTER TABLE users ALTER coupon_code TYPE int;

Note that existing coupon codes will not be validated against the new type, only newly inserted values.

Specifying Column Expiration with TTL

Both the INSERT and UPDATE commands support setting a column expiration time (TTL). In the INSERT example above for the key jsmith we set the password column to expire at 86400 seconds, or one day. If we wanted to extend the expiration period to five days, we could use the UPDATE command a shown:

cqlsh> UPDATE users USING TTL 432000 SET 'password' = 'ch@ngem3a' WHERE KEY = 'jsmith';

Dropping Column Metadata

If your aim is to remove a column's metadata entirely, including the column name and validation type, you can use ALTER TABLE <columnFamily> DROP <column>. The following command removes the name and validator without affecting or deleting any existing data:

cqlsh> ALTER TABLE users DROP coupon_code;

After you run this command, clients can still add new columns named coupon_code to the users column family, but they will not be validated until you explicitly add a type again.

Indexing a Column

cqlsh can be used to create secondary indexes, or indexes on column values. In this example, we will create an index on the state and birth_year columns in the users column family.

cqlsh> CREATE INDEX state_key ON users (state);
cqlsh> CREATE INDEX birth_year_key ON users (birth_year);

Because of the secondary index created for the two columns, their values can be queried directly as follows:

cqlsh> SELECT * FROM users
 ... WHERE gender='f' AND
 ...  state='TX' AND
...  birth_year='1968';
u'user1' | u'birth_year',1968 | u'gender',u'f' | u'password',u'ch@ngem3' | u'state',u'TX'

Deleting Columns and Rows

cqlsh provides the DELETE command to delete a column or row. In this example we will delete user jsmith's session token column, and then delete jsmith's row entirely.

cqlsh> DELETE session_token FROM users where KEY = 'jsmith';
cqlsh> DELETE FROM users where KEY = 'jsmith';

Note, however, that the phenomena called "range ghosts" in Cassandra may mean that keys for deleted rows are still retrieved by SELECT statements and other "get" operations. Deleted values, including range ghosts, are removed completely by the first compaction following deletion.

Dropping Column Families and Keyspaces

With cqlsh commands you can drop column families and keyspaces in much the same way that tables and databases are dropped in relational models. This example shows the commands to drop our example users column family and then drop the twissandra keyspace altogether:

cqlsh> DROP COLUMNFAMILY users;
cqlsh> DROP KEYSPACE twissandra;