Clustering order using timeuuid CQL

Question

My usecase

I want to order by timestamp DESC for the results. But I do not want the timestamp to be the second column in the primary key, as this will require my query capability

eg

create table demo(oid int,cid int,ts timeuuid,PRIMARY KEY (oid,cid,ts)) WITH CLUSTERING ORDER BY (ts DESC); 

Required Requests:

 I want the result for all the below queries to be in DESC order of timestamp select * from demo where oid = 100; select * from demo where oid = 100 and cid = 10; select * from demo where oid = 100 and cid = 100 and ts > minTimeuuid('something'); 

I am trying to create this table with CLUSTERING ORDER IN CQL and get this error

 cqlsh:v> create table demo(oid int,cid int,ts timeuuid,PRIMARY KEY (oid,cid,ts)) WITH CLUSTERING ORDER BY (ts desc); Bad Request: Missing CLUSTERING ORDER for column cid 

This document mentions that we can have multi-valued keys for arranging clusters. Does anyone know how to do this?

Go here Datastax doc

Answer 1

 CREATE TABLE example ( a int, b int, c int, d int, PRIMARY KEY (a,b,c)) WITH CLUSTERING ORDER BY (b DESC , c ASC ) ; 

It is the correct syntax for ordering with multiple columns.

---

For your specific application, you are actually trying to get results from completely different types of queries. In Kassandra, it is best to form each table as a response to a specific query.

For example (not knowing about your application)

 select * from demo where oid = 100 and cid = 100 and ts > minTimeuuid('something'); select * from demo where oid = 100 and cid = 10; 

May be better served by table structure e.g.

 create table demo_oct(oid int,cid int,ts timeuuid, body, other ...., PRIMARY KEY ((oid,cid),ts)) WITH CLUSTERING ORDER BY (ts DESC); 

Thus, each set of time series for the oid and cid data pair will be in its own part and easily retrieved. This is because I am using the Parition key, which consists of both oid and cid. That is why there is an additional set of brackets in the key. The clustering key ts ensures that the data is in the order you want.

But, as you noticed, you cannot execute select * from table oid == 10 in this table, because this will require scanning the entire database (due to the partition structure)

For queries such as

select * from the demo, where oid = 100;

you will need a second table (again, not knowing your specific application)

 create table demo_ot(oid int,cid int,ts timeuuid, body, other ...., PRIMARY KEY (oid,ts)) WITH CLUSTERING ORDER BY (ts DESC); 

This table stores the time series for each OID in one part, which allows the use of very fast fragments. Here, the partition key is only the OID, and ts is still the clustering key.

On the application side, you must insert both of these tables at the same time.

Additional Info About Datamodeling