Create a Cassandra schema for a super column with metadata

Question

I want the following structure in my database with the cassandra -cli

for example I have a person with an address and the address contains a zipcode, housenumber etc schematic it looks like this:

name: "address",
value: {
    city: {name: "city", value: "San Francisco"},
    street: {name: "street", value: "555 Union Street"},
    zip: {name: "zipcode", value: "94105"},
}

I know how to create a supercolumn and normal columns. But I want to use the column_metadata to specify how the colums in a supercolumn look like. So that would end up something like this:

Create column family person
with comparator = UTF8Type
and key_validation_class=UTF8Type
and default_validation_class = UTF8Type
and compression_options =
{sstable_compression:SnappyCompressor, chunk_length_kb:64}
and column_metadata = [
{column_type: super, column_name: address, validation_class: UTF8Type
column_metadata =[
{column_name: city, validation_class: UTF8Type}
{column_name: Street, validation_class: UTF8Type}
{column_name: zip, validation_class: UTF8Type}
{column_name: housnumber, validation_class: LongType}
];

Is this at all possible?

It would be even better if I can put a super column in another super column. This way the super column "addresses" could contain multiple super columns "address" with columns: "street", "zip", etc.

Answer 1

Column metadata on super columns already applys to the sub columns themselves. In your example you set a validation_class for the super column to UTF8Type. This doesn't really make sense since the values of the super column are columns themselves not UTF8. The super column names are already validated and sorted according to the comparator type which you have set to UTF8Type.

So what you need is:

column_metadata = [
  {column_name: city, validation_class: UTF8Type}
  {column_name: Street, validation_class: UTF8Type}
  {column_name: zip, validation_class: UTF8Type}
  {column_name: housnumber, validation_class: LongType}
];

It's worth noting that this metadata will apply to all super columns in the row. So if you have multiple super columns with a sub column named 'city' the validation will apply to all of them.

I'll also note that use of super columns is generally discouraged as they have several disadvantages. All subcolumns in a super column need to be deserialized when reading one sub column and you can not set secondary indexes on super columns. They also only support one level of nesting.

http://www.datastax.com/docs/1.0/ddl/column_family#about-super-columns

To achieve an arbitrary level of nesting you can use CompositeType as your column comparator on a regular column family. Unfortunately there isn't much documentation on CompositeType at the moment. I'd suggest looking over the source for more info src/java/org/apache/cassandra/db/marshal/CompositeType.java.