Cassandra - 如何使用cqlsh创建复合列名称（而不是键） [英] Cassandra -How to create composite column name (not key) using cqlsh

问题描述

  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，
 ... 
}，
规则：{rule_1 :, rule_2 :, rule_3：} 
} 
  

我想要建模的查询是：
获取所有给定entp名称的策略，获取所有规则给定entp，Get所有列给定一个entp_name
我计划将此列族建模为宽行，其中一行如下所示：

  RowKey： -  entp_name_xyz，
 column_name： -  policy：p1 
值： -  {JSON object  -  {policy_name：default policy，type：type 1，prop_1： prop 1，...}} 
 column_name： -  policy：p2 
值： -  {JSON对象 -  {policy_name：default policy2，type：type 1，prop_1：prop 1 ，...}} 
 column_name：rule：r1其中r1是Rules列族的rowkey 
值：Null 
  

现在我的问题是在cqlsh或cassandra-cli中，

1. 复合列名称，如policy：p1？


2. 使用此方案，是否可能有一个查询如：
   select * from entp where column_name likepolicy：* entp_name = xyz，以便只读取所有策略列？



3. 如何为列设置空值。我在一些论坛读到，你不应该需要设置一个null，因为它相当于没有值。但是考虑这样的情况，
   你有一个静态模式col1，col2和col3，我想插入一行col3 = null，但col1和col2有一些值。什么是cqlsh语法插入这样的数据（我在文档中找不到它），因为下面给出一个错误：

   
   
   

   insert into entp（col1，col2，col3 ）值（abc，xyz，null）

解决方案

1. 复合材料远比CQL3更容易使用给你在cassandra 1.1，所以我会在我的答案使用。 CQL3中具有多组件主键的表等同于存储引擎（Cassandra）层中的宽行。

   
   
   

   如果我解释了您的策略和规则数据的外观那么这是一个可能的答案：

     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）
   ）; 
     

   您可以这样使用：

   
   
   < pre-name =lang-sql> 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'）;

- 获取所有给定entp名称的策略
SELECT * FROM entp_policies WHERE entp_name ='entp_name_xyz';

- 获取所有给定entp的规则
SELECT * FROM entp_rules WHERE entp_name ='entp_name_xyz';

- 获取给定entp_name的所有列（上述两者）







3. 没错，你只是避免插入值。在cql中没有任何显式的 NULL （尚未），但您可以这样做：

     insert into entp col1，col2）values（'abc'，'xyz'）; 
     

希望有所帮助！

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!

解决方案

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!

这篇关于Cassandra - 如何使用cqlsh创建复合列名称（而不是键）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！