关闭

Cassandra 在2.1中的升级

标签: cassandra数据库
560人阅读 评论(0) 收藏 举报
分类:

Cassandra在2.1中进行了很多有意思的升级,其中有一项升级,是非常不错的,那就是 User defined type,简称就是UDT,代表用户自顶一个类型。下面我们用一个例子来说明。
假如你要存放你的profile,这其中包括以下几个属性,name,email,phone…………,这个时候,一个用户可能有多个email,也可能有多个phone,用关系型数据库的想法,就是创建两个关联表,进行一对多的关联,但是现在我们有UDT后,可以这样来做。

CREATE TYPE address (
      street text,
      city text,
      zip int
  );

  CREATE TABLE user_profiles (
      login text PRIMARY KEY,
      first_name text,
      last_name text,
      email text,
      addresses map<text, frozen<address>>
  );

  // Inserts a user with a home address
  INSERT INTO user_profiles(login, first_name, last_name, email, addresses)
  VALUES ('tsmith',
          'Tom',
          'Smith',
          'tsmith@gmail.com',
          { 'home': { street: '1021 West 4th St. #202',
                      city: 'San Fransisco',
                      zip: 94110 }});

  // Adds a work address for our user
  UPDATE user_profiles
     SET addresses = addresses
                   + { 'work': { street: '3975 Freedom Circle Blvd',
                                 city: 'Santa Clara',
                                 zip: 95050 }}
   WHERE login = 'tsmith';

我们先是创建了一个address的UDT,然后将它在profile中进行引用。使用UDT有很多好处,他非常的灵活,你可以在你的表用去引用这些UDT,同样可以在collection进行引用,同时UDT里面还可以继续定义UDT,假如address里面一个人有很多个phone,我们可以这样

CREATE TYPE phone (
      number text,
      tags set<text>
  );

  // Add a 'phones' field to address that is a set of the 'phone' UDT above
  ALTER TYPE address ADD phones set<frozen<phone>>;

这样就达到了UDT里面嵌套UDT了,那么现在可以这样更新

UPDATE user_profiles
     SET addresses['work'] = {
             street: '3975 Freedom Circle Blvd',
             city: 'Santa Clara',
             zip: 95050,
             phones : {
               {number: '212 221 9165', tags: { 'preferred', 'direct line' }},
               {number: '500 310 2342', tags: { 'fax' }}
             }
         }
   WHERE login = 'tsmith';

然后我们来进行一个查询,结果会是这样

SELECT * FROM user_profiles;

  login  | addresses                                                                                                                                                                                                                                                                       | email            | first_name | last_name
  -------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+------------+-----------
  tsmith | {'home': {street: '1021 West 4th St. #202', city: 'San Fransisco', zip: 94110}, 'work': {street: '3975 Freedom Circle Blvd', city: 'Santa Clara', zip: 95050, phones: {{number: '212 221 9165', tags: {'direct line', 'preferred'}}, {number: '500 310 2342', tags: {'fax'}}}}} | tsmith@gmail.com |        Tom |     Smith

使用了UDT后,我们的查询也可以用.的方式去查询,例如

CREATE TABLE location (
      id int PRIMARY KEY,
      addr frozen<address>, // Reuse the address type from above
      longitude double,
      latitude double
  );

  // The following is allowed
  SELECT addr.street, addr.city FROM location WHERE id=42;

addr.street, addr.city这两个就是用.的方式去查询。
另外在2.1后,UDT可以作为Secondary index,例如

CREATE TABLE products (
      id int PRIMARY KEY,
      description text,
      price int,
      categories set<text>,
      features map<text, text>
  );

  // This is now allowed in Cassandra 2.1
  CREATE INDEX cat_index ON products(categories);
  CREATE INDEX feat_index ON products(features);

  INSERT INTO products(id, description, price, categories, features)
       VALUES (34134,
               '120-inch 1080p 3D plasma TV',
               9999,
               {'tv', '3D', 'hdtv'},
               {'screen' : '120-inch', 'refresh-rate' : '400hz', 'techno' : 'plasma'});

  INSERT INTO products(id, description, price, categories, features)
       VALUES (29412,
               '32-inch LED HDTV (black)',
               929,
               {'tv', 'hdtv'},
               {'screen' : '32-inch', 'techno' : 'LED'});

  INSERT INTO products(id, description, price, categories, features)
       VALUES (38471,
               '32-inch LCD TV',
               110,
               {'tv', 'used'},
               {'screen' : '32-inch', 'techno' : 'LCD'});

  // You can then query those index through CONTAINS
  SELECT id, description FROM products WHERE categories CONTAINS 'hdtv';

   id    | description
  -------+-----------------------------
   29412 |    32-inch LED HDTV (black)
   34134 | 120-inch 1080p 3D plasma TV

  SELECT id, description FROM products WHERE features CONTAINS '32-inch';

   id    | description
  -------+--------------------------
   29412 | 32-inch LED HDTV (black)
   38471 |           32-inch LCD TV

同时,上面我们是对map的value进行了创建index,你也可以通过key去创建index,例如

DROP INDEX feat_index;
  CREATE INDEX feat_key_index ON products(KEYS(features));

  SELECT id, description
    FROM products
    WHERE features CONTAINS KEY 'refresh-rate';

   id    | description
  -------+-----------------------------
   34134 | 120-inch 1080p 3D plasma TV
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:20445次
    • 积分:413
    • 等级:
    • 排名:千里之外
    • 原创:26篇
    • 转载:4篇
    • 译文:0篇
    • 评论:4条
    文章分类
    最新评论