Is it possible to use SQLAlchemy with Cassandra CQL?

Question

I am using Python with SQLAlchemy for some relational tables. For storage of some larger data structures I use Cassandra. I would prefer to use only one technology (cassandra) instead of two (cassandra and PostgreSQL). Is it possible to save relational data in cassandra?

Answer 1

No, Cassandra is a NoSQL storage system and does not support fundamental SQL semantics, such as joins, not to mention SQL queries. SQLAlchemy works exclusively with SQL statements. CQL is just SQL-like, not SQL itself.

To quote the Cassandra CQL documentation :

Although CQL has much in common with SQL, there are some fundamental differences. For example, CQL is adapted to the Cassandra data model and architecture, so SQL-like operations such as JOINs or row range queries on clusters that use a random delimiter are still not taken into account.

Of course, you can store all your data in Casandra, but that means you need to rethink how you store this data and find it again. You cannot use SQLAlchemy to map this data to Python objects.

Answer 2

As already mentioned, Cassandra does not support JOIN design. Use the Pycassa mapping instead: http://pycassa.github.com/pycassa/api/pycassa/columnfamilymap.html

Answer 3

playOrm supports JOIN on noSQL so you can put relational data in noSQL, but it is currently in java. We thought to expose the S-SQL language from the server for programs like yours. Would that interest you?

S-SQL will look like this (if you are not using partitions, you don’t even need anything before the SELECT statement part) ...

PARTITIONS t (: partId) SELECT t FROM TABLE as t INNER JOIN t.security as s WHERE s.securityType =: type and t.numShares =: share ")

This allows relational data in a noSQL environment. And if you break up your data, you can scale it very well with fast queries and fast connections.

If you like, we can quickly copy the prototype server , which provides an interface in which you send S-SQL queries, and we will return some kind of json to you. We would like it to be different from SQL result sets, which was a very bad idea when there were internal joins on the left.

i.e., we would return results on such a union (so that you can set maximum results that really work).

tableA row A - tableB row45 - tableB row65 - row tableB 78 tableA row C - tableB row46 - tableB row93

NOTICE that we are not returning multiple rows A, so if you have maximum results 2, you get row A and row C, where, as in ODBC / JDBC, you will get ONLY rowA twice with row45 and row 65 because this is what the table looks like when it returns (which is stupid when you are in any type of OO language).

just tell the playOrm team if you need anything on the playOrm github site.

Dean