Cassandra -How to create composite column name (not key) using cqlsh

Question

I'm trying to model a column family in Cassandra 1.1 which logically looks like this:

Entp: //CF
 //rowkey->  entp_name_xyz: 
                   {entp_full_name: "full_name_xyz",
                    some_value: 1,
                    policy: {policy_name: "default policy",
                             type: "type 1",
                             prop_1: "prop 1",
                             ...
                             },
                    rules: {rule_1:, rule_2:,rule_3:}
                   }

The queries I'm trying to model are: Get all policies given an entp name, Get all rules given an entp, Get all columns given an entp_name I'm planning to model this column family as having "wide rows" where one row would look like this:

RowKey:- entp_name_xyz,
column_name:- policy:p1
Value:-{JSON object - {policy_name: "default policy", type: "type 1",                 prop_1: "prop 1", ...}}
column_name:- policy:p2
Value:-{JSON object - {policy_name: "default policy2", type: "type 1",                 prop_1: "prop 1", ...}}
column_name: rule:r1 where r1 is a rowkey of a Rules column family
Value: Null

Now my question is in cqlsh or cassandra-cli,

1. how do I insert a composite column name such as policy:p1?
2. With this scheme, is it possible to have a query like: select * from entp where column_name like "policy:*" and entp_name= xyz in order to just read all the policy columns ?

3. How do I set a null value for a column. I read in some forums that you shouldn't need to set a null because its equivalent to not having a value. But consider the case where you have a static schema with col1, col2 and col3 and I want to insert a row with col3 =null, but with col1 and col2 having some values. What is the cqlsh syntax to insert such data (I could not find it in the documentation) because the following gives an error:

   insert into entp (col1,col2,col3) values ("abc","xyz", null)

Thanks!

Answer 1

1. Composites are far, far easier to work with in CQL3, which is available to you in cassandra 1.1, so I'll use that in my answer. Tables with multiple-component primary keys in CQL3 are equivalent to wide rows in the storage engine (Cassandra) layer.

   If I've interpreted what your policy and rules data looks like, then this is a possible answer:

   CREATE TABLE entp_policies (
       entp_name text,
       policy_name text,
       policy_value text,
       PRIMARY KEY (entp_name, policy_name)
   );
   CREATE TABLE entp_rules (
       entp_name text,
       rule_name text,
       rule_value text,
       PRIMARY KEY (entp_name, rule_name)
   );
   

   You'd use it like this:

   INSERT INTO entp_policies (entp_name, policy_name, policy_value)
        VALUES ('entp_name_xyz', 'p1',
                '{policy_name: "default policy", type: "type 1", ...}');
   
   INSERT INTO entp_policies (entp_name, policy_name, policy_value)
        VALUES ('entp_name_xyz', 'p2',
                '{policy_name: "default policy2", type: "type 1", ...}');
   
   INSERT INTO entp_rules (entp_name, rule_name) VALUES ('entp_name_xyz', 'r1');
   
   -- Get all policies given an entp name
   SELECT * FROM entp_policies WHERE entp_name = 'entp_name_xyz';
   
   -- Get all rules given an entp
   SELECT * FROM entp_rules WHERE entp_name = 'entp_name_xyz';
   
   -- Get all columns given an entp_name (both of the above)
   

2. With your scheme, yes, it would be possible to have a query like that, but it would be a bit more finicky than with my version, plus CQL2 is deprecated.

3. That's right, you just avoid inserting the value. There isn't any explicit NULL in cql (yet), but you could just do:

   insert into entp (col1,col2) values ('abc','xyz');
   

Hope that helps!

Answer 2

You can use both rules and policies in one table if you define the another column in the composite

create table entp_details(
    entp_name text,
    type text,
    name text,
    value text,
    primary key (entp_name, type, name));

In here type is either (Policy or Rule).

INSERT INTO entp_details (entp_name, type, name, value)
     VALUES ('entp_name_xyz', 'Policy', 'p1',
             '{policy_name: "default policy", type: "type 1", ...}');

INSERT INTO entp_details (entp_name,  type, name, value)
     VALUES ('entp_name_xyz', 'Policy', 'p2',
             '{policy_name: "default policy2", type: "type 1", ...}');

INSERT INTO entp_details (entp_name, type, name, value) VALUES ('entp_name_xyz', 'Rule', 'r1', null);

And the queries are like

select * from entp_details WHERE entp_name = 'entp_name_xyz' and type = 'Policy';
select * from entp_details WHERE entp_name = 'entp_name_xyz' and type = 'Rule';