CODEWITHSUNDEEP
cassandra
user2243225
user2243225

Reputation: 33

adding set of columns in row in cassandra

i am a newbie in cassandra. I am not sure about adding set of columns in a row many times, e.g., i want to add call related information columns (columns like timestamp_calling_no, timestamp_tower_id, timestamp_start_time, timestamp_end_time, timestamp_duration, timestamp_call_type,etc.) in a row whenever same mobile number make a call by using hector/astyanax/java/CQL.

please give your suggestions. Thanx in advance.

Upvotes: 2

Views: 332

Answers (3)

adityak
adityak

Reputation: 192

adding columns to a row is an inexpensive operation and one more thing cassandra stores column names as sorted, so by using timestamp as a column name will solve the problem of slicing.cassandra can have 2 billion columns in a row in the CDR CF, so we can easily keep adding columns. you were trying to run a query which required cassandra to scan all rows then yes, it would perform poorly.

Upvotes: 1

Easility
Easility

Reputation: 716

In frameworks like Playorm, there are multiple ways to do that. For e.g. you can use @OneToMany OR @NoSqlEmbedded pattern. For more details, visit http://buffalosw.com/wiki/Patterns-Page/

Upvotes: 1

Lyuben Todorov
Lyuben Todorov

Reputation: 14153

It's good that you recognise that there are a number of APIs available. I would recommend using CQL3, mostly because thrift apis are now being kept only for backwards compatibility and because CQL can be used with most languages while Astyanax and hector are java specific.

I'd use a compound key (under "Clustering, compound keys, and more"). 

//An example keyspace using CQL2
cqlsh>
 CREATE KEYSPACE calls WITH strategy_class = 'SimpleStrategy'
 AND strategy_options:replication_factor = 1;

//And next create a CQL3 table with a compound key
//Compound key is formed from the number and call's start time
cqlsh>
 CREATE TABLE calls.calldata (
      number text,
      timestamp_start_time timestamp,
      timestamp_end_time timestamp,
      PRIMARY KEY (number, timestamp_start_time)
    ) WITH COMPACT STORAGE

The above schema would allow you to insert a row containing the same number as a key multiple times but because the start of the call is part of the key, it will ensure that the combination creates a unique key each time.

Next insert some test data using CQL3(for the purpose of the example of-course)

cqlsh>  //This example data below uses 2 diffrent numbers
 insert into calls.calldata (number, timestamp_start_time, timestamp_end_time)
 values ('+441234567890', 1335361733545850, 1335361773545850);
 insert into calls.calldata (number, timestamp_start_time, timestamp_end_time)
 values ('+440987654321', 1335361734678700, 1335361737678700);
 insert into calls.calldata (number, timestamp_start_time, timestamp_end_time)
 values ('+441234567890', 1335361738208700, 1335361738900032);
 insert into calls.calldata (number, timestamp_start_time, timestamp_end_time)
 values ('+441234567890', 1335361740100277, 1335361740131251);
 insert into calls.calldata (number, timestamp_start_time, timestamp_end_time)
 values ('+440987654321', 1335361740176666, 1335361740213000);

And now we can retreive all the data (again using CQL3):

cqlsh> SELECT * FROM calls.calldata;

 number        | timestamp_start_time      | timestamp_end_time
---------------+---------------------------+---------------------------
 +440987654321 | 44285-12-05 15:11:18+0000 | 44285-12-05 16:01:18+0000
 +440987654321 | 44285-12-05 16:42:56+0000 | 44285-12-05 16:43:33+0000
 +441234567890 | 44285-12-05 14:52:25+0000 | 44285-12-06 01:59:05+0000
 +441234567890 | 44285-12-05 16:10:08+0000 | 44285-12-05 16:21:40+0000
 +441234567890 | 44285-12-05 16:41:40+0000 | 44285-12-05 16:42:11+0000

Or part of of the data. Because a compound key is used you can retrieve all the rows of a specific number using CQL3:

cqlsh> SELECT * FROM calls.calldata WHERE number='+441234567890';     

 number        | timestamp_start_time      | timestamp_end_time
---------------+---------------------------+---------------------------
 +441234567890 | 44285-12-05 14:52:25+0000 | 44285-12-06 01:59:05+0000
 +441234567890 | 44285-12-05 16:10:08+0000 | 44285-12-05 16:21:40+0000
 +441234567890 | 44285-12-05 16:41:40+0000 | 44285-12-05 16:42:11+0000

And if you want to be really specific, you can retrieve a specific number where the call started at a specific time (again thanks to the compound key)

    cqlsh> SELECT * FROM calls.calldata WHERE number='+441234567890' 
           and timestamp_start_time=1335361733545850;

 number        | timestamp_start_time      | timestamp_end_time
---------------+---------------------------+---------------------------
 +441234567890 | 44285-12-05 14:52:25+0000 | 44285-12-06 01:59:05+0000

Upvotes: 1

Related Questions