Cassandra Composite Column Family

Question

I have a simple requirement in sql world i want to create

CREATE TABLE event_tracking (
  key text,
  trackingid timeuuid,
  entityId bigint,
  entityType text
  userid bigint
  PRIMARY KEY (key, trackingid)
)

I need a cli create command which is I am not able to do it. I need to create column family through cli as pig cannot read column family created through cqlsh (duh)

Here what I tried and didnt worked

 create column family event_tracking
...  WITH comparator='CompositeType(TimeUUIDType)'
...  AND key_validation_class=UTF8Type
...  AND default_validation_class = UTF8Type;

1) I dont know why it add the value column to it when I see it in cqlsh

CREATE TABLE event_tracking (
  key text,
  trackingid timeuuid,
  value text,
  PRIMARY KEY (key, trackingid)
) WITH COMPACT STORAGE AND
  bloom_filter_fp_chance=0.010000 AND
  caching='KEYS_ONLY' AND
  comment='' AND
  dclocal_read_repair_chance=0.000000 AND
  gc_grace_seconds=864000 AND
  read_repair_chance=0.100000 AND
  replicate_on_write='true' AND
  populate_io_cache_on_flush='false' AND
  compaction={'class': 'SizeTieredCompactionStrategy'} AND
  compression={'sstable_compression': 'SnappyCompressor'};

2) I am using asynatax to insert the row.

OperationResult<CqlResult<Integer, String>> result = keyspace.prepareQuery(CQL3_CF)
    .withCql("INSERT INTO event_tracking (key, column1, value) VALUES ("+System.currentTimeMillis()+","+TimeUUIDUtils.getTimeUUID(System.currentTimeMillis())+",'23232323');").execute();

but as soon as i try to add dynamic columns, it is not able to recognize

OperationResult<CqlResult<Integer, String>> result = keyspace.prepareQuery(CQL3_CF)
.withCql("INSERT INTO event_tracking (key, column1, value, userId, event) VALUES ("+System.currentTimeMillis()+","+TimeUUIDUtils.getTimeUUID(System.currentTimeMillis())+",'23232323', 123455, 'view');").execute(); 

looks like I cannot add dynamic columns through cql3

3) If I try to add new column through cql3

alter table event_tracking add eventid bigint;

it gives me

Bad Request: Cannot add new column to a compact CF

Answer 1

0) If you create the table with COMPACT STORAGE Pig should be able to see it, even if you create it from CQL3. But you would need to put entityId and entityType into the primary key too for that to work (compact storage basically means that the first column in the primary key becomes the row key and the following become a composite type used as the column key, and then there is only room for one more column which will be the value).

1) When you create tables the old way there will always be a value, it's the value of the column, and in CQL3 that is represented as a column called value. This is just how CQL3 maps the underlying storage model onto tables.

2) You have created a table whose columns are of the type CompositeType(TimeUUIDType), so you can only add columns that are TimeUUIDs. You can't tell C* to save a string as a TimeUUID column key.

3) Looping back to 0 use this table:

CREATE TABLE event_tracking (
  key text,
  trackingid timeuuid,
  entityId bigint,
  entityType text,
  userid bigint,
  PRIMARY KEY (key, trackingid, entityId, entityType)
) WITH COMPACT STORAGE

this one assumes that there can only be one trackingId/entityId/entityType combination for each userid (what's up with your inconsistent capitalization, btw?). It that's not the case you need to go the full dynamic columns route, but then you can't have different data types for entityId and entityType (but this would have been the case before CQL3 too), see this question for an example of how to do dynamic columns: Inserting arbitrary columns in Cassandra using CQL3