cassandra, select via a non primary key

cassandra cql cqlsh
mel picture mel · Apr 22, 2015 · Viewed 21.9k times · Source

I'm new with cassandra and I met a problem. I created a keyspace demodb and a table users. This table got 3 columns: id (int and primary key), firstname (varchar), name (varchar). this request send me the good result:

SELECT * FROM demodb.users WHERE id = 3;

but this one:

SELECT * FROM demodb.users WHERE firstname = 'francois';

doesn't work and I get the following error message:

InvalidRequest: code=2200 [Invalid query] message="No secondary indexes on the restricted columns support the provided operators: "

This request also doesn't work:

SELECT * FROM users WHERE firstname  = 'francois'  ORDER BY id DESC LIMIT 5;
InvalidRequest: code=2200 [Invalid query] message="ORDER BY with 2ndary indexes is not supported."

Thanks in advance.

Answer

Aaron picture Aaron · Apr 22, 2015

This request also doesn't work:

That's because you are mis-understanding how sort order works in Cassandra. Instead of using a secondary index on firstname, create a table specifically for this query, like this:

CREATE TABLE usersByFirstName (
  id int,
  firstname text,
  lastname text,
  PRIMARY KEY (firstname,id));

This query should now work:

SELECT * FROM usersByFirstName WHERE firstname='francois'
ORDER BY id DESC LIMIT 5;

Note, that I have created a compound primary key on firstname and id. This will partition your data on firstname (allowing you to query by it), while also clustering your data by id. By default, your data will be clustered by id in ascending order. To alter this behavior, you can specify a CLUSTERING ORDER in your table creation statement:

WITH CLUSTERING ORDER BY (id DESC)

...and then you won't even need an ORDER BY clause.

I recently wrote an article on how clustering order works in Cassandra (We Shall Have Order). It explains this, and covers some ordering strategies as well.

Related questions

How do you create the first user in Cassandra DB

How does one create the first user in a cassandra database? I tried: CREATE USER username WITH PASSWORD ""; and its says: Bad Request: Only superusers are allowed to perform CREATE USER queries But I have never created a user before …

Read More
Is there a clear equivalent of 'show keyspaces' in cqlsh 2?

What cqlsh command can I use to quickly see the keyspaces in a cluster? cqlsh does not provide show keyspaces and describe cluster isn't as concise as I want. I'm working using the following specifications: cqlsh 2.2.0, Cassandra 1.1.10, CQL spec 2.0.0, Thrift …

Read More
Executing CQL through Shell Script?

I am trying to execute the CQL commands from shell script. I am able to connect to the cqlsh (CQL version i'm using is 1.1.18) but unable to send the queries to cql. Any ideas or suggestion how to proceed on …

Read More