insert into column family with case sensitive column name

Question

I am using the following Cassandra/CQL versions:

[cqlsh 4.0.1 | Cassandra 2.0.1 | CQL spec 3.1.1 | Thrift protocol 19.37.0]

I am trying to insert data into a pre-existing CF with case sensitive column names. I hit "unknown identifier" errors when trying to insert data.

Following is how the column family is described:

CREATE TABLE "Sample_List_CS" (
  key text,
  column1 text,
  "fName" text,
  "ipSubnet" text,
  "ipSubnetMask" text,
  value text,
  PRIMARY KEY (key, column1)
) 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=0 AND
  index_interval=128 AND
  read_repair_chance=0.000000 AND
  replicate_on_write='false' AND
  populate_io_cache_on_flush='false' AND
  default_time_to_live=0 AND
  speculative_retry='NONE' AND
  memtable_flush_period_in_ms=0 AND
  compaction={'class': 'SizeTieredCompactionStrategy'} AND
  compression={'sstable_compression': 'LZ4Compressor'};

CREATE INDEX ipSubnet ON "Sample_List_CS" ("ipSubnet");

The insert statements result in errors:

cqlsh:Sample_KS> INSERT INTO "Sample_List_CS" (key,column1,"fName") VALUES    ('123','1','myValue');
Bad Request: Unknown identifier fName

cqlsh:Sample_KS> INSERT INTO "Sample_List_CS" (key,column1,"ipSubnet") VALUES    ('123','1','255');
Bad Request: Unknown identifier ipSubnet

Any idea what I am doing wrong?

Fiddlededee

I've run these commands with [cqlsh 4.1.0 | Cassandra 2.0.2 | CQL spec 3.1.1 | Thrift protocol 19.38.0] and they work for me. (without the WITH COMPACT STORAGE on CREATE TABLE) – Placet

thanks for validating, I tried the same without compact storage and it worked too. – Fiddlededee

Answer 1

As I understand it when using WITH COMPACT STORAGE a table may only have one column other than the primary key.

As quoted in the manual:

Using the compact storage directive prevents you from adding more than one column that is not part of the PRIMARY KEY.

For you that means you can only have one of these 4 columns in your table:

• "fName"
• "ipSubnet"
• "ipSubnetMask"
• value

(Alternatively, you could add 3 of them to the primary key definition.)

Thus it makes sense that the other three columns lead to an Unknown identifier error.

Wellstacked

thanks for the pointer. It turns out that the pre-existing column family was created using hector api and is configured for 'compact storage' by default. Your reasoning looks to be correct, I created a new CF without compact storage and things work fine. – Fiddlededee