Cql and cql3

Important Note - default consistency level for CQL3 queries

Note that the default consistency level for CQL3 is CL_ONE. You can configure the consistency level using the API provided to override this behavior.

Basic CQL

CQL is the Cassandra equivalent to SQL but only supports a small subset of the SQL syntax.

Query for row data from cassandra. Notice that this query always returns a list of Rows even if the query is for a specific column.

try {
	OperationResult<CqlResult<String, String>> result
		= keyspace.prepareQuery(CF_STANDARD1)
			.withCql("SELECT * FROM Standard1;")
			.execute();
	for (Row<String, String> row : result.getResult().getRows()) {
	}
} catch (ConnectionException e) {
}

Query for count of columns

try {
	OperationResult<CqlResult<String, String>> result
		= keyspace.prepareQuery(CF_STANDARD1)
			.withCql("SELECT count(*) FROM Standard1 where KEY='A';")
			.execute();

	System.out.println("CQL Count: " + result.getResult().getNumber());
} catch (ConnectionException e) {
}

CQL3 and compound keys

CQL3 has a nice feature which lets you create a compound primary key consisting of multiple fields. Under the hood cassandra uses the first field of the compound key as the row key with the subsequent fields making up a composite column that has the field name (as a string) as the last part of the composite.

For example,

The following CQL command,

CREATE TABLE employees (empID int, deptID int, 
first_name varchar, last_name varchar, PRIMARY KEY (empID, deptID));

will create the column family 'employees' with key_validation_class=Int32Type and comparator CompositeType(Int32Type,UTF8Type)

The first component of the composite column represents 'deptID' and the second is the column/field name ('first_name' or 'last_name').

So, the following insert command,

INSERT INTO employees (empID, deptID, first_name, last_name) 
VALUES ('111', '222', 'eran', 'landau');

will create two column under row key 111.

<222 : 'first_name'> = 'eran'
<222 : 'last_name' > = 'landau'

When reading the data back Cassandra will hide the compound key implementation and will return a simple response where the row key is the first part of the compound key and each row will contain only the string column names. Note that cassandra will return a separate row for each unique compound key.

So, the following select command,

SELECT * FROM employees WHERE empId='111';

Will return a single row with key='111' and columns

empid=111
deptid=222
first_name=eran
last_name=landau

Notice that although empid and deptid are not real columns in the storage layer cassandra will return them as distinct columns in the response. Also, notice that cassandra stores all column names as lower case.

You can use Astyanax to access a CQL3 row as follows

ColumnFamily definition

The key type needs to match the first part of the compound key and is the actual row key type stored in cassandra. The column name type must be a string.

ColumnFamily<Integer, String> CQL3_CF = ColumnFamily.newColumnFamily(
                    "Cql3CF", 
                    IntegerSerializer.get(), 
                    StringSerializer.get());

Keyspace client configuration

To use compound columns you must set the keyspace client cql version to 3.0.0

withAstyanaxConfiguration(
    new AstyanaxConfigurationImpl()
    ...
         .setCqlVersion("3.0.0")
    ...

If using Cassandra 1.2+, you will need to set the following as well:

withAstyanaxConfiguration(
    new AstyanaxConfigurationImpl()
    ...
         .setTargetCassandraVersion("1.2")
    ...

Create a column family

OperationResult<CqlResult<Integer, String>> result;
result = keyspace
    .prepareQuery(CQL3_CF)
    .withCql("CREATE TABLE employees (empID int, deptID int, first_name varchar, 
              last_name varchar, PRIMARY KEY (empID, deptID));")
    .execute();

Writing data

result = keyspace
        .prepareQuery(CQL3_CF)
        .withCql("INSERT INTO employees (empID, deptID, first_name, last_name) 
                  VALUES ('111', '222', 'eran', 'landau');")
        .execute();

Reading data

result = keyspace
        .prepareQuery(CQL3_CF)
        .withCql("SELECT * FROM employees WHERE empId='111';")
        .execute();

for (Row<Integer, String> row : result.getResult().getRows()) {
    LOG.info("CQL Key: " + row.getKey());

    ColumnList<String> columns = row.getColumns();
    
    LOG.info("   empid      : " + columns.getIntegerValue("empid",      null));
    LOG.info("   deptid     : " + columns.getIntegerValue("deptid",     null));
    LOG.info("   first_name : " + columns.getStringValue ("first_name", null));
    LOG.info("   last_name  : " + columns.getStringValue ("last_name",  null));
}        

Prepared CQL

Support for prepared CQL statements builds on top of the existing CQL API. Simply provide a parameterized CQL statement to withCql() and then specify parameter values by chaining .asPreparedStatement(). Note that parameter types aren't checked in the client and values must be provided in the correct order.

Astyanax will internally track prepared CQL statement id's on each connection by maintaining a map between the CQL statement and the ID returned from cassandra. This makes it possible to support prepared CQL when using a connection pool. There may be additional latency when a CQL statement is first used on a connection since it requires an additional call to Cassandra.

final String INSERT_STATEMENT = "INSERT INTO employees (empID, deptID, first_name, last_name) VALUES (?, ?, ?, ?);";

result = keyspace
        .prepareQuery(CQL3_CF)
            .withCql(INSERT_STATEMENT)
        .asPreparedStatement()
            .withIntegerValue(222)
            .withIntegerValue(333)
            .withStringValue("Eric")
            .withStringValue("Cartman")
        .execute();

Gotchas

• You cannot define dynamic column names. All columns must be defined in the schema.