SELECT Specific Value from map

Question

I am trying to create a WIDE Column Table, 20,000+ columns

Initially I was thinking I would use:

CREATE TABLE details (
   key TEXT,
   detail map<TEXT, TEXT>
   PRIMARY KEY (KEY)
  );

Inserting into this table works fine

UPDATE details SET detail = detail + { 'col1': '12'} where key='123' ;
UPDATE details SET detail = detail + { 'col20000': 'ABCD'} where key='123' ;

However, I would like to read an individual detail:

   select detail[col1] where key='123'

when executing this query I get the following error:

 no viable alternative at input '['

Will this work, or do I need a different approach?

Mallissa

Answer 1

Collections are small groups of data that you fetch all at once.

If you want to access tuples at a finer level, and still be able to ask "what are all the pairs of data for a given key," you should use a table like this:

CREATE TABLE details (
  key TEXT,
  detail_key text,
  detail_value text,
  PRIMARY KEY (key, detail_key)
);

This will allow SELECT * FROM details WHERE key = ? as well as SELECT * FROM detail WHERE key = ? AND detail_key = ?.

Parachronism

Thanks, that is the approach I have taken. – Mallissa

Still don't see it 3.9, wonder what the reason is, couldn't find it. AFAIK, they are stored as columns themselves. "Partial reads of collection columns are not possible in CQL. The only way to retrieve data from a collection is to read the collection in its entirety; for this reason, it's generally impractical to store large, unbounded datasets within a collection column" github.com/apache/cassandra/blob/cassandra-3.6/CHANGES.txt – Neutralize

Answer 2

Basically this functionality is not yet supported by cassandra.

See this cql3 collections

Cozen

Answer 3

You can use user-define type instead of map type. Try to define table in this way:

CREATE TYPE detailtype (
    col1 TEXT,
    col2 TEXT
);

CREATE TABLE details (
   key TEXT,
   detail frozen<detailtype>,
   PRIMARY KEY (KEY)
);

Then you can query by this way:

select detail.col1 where key='123';

Gingergingerbread