CQL - rows with different columns

Question

Is it possible to store in Cassandra (using CQL) two rows in a one column family with the same column quantity but with a one different column? Something like this:

// column family
'users' : {

    // row 1
    'john' : {
        name: 'John',
        lastname: 'Smith',
        email: '[email protected]'
    }

    // row 2
    'jack' : {
        name: 'Jack',
        lastname: 'Sparrow',
        age: 33
    }
}

My current CQL code:

CREATE KEYSPACE people WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };
USE people;

CREATE COLUMNFAMILY users (
    username varchar PRIMARY KEY,
    name varchar,
    lastname varchar,
    email varchar
);

INSERT INTO users (username, name, lastname, email) VALUES ('john', 'John', 'Smith', '[email protected]');

ALTER TABLE users DROP email;
ALTER TABLE users ADD age int;

INSERT INTO users (username, name, lastname, age) VALUES ('jack', 'Jack', 'Sparrow', 33);

SELECT * FROM users;

OUTPUT:

 username | age  | lastname | name
----------+------+----------+------
     john | null |    Smith | John
     jack |   33 |  Sparrow | Jack

I don't understand your question. What do you mean by "same column quantity but with a one different column?" Maybe read up on cassandra's sparse data model. Basically, we won't create a cell in the underlying sstable if you don't insert a value for a given cql row. I.E. if you hadn't said DROP email, Jack would not have an empty cell for email unless you deliberately inserted a null / tombstone. — phact
So, If I skip ALTER TABLE users DROP email; I will get for Jack's email null value but "physically" this column won't be create for this row, I'm right? — Bakus123
Yep use sstable2json to introspect. Remember you'll have to flush to see the tables. — phact
Thx a lot, that's what I mean :) You can write answer and I will accept it. — Bakus123

phact phact · Accepted Answer · 2015-10-30T03:55:09

Cassandra has a sparse data model. We won't create a cell in the underlying sstable if you don't insert a value for a given cql row.

I.E. if you hadn't said DROP email, Jack would not have an empty cell for email unless you deliberately inserted a null / tombstone.

You can introspect sstables using sstable2json to understand how the data is laid out on disk. Remember to use nodetool flush before you try this or you may end up introspecting an empty directory!

CQL - rows with different columns

1 Answers