How to SELECT DISTINCT in cassandra

Question

I want to SELECT DISTINCT in Cassandra, but I think Cassandra doesn't support these operations. How can I do SELECT DISTINCT in Cassandra? Is it possible?

Answer 1

CQL 3.1.1 and onwards support DISTINCT operation only for partition keys.

SELECT statement now allows listing the partition keys (using the DISTINCT modifier). See CASSANDRA-4536.

Select Syntax

select_statement ::=  SELECT [ JSON | DISTINCT ] ( select_clause | '*' )
                      FROM table_name
                      [ WHERE where_clause ]
                      [ GROUP BY group_by_clause ]
                      [ ORDER BY ordering_clause ]
                      [ PER PARTITION LIMIT (integer | bind_marker) ]
                      [ LIMIT (integer | bind_marker) ]
                      [ ALLOW FILTERING ]
select_clause    ::=  selector [ AS identifier ] ( ',' selector [ AS identifier ] )
selector         ::=  column_name
                      | term
                      | CAST '(' selector AS cql_type ')'
                      | function_name '(' [ selector ( ',' selector )* ] ')'
                      | COUNT '(' '*' ')'
where_clause     ::=  relation ( AND relation )*
relation         ::=  column_name operator term
                      '(' column_name ( ',' column_name )* ')' operator tuple_literal
                      TOKEN '(' column_name ( ',' column_name )* ')' operator term
operator         ::=  '=' | '<' | '>' | '<=' | '>=' | '!=' | IN | CONTAINS | CONTAINS KEY
group_by_clause  ::=  column_name ( ',' column_name )*
ordering_clause  ::=  column_name [ ASC | DESC ] ( ',' column_name [ ASC | DESC ] )*

Answer 2

As others wrote, there is no support for distinct on columns in Cassandra, only on primary key. Two options to do this with cassandra:

1. Process in application - requires loading reading entire table from server and doing the distinct in code.
2. Create a secondary table in which the key is the column you want to be distinct, and double insert there when doing an operation on original table.

The decision between the two depends on your actual data structure/size and needs, if the table size is small or you do this operation very little, option 1 will be enough and fast, if the table is large, and/or you do this query a lot of times go with #2.