Cassandra and using cql to INSERT a column

Question

Trying to understand some fundamentals in Cassandra, I was under the impression that one of the advantages a developer can take in designing a data model is by dynamically adding columns to a row identified by a key. That means I can model my data so that if it makes sense, a key can be something such as a user_id from a relational database, and I can for example, create arbitrary amounts of columns that relate to that user.

What I'm not understanding is why there is so much emphasis to predefined columns in CLQ examples, particularly in the CREATE TABLE/COLUMNFAMILY examples:

CREATE TABLE emp (
  empID int,
  deptID int,
  first_name varchar,
  last_name varchar,
  PRIMARY KEY (empID, deptID)
);

Wouldn't this type of model make more sense to just stuff into a relational database? What if I don't know my column name until runtime and need to dynamically create it? Do I have to use ALTER TABLE to add a new column to the row using CLQ? The particular app use-case I have in mind I would just need a key identifier and arbitrary column names where the column name might include a timestamp+variable_identifier.

Is Cassandra the right tool for that? Are the predefined columns in documentation nothing more than an example? How does one add a dynamic column name with an existing column family/table?

Why did you repost to the mailing list after Lyuben already answered here? Have some patience, especially over a weekend. — jbellis
I do apologize for that. I made it clear on the post that I was doing that. I'll see if I can get this thread removed and follow up on the mail thread instead. — randombits

Lyuben Todorov Lyuben Todorov · Accepted Answer · 2013-05-27T08:26:42

Do I have to use ALTER TABLE to add a new column to the row using CLQ?

Yes, the schema must be defined before you can insert into 'new columns'. However you could define 1 column that is a collection of data. Look at the 'tag' example in datastax's 'thrift to cql upgrade' blog under mixing dynamic and static columns.

How does one add a dynamic column name with an existing column family/table?

In CQL you have to first alter the structure of the table (column family) using the ALTER keyword. My guess is that this is to ensure that column families contain the specified columns eliminating the chance of a column being added by mistake (better data quality).

Is Cassandra the right tool for that?

I think it is, but if you need to add columns on-the-fly without specifying schema altering statements then you should probably look into thrift based APIs which can do that, but just a friendly warning, datastax advise that new applications use CQL.

Cassandra and using cql to INSERT a column

2 Answers