sql - Cassandra: Update multiple rows with different values -

hi have similar table in cassandra:

create table testtable( id text,          group text,         date text,         user text,         dept text,         orderby int,         files list<text>,         users list<text>,          family_memebrs list<frozen <member>>,         primary key ((id)));' create index on testtable (user); create index on testtable (dept); create index on testtable (group); create index on testtable (date); 

---

 id    | orderby :----  | :---- 101    |  1 102    |  2 105    |  3 

i want change existing order following ids 105,102,103 in same order. i.e., (105, 1) (102, 2) (103, 3). i'm new cassandra, please me. think possible in sql byusing rownum , join.

i'm new cassandra

i can tell. first clue, order of results. id sole primary key (making partition key) results never come sorted that. this how should sorted:

aploetz@cqlsh:stackoverflow> select id,orderby,token(id) testtable  ;   id  | orderby | system.token(id) -----+---------+---------------------  102 |       2 | -963541259029995480  105 |       3 | 2376737131193407616  101 |       1 | 4965004472028601333  (3 rows) 

unbound queries return results sorted hashed token value of partition key. have run token() function on partition key (id) show this.

i want change existing order following ids 105,102,103 in same order. i.e., (105, 1) (102, 2) (103, 3).

if need change values in orderby column, that's easy:

aploetz@cqlsh:stackoverflow> insert testtable(id,orderby) values ('101',3); aploetz@cqlsh:stackoverflow> insert testtable(id,orderby) values ('102',2); aploetz@cqlsh:stackoverflow> insert testtable(id,orderby) values ('105',1); aploetz@cqlsh:stackoverflow> select id,orderby,token(id) testtable  ;   id  | orderby | system.token(id) -----+---------+---------------------  102 |       2 | -963541259029995480  105 |       1 | 2376737131193407616  101 |       3 | 4965004472028601333  (3 rows) 

as cassandra primary keys unique, inserting new non-key column value key changes orderby.

now if want able sort results orderby column, that's issue entirely, , cannot solved current model.

if that's want do, you'll need new table different primary key definition. i'll create same table 2 changes: i'll name testtable_by_group, , i'll use composite primary key of primary key (group,orderby,id)). can query specific group "group1" , see results sorted.

aploetz@cqlsh:stackoverflow> create table testtable_by_group (group text,id text,orderby int,primary key (group,orderby,id)); aploetz@cqlsh:stackoverflow> insert testtable_by_group(group,id,orderby) values ('group1','101',3); aploetz@cqlsh:stackoverflow> insert testtable_by_group(group,id,orderby) values ('group1','102',2); aploetz@cqlsh:stackoverflow> insert testtable_by_group(group,id,orderby) values ('group1','105',1); aploetz@cqlsh:stackoverflow> select group,id,orderby,token(group) testtable_by_group group='group1';   group  | id  | orderby | system.token(group) --------+-----+---------+----------------------  group1 | 105 |       1 | -2413872665919611707  group1 | 102 |       2 | -2413872665919611707  group1 | 101 |       3 | -2413872665919611707  (3 rows) 

in way, group new partition key. orderby first clustering key, rows within group automatically sorted it. id on end ensure uniqueness, if 2 rows have same orderby.

note left token() function in result set, ran on new partition key (group). can see, key of group1 hashed same token 3 rows, means in multi-node environment 3 rows stored together. can create "hotspot" in cluster, nodes have more data others. that's why primary key definition ensures both query satisfaction and data distribution.

i wrote article datastax on topic while back. give read, , should out: http://www.datastax.com/dev/blog/we-shall-have-order