CODEWITHSUNDEEP
database-designcassandradata-modelingdatastaxdenormalization
Chamila Wijayarathna
Chamila Wijayarathna

Reputation: 1933

Cassandra sort using updatable query

I have a data set with attributes content and year. I want to put them in to CF 'words' with attributes ('content','year','frequency'). The CF should support following operations.

Is this kind of requirement can be fulfilled using Cassandra? What is the CF structure and indexing I need to use here? What queries should I use to create CF and in indexing?

Upvotes: 0

Views: 138

Answers (2)

Chamila Wijayarathna
Chamila Wijayarathna

Reputation: 1933

I used following table structure as the solution.

create table words (
  year int,
  frequency int,
  content text,
  primary key (year, frequency, content) );

UPDATE statement won't work since we can't change the value of a clustering column. But we can use DELETE and INSERT instead. Since we don't know what the old frequency ahead of time (to do the delete), we'll need to keep another table mapping content,year -> frequency.

Upvotes: 0

catpaws
catpaws

Reputation: 2283

To use ORDER BY, the frequency has to be the second column in a compound PRIMARY KEY (http://www.datastax.com/documentation/cql/3.1/cql/cql_reference/select_r.html?scroll=reference_ds_d35_v2q_xj__using-compound-primary-keys-and-sorting-results). Using the frequency as the key prohibits updates to the value of the key: "Specify the row to update in the WHERE clause by including all columns composing the partition key. The IN relation is supported only for the last column of the partition key. The UPDATE SET operation is not valid on a primary key field." (http://www.datastax.com/documentation/cql/3.1/cql/cql_reference/update_r.html)

create table words (
content text,
year int,
frequency int,
PRIMARY KEY (year, frequency));

insert into words (content, year, frequency) VALUES ('lorem ipsum dolor sit amet', 2014, 10 );
insert into words (content, year, frequency) VALUES ('Sed ut perspiciatis unde', 2010, 3 );
insert into words (content, year, frequency) VALUES ('Excepteur sint occaecat', 2010, 4 );
select content, frequency from words where year = 2010 ORDER BY frequency desc limit 2;

 content                  | frequency
--------------------------+-----------
  Excepteur sint occaecat |         4
 Sed ut perspiciatis unde |         3

(2 rows)

Upvotes: 2

Related Questions