Search code examples
cassandracomposite-keycql3

Create composite column keys/names in Cassandra CQL3

I would like to create a table in Cassandra using CQL3 queries with structure similar to:

myTable
        myTable_uuid 
            <key_1|second_key_1> : <value_1>,
            <key_1|second_key_2> : <value_2>,
            <key_1|second_key_3> : <value_3>,
            <key_2|second_key_4> : <value_4>,
            <key_2|second_key_5> : <value_5>,
            <key_2|second_key_6> : <value_6>
            PRIMARY KEY(myTable_uuid, first_key_1, first_key_2, second_key_1)

Table/CF name is 'myTable' and myTable_uuid is the row key. Additionally I would like to create composite column keys, using CQL3, to be able to group data (key_1 and key_2 form the groups). Is this supported? If yes could someone help me with an example CQL3 query, or the syntax.

Purpose: I am trying to design CFs so that data/values are grouped logically, something like the data model explained in Cassandra at ebay blog (http://www.ebaytechblog.com/2012/07/16/cassandra-data-modeling-best-practices-part-1/).

UPDATE

I am designing keeping in mind my application and the queries I need. I should have used a real-life query instead of dummy, but here is what I am trying to achieve:

myTable
        myTable_uuid 
            <email|fname> : <value_1>,
            <email|lname> : <value_2>,
            <email|age> : <value_3>,
            <email|timestamp> : <value_4>,
            <shool_id|school_name> : <value_5>,
            <shool_id|zip_code> : <value_6>
            PRIMARY KEY(myTable_uuid, email, school_id, school_name)

The <email|fname> , <email|lname>, <email|age>, <shool_id|school_name> and <shool_id|zip_code> would behave as 'Composite Column Keys' and not Row Keys, the table has 'myTable_uuid' as it's Row Key. This approach would help me to group my data into 'email' and 'school_id'. How do I create 'Composite Column Keys' and is it supported to query based on only 1 of the Composite CK? For example:

SELECT fname, age WHERE email = 'xyz@gmail.com'

I'm using Cassandra 1.2.6 and CQL3. Any help is appreciated.

Solution

  • Yes, this is supported. CQL3 supports all features. (Except deleting columns, but this will be fixed pretty soon, does not take up much storage in the meanwhile and is off-topic).

    In CQL3, composite column keys are created by adding them to the primary keys clause. The first key given forms the row key, all following columns form a (composite) column key. This blog entry explains it really well.

    CREATE TABLE myTable 
(
  myId uuid, 
  key_1 varchar,
  key_2 varchar,
  value varchar, 
  PRIMARY KEY (myId, key_1, key_2)
);

    Thus, myId would be the partition key and key_1 and key_2 would "group" the data and form a composite column key.

    Hope that helps.