How to get all rows with one text column which has not empty string using CQL?

Question

I have a Cassandra Table called testTable:

create table testTable(
  col1 text primary key,
  col2 text
);

After inserting some data, I want to get all rows, whose col2 is not empty. Such as:

select * from testTable where col2 <> null;

From CQL reference and other google results, there is no good answers, I don't know whether any one tell me what should I do to finish this task?

Answer 1

See this post for a detailed explanation on why this is not possible with Cassandra. http://www.aaronstechcenter.com/null-query-cassandra.php

Essentially as col2 is not part of the primary key, you can't filter by this column. Cassandra works as a distributed hash table, where you specify a key and get the associated data. In that way, nulls are not supported in clustering columns either.

To achieve what you want, you could filter the resultant dataset to check whether col2 is null in the client or using something like Spark, but this is not an efficient thing to do within Cassandra.