Kassandra How to create a compound column name (not key) using cqlsh

Question

I am trying to model a family of columns 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 simulate are: Get all policies with the entp name, Get all the rules specified by entp, Get all columns with the name entp_name. I plan to model this column family as "wide rows", where one row will 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,

• how to insert a compound column name, for example a policy: p1?
• Using this scheme, you can ask a query like: select * from entp where column_name, for example, "policy: *" and entp_name = xyz, to just read all the columns of the policy?

• How to set a zero value for a column. I read on some forums that you do not need to set to null, because its equivalent does not matter. But consider the case where you have a static circuit 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 for inserting such data (I could not find it in the documentation) because the following message gives an error:

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

Thanks!

Answer 1

• Composites are much easier to work in CQL3, which are available to you in cassandra 1.1, so I will use this in my answer. Tables with multicomponent primary keys in CQL3 are equivalent to wide rows at the storage engine level (Cassandra).

  If I interpret how your policies and rules look, 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 would use it as follows:

   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) 

• With your schema, yes, you could have such a query, but it would be slightly better than with my version, plus CQL2 is deprecated.

• That's right, you just don't paste the value. There is no explicit NULL in cql ( yet ), but you can just do:

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

Hope this helps!

Answer 2

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

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

Here the 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';