Is there a way to specify a range of query strings using a combined row key when using random split?
Working with column families created using CQL v3 is as follows:
CREATE TABLE products ( rowkey CompositeType(UTF8Type,UTF8Type,UTF8Type,UTF8Type) PRIMARY KEY, prod_id varchar, class_id varchar, date varchar);
The data in the table is as follows:
RowKey: 6:3:2:19 => (column=class_id, value=254, timestamp=1346800102625002) => (column=date, value=2034, timestamp=1346800102625000) => (column=prod_id, value=1922, timestamp=1346800102625001) ------------------- RowKey: 0:14:1:16 => (column=class_id, value=144, timestamp=1346797896819002) => (column=date, value=234, timestamp=1346797896819000) => (column=prod_id, value=4322, timestamp=1346797896819001) -------------------
I'm trying to find a way to query for these compound string keys, similar to how we process a query for compound columns. The follow-up approach sometimes really manages to return something useful, depending on the start and stop key that I choose.
Composite startKey = new Composite(); startKey.addComponent(0, "3", Composite.ComponentEquality.EQUAL); startKey.addComponent(1, "3", Composite.ComponentEquality.EQUAL); startKey.addComponent(2, "3", Composite.ComponentEquality.EQUAL); startKey.addComponent(3, "3", Composite.ComponentEquality.EQUAL); Composite stopKey = new Composite(); stopKey.addComponent(0, "6", Composite.ComponentEquality.EQUAL); stopKey.addComponent(1, "6", Composite.ComponentEquality.EQUAL); stopKey.addComponent(2, "6", Composite.ComponentEquality.EQUAL); stopKey.addComponent(3, "6" , Composite.ComponentEquality.GREATER_THAN_EQUAL); RangeSlicesQuery<Composite, String, String> rangeSlicesQuery = HFactory.createRangeSlicesQuery(keyspace, CompositeSerializer.get(), StringSerializer.get(), StringSerializer.get()); rangeSlicesQuery.setColumnFamily(columnFamilyName); rangeSlicesQuery.setKeys(startKey,stopKey); rangeSlicesQuery.setRange("", "", false, 3);
In most cases, the database returns this:
InvalidRequestException(why:start key md5 sorts after end key md5. this is not allowed; you probably should not specify end key at all, under RandomPartitioner)
Does anyone have an idea if something like this can be achieved WITHOUT using an order-preserving delimiter? Should I create my own row key index for this use case?
Thanks a lot!
Additional Information:
What I'm trying to do is store the sales transaction data in a table that uses both compound row keys to encode the date / time / place, and compound columns to store information about sold items:
The set of elements for each transaction varies in size and includes information about the size, color and quantity of each element:
{ ... items : [ { item_id : 43523 , size : 050 , color : 123 , qty : 1 } , { item_id : 64233 , size : 048 , color : 834 , qty : 1 } , { item_id : 23984 , size : 000 , color : 341 , qty : 3 } , … ] }
There is also information about where and when the transaction occurred, including a unique transaction identifier:
{ trx_id : 23324827346, store_id : 8934 , date : 20110303 , time : 0947 , …
My initial approach was to put each element on a separate line and combine the elements of the application group using a transaction identifier. This works fine. But now Im trying to use the ability to structure composite columns to save the embedded data of an element in the view (for each element) as follows:
item_id:'size' = <value> ; item_id:'color' = <value> ; item_id:'qty' = <value> ; … 43523:size = 050 ; 43523:color = 123 ; 43523:qty = 1 ; …
The remaining data will be encoded in the composite line of the line as follows:
date : time : store_id : trx_id 20110303 : 0947 : 001 : 23324827346
I need to be able to run queries, such as: all items that were sold between dates 20110301 and 20110310 between times 1200 and 1400 in stores 25 - 50. What I have achieved so far with compound columns was to use one big row on each store and put all other data in 3 different composite columns for each item:
date:time:<type>:prod_id:transaction_id = <value> ; … 20110303:0947:size:43523:23324827346 = 050 ; 20110303:0947:color:43523:23324827346 = 123 ; 20110303:0947:qty:43523:23324827346 = 1 ;
His work, but he really does not look highly effective. Is there any other alternative?