table creation with clustering order by on partition key does not work

[hemabs]

Hi,

I want to have the partiton key ordered and stored irrespective of records being inserted.

"create table msg (day timestamp, notes text, primary key(day)) WITH clustering order by (day desc)" always fails with below error:

Bad Request: Only clustering key columns can be defined in CLUSTERING ORDER directive

I have the below insertions made into the table (so when I execute "select * from msg"; here are the results):

 2014-09-13 00:00:00Pacific Daylight Time | Sending message

 2014-09-13 00:00:00Pacific Daylight Time | Broadcasting message

 2014-10-01 00:00:00Pacific Daylight Time | Sending message

 2014-09-22 00:00:00Pacific Daylight Time | Broadcasting message

Is there a way for partition key to be sorted asc/desc by default (which can be specified while creating the table)?

Appreciate the help. Thanks

[Olivier Michallat]

Hi,

CLUSTERING ORDER applies to clustering columns, not the partition key. It describes how rows are ordered within a partition. As a reminder, the partition key is the first component of the the "primary key" directive:

   primary key (p, c1, c2) => p is the partition key, c1 and c2 are clustering columns

   primary key((p1, p2), c1, c2) => (p1, p2) is the partition key, ...

To have partition keys sorted by default, you change the partitioner of your cluster[1], but this is not a recommended practice.

Looking at your example, I have a couple of remarks: are the messages really global to the application, or isn't there a natural way to partition them apart from the date? For instance, if you always retrieve the messages for a particular user:

    create table msg(user_id uuid, day timestamp, notes text,

        primary key(user_id, day))

        with clustering order by (day desc);

Or maybe you could partition on the date, but at a more granular level by denormalizing some of the date's components:

    create table msg(year int, month int, ts timestamp, notes text,

        primary key((year, month), day))

        with clustering order by (day desc);

Then if you want to display e.g. a full year you have to query the months in the right order, but you don't need Cassandra to tell you that 2014/05 comes before 2014/06, right?

So it all boils down to how you plan to query the data later, and ensuring that your partitions don't get too big (don't put everything in a single partition with a dummy partition key). If you want to go deeper into data modeling, I suggest Patrick McFadin's talks on youtube.

[1] http://www.datastax.com/documentation/cassandra/2.1/cassandra/architecture/architecturePartitionerAbout_c.html

--

Olivier Michallat

Driver & tools engineer, DataStax

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-us...@lists.datastax.com.

[hemabs]

Thanks Olivier. This is very helpful to redesign my table. Looking at your example I feel we can take advantage of specific messages..