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.

Horotelic

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. – Chaps

Answer 1

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.

Atwood

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 . – Horotelic

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). – Atwood

@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. – Goree

@Goree 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: datastax.com/dev/blog/… – Atwood

@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. – Goree

Answer 2

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

Urbanist

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.. – Horotelic

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. – Urbanist