CQL3 and millions of columns composite key use case

1
votes

How in CQL3 do we do millions of columns? We have one special table where all rows are basically composite keys and very very wide.

I was reading this question that implied two ways Does collections in CQL3 have certain limits?

Also, the types of our composite keys are String.bytes and ordered by STring

We have an exact matching table that is Decimal.bytes and ordered by decimal.

How would one handle this in CQL3?

thanks, Dean

2
cassandra

2 Answers

1
votes

"oh, and part of my question was missing since SO formatted it out of the question. I was looking for Decimal.bytes and String.bytes as my composite key....there is no "value", just a col name and I want all columns were decimal > 10 and decimal < 20 so to speak and the column name = 10 occurs multiple times as in 10.a, 10.b 11.c, 11.d, 11.e"

CREATE TABLE widerow
(    
   row_key text, //whatever
   column_composite1 decimal,
   column_composite2 text,
   PRIMARY KEY(row_key,column_composite1,column_composite2)
)

SELECT * FROM widerow WHERE row_key=... 
AND column_composite1>=10.0
AND column_composite1<=20.0

In that case, you can query with range over column_composite1 and have for EACH column_composite1, different values of column_composite2 (10.a, 10.b 11.c, 11.d, 11.e...)

"How do I get all the columns where row_composite1 > "a" and row_composite1 < "b" in that use case? ie. I dont' care about the second half of the composite name. "

2 possible solutions here

  1. Make row_composite1 a composite component of column
  2. Use OrderPreservingPartitioner (this is indeed strongly discouraged)

For solution 1 

 CREATE TABLE widerow
    (    
       fake_row_key text, //whatever
       column_composite1 text, // previously row_composite1
       column_composite2 decimal,
       column_composite3 text,
       PRIMARY KEY(row_key,column_composite1,column_composite2,column_composite3)
    )

SELECT * FROM widerow WHERE row_key=... 
AND column_composite1>='a'
AND column_composite1<='b'

This modeling has some drawback though. To be able to range query over DOUBLE values, you need to provide first the column_composite1:

SELECT * FROM widerow WHERE row_key=... 
AND column_composite1='a'
AND column_composite2>=10.0
AND column_composite2<=20.0
0
votes
CREATE TABLE widerow
(    
   row_composite1 text,
   row_composite2 text,
   column_name decimal,
   value text, 
   PRIMARY KEY((row_composite1,row_composite2),column_name)
)

SELECT * FROM widerow WHERE row_composite1=... 
AND row_composite2=...
AND column_name>=10.0
AND column_name<=20.0
ORDER BY column_name DESC