Add columns dynamically in cassandra

Question

I have a table like this in CQL3

create table product_info
(
 key text,
 value text,
 Primary key (key)
);

It is a vertical table . Since I can insert new rows with (key , value ) pair.

Sample data will be :

product_info

  key                |     value       
  -------------------------------------------
  product_name       |   sample_product   
  quantity           |   2
  manufacture        |   sample_manufacturer   
  ....                   ....

But what I need is a horizontal table , where I could able to add columns dynamically without altering the table.

product_info

    product_name     |   quantity   |  manufacture           |  ....
   ------------------------------------------------------------------------------    
    sample_product   |    2         |  sample_manufacturer   |  ....

I need the structure like the above table , need to keep on add the columns on the fly.

CQL3 provides an option to add columns dynamically , but before that we need to alter the table.

I need to know is there any other method which allows this.

I found that by using thrift api it is possible, but since thrift is not more supported , can not use that.

Is there any other API like hector or anything else supporting this ?

I did go through the similar stack overflow posts , but I didn't get a better solution.

My first though was: You are doing it wrong™. Cassandra is already a key/value store and able to handle this exact scenario OOTB. Essentially you are simulating a key/value store within another key/value store, which makes not much sense, IMHO. So the second approach is exactly what you should do and what Cassandra is able to do anyway if you use it right. The first approach is just overcomplicating simple things. — JensG, Dec 23 '14 at 10:22

score 5 · Accepted Answer · answered Dec 23 '14 at 02:43

5

CREATE TABLE product_info(
    product_name text,
    key text,
    value text,
    PRIMARY KEY (product_name, key)
);

Now you can insert up to 2B k/v pairs because the key is now the clustering column.

INSERT INTO product_info (product_name, key, value) 
    VALUES ('iPhone 6', 'quantity', '2');

INSERT INTO product_info (product_name, key, value) 
    VALUES ('iPhone 6', 'manufacturer', 'Apple');

INSERT INTO product_info (product_name, key, value) 
    VALUES ('iPhone 6', 'quantity', '2');

INSERT INTO product_info (product_name, key, value) 
    VALUES ('iPhone 6', 'another column name', 'another column value');

However, you did not specify your query access patterns, so this data model may be totally wrong (or ok) for your application.

answered Dec 23 '14 at 02:43

Akbar Ahmed

1,412
10
9

The answer looks good for normal scenario. But still the result table is vertical. I need a horizontally scaling table. Like , each new value must be added as a column, not as row . – Sujith PS Dec 23 '14 at 05:11
1

Hi Sunjith, C* is wide column. The "rows" are just a CQL abstraction of the underlying physical wide-column. If you look at the data in `cassandra-cli` you'll see that there is a single partition key followed by column after column. The "vertical rows" in CQL are physically wide columns. CQL is nothing more than an abstraction that displays physical wide-columns as vertical rows (similar, but not the same as a CTE in an RDBMS). – Akbar Ahmed Dec 23 '14 at 08:09
@AkbarAhmed I don't think your answer is relevant to his question. In his example, he doesn't care how the data will actually be stored on the disc. If you select from such CF, you'll still see a vertical table. He is asking how to add columns to one row. For instance, he might want to have firmaware versions as new columns without values. In such example, if you select such CF, you'll get a wide row of data. As far as I am aware, it's not possible with CQL3 anymore. – I4004 Mar 12 '15 at 22:28
@I4004 CQL3 still has wide columns. The storage is exactly the same as with thrift, the **only** difference is how the data is projected. CQL projects a relational structure on wide column data. Please see: http://www.datastax.com/dev/blog/does-cql-support-dynamic-columns-wide-rows – Akbar Ahmed Mar 13 '15 at 20:17
@AkbarAhmed, yes I read this article. But you see, the table's structure is defined strictly, and you CANNOT add an arbitrary column to a row easily. Indeed, if you use clustering, the data will actually be stored in a different way, BUT you cannot add a new column anymore, you MUST ALTER the whole CF to do that. Lets consider an example: in my CF I have 2 colums - PK 'id' and 'weight'. So now, I cannot insert a row with an unknown column 'height' anymore, CQL doesn't allow that. That means, I cannot create Wide rows in Thrift-understanding. – I4004 Mar 16 '15 at 21:35

score 1 · Answer 2 · answered Dec 23 '14 at 11:38

1

Have you considered using a map?

create table products(
 id text primary key,
 something text,
 attributes map<text, text>
);

See the end of http://www.datastax.com/documentation/cql/3.1/cql/cql_using/use_collections_c.html

answered Dec 23 '14 at 11:38

ashic

6,367
5
33
54

Yes I did go through the map.But I don't think it is the best solution. Since it is not like what my expectation is.. – Sujith PS Dec 23 '14 at 12:35
What is your expectation? You can add new keys with values on the fly, and you can update the value for a key. Internally, cassandra maintains the mappings so you can think in terms of the map abstraction. – ashic Dec 23 '14 at 13:30

Add columns dynamically in cassandra

2 Answers2