Getting Started with CQL¶

Installing Thrift¶

All CQL drivers require Thrift 0.6. If you run CQL commands on a Cassandra 0.8 node, the required Thrift jar files are already present in the environment. However, to run CQL commands from a remote machine, you must install Thrift 0.6 as described in the Cassandra Wiki.

Installing the Python Driver for CQL¶

Installation of the Python driver and cqlsh is facilitated by a setup program. The setup program and driver require Python 2.6 or 2.7 – 3.x versions are not supported.

The python driver is available as a separate package at CQL Drivers and Client Libraries, and also in the Cassandra source distribution.

To install the Python Driver for CQL:

1. Copy or unpack the /drivers/py folder and all its contents to the machine where you want to run CQL commands.
2. From the /drivers/py directory, run the setup program setup.py from a Python prompt. For example:

[test-dev cql-1.0.0]$ sudo python2.6 setup.py install
[sudo] password for testuser:
running install
running build
running build_py
...
changing mode of /usr/bin/cqlsh to 755
running install_egg_info
Writing /usr/lib/python2.6/site-packages/cql-1.0.0-py2.6.egg-info
[test-dev cql-1.0.0]$

3. To start cqlsh, provide a valid Cassandra node host and RPC port. For example:

[test-dev ~]$ cqlsh 103.263.89.126 9160
cqlsh>

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 3 and implementation of the NetworkTopologyStrategy 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 = 'NetworkTopologyStrategy'
       AND strategy_options:DC1 = 3;

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 programatically, 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. 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');
cqlsh> select * from users where KEY='jsmith';
u'jsmith' | u'password',u'ch@ngem3a'

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 column, 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;