Cassandra (CQL) select statement with 'where' is not working

Question

I am using Cassandra last few days. I am using PHPCassa library for that.

When I am trying to use the following code, Its not working correctly.

 require_once('phpcassa/connection.php');
 require_once "phpcassa/columnfamily.php";

 // Create new ConnectionPool like you normally would
 $pool = new ConnectionPool("newtest");

 // Retrieve a raw connection from the ConnectionPool
 $raw = $pool->get();

 $rows = $raw->client->execute_cql_query("SELECT * FROM User WHERE KEY='phpqa'", cassandra_Compression::NONE);

 echo "<pre>";
 print_r($rows);
 echo "<pre>";

// Return the connection to the pool so it may be used by other callers. Otherwise,
// the connection will be unavailable for use.
$pool->return_connection($raw);
unset($raw);

Its returning nothing, I have also tried following queries

$rows = $raw->client->execute_cql_query("SELECT * FROM User WHERE age='32'", cassandra_Compression::NONE);
$rows = $raw->client->execute_cql_query("SELECT * FROM User WHERE name='jack'", cassandra_Compression::NONE);

But When I tried

 $rows = $raw->client->execute_cql_query("SELECT * FROM User", cassandra_Compression::NONE);

Its given the correct answer, displayed all the rows. Please advise me, how to use 'WHERE' properly.

Keyspace Details

Strategy Class:     org.apache.cassandra.locator.SimpleStrategy
Strategy Options:   None
Replication Factor: 1

Ring

   Start Token: 6064078270600954295
   End Token: 6064078270600954295
   Endpoints: 127.0.0.1

Hibernate

What's the output of 'DESCRIBE USER KEYSPACE'? – Reason

Are 'age' and 'name' indexed? – Reason

In this query "SELECT * FROM User WHERE KEY='phpqa'", phpqa is the key name for that row data. – Hibernate

Do we need to index the KEY for that ? – Hibernate

Answer 1

In cassandra you cant just query a 'table' as you would normally. You need to set up secondary indexes for every column you might want to query.

Say we have a table:

 key      | User |   Age 
----------+----------------
 phpqa    | Jack |   20    

You can query directly on the key:

SELECT * FROM User WHERE key='phpqa';

But to carry out other WHERE queries you'd need a secondary index on the columns you want to have available in the WHERE clause.

What can you do to make your querying flexible in the way that you desire:

1. Secondary indexes as described above.
2. Use composite columns as your key. Its a good idea if you only have 2-3 columns you want to query, but have a read through this article detailing how and when to use composite keys, and here is a link in how to implement it in phpcassa.

Plywood

Do we need to index the KEY for that ? – Hibernate

@Hibernate Say you want to have the Age column in the WHERE clause (as in SELECT * FROM user WHERE age=12 You need to index the AGE column. If you want AGE and NAME you need to index both columns. The key column is always indexed. – Plywood

Thanks for the help, After adding the secondory index for age, now its working, But i am not able to do <, <= like operators. Also Query with KEY is not working. – Hibernate

Answer 2

Add 'name' and 'age' as secondary indexes:

CREATE INDEX name_key on User( name );  
CREATE INDEX age_key on User( age );

Then you should be able to use your select statement(s).

Read more here.

Reason

Thanks. Do we need to add separate index for KEY ? – Hibernate

so this should smoothly right? SELECT * FROM User WHERE key='phpqa'; But fortunetelling its not. – Hibernate

Hard to know without seeing the data. – Reason

Answer 3

you are using a reserved word as a column name:

http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

$raw->client->execute_cql_query("SELECT * FROM User WHERE KEY='phpqa'", 
cassandra_Compression::NONE)

Headstock

Isn't the post about cassandra? Not MySQL. – Reason