0

I'm using Cassandra 1.2.12 with CQL 3, and am having trouble modeling my column family.

I currently store snapshots of customer data at particular times. Works great:

CREATE TABLE data (
  cust_id varchar,
  time timeuuid,
  data_text text,
  PRIMARY KEY (cust_id, time)
);

The cust_id is the partition key and time is the clustering id, so, as I understand it, I can think of each row in the table like:

| cust_id | timeuuid1 : data_text | timeuuid2 : data_text |

| CUST1 | data at this time | data at this time |

Now I'd like to store another group of metrics for each snapshot - but the name of each of these columns isn't fixed. So something like:

| cust_id | timeuuid1 : data_text | timeuuid1 : dynamicCol1 | timeuuid1 : dynamicCol2 | timeuuid1 : dynamicColN |

| CUST1 | data |{some value} |{some value} |{some value} |

I've achieved dynamic columns for timestamp by using a composite primary key, but I can't see how to achieve this within each cluster of columns, if you see what I mean. If I add, say, "dynamicColumnName" to the existing composite key, I'll end up with customer data stored for each dynamic column, which is not what I want.

Is this possible, without using a Map column? Hope you can help, thanks!

Community
  • 1
  • 1
Ben Kirby
  • 904
  • 2
  • 11
  • 29
  • Any help with formatting tables greatly appreciated... – Ben Kirby Feb 04 '14 at 12:40
  • CQL doesn't support "schemaless" inserts in this manner. If you have a sensible number of extra columns then you can add them to the schema. Cassandra's data store is sparse so if a column has no data it isn't filled and takes up no space. Map is an alternative, but then you have to query and pull back the full map each time - that may or may not meet your needs. – AndySavage Feb 04 '14 at 18:26
  • Great, thanks for the info @AndySavage. Add as answer and I'll mark accepted? – Ben Kirby Feb 05 '14 at 10:08

1 Answers1

0

I am not a CQL user... With the thrift API you dynamically add a column to a column family by inserting/updating a record with a value for a column with name X. The column X will start to exist right there and then for that record.

Have you tried an INSERT statement specifying a column that you have not explicitly defined? I would expect that to have the same effect (column is created).

Ralf
  • 6,735
  • 3
  • 16
  • 32
  • Thanks @Ralf - unfortunately such an INSERT won't work with CQL 3 - you have to run an ALTER TABLE first. And I'm not sure how I would use that to add a column nested under the time cluster key, rather than just to the row. – Ben Kirby Feb 05 '14 at 10:07