Search code examples
nosqlcassandracqlcql3

Cassandra CQL3 select row keys from table with compound primary key

I'm using Cassandra 1.2.7 with the official Java driver that uses CQL3.

Suppose a table created by

CREATE TABLE foo ( 
    row int, 
    column int, 
    txt text, 
    PRIMARY KEY (row, column)
);

Then I'd like to preform the equivalent of SELECT DISTINCT row FROM foo

As for my understanding it should be possible to execute this query efficiently inside Cassandra's data model(given the way compound primary keys are implemented) as it would just query the 'raw' table.

I searched the CQL documentation but I didn't find any options to do that.

My backup plan is to create a separate table - something like

CREATE TABLE foo_rows (
    row int,
    PRIMARY KEY (row)
);

But this requires the hassle of keeping the two in sync - writing to foo_rows for any write in foo(also a performance penalty).

So is there any way to query for distinct row(partition) keys?

Solution

  • according to the documentation, from CQL version 3.11, cassandra understands DISTINCT modifier. So you can now write 

    SELECT DISTINCT row FROM foo