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