How to define dynamic column families in cassandra

Question

Here it is said, that no special effort is need to get a dynamic column family. But I always get an exception, when I try to set a value for an undefined column.

I created a column family like this:

CREATE TABLE places (
   latitude double,
   longitude double,
   name text,
   tags text,
   PRIMARY KEY (latitude, longitude, name)
)

BTW: I had to define the tags column. Can somebody explain me why? Maybe because all other columns are part of the Index?

Now when inserting data like this:

INSERT INTO places ("latitude","longitude","name","tags") VALUES (49.797888,9.934771,'Test','foo,bar')

it works just fine! But when I try:

INSERT INTO places ("latitude","longitude","name","tags","website") VALUES (49.797888,9.934771,'Test','foo,bar','test.de')

I get following error:

Bad Request: Unknown identifier website
text could not be lexed at line 1, char 21

Which changes are needed so I can dynamically add columns?

I am using Cassandra 1.1.9 with CQL3 with the cqlsh directly on a server.

Decolonize

Answer 1

CQL3 supports dynamic column family but you have to alter the table schema first

ALTER TABLE places ADD website varchar;

Check out the 1.2 documentation and CQL in depth slides

Archidiaconal

Thank you! Now I understood the changes regarding wide rows! But I don't like them, but you helped me a lot. – Decolonize

Hi am confused, is there any way to create a column on the fly? – Thebault

Answer 2

CQL3 requires column metadata to exist. CQL3 is actually an abstraction over the underlying storage rows, so it's not a one-to-one. If you want to use dynamic column names (and there are lots of excellent use cases for them), use the traditional Thrift interface (via the client library of your choice). This will give you full control over what gets stored.

Multifaceted

Thrift is very much deprecated in favor of CQL, correct? Is the current best practice, then, to run lots of ALTER TABLE statements before every insert? – Vietnam

The easiest way to do this is via the map data type, which actually creates dynamic column names under the hood. – Multifaceted

Here's a great article about suporting dynamic columns in FQL: datastax.com/dev/blog/… – Shortcake