17/07 - Cassandra 3.0 Data Modeling

Cassandra 3.0 Data Modeling
展开查看详情

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

21.SASI

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

29.