Can I use SQLAlchemy with Cassandra CQL?

Question

I use Python with SQLAlchemy for some relational tables. For the storage of some larger data-structures I use Cassandra. I'd prefer to use just one technology (cassandra) instead of two (cassandra and PostgreSQL). Is it possible to store the relational data in cassandra as well?

Answer 1

No, Cassandra is a NoSQL storage system, and doesn't support fundamental SQL semantics like joins, let alone SQL queries. SQLAlchemy works exclusively with SQL statements. CQL is only SQL-like, not actual SQL itself.

To quote from the Cassandra CQL documentation:

Although CQL has many similarities to SQL, there are some fundamental differences. For example, CQL is adapted to the Cassandra data model and architecture so there is still no allowance for SQL-like operations such as JOINs or range queries over rows on clusters that use the random partitioner.

You are of course free to store all your data in Casandra, but that means you have to re-think how you store that data and find it again. You cannot use SQLAlchemy to map that data into Python Objects.

Answer 2

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