How to use CQL with PHPCassa

• June 13, 2011
• 10 notes
• #cassandra
• #nosql
• #php
• #cql
• #phpcassa

The latest version of Cassandra, 0.8, includes a really awesome new feature, CQL. If you’re not already familiar with it, CQL is basically a SQL-like syntax for interacting with data in Cassandra. You can find an in depth overview of the CQL syntax at Courtney Robinson’s Blog.

Anyways, since CQL is so bleeding edge, many Cassandra drivers don’t support it yet. This is kind of a bummer since it’s a huge pain in the ass to interact with the Thrift API directly. Luckily, PHPCassa exposes a method to access the raw cassandra client, so it is possible (although, a little hacky) to use CQL with PHPCassa.

First of all, you absolutely must use the latest version of PHPCassa from GitHub. Older versions don’t have the updated Thrift bindings and will not expose the necessary API calls to make CQL work.

Awesome, but watch out- there are a couple of “gotchas”. First of all, execute_cql_query returns a cassandra_CqlResult object, which contains an array of cassandra_CqlRow objects. Each cassandra_CqlRow object has an array of cassandra_Column objects. It’s pretty verbose and not the most convenient format to work with.

Also, there is no concept of prepared statements yet, although CASSANDRA-2475 will address that. Without prepared statements, you absolutely have to make sure you extensively filter and validate your input data for SQL (or, I guess, CQL) injection.

Even though it’s a little bit of trouble to get working, the effort is totally worth it to use a game changing new feature like CQL right away.