16

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?

dofamin
  • 65
  • 9
e90jimmy
  • 272
  • 1
  • 2
  • 11

3 Answers3

15

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 = ?.

mcuadros
  • 4,098
  • 3
  • 37
  • 44
jbellis
  • 19,347
  • 2
  • 38
  • 47
  • Thanks, that is the approach I have taken. – e90jimmy Apr 18 '13 at 12:30
  • 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" https://github.com/apache/cassandra/blob/cassandra-3.6/CHANGES.txt – kisna Nov 19 '16 at 02:51
7

Basically this functionality is not yet supported by cassandra.

See this cql3 collections

abhi
  • 4,762
  • 4
  • 29
  • 49
1

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';
Liangmp
  • 91
  • 5