Is it possible to pass any other columns after the where clause in select statement Cassandra

Question

I have been trying to retrieve data from Cassandra database using this command:

 select A_NO from Activity where CALL_TYPE IN ('MOC');//here CALL_TYPE is not the row id

But its showing :

Expected key 'KEY' to be present in where clause for 'Activity'

Is it not possible in cassandra to filter the data with the help of a non-primary key?

Answer 1

You cannot do this directly on a vanilla column family. Cassandra by default only let's you query on key's or a key range. You can accomplish this by creating a secondary index on a column

You would run a CQL query like this to create two indexes:

cqlsh> CREATE INDEX state_key ON users (state);
cqlsh> CREATE INDEX birth_year_key ON users (birth_year);

And then queried like this:

cqlsh> SELECT * FROM users
 ... WHERE gender='f' AND
 ...  state='TX' AND
...  birth_year='1968';

Here is more on Secondary indexes.

Here is the documentation on using CQL for this.