cassandra 1.1.x get by composite key

Question

Is it possible using Hector or Astyanax get rows by composite keys (in multiple columns, not the ones serialized to one column)?

In cqlsh i created simple column family:

CREATE COLUMNFAMILY kkvv (x int, y int, val1 varchar, val2 varchar, PRIMARY KEY (x,y));

According to Cassandra Developer Center the rows are stored by x as a key and rest is stored in columns.

I cant' figure how to get columns slice for given x and y.

Executing cql in hector that cql

cqlQuery.setQuery("select * from kkvv")

gives me rows:

Row(2,ColumnSlice([HColumn(x=2)]))

Row(10,ColumnSlice([HColumn(x=10)]))

and console cqlsh gives:

x | y | val1 | val2

----+-----+-------+-----------

2 | 1 | v1_1 | v2_1

10 | 27 | v1_4b | v2_4b

10 | 91 | v1_4a | v2_4a

Anyone has managed to do that in any cassandra client for java? Can i use thrift for that, or it is cql only feature?

Answer 1

There are two somewhat-different syntaxes at work here: CQL 2 and CQL 3. By default, a Cassandra connection expects CQL 2. CQL 2, though, doesn't understand composite key columnfamilies of the sort you've made here.

So you are apparently correctly using CQL 3 with cqlsh, since it's displaying your columns in a sane way, but you're not using it with Hector. I'm not sure whether Hector or Astyanax even support that yet. The latest release of the cassandra-jdbc driver does, so, if Hector and/or Astyanax use that, then they should work too.

There isn't (and probably won't be) any support in Thrift for treating composite-comparator columnfamilies as tables with multi-component primary keys, the way CQL 3 does it. Use CQL 3 if you want this.

Answer 2

Did you try the CompositeQuery.java example provided in the cassandra-tutorial project?

Also, have you read Introduction to Composite Columns by DataStax?

Answer 3

Good explanation how rows with composite keys are stored in Cassandra is here.

In Astyanax and Hector i noticed funny thing - when a tried to connect - it used CQL2. When i connect to Cassandra with CQL3 with cassandra api (code from example bellow), somewhere was stored this setting, after that Astyanax and Hector used cql3 instead of CQL2. Connections were made as separate executions, so it couldn't be stored on the client side... Someone has any thoughts about it?

CQL version can be set on org.apache.cassandra.thrift.Cassandra.Client with set_cql_version method.

If someone is interested in working example using pure Cassandra api:

import java.io.UnsupportedEncodingException;
import java.nio.ByteBuffer;
import java.util.List;

import org.apache.cassandra.thrift.Cassandra;
import org.apache.cassandra.thrift.Column;
import org.apache.cassandra.thrift.Compression;
import org.apache.cassandra.thrift.CqlResult;
import org.apache.cassandra.thrift.CqlRow;
import org.apache.cassandra.thrift.InvalidRequestException;
import org.apache.cassandra.thrift.SchemaDisagreementException;
import org.apache.cassandra.thrift.TimedOutException;
import org.apache.cassandra.thrift.UnavailableException;
import org.apache.thrift.TException;
import org.apache.thrift.protocol.TBinaryProtocol;
import org.apache.thrift.protocol.TProtocol;
import org.apache.thrift.transport.TFramedTransport;
import org.apache.thrift.transport.TSocket;
import org.apache.thrift.transport.TTransport;

public class KKVVGetter {
    private static Cassandra.Client client;
    private static TTransport       transport;

    public static void main(String[] args) throws UnsupportedEncodingException, InvalidRequestException,
            UnavailableException, TimedOutException, SchemaDisagreementException, TException {

        transport = new TFramedTransport(new TSocket("localhost", 9160));
        TProtocol protocol = new TBinaryProtocol(transport);
        client = new Cassandra.Client(protocol);        
        transport.open();
        client.set_cql_version("3.0.0");

        executeQuery("USE ks_test3");

        show("select x,y,val1,val2 from kkvv where x > 1 and x < 11 and y < 100 and y > 2");

        System.out.println("\n\n*****************************\n\n");

        show("select x,y,val1,val2 from kkvv");

        transport.close();
    }

    private static int toInt(byte[] bytes) {
        int result = 0;
        for (int i = 0; i < 4; i++) {
            result = (result << 4) + (int) bytes[i];
        }
        return result;
    }

    private static CqlResult executeQuery(String query) throws UnsupportedEncodingException, InvalidRequestException,
            UnavailableException, TimedOutException, SchemaDisagreementException, TException {
        return client.execute_cql_query(ByteBuffer.wrap(query.getBytes("UTF-8")), Compression.NONE);
    }

    private static void show(String query) throws UnsupportedEncodingException, InvalidRequestException,
            UnavailableException, TimedOutException, SchemaDisagreementException, TException {
        CqlResult result = executeQuery(query);
        List<CqlRow> rows = result.getRows();
        System.out.println("rows: " + rows.size());
        for (CqlRow row : rows) {
            System.out.println("columns: " + row.getColumnsSize());
            for (Column c : row.getColumns()) {
                System.out.print("  " + new String(c.getName()));
                switch (new String(c.getName())) {
                    case "x":
                    case "y":
                        System.out.print("  " + toInt(c.getValue()));
                        break;
                    case "val1":
                    case "val2":
                        System.out.print("  " + new String(c.getValue()));
                        break;

                    default:
                        break;
                }
                System.out.println();
            }
        }
    }
}

Example for schema in question.