Cassandra 在2.1中的更新

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',
          '[email protected]',
          { 'home': { street: '1021 West 4th St. #202',
                      city: 'San Fransisco',
                      zip:  }});

  // Adds a work address for our user
  UPDATE user_profiles
     SET addresses = addresses
                   + { 'work': { street: '3975 Freedom Circle Blvd',
                                 city: 'Santa Clara',
                                 zip:  }}
   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: ,
             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: }, 'work': {street: '3975 Freedom Circle Blvd', city: 'Santa Clara', zip: , phones: {{number: '212 221 9165', tags: {'direct line', 'preferred'}}, {number: '500 310 2342', tags: {'fax'}}}}} | [email protected] |        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=;
           

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 (,
               '120-inch 1080p 3D plasma TV',
               ,
               {'tv', '3D', 'hdtv'},
               {'screen' : '120-inch', 'refresh-rate' : '400hz', 'techno' : 'plasma'});

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

  INSERT INTO products(id, description, price, categories, features)
       VALUES (,
               '32-inch LCD TV',
               ,
               {'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