展开查看详情
1 .Cassandra 3.0 Data Modeling
Patrick McFadin
Chief Evangelist, DataStax
@PatrickMcFadin
1
2 .A brief history of CQL
You
3 .CQL 3.0 - Cassandra 1.2
• Goodbye CQL 2.0!
• Custom secondary indexes
• Empty IN
4 .CQL 3.1 - Cassandra 2.0
• Aliases
• CREATE <table> IF NOT EXISTS
• INSERT IF NOT EXISTS
• UPDATE IF
LWT
• DELETE IF EXISTS
• IN supports cluster columns
5 .CQL 3.2 - Cassandra 2.1
• User Defined Types
• Collection Indexing
• Indexes can use contains
• Tuples?
6 .User Defined Types
CREATE TYPE video_metadata (
height int,
width int,
video_bit_rate set<text>,
encoding text
);
7 .User Defined Types
CREATE TABLE videos (
videoid uuid,
userid uuid,
name varchar,
description varchar,
location text,
location_type int,
preview_thumbnails map<text,text>,
tags set<varchar>,
metadata set <frozen<video_metadata>>,
added_date timestamp,
PRIMARY KEY (videoid)
);
8 .CQL 3.3 - Cassandra 2.2
• Date and Time are now types
• TinyInt and SmallInt
• User Defined Functions
• Aggregates
• User Defined Aggregates
9 .User Defined Functions
CREATE TABLE video_rating (
videoid uuid,
rating_counter counter,
rating_total counter,
PRIMARY KEY (videoid)
);
CREATE OR REPLACE FUNCTION
avg_rating (rating_counter counter, rating_total counter)
CALLED ON NULL INPUT
RETURNS double
LANGUAGE java AS
'return Double.valueOf(rating_total.doubleValue()/
rating_counter.doubleValue());';
10 .User Defined Functions
SELECT avg_rating(rating_counter, rating_total) AS avg_rating
FROM video_rating
WHERE videoid = 99051fe9-6a9c-46c2-b949-38ef78858dd0;
11 .Aggregates
CREATE TABLE video_ratings_by_user (
videoid uuid,
userid uuid,
rating int,
PRIMARY KEY (videoid, userid)
);
SELECT count(userid)
FROM video_ratings_by_user
WHERE videoed = 49f64d40-7d89-4890-b910-dbf923563a33
12 .CQL 3.4 - Cassandra 3.x
• CAST operator
• Per Partition Limit
• Materialized Views
• SASI
13 .Materialized View
CREATE TABLE videos (
videoid uuid,
userid uuid,
name varchar,
description varchar, Lookup by this?
location text,
location_type int,
preview_thumbnails map<text,text>,
tags set<varchar>,
metadata set <frozen<video_metadata>>,
added_date timestamp,
PRIMARY KEY (videoid)
);
14 .Materialized View
Roll your own
CREATE TABLE videos_by_location (
videoid uuid,
userid uuid,
location text,
added_date timestamp,
PRIMARY KEY (location, videoid)
);
15 .Materialized View
Cassandra rolls for you
CREATE MATERIALIZED VIEW videos_by_location
AS SELECT userid, added_date, videoid, location
FROM videos
WHERE videoId IS NOT NULL AND location IS NOT NULL
PRIMARY KEY(location, videoid);
16 .Materialized View Perf
17 .Materialized View Perf
5 Materialized Views vs 5 tables writes async
18 .Materialized View
SELECT location, videoid
FROM videos_by_location ;
location | videoid
-------------------------------------------------+--------------------------------------
http://www.youtube.com/watch?v=px6U2n74q3g | 06049cbb-dfed-421f-b889-5f649a0de1ed
http://www.youtube.com/watch?v=qphhxujn5Es | 873ff430-9c23-4e60-be5f-278ea2bb21bd
/us/vid/0c/0c3f7e87-f6b6-41d2-9668-2b64d117102c | 0c3f7e87-f6b6-41d2-9668-2b64d117102c
/us/vid/b3/b3a76c6b-7c7f-4af6-964f-803a9283c401 | 99051fe9-6a9c-46c2-b949-38ef78858dd0
/us/vid/b3/b3a76c6b-7c7f-4af6-964f-803a9283c401 | b3a76c6b-7c7f-4af6-964f-803a9283c401
http://www.youtube.com/watch?v=HdJlsOZVGwM | 49f64d40-7d89-4890-b910-dbf923563a33
/us/vid/41/416a5ddc-00a5-49ed-adde-d99da9a27c0c | 416a5ddc-00a5-49ed-adde-d99da9a27c0c
19 .SASI
CREATE TABLE users (
userid uuid,
firstname varchar,
lastname varchar, Lookup by this?
email text,
created_date timestamp,
PRIMARY KEY (userid)
);
20 .Storage Attached Secondary Index
22 .SASI
CREATE CUSTOM INDEX ON users (firstname)
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = {
'analyzer_class':
'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer',
'case_sensitive': 'false'
};
23 .SASI
CREATE CUSTOM INDEX ON users (lastname)
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = {'mode': 'CONTAINS'};
24 .SASI
CREATE CUSTOM INDEX ON users (created_date)
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = {'mode': 'SPARSE'};
25 .SASI Indexes
INSERT INTO users(userid,firstname,lastname,email,created_date)
Client VALUES (9761d3d7-7fbd-4269-9988-6cfd4e188678,’Patrick’,’McFadin’,
’patrick@datastax.com’,’2015-06-01’);
Node
Memtable
userid 1 firstname lastname email created_date
userid 2 firstname lastname email created_date
Indexer
SSTable SASI Index
SSTable SASI Index
SSTable SASI Index
Data
26 .SASI Queries
userid | created_date | email | firstname | lastname
--------------------------------------+---------------------------------+----------------------+-----------+----------
9761d3d7-7fbd-4269-9988-6cfd4e188678 | 2011-06-20 20:50:00.000000+0000 | patrick@datastax.com | Patrick | McFadin
SELECT * FROM users WHERE firstname LIKE 'pat%';
SELECT * FROM users WHERE lastname LIKE ‘%Fad%';
SELECT * FROM users WHERE email LIKE '%data%';
SELECT * FROM users
WHERE created_date > '2011-6-15'
AND created_date < '2011-06-30';
27 .SASI Guidelines
Use SASI when…
• Multiple fields to search
• No more than 1000 rows returned
• You know the partition key
• Indexing static columns
28 .SASI Guidelines
Don’t Use SASI when…
• Searching large partitions
• Tight SLA on reads
• Search for analytics
• Ordering search is important
热门城市
