Developers can access CQL commands in a variety of ways. Drivers are available for Python, Twisted Python, and Java-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. This page contains details on how to set up the Python driver and get started using cqlsh.
Installation files for the CQL drivers are available in the /drivers directory of the Cassandra source distribution (they are not available in the binary release). Alternately, you can download an archive of the required resources from the Apache Cassandra project and unpack them on the host.
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.
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:
[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]$
[test-dev ~]$ cqlsh 103.263.89.126 9160 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 Command Reference.
As of version 0.8, cqlsh has the following limitations in support for Cassandra operations and data objects:
Also, cqlsh does not support some of the specific user operations that are available with the Cassandra CLI. These operations include the following:
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.
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;
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);
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'
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'
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.
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;