Cassandra Contains query error

Question

I am new to Cassandra and trying to figure out how to get a simple contains query working with Cassandra.

My table looks like this

CREATE TABLE events (
  timekey text,
  id timeuuid,
  event_types list<text>,
  PRIMARY KEY ((timekey), id)
)

My query:

cqlsh> select count(1) from events where event_types contains 'foo';

**Bad Request: line 1:46 no viable alternative at input 'contains'**

Any thoughts about the error?

Also Is it possible to query for multiple event_types in one single query. I could not see any way to do this with Contains. Something equivalent to this in a regular sql

Relational SQL example:

select count(1) from events where event_types in ('foo', 'bar')

Aaron Aaron · Accepted Answer · 2015-07-15T21:50:16

A couple of things. First of all, when I create your schema, insert a row, I get a different error message than you do:

aploetz@cqlsh:stackoverflow2> CREATE TABLE events (
              ...   timekey text,
              ...   id timeuuid,
              ...   event_types list<text>,
              ...   PRIMARY KEY ((timekey), id)
              ... );
aploetz@cqlsh:stackoverflow2> INSERT INTO events (timekey, id, event_types) 
    VALUES ('1', now(),['foo','bar']);
aploetz@cqlsh:stackoverflow2> select count(1) from events where event_types contains 'foo';
InvalidRequest: code=2200 [Invalid query] message="No secondary indexes on the restricted
    columns support the provided operators: "

To get this to work, you will need to create a secondary index on your event_types collection. Of course secondary indexes on collections are a new feature as of Cassandra 2.1. By virtue of the fact that your error message is different, I'm going to guess that you would need to upgrade to 2.1.

I'm using 2.1.5 in my sandbox right now, so when I create an index on event_types this works:

aploetz@cqlsh:stackoverflow2> CREATE INDEX eventTypeIdx ON events(event_types);
aploetz@cqlsh:stackoverflow2> select count(1) from events where event_types contains 'foo';

 count
-------
     1

(1 rows)

Even though this may work, secondary indexes on large tables or in large clusters are known not to perform well. I would expect that secondary indexes on collections would perform even worse, so just take that as a warning.

Also Is it possible to query for multiple event_types in one single query?

There are ways to accomplish this, but I recommend against it for the aforementioned performance issues. I answered a similar question here, if you are interested: Cassandra CQL where clause with multiple collection values?

Cassandra Contains query error

1 Answers