DataStax Academy, profile picture
Uploaded byDataStax Academy
2,344 views

Introduction to Data Modeling with Apache Cassandra

This document provides an introduction to data modeling with Apache Cassandra. It discusses how Cassandra data models are designed based on the queries an application will perform, unlike relational databases which are designed based on normalization rules. Key aspects covered include avoiding joins by denormalizing data, using a partition key to group related data on nodes, and controlling the clustering order of columns. The document provides examples of modeling time series and tag data in Cassandra.

Technology◦
Related topics:
Data Modeling Techniques•
In this document
Powered by AI

Introduction to Data Modeling using Apache Cassandra by Patrick McFadin, Chief Evangelist for Apache Cassandra.

Explains relational data models including normal forms, foreign keys, joins, and relational modeling structures.

Introduction to Cassandra data models, focusing on application workflows, data access, and the importance of pre-defined queries.

Details user workflows and necessary queries to support the operations of the KillrVideo application.

Contrasts CQL with SQL, highlighting the absence of joins and the concept of denormalization in Cassandra.

Demonstrates how to create tables in Cassandra, focusing on data types, primary key designations, and insert commands.

Illustrates partition keys, data insertion, and the significance of UUIDs and natural keys in Cassandra.

Explains dynamic and 'static' tables in Cassandra, focusing on primary key relationships with partition keys and clustering columns.

Describes partitioning and clustering in Cassandra, emphasizing the organization of data in tables and clusters.

Discusses data storage models, including logical view, disk layout, read and write paths for efficient data retrieval.

Describes query patterns in Cassandra focusing on range queries and disk operations for data extraction.

Introduces CQL collections, their operations, dynamics, and how they interact with tables in Cassandra.

Demonstrates various operations on CQL sets, lists, and maps, showing how to manipulate collection types.

Discusses entity modeling with collections in Cassandra and the use of index tables for fast data access.

Outlines user management in Cassandra, including table structures for user credentials and video management.

Explains multiple lookup patterns and the implementation of many-to-many relationships in Cassandra.

Describes methods for deleting data, handling tombstones, and managing data expiration using TTL in Cassandra.

Ends with a thank you message and invitation for questions, encouraging follow-up on Twitter.

Embed presentation

Downloaded 116 times
©2013 DataStax Confidential. Do not distribute without consent. @PatrickMcFadin Patrick McFadin
 Chief Evangelist for Apache Cassandra Introduction to Data Modeling with Apache Cassandra 1
Relational Data Models • 5 normal forms • Foreign Keys • Joins deptId First Last 1 Edgar Codd 2 Raymond Boyce id Dept 1 Engineering 2 Math Employees Department
Relational Modeling CREATE TABLE users (
 id number(12) NOT NULL ,
 firstname nvarchar2(25) NOT NULL ,
 lastname nvarchar2(25) NOT NULL,
 email nvarchar2(50) NOT NULL,
 password nvarchar2(255) NOT NULL,
 created_date timestamp(6),
 PRIMARY KEY (id),
 CONSTRAINT email_uq UNIQUE (email)
 ); -- Users by email address index
 CREATE INDEX idx_users_email ON users (email); • Create entity table • Add constraints • Index fields • Foreign Key relationships CREATE TABLE videos (
 id number(12),
 userid number(12) NOT NULL,
 name nvarchar2(255),
 description nvarchar2(500),
 location nvarchar2(255),
 location_type int,
 added_date timestamp,
 CONSTRAINT users_userid_fk FOREIGN KEY (userid) REFERENCES users (Id) ON DELETE CASCADE,
 PRIMARY KEY (id)
 );
Relational Modeling Data Models Application
Cassandra Modeling Data Models Application
• What are your application’s workflows? • How will I access the data? • Knowing your queries in advance is NOT optional • Different from RDBMS because I can’t just JOIN or create a new indexes to support new queries 7 Modeling Queries
Some Application Workflows in KillrVideo 8 User Logs into site Show basic information about user Show videos added by a user Show comments posted by a user Search for a video by tag Show latest videos added to the site Show comments for a video Show ratings for a video Show video and its details
Some Queries in KillrVideo to Support Workflows 9 Users User Logs into site Find user by email address Show basic information about user Find user by id Comments Show comments for a video Find comments by video (latest first) Show comments posted by a user Find comments by user (latest first) Ratings Show ratings for a video Find ratings by video
CQL vs SQL • No joins • Limited aggregations deptId First Last 1 Edgar Codd 2 Raymond Boyce id Dept 1 Engineering 2 Math Employees Department SELECT e.First, e.Last, d.Dept FROM Department d, Employees e WHERE ‘Codd’ = e.Last AND e.deptId = d.id
Denormalization • Combine table columns into a single view • Eliminate the need for joins SELECT First, Last, Dept FROM employees WHERE id = ‘1’ id First Last Dept 1 Edgar Codd Engineering 2 Raymond Boyce Math Employees
“Static” Table CREATE TABLE videos (
 videoid uuid,
 userid uuid,
 name varchar,
 description varchar,
 location text,
 location_type int,
 preview_thumbnails map<text,text>,
 tags set<varchar>,
 added_date timestamp,
 PRIMARY KEY (videoid)
 ); Table Name Column Name Column CQL Type Primary Key Designation Partition Key
Insert INSERT INTO videos (videoid, name, userid, description, location, location_type, preview_thumbnails, tags, added_date, metadata)
 VALUES (06049cbb-dfed-421f-b889-5f649a0de1ed,'The data model is dead. Long live the data model.', 9761d3d7-7fbd-4269-9988-6cfd4e188678, 
 'First in a three part series for Cassandra Data Modeling','http://www.youtube.com/watch?v=px6U2n74q3g',1,
 {'YouTube':'http://www.youtube.com/watch?v=px6U2n74q3g'},{'cassandra','data model','relational','instruction'},
 '2013-05-02 12:30:29'); Table Name Fields Values Partition Key: Required
Partition keys 06049cbb-dfed-421f-b889-5f649a0de1ed Murmur3 Hash Token = 7224631062609997448 873ff430-9c23-4e60-be5f-278ea2bb21bd Murmur3 Hash Token = -6804302034103043898 Consistent hash. 128 bit number between 2-63 and 264 INSERT INTO videos (videoid, name, userid, description)
 VALUES (06049cbb-dfed-421f-b889-5f649a0de1ed,'The data model is dead. Long live the data model.’, 9761d3d7-7fbd-4269-9988-6cfd4e188678, 'First in a three part series for Cassandra Data Modeling'); INSERT INTO videos (videoid, name, userid, description)
 VALUES (873ff430-9c23-4e60-be5f-278ea2bb21bd,'Become a Super Modeler’, 9761d3d7-7fbd-4269-9988-6cfd4e188678, 'Second in a three part series for Cassandra Data Modeling');
Select name | description | added_date
 ---------------------------------------------------+----------------------------------------------------------+--------------------------
 The data model is dead. Long live the data model. | First in a three part series for Cassandra Data Modeling | 2013-05-02 12:30:29-0700 SELECT name, description, added_date
 FROM videos
 WHERE videoid = 06049cbb-dfed-421f-b889-5f649a0de1ed; Fields Table Name Primary Key: Partition Key Required
Locality 1000 Node Cluster videoid = 06049cbb-dfed-421f-b889-5f649a0de1ed SELECT name, description, added_date
 FROM videos
 WHERE videoid = 06049cbb-dfed-421f-b889-5f649a0de1ed;
No more sequences • Great for auto-creation of Ids • Guaranteed unique • Needs ACID to work. (Sorry. No sharding) INSERT INTO user (id, firstName, LastName) VALUES (users_sequence.nextVal(), ‘Ted’, ‘Codd’) CREATE SEQUENCE users_sequence
 INCREMENT BY 1
 START WITH 1
 NOMAXVALUE
 NOCYCLE
 CACHE 10;
No sequences??? • Almost impossible in a distributed system • Couple of great choices • Natural Key - Unique values like email • Surrogate Key - UUID • Universal Unique ID • 128 bit number represented in character form • Easily generated on the client • Same as GUID for the MS folks 99051fe9-6a9c-46c2-b949-38ef78858dd0
“Dynamic” Table CREATE TABLE videos_by_tag (
 tag text,
 videoid uuid,
 added_date timestamp,
 name text,
 preview_image_location text,
 tagged_date timestamp,
 PRIMARY KEY (tag, videoid)
 ); Partition Key Clustering Column
Primary key relationship PRIMARY KEY (tag,videoid)
Primary key relationship Partition Key PRIMARY KEY (tag,videoid)
Primary key relationship Partition Key Clustering Column PRIMARY KEY (tag,videoid)
Primary key relationship Partition Key data model PRIMARY KEY (tag,videoid) Clustering Column
-5.6 06049cbb-dfed-421f-b889-5f649a0de1ed Primary key relationship Partition Key 2013-05-16 16:50:002013-05-02 12:30:29 873ff430-9c23-4e60-be5f-278ea2bb21bd PRIMARY KEY (tag,videoid) Clustering Column data model 49f64d40-7d89-4890-b910-dbf923563a33 2013-06-11 11:00:00
Row Column 1 Partition Key 1 Column 2 Column 3 Column 4
Partition with Clustering Cluster 1 Partition Key 1 Column 1 Column 2 Column 3 Cluster 2 Partition Key 1 Column 1 Column 2 Column 3 Cluster 3 Partition Key 1 Column 1 Column 2 Column 3 Cluster 4 Partition Key 1 Column 1 Column 2 Column 3 Order By
Table Partition Key 1 Partition Key 1 Partition Key 1 Partition Key 1 Partition Key 2 Partition Key 2 Partition Key 2 Partition Key 2 Cluster 1 Column 1 Column 2 Column 3 Cluster 2 Column 1 Column 2 Column 3 Cluster 3 Column 1 Column 2 Column 3 Cluster 4 Column 1 Column 2 Column 3 Cluster 1 Column 1 Column 2 Column 3 Cluster 2 Column 1 Column 2 Column 3 Cluster 3 Column 1 Column 2 Column 3 Cluster 4 Column 1 Column 2 Column 3
Keyspace Cluster 1 Partition Key 1 Column 2 Column 3 Column 4 Partition Key 2 Column 2 Column 3 Column 4 Cluster 2 Partition Key 1 Column 2 Column 3 Column 4 Cluster 3 Partition Key 1 Column 2 Column 3 Column 4 Cluster 4 Partition Key 1 Column 2 Column 3 Column 4 Partition Key 2 Column 2 Column 3 Column 4 Partition Key 2 Column 2 Column 3 Column 4 Partition Key 2 Column 2 Column 3 Column 4 Partition Key 1 Column 2 Column 3 Column 4 Partition Key 2 Column 2 Column 3 Column 4 Partition Key 1 Column 2 Column 3 Column 4 Partition Key 1 Column 2 Column 3 Column 4 Partition Key 1 Column 2 Column 3 Column 4 Partition Key 2 Column 2 Column 3 Column 4 Partition Key 2 Column 2 Column 3 Column 4 Partition Key 2 Column 2 Column 3 Column 4 Table 1 Table 2 Keyspace 1 Cluster 1 Cluster 2 Cluster 3 Cluster 4 Cluster 1 Cluster 2 Cluster 3 Cluster 4 Cluster 1 Cluster 2 Cluster 3 Cluster 4
Controlling Order CREATE TABLE raw_weather_data (
 wsid text,
 year int,
 month int,
 day int,
 hour int,
 temperature double,
 PRIMARY KEY ((wsid), year, month, day, hour)
 ) WITH CLUSTERING ORDER BY (year DESC, month DESC, day DESC, hour DESC); INSERT INTO raw_weather_data(wsid,year,month,day,hour,temperature)
 VALUES (‘10010:99999’,2005,12,1,10,-5.6); INSERT INTO raw_weather_data(wsid,year,month,day,hour,temperature)
 VALUES (‘10010:99999’,2005,12,1,9,-5.1); INSERT INTO raw_weather_data(wsid,year,month,day,hour,temperature)
 VALUES (‘10010:99999’,2005,12,1,8,-4.9); INSERT INTO raw_weather_data(wsid,year,month,day,hour,temperature)
 VALUES (‘10010:99999’,2005,12,1,7,-5.3);
Clustering Order 200510010:99999 12 1 10 200510010:99999 12 1 9 raw_weather_data -5.6 -5.1 200510010:99999 12 1 8 200510010:99999 12 1 7 -4.9 -5.3 Order By DESC
Write Path Client INSERT INTO raw_weather_data(wsid,year,month,day,hour,temperature)
 VALUES (‘10010:99999’,2005,12,1,7,-5.3); year 1wsid 1 month 1 day 1 hour 1 year 2wsid 2 month 2 day 2 hour 2 Memtable SSTable SSTable SSTable SSTable Node Commit Log Data * Compaction * Temp Temp
Storage Model - Logical View 2005:12:1:10 -5.6 2005:12:1:9 -5.1 2005:12:1:8 -4.9 10010:99999 10010:99999 10010:99999 wsid hour temperature 2005:12:1:7 -5.3 10010:99999 SELECT wsid, hour, temperature
 FROM raw_weather_data
 WHERE wsid=‘10010:99999’
 AND year = 2005 AND month = 12 AND day = 1;
2005:12:1:10 -5.6 -5.3-4.9-5.1 Storage Model - Disk Layout 2005:12:1:9 2005:12:1:8 10010:99999 2005:12:1:7 Merged, Sorted and Stored Sequentially SELECT wsid, hour, temperature
 FROM raw_weather_data
 WHERE wsid=‘10010:99999’
 AND year = 2005 AND month = 12 AND day = 1;
2005:12:1:10 -5.6 2005:12:1:11 -4.9 -5.3-4.9-5.1 Storage Model - Disk Layout 2005:12:1:9 2005:12:1:8 10010:99999 2005:12:1:7 Merged, Sorted and Stored Sequentially SELECT wsid, hour, temperature
 FROM raw_weather_data
 WHERE wsid=‘10010:99999’
 AND year = 2005 AND month = 12 AND day = 1;
2005:12:1:10 -5.6 2005:12:1:11 -4.9 -5.3-4.9-5.1 Storage Model - Disk Layout 2005:12:1:9 2005:12:1:8 10010:99999 2005:12:1:7 Merged, Sorted and Stored Sequentially SELECT wsid, hour, temperature
 FROM raw_weather_data
 WHERE wsid=‘10010:99999’
 AND year = 2005 AND month = 12 AND day = 1; 2005:12:1:12 -5.4
Read Path Client SSTable SSTable SSTable Node Data SELECT wsid,hour,temperature
 FROM raw_weather_data
 WHERE wsid='10010:99999'
 AND year = 2005 AND month = 12 AND day = 1 
 AND hour >= 7 AND hour <= 10; year 1wsid 1 month 1 day 1 hour 1 year 2wsid 2 month 2 day 2 hour 2 Memtable Temp Temp
Query patterns • Range queries • “Slice” operation on disk Single seek on disk 10010:99999 Partition key for locality SELECT wsid,hour,temperature
 FROM raw_weather_data
 WHERE wsid='10010:99999'
 AND year = 2005 AND month = 12 AND day = 1 
 AND hour >= 7 AND hour <= 10; 2005:12:1:10 -5.6 -5.3-4.9-5.1 2005:12:1:9 2005:12:1:8 2005:12:1:7
Query patterns • Range queries • “Slice” operation on disk Programmers like this Sorted by event_time 2005:12:1:10 -5.6 2005:12:1:9 -5.1 2005:12:1:8 -4.9 10010:99999 10010:99999 10010:99999 weather_station hour temperature 2005:12:1:7 -5.3 10010:99999 SELECT weatherstation,hour,temperature FROM temperature WHERE weatherstation_id=‘10010:99999' AND year = 2005 AND month = 12 AND day = 1 AND hour >= 7 AND hour <= 10;
CQL Collections
CQL Collections • Meant to be dynamic part of table • Update syntax is very different from insert • Reads require all of collection to be read
CQL Set • Set is sorted by CQL type comparator INSERT INTO collections_example (id, set_example) VALUES(1, {'1-one', '2-two'}); set_example set<text> Collection name Collection type CQLType
CQL Set Operations • Adding an element to the set • After adding this element, it will sort to the beginning. • Removing an element from the set UPDATE collections_example SET set_example = set_example + {'3-three'} WHERE id = 1; UPDATE collections_example SET set_example = set_example + {'0-zero'} WHERE id = 1; UPDATE collections_example SET set_example = set_example - {'3-three'} WHERE id = 1;
CQL List • Ordered by insertion • Use with caution list_example list<text> Collection name Collection type INSERT INTO collections_example (id, list_example) VALUES(1, ['1-one', '2-two']); CQLType
CQL List Operations • Adding an element to the end of a list • Adding an element to the beginning of a list • Deleting an element from a list UPDATE collections_example SET list_example = list_example + ['3-three'] WHERE id = 1; UPDATE collections_example SET list_example = ['0-zero'] + list_example WHERE id = 1; UPDATE collections_example SET list_example = list_example - ['3-three'] WHERE id = 1;
CQL Map • Key and value • Key is sorted by CQL type comparator INSERT INTO collections_example (id, map_example) VALUES(1, { 1 : 'one', 2 : 'two' }); map_example map<int,text> Collection name Collection type Value CQLTypeKey CQLType
CQL Map Operations • Add an element to the map • Update an existing element in the map • Delete an element in the map UPDATE collections_example SET map_example[3] = 'three' WHERE id = 1; UPDATE collections_example SET map_example[3] = 'tres' WHERE id = 1; DELETE map_example[3] FROM collections_example WHERE id = 1;
Entity with collections • Same type of entity • SET type for dynamic data • tags for each video // Videos by id CREATE TABLE videos ( videoid uuid, userid uuid, name text, description text, location text, location_type int, preview_image_location text, tags set<text>, added_date timestamp, PRIMARY KEY (videoid) );
Index (or lookup) tables • Table arranged to find data • Denormalized for speed
Users – The Cassandra Way User Logs into site Find user by email address Show basic information about user Find user by id CREATE TABLE user_credentials (
 email text,
 password text,
 userid uuid,
 PRIMARY KEY (email)
 ); CREATE TABLE users (
 userid uuid,
 firstname text,
 lastname text,
 email text,
 created_date timestamp,
 PRIMARY KEY (userid)
 );
50 Show video and its details Find video by id Show videos added by a user Find videos by user (latest first) CREATE TABLE videos (
 videoid uuid,
 userid uuid,
 name text,
 description text,
 location text,
 location_type int,
 preview_image_location text,
 tags set<text>,
 added_date timestamp,
 PRIMARY KEY (videoid)
 ); CREATE TABLE user_videos (
 userid uuid,
 added_date timestamp,
 videoid uuid,
 name text,
 preview_image_location text,
 PRIMARY KEY (userid, added_date, videoid)
 ) WITH CLUSTERING ORDER BY (added_date DESC, videoid ASC); Views or indexes? Denormalized data
Multiple Lookups • Same data • Different lookup pattern // Index for tag keywords CREATE TABLE videos_by_tag ( tag text, videoid uuid, added_date timestamp, name text, preview_image_location text, tagged_date timestamp, PRIMARY KEY (tag, videoid) ); // Index for tags by first letter in the tag CREATE TABLE tags_by_letter ( first_letter text, tag text, PRIMARY KEY (first_letter, tag) );
Many to Many Relationships • Two views • Different directions • Insert data in a batch // Comments for a given video CREATE TABLE comments_by_video ( videoid uuid, commentid timeuuid, userid uuid, comment text, PRIMARY KEY (videoid, commentid) ) WITH CLUSTERING ORDER BY (commentid DESC); // Comments for a given user CREATE TABLE comments_by_user ( userid uuid, commentid timeuuid, videoid uuid, comment text, PRIMARY KEY (userid, commentid) ) WITH CLUSTERING ORDER BY (commentid DESC);
Deleting Data
Delete DELETE FROM videos
 WHERE id = 06049cbb-dfed-421f-b889-5f649a0de1ed; Table Name Primary Key: Required
Tombstones -5.6 06049cbb-dfed-421f-b889-5f649a0de1ed 2013-05-16 16:50:002013-05-02 12:30:29 873ff430-9c23-4e60-be5f-278ea2bb21bd data model 49f64d40-7d89-4890-b910-dbf923563a33 2013-06-11 11:00:00 DELETE FROM videos_by_tag
 WHERE videioId = 06049cbb-dfed-421f-b889-5f649a0de1ed; Deleted 2016-02-02 08:15:41
Expiring Data Time To Live = TTL INSERT INTO videos (videoid, name, userid, description, location, location_type, preview_thumbnails, tags, added_date, metadata)
 VALUES (06049cbb-dfed-421f-b889-5f649a0de1ed,'The data model is dead. Long live the data model.', 9761d3d7-7fbd-4269-9988-6cfd4e188678, 
 'First in a three part series for Cassandra Data Modeling','http://www.youtube.com/watch?v=px6U2n74q3g',1,
 {'YouTube':'http://www.youtube.com/watch?v=px6U2n74q3g'},{'cassandra','data model','relational','instruction'},
 '2013-05-02 12:30:29’) USING TTL = 2592000 Expire Data: 30 Days
Thank you! Bring the questions Follow me on twitter @PatrickMcFadin

More Related Content

PDF
Basics of React Hooks.pptx.pdf
byKnoldus Inc.
 
PPTX
Getting the Scylla Shard-Aware Drivers Faster
byScyllaDB
 
PDF
15 Troubleshooting Tips and Tricks for database 21c - OGBEMEA KSAOUG
bySandesh Rao
 
PDF
Object-oriented Programming in Python
byJuan-Manuel Gimeno
 
PDF
OOPs & Inheritance Notes
byShalabh Chaudhary
 
PPTX
DNS AND DDNS
byVirendra thakur
 
PPTX
Oracle Data Redaction
byAlex Zaballa
 
PDF
Packages - PL/SQL
byEsmita Gupta
 
Basics of React Hooks.pptx.pdf
byKnoldus Inc.
 
Getting the Scylla Shard-Aware Drivers Faster
byScyllaDB
 
15 Troubleshooting Tips and Tricks for database 21c - OGBEMEA KSAOUG
bySandesh Rao
 
Object-oriented Programming in Python
byJuan-Manuel Gimeno
 
OOPs & Inheritance Notes
byShalabh Chaudhary
 
DNS AND DDNS
byVirendra thakur
 
Oracle Data Redaction
byAlex Zaballa
 
Packages - PL/SQL
byEsmita Gupta
 

What's hot

PPT
Using AWR for IO Subsystem Analysis
byTexas Memory Systems, and IBM Company
 
PPTX
Spring mvc
byPravin Pundge
 
PDF
ClickHouse new features and development roadmap, by Aleksei Milovidov
byAltinity Ltd
 
PPTX
Arrays C#
byRaghuveer Guthikonda
 
PPTX
Fetch API Talk
byChiamaka Nwolisa
 
PPTX
Bringing NetApp Data ONTAP & Apache CloudStack Together
byDavid La Motta
 
Using AWR for IO Subsystem Analysis
byTexas Memory Systems, and IBM Company
 
Spring mvc
byPravin Pundge
 
ClickHouse new features and development roadmap, by Aleksei Milovidov
byAltinity Ltd
 
Arrays C#
byRaghuveer Guthikonda
 
Fetch API Talk
byChiamaka Nwolisa
 
Bringing NetApp Data ONTAP & Apache CloudStack Together
byDavid La Motta
 

Viewers also liked

PPTX
Bad Habits Die Hard
byDataStax Academy
 
PDF
Analytics with Spark and Cassandra
byDataStax Academy
 
PDF
Standing Up Your First Cluster
byDataStax Academy
 
PDF
Real Time Analytics with Dse
byDataStax Academy
 
PDF
Cassandra Data Maintenance with Spark
byDataStax Academy
 
PDF
Advanced Data Modeling with Apache Cassandra
byDataStax Academy
 
PDF
Advanced Cassandra
byDataStax Academy
 
Bad Habits Die Hard
byDataStax Academy
 
Analytics with Spark and Cassandra
byDataStax Academy
 
Standing Up Your First Cluster
byDataStax Academy
 
Real Time Analytics with Dse
byDataStax Academy
 
Cassandra Data Maintenance with Spark
byDataStax Academy
 
Advanced Data Modeling with Apache Cassandra
byDataStax Academy
 
Advanced Cassandra
byDataStax Academy
 

Similar to Introduction to Data Modeling with Apache Cassandra

PDF
DataStax: Old Dogs, New Tricks. Teaching your Relational DBA to fetch
byDataStax Academy
 
PDF
Cassandra Day Atlanta 2015: Data Modeling 101
byDataStax Academy
 
PDF
Cassandra Day Chicago 2015: Apache Cassandra Data Modeling 101
byDataStax Academy
 
PDF
Cassandra Day London 2015: Data Modeling 101
byDataStax Academy
 
PDF
Introduction to data modeling with apache cassandra
byPatrick McFadin
 
PDF
Cassandra Data Modeling
byBen Knear
 
PDF
Introduction to Data Modeling with Apache Cassandra
byLuke Tillman
 
PDF
Apache Cassandra & Data Modeling
byMassimiliano Tomassi
 
PDF
Oracle to Cassandra Core Concepts Guide Pt. 2
byDataStax Academy
 
PDF
Cassandra Day Denver 2014: A Cassandra Data Model for Serving up Cat Videos
byDataStax Academy
 
PDF
Cassandra - lesson learned
byAndrzej Ludwikowski
 
PDF
Javantura v2 - Data modeling with Apapche Cassandra - Marko Švaljek
byHUJAK - Hrvatska udruga Java korisnika / Croatian Java User Association
 
PDF
The data model is dead, long live the data model
byPatrick McFadin
 
PDF
Building your First Application with Cassandra
byLuke Tillman
 
PDF
KillrVideo: Data Modeling Evolved (Patrick McFadin, Datastax) | Cassandra Sum...
byDataStax
 
PDF
Cassandra 3.0 advanced preview
byPatrick McFadin
 
PPTX
Apache Cassandra Developer Training Slide Deck
byDataStax Academy
 
PDF
Become a super modeler
byPatrick McFadin
 
PDF
Cassandra lesson learned - extended
byAndrzej Ludwikowski
 
PDF
Cassandra 3.0 Data Modeling
byDataStax Academy
 
DataStax: Old Dogs, New Tricks. Teaching your Relational DBA to fetch
byDataStax Academy
 
Cassandra Day Atlanta 2015: Data Modeling 101
byDataStax Academy
 
Cassandra Day Chicago 2015: Apache Cassandra Data Modeling 101
byDataStax Academy
 
Cassandra Day London 2015: Data Modeling 101
byDataStax Academy
 
Introduction to data modeling with apache cassandra
byPatrick McFadin
 
Cassandra Data Modeling
byBen Knear
 
Introduction to Data Modeling with Apache Cassandra
byLuke Tillman
 
Apache Cassandra & Data Modeling
byMassimiliano Tomassi
 
Oracle to Cassandra Core Concepts Guide Pt. 2
byDataStax Academy
 
Cassandra Day Denver 2014: A Cassandra Data Model for Serving up Cat Videos
byDataStax Academy
 
Cassandra - lesson learned
byAndrzej Ludwikowski
 
Javantura v2 - Data modeling with Apapche Cassandra - Marko Švaljek
byHUJAK - Hrvatska udruga Java korisnika / Croatian Java User Association
 
The data model is dead, long live the data model
byPatrick McFadin
 
Building your First Application with Cassandra
byLuke Tillman
 
KillrVideo: Data Modeling Evolved (Patrick McFadin, Datastax) | Cassandra Sum...
byDataStax
 
Cassandra 3.0 advanced preview
byPatrick McFadin
 
Apache Cassandra Developer Training Slide Deck
byDataStax Academy
 
Become a super modeler
byPatrick McFadin
 
Cassandra lesson learned - extended
byAndrzej Ludwikowski
 
Cassandra 3.0 Data Modeling
byDataStax Academy
 

More from DataStax Academy

PDF
Forrester CXNYC 2017 - Delivering great real-time cx is a true craft
byDataStax Academy
 
PPTX
Introduction to DataStax Enterprise Graph Database
byDataStax Academy
 
PPTX
Introduction to DataStax Enterprise Advanced Replication with Apache Cassandra
byDataStax Academy
 
PPTX
Cassandra on Docker @ Walmart Labs
byDataStax Academy
 
PPTX
Cassandra Adoption on Cisco UCS & Open stack
byDataStax Academy
 
PDF
Data Modeling for Apache Cassandra
byDataStax Academy
 
PDF
Coursera Cassandra Driver
byDataStax Academy
 
PDF
Production Ready Cassandra
byDataStax Academy
 
PDF
Cassandra @ Netflix: Monitoring C* at Scale, Gossip and Tickler & Python
byDataStax Academy
 
PPTX
Cassandra @ Sony: The good, the bad, and the ugly part 1
byDataStax Academy
 
PPTX
Cassandra @ Sony: The good, the bad, and the ugly part 2
byDataStax Academy
 
PDF
Cassandra Core Concepts
byDataStax Academy
 
PPTX
Enabling Search in your Cassandra Application with DataStax Enterprise
byDataStax Academy
 
PDF
Apache Cassandra and Drivers
byDataStax Academy
 
PDF
Getting Started with Graph Databases
byDataStax Academy
 
PDF
Make 2016 your year of SMACK talk
byDataStax Academy
 
PDF
Client Drivers and Cassandra, the Right Way
byDataStax Academy
 
PPTX
Tales From The Front: An Architecture For Multi-Data Center Scalable Applicat...
byDataStax Academy
 
PDF
Traveler's Guide to Cassandra
byDataStax Academy
 
PDF
Cassandra: One (is the loneliest number)
byDataStax Academy
 
Forrester CXNYC 2017 - Delivering great real-time cx is a true craft
byDataStax Academy
 
Introduction to DataStax Enterprise Graph Database
byDataStax Academy
 
Introduction to DataStax Enterprise Advanced Replication with Apache Cassandra
byDataStax Academy
 
Cassandra on Docker @ Walmart Labs
byDataStax Academy
 
Cassandra Adoption on Cisco UCS & Open stack
byDataStax Academy
 
Data Modeling for Apache Cassandra
byDataStax Academy
 
Coursera Cassandra Driver
byDataStax Academy
 
Production Ready Cassandra
byDataStax Academy
 
Cassandra @ Netflix: Monitoring C* at Scale, Gossip and Tickler & Python
byDataStax Academy
 
Cassandra @ Sony: The good, the bad, and the ugly part 1
byDataStax Academy
 
Cassandra @ Sony: The good, the bad, and the ugly part 2
byDataStax Academy
 
Cassandra Core Concepts
byDataStax Academy
 
Enabling Search in your Cassandra Application with DataStax Enterprise
byDataStax Academy
 
Apache Cassandra and Drivers
byDataStax Academy
 
Getting Started with Graph Databases
byDataStax Academy
 
Make 2016 your year of SMACK talk
byDataStax Academy
 
Client Drivers and Cassandra, the Right Way
byDataStax Academy
 
Tales From The Front: An Architecture For Multi-Data Center Scalable Applicat...
byDataStax Academy
 
Traveler's Guide to Cassandra
byDataStax Academy
 
Cassandra: One (is the loneliest number)
byDataStax Academy
 

Recently uploaded

PDF
PCCC25（設立25年記念PCクラスタシンポジウム）：エヌビディア合同会社 テーマ２「NVIDIA BlueField-4 DPU」
byPC Cluster Consortium
 
PDF
How Much Does It Cost To Build Software
bycollinmishell2
 
PDF
So You Want to Work at Google | DevFest Seattle 2025
byMargaret Maynard-Reid
 
PDF
MuleSoft Meetup: Dreamforce'25 Tour- Vibing With AI & Agents.pdf
byTintu Jacob Shaji
 
PPTX
Leon Brands - Methodical GPU Profiling (Graphics Programming Conference 2025)
byleonbrands1998
 
PDF
[BDD 2025 - Full-Stack Development] PHP in AI Age: The Laravel Way. (Rizqy Hi...
byWintari Yasmin
 
PDF
Welcome by Uni Systems: Shaping Experiences
byUni Systems S.M.S.A.
 
PDF
Cloud Infrastructure monitoring with Grafana Cloud
byImma Valls Bernaus
 
PDF
Cheryl Hung, Vibe Coding Auth Without Melting Down! isaqb Software Architectu...
byCheryl Hung
 
PPTX
Leon Brands - Intro to GPU Occlusion (Graphics Programming Conference 2024)
byleonbrands1998
 
PDF
[BDD 2025 - Mobile Development] Crafting Immersive UI with E2E and AGSL Shade...
byWintari Yasmin
 
PDF
[DevFest Strasbourg 2025] - NodeJs Can do that !!
bymoassiongbon
 
PPTX
"Feelings versus facts: why metrics are more important than intuition", Igor ...
byFwdays
 
PDF
Parallel Computing BCS702 Module notes of the vtu college 7th sem 4.pdf
byMatheshVishnu
 
PPTX
Formulation and Evaluation of herbal peel off mask gel
byRanikonde
 
PDF
[BDD 2025 - Artificial Intelligence] AI for the Underdogs: Innovation for Sma...
byWintari Yasmin
 
PDF
The partnership effect: Libraries and publishers on collaborating and thrivin...
byBookNet Canada
 
PPTX
MuleSoft AI Series : Introduction to MCP
byMulesoftMunichMeetup
 
PDF
Cybersecurity Prevention and Detection: Unit 2
byVICTOR MAESTRE RAMIREZ
 
PDF
[BDD 2025 - Full-Stack Development] Digital Accessibility: Why Developers nee...
bywintari3
 
PCCC25（設立25年記念PCクラスタシンポジウム）：エヌビディア合同会社 テーマ２「NVIDIA BlueField-4 DPU」
byPC Cluster Consortium
 
How Much Does It Cost To Build Software
bycollinmishell2
 
So You Want to Work at Google | DevFest Seattle 2025
byMargaret Maynard-Reid
 
MuleSoft Meetup: Dreamforce'25 Tour- Vibing With AI & Agents.pdf
byTintu Jacob Shaji
 
Leon Brands - Methodical GPU Profiling (Graphics Programming Conference 2025)
byleonbrands1998
 
[BDD 2025 - Full-Stack Development] PHP in AI Age: The Laravel Way. (Rizqy Hi...
byWintari Yasmin
 
Welcome by Uni Systems: Shaping Experiences
byUni Systems S.M.S.A.
 
Cloud Infrastructure monitoring with Grafana Cloud
byImma Valls Bernaus
 
Cheryl Hung, Vibe Coding Auth Without Melting Down! isaqb Software Architectu...
byCheryl Hung
 
Leon Brands - Intro to GPU Occlusion (Graphics Programming Conference 2024)
byleonbrands1998
 
[BDD 2025 - Mobile Development] Crafting Immersive UI with E2E and AGSL Shade...
byWintari Yasmin
 
[DevFest Strasbourg 2025] - NodeJs Can do that !!
bymoassiongbon
 
"Feelings versus facts: why metrics are more important than intuition", Igor ...
byFwdays
 
Parallel Computing BCS702 Module notes of the vtu college 7th sem 4.pdf
byMatheshVishnu
 
Formulation and Evaluation of herbal peel off mask gel
byRanikonde
 
[BDD 2025 - Artificial Intelligence] AI for the Underdogs: Innovation for Sma...
byWintari Yasmin
 
The partnership effect: Libraries and publishers on collaborating and thrivin...
byBookNet Canada
 
MuleSoft AI Series : Introduction to MCP
byMulesoftMunichMeetup
 
Cybersecurity Prevention and Detection: Unit 2
byVICTOR MAESTRE RAMIREZ
 
[BDD 2025 - Full-Stack Development] Digital Accessibility: Why Developers nee...
bywintari3
 

Introduction to Data Modeling with Apache Cassandra

  • 1.
    ©2013 DataStax Confidential.Do not distribute without consent. @PatrickMcFadin Patrick McFadin
 Chief Evangelist for Apache Cassandra Introduction to Data Modeling with Apache Cassandra 1
  • 2.
    Relational Data Models •5 normal forms • Foreign Keys • Joins deptId First Last 1 Edgar Codd 2 Raymond Boyce id Dept 1 Engineering 2 Math Employees Department
  • 4.
    Relational Modeling CREATE TABLEusers (
 id number(12) NOT NULL ,
 firstname nvarchar2(25) NOT NULL ,
 lastname nvarchar2(25) NOT NULL,
 email nvarchar2(50) NOT NULL,
 password nvarchar2(255) NOT NULL,
 created_date timestamp(6),
 PRIMARY KEY (id),
 CONSTRAINT email_uq UNIQUE (email)
 ); -- Users by email address index
 CREATE INDEX idx_users_email ON users (email); • Create entity table • Add constraints • Index fields • Foreign Key relationships CREATE TABLE videos (
 id number(12),
 userid number(12) NOT NULL,
 name nvarchar2(255),
 description nvarchar2(500),
 location nvarchar2(255),
 location_type int,
 added_date timestamp,
 CONSTRAINT users_userid_fk FOREIGN KEY (userid) REFERENCES users (Id) ON DELETE CASCADE,
 PRIMARY KEY (id)
 );
  • 5.
  • 6.
  • 7.
    • What areyour application’s workflows? • How will I access the data? • Knowing your queries in advance is NOT optional • Different from RDBMS because I can’t just JOIN or create a new indexes to support new queries 7 Modeling Queries
  • 8.
    Some Application Workflowsin KillrVideo 8 User Logs into site Show basic information about user Show videos added by a user Show comments posted by a user Search for a video by tag Show latest videos added to the site Show comments for a video Show ratings for a video Show video and its details
  • 9.
    Some Queries inKillrVideo to Support Workflows 9 Users User Logs into site Find user by email address Show basic information about user Find user by id Comments Show comments for a video Find comments by video (latest first) Show comments posted by a user Find comments by user (latest first) Ratings Show ratings for a video Find ratings by video
  • 10.
    CQL vs SQL •No joins • Limited aggregations deptId First Last 1 Edgar Codd 2 Raymond Boyce id Dept 1 Engineering 2 Math Employees Department SELECT e.First, e.Last, d.Dept FROM Department d, Employees e WHERE ‘Codd’ = e.Last AND e.deptId = d.id
  • 11.
    Denormalization • Combine tablecolumns into a single view • Eliminate the need for joins SELECT First, Last, Dept FROM employees WHERE id = ‘1’ id First Last Dept 1 Edgar Codd Engineering 2 Raymond Boyce Math Employees
  • 12.
    “Static” Table CREATE TABLEvideos (
 videoid uuid,
 userid uuid,
 name varchar,
 description varchar,
 location text,
 location_type int,
 preview_thumbnails map<text,text>,
 tags set<varchar>,
 added_date timestamp,
 PRIMARY KEY (videoid)
 ); Table Name Column Name Column CQL Type Primary Key Designation Partition Key
  • 13.
    Insert INSERT INTO videos(videoid, name, userid, description, location, location_type, preview_thumbnails, tags, added_date, metadata)
 VALUES (06049cbb-dfed-421f-b889-5f649a0de1ed,'The data model is dead. Long live the data model.', 9761d3d7-7fbd-4269-9988-6cfd4e188678, 
 'First in a three part series for Cassandra Data Modeling','http://www.youtube.com/watch?v=px6U2n74q3g',1,
 {'YouTube':'http://www.youtube.com/watch?v=px6U2n74q3g'},{'cassandra','data model','relational','instruction'},
 '2013-05-02 12:30:29'); Table Name Fields Values Partition Key: Required
  • 14.
    Partition keys 06049cbb-dfed-421f-b889-5f649a0de1ed Murmur3Hash Token = 7224631062609997448 873ff430-9c23-4e60-be5f-278ea2bb21bd Murmur3 Hash Token = -6804302034103043898 Consistent hash. 128 bit number between 2-63 and 264 INSERT INTO videos (videoid, name, userid, description)
 VALUES (06049cbb-dfed-421f-b889-5f649a0de1ed,'The data model is dead. Long live the data model.’, 9761d3d7-7fbd-4269-9988-6cfd4e188678, 'First in a three part series for Cassandra Data Modeling'); INSERT INTO videos (videoid, name, userid, description)
 VALUES (873ff430-9c23-4e60-be5f-278ea2bb21bd,'Become a Super Modeler’, 9761d3d7-7fbd-4269-9988-6cfd4e188678, 'Second in a three part series for Cassandra Data Modeling');
  • 15.
    Select name | description| added_date
 ---------------------------------------------------+----------------------------------------------------------+--------------------------
 The data model is dead. Long live the data model. | First in a three part series for Cassandra Data Modeling | 2013-05-02 12:30:29-0700 SELECT name, description, added_date
 FROM videos
 WHERE videoid = 06049cbb-dfed-421f-b889-5f649a0de1ed; Fields Table Name Primary Key: Partition Key Required
  • 16.
    Locality 1000 Node Cluster videoid= 06049cbb-dfed-421f-b889-5f649a0de1ed SELECT name, description, added_date
 FROM videos
 WHERE videoid = 06049cbb-dfed-421f-b889-5f649a0de1ed;
  • 17.
    No more sequences •Great for auto-creation of Ids • Guaranteed unique • Needs ACID to work. (Sorry. No sharding) INSERT INTO user (id, firstName, LastName) VALUES (users_sequence.nextVal(), ‘Ted’, ‘Codd’) CREATE SEQUENCE users_sequence
 INCREMENT BY 1
 START WITH 1
 NOMAXVALUE
 NOCYCLE
 CACHE 10;
  • 18.
    No sequences??? • Almostimpossible in a distributed system • Couple of great choices • Natural Key - Unique values like email • Surrogate Key - UUID • Universal Unique ID • 128 bit number represented in character form • Easily generated on the client • Same as GUID for the MS folks 99051fe9-6a9c-46c2-b949-38ef78858dd0
  • 19.
    “Dynamic” Table CREATE TABLEvideos_by_tag (
 tag text,
 videoid uuid,
 added_date timestamp,
 name text,
 preview_image_location text,
 tagged_date timestamp,
 PRIMARY KEY (tag, videoid)
 ); Partition Key Clustering Column
  • 20.
  • 21.
    Primary key relationship PartitionKey PRIMARY KEY (tag,videoid)
  • 22.
    Primary key relationship PartitionKey Clustering Column PRIMARY KEY (tag,videoid)
  • 23.
    Primary key relationship PartitionKey data model PRIMARY KEY (tag,videoid) Clustering Column
  • 24.
    -5.6 06049cbb-dfed-421f-b889-5f649a0de1ed Primary key relationship PartitionKey 2013-05-16 16:50:002013-05-02 12:30:29 873ff430-9c23-4e60-be5f-278ea2bb21bd PRIMARY KEY (tag,videoid) Clustering Column data model 49f64d40-7d89-4890-b910-dbf923563a33 2013-06-11 11:00:00
  • 25.
  • 26.
    Partition with Clustering Cluster 1 Partition Key1 Column 1 Column 2 Column 3 Cluster 2 Partition Key 1 Column 1 Column 2 Column 3 Cluster 3 Partition Key 1 Column 1 Column 2 Column 3 Cluster 4 Partition Key 1 Column 1 Column 2 Column 3 Order By
  • 27.
    Table Partition Key 1 Partition Key1 Partition Key 1 Partition Key 1 Partition Key 2 Partition Key 2 Partition Key 2 Partition Key 2 Cluster 1 Column 1 Column 2 Column 3 Cluster 2 Column 1 Column 2 Column 3 Cluster 3 Column 1 Column 2 Column 3 Cluster 4 Column 1 Column 2 Column 3 Cluster 1 Column 1 Column 2 Column 3 Cluster 2 Column 1 Column 2 Column 3 Cluster 3 Column 1 Column 2 Column 3 Cluster 4 Column 1 Column 2 Column 3
  • 28.
    Keyspace Cluster 1 Partition Key 1 Column 2 Column 3 Column 4 Partition Key 2 Column 2 Column 3 Column 4 Cluster 2 Partition Key1 Column 2 Column 3 Column 4 Cluster 3 Partition Key 1 Column 2 Column 3 Column 4 Cluster 4 Partition Key 1 Column 2 Column 3 Column 4 Partition Key 2 Column 2 Column 3 Column 4 Partition Key 2 Column 2 Column 3 Column 4 Partition Key 2 Column 2 Column 3 Column 4 Partition Key 1 Column 2 Column 3 Column 4 Partition Key 2 Column 2 Column 3 Column 4 Partition Key 1 Column 2 Column 3 Column 4 Partition Key 1 Column 2 Column 3 Column 4 Partition Key 1 Column 2 Column 3 Column 4 Partition Key 2 Column 2 Column 3 Column 4 Partition Key 2 Column 2 Column 3 Column 4 Partition Key 2 Column 2 Column 3 Column 4 Table 1 Table 2 Keyspace 1 Cluster 1 Cluster 2 Cluster 3 Cluster 4 Cluster 1 Cluster 2 Cluster 3 Cluster 4 Cluster 1 Cluster 2 Cluster 3 Cluster 4
  • 29.
    Controlling Order CREATE TABLEraw_weather_data (
 wsid text,
 year int,
 month int,
 day int,
 hour int,
 temperature double,
 PRIMARY KEY ((wsid), year, month, day, hour)
 ) WITH CLUSTERING ORDER BY (year DESC, month DESC, day DESC, hour DESC); INSERT INTO raw_weather_data(wsid,year,month,day,hour,temperature)
 VALUES (‘10010:99999’,2005,12,1,10,-5.6); INSERT INTO raw_weather_data(wsid,year,month,day,hour,temperature)
 VALUES (‘10010:99999’,2005,12,1,9,-5.1); INSERT INTO raw_weather_data(wsid,year,month,day,hour,temperature)
 VALUES (‘10010:99999’,2005,12,1,8,-4.9); INSERT INTO raw_weather_data(wsid,year,month,day,hour,temperature)
 VALUES (‘10010:99999’,2005,12,1,7,-5.3);
  • 30.
    Clustering Order 200510010:99999 121 10 200510010:99999 12 1 9 raw_weather_data -5.6 -5.1 200510010:99999 12 1 8 200510010:99999 12 1 7 -4.9 -5.3 Order By DESC
  • 31.
    Write Path Client INSERT INTOraw_weather_data(wsid,year,month,day,hour,temperature)
 VALUES (‘10010:99999’,2005,12,1,7,-5.3); year 1wsid 1 month 1 day 1 hour 1 year 2wsid 2 month 2 day 2 hour 2 Memtable SSTable SSTable SSTable SSTable Node Commit Log Data * Compaction * Temp Temp
  • 32.
    Storage Model -Logical View 2005:12:1:10 -5.6 2005:12:1:9 -5.1 2005:12:1:8 -4.9 10010:99999 10010:99999 10010:99999 wsid hour temperature 2005:12:1:7 -5.3 10010:99999 SELECT wsid, hour, temperature
 FROM raw_weather_data
 WHERE wsid=‘10010:99999’
 AND year = 2005 AND month = 12 AND day = 1;
  • 33.
    2005:12:1:10 -5.6 -5.3-4.9-5.1 Storage Model- Disk Layout 2005:12:1:9 2005:12:1:8 10010:99999 2005:12:1:7 Merged, Sorted and Stored Sequentially SELECT wsid, hour, temperature
 FROM raw_weather_data
 WHERE wsid=‘10010:99999’
 AND year = 2005 AND month = 12 AND day = 1;
  • 34.
    2005:12:1:10 -5.6 2005:12:1:11 -4.9 -5.3-4.9-5.1 Storage Model- Disk Layout 2005:12:1:9 2005:12:1:8 10010:99999 2005:12:1:7 Merged, Sorted and Stored Sequentially SELECT wsid, hour, temperature
 FROM raw_weather_data
 WHERE wsid=‘10010:99999’
 AND year = 2005 AND month = 12 AND day = 1;
  • 35.
    2005:12:1:10 -5.6 2005:12:1:11 -4.9 -5.3-4.9-5.1 Storage Model- Disk Layout 2005:12:1:9 2005:12:1:8 10010:99999 2005:12:1:7 Merged, Sorted and Stored Sequentially SELECT wsid, hour, temperature
 FROM raw_weather_data
 WHERE wsid=‘10010:99999’
 AND year = 2005 AND month = 12 AND day = 1; 2005:12:1:12 -5.4
  • 36.
    Read Path Client SSTable SSTable SSTable Node Data SELECT wsid,hour,temperature
 FROMraw_weather_data
 WHERE wsid='10010:99999'
 AND year = 2005 AND month = 12 AND day = 1 
 AND hour >= 7 AND hour <= 10; year 1wsid 1 month 1 day 1 hour 1 year 2wsid 2 month 2 day 2 hour 2 Memtable Temp Temp
  • 37.
    Query patterns • Rangequeries • “Slice” operation on disk Single seek on disk 10010:99999 Partition key for locality SELECT wsid,hour,temperature
 FROM raw_weather_data
 WHERE wsid='10010:99999'
 AND year = 2005 AND month = 12 AND day = 1 
 AND hour >= 7 AND hour <= 10; 2005:12:1:10 -5.6 -5.3-4.9-5.1 2005:12:1:9 2005:12:1:8 2005:12:1:7
  • 38.
    Query patterns • Rangequeries • “Slice” operation on disk Programmers like this Sorted by event_time 2005:12:1:10 -5.6 2005:12:1:9 -5.1 2005:12:1:8 -4.9 10010:99999 10010:99999 10010:99999 weather_station hour temperature 2005:12:1:7 -5.3 10010:99999 SELECT weatherstation,hour,temperature FROM temperature WHERE weatherstation_id=‘10010:99999' AND year = 2005 AND month = 12 AND day = 1 AND hour >= 7 AND hour <= 10;
  • 39.
  • 40.
    CQL Collections • Meantto be dynamic part of table • Update syntax is very different from insert • Reads require all of collection to be read
  • 41.
    CQL Set • Setis sorted by CQL type comparator INSERT INTO collections_example (id, set_example) VALUES(1, {'1-one', '2-two'}); set_example set<text> Collection name Collection type CQLType
  • 42.
    CQL Set Operations •Adding an element to the set • After adding this element, it will sort to the beginning. • Removing an element from the set UPDATE collections_example SET set_example = set_example + {'3-three'} WHERE id = 1; UPDATE collections_example SET set_example = set_example + {'0-zero'} WHERE id = 1; UPDATE collections_example SET set_example = set_example - {'3-three'} WHERE id = 1;
  • 43.
    CQL List • Orderedby insertion • Use with caution list_example list<text> Collection name Collection type INSERT INTO collections_example (id, list_example) VALUES(1, ['1-one', '2-two']); CQLType
  • 44.
    CQL List Operations •Adding an element to the end of a list • Adding an element to the beginning of a list • Deleting an element from a list UPDATE collections_example SET list_example = list_example + ['3-three'] WHERE id = 1; UPDATE collections_example SET list_example = ['0-zero'] + list_example WHERE id = 1; UPDATE collections_example SET list_example = list_example - ['3-three'] WHERE id = 1;
  • 45.
    CQL Map • Keyand value • Key is sorted by CQL type comparator INSERT INTO collections_example (id, map_example) VALUES(1, { 1 : 'one', 2 : 'two' }); map_example map<int,text> Collection name Collection type Value CQLTypeKey CQLType
  • 46.
    CQL Map Operations •Add an element to the map • Update an existing element in the map • Delete an element in the map UPDATE collections_example SET map_example[3] = 'three' WHERE id = 1; UPDATE collections_example SET map_example[3] = 'tres' WHERE id = 1; DELETE map_example[3] FROM collections_example WHERE id = 1;
  • 47.
    Entity with collections •Same type of entity • SET type for dynamic data • tags for each video // Videos by id CREATE TABLE videos ( videoid uuid, userid uuid, name text, description text, location text, location_type int, preview_image_location text, tags set<text>, added_date timestamp, PRIMARY KEY (videoid) );
  • 48.
    Index (or lookup)tables • Table arranged to find data • Denormalized for speed
  • 49.
    Users – TheCassandra Way User Logs into site Find user by email address Show basic information about user Find user by id CREATE TABLE user_credentials (
 email text,
 password text,
 userid uuid,
 PRIMARY KEY (email)
 ); CREATE TABLE users (
 userid uuid,
 firstname text,
 lastname text,
 email text,
 created_date timestamp,
 PRIMARY KEY (userid)
 );
  • 50.
    50 Show video and its details Findvideo by id Show videos added by a user Find videos by user (latest first) CREATE TABLE videos (
 videoid uuid,
 userid uuid,
 name text,
 description text,
 location text,
 location_type int,
 preview_image_location text,
 tags set<text>,
 added_date timestamp,
 PRIMARY KEY (videoid)
 ); CREATE TABLE user_videos (
 userid uuid,
 added_date timestamp,
 videoid uuid,
 name text,
 preview_image_location text,
 PRIMARY KEY (userid, added_date, videoid)
 ) WITH CLUSTERING ORDER BY (added_date DESC, videoid ASC); Views or indexes? Denormalized data
  • 51.
    Multiple Lookups • Samedata • Different lookup pattern // Index for tag keywords CREATE TABLE videos_by_tag ( tag text, videoid uuid, added_date timestamp, name text, preview_image_location text, tagged_date timestamp, PRIMARY KEY (tag, videoid) ); // Index for tags by first letter in the tag CREATE TABLE tags_by_letter ( first_letter text, tag text, PRIMARY KEY (first_letter, tag) );
  • 52.
    Many to ManyRelationships • Two views • Different directions • Insert data in a batch // Comments for a given video CREATE TABLE comments_by_video ( videoid uuid, commentid timeuuid, userid uuid, comment text, PRIMARY KEY (videoid, commentid) ) WITH CLUSTERING ORDER BY (commentid DESC); // Comments for a given user CREATE TABLE comments_by_user ( userid uuid, commentid timeuuid, videoid uuid, comment text, PRIMARY KEY (userid, commentid) ) WITH CLUSTERING ORDER BY (commentid DESC);
  • 53.
  • 54.
    Delete DELETE FROM videos
 WHEREid = 06049cbb-dfed-421f-b889-5f649a0de1ed; Table Name Primary Key: Required
  • 55.
    Tombstones -5.6 06049cbb-dfed-421f-b889-5f649a0de1ed 2013-05-16 16:50:002013-05-02 12:30:29 873ff430-9c23-4e60-be5f-278ea2bb21bd datamodel 49f64d40-7d89-4890-b910-dbf923563a33 2013-06-11 11:00:00 DELETE FROM videos_by_tag
 WHERE videioId = 06049cbb-dfed-421f-b889-5f649a0de1ed; Deleted 2016-02-02 08:15:41
  • 56.
    Expiring Data Time ToLive = TTL INSERT INTO videos (videoid, name, userid, description, location, location_type, preview_thumbnails, tags, added_date, metadata)
 VALUES (06049cbb-dfed-421f-b889-5f649a0de1ed,'The data model is dead. Long live the data model.', 9761d3d7-7fbd-4269-9988-6cfd4e188678, 
 'First in a three part series for Cassandra Data Modeling','http://www.youtube.com/watch?v=px6U2n74q3g',1,
 {'YouTube':'http://www.youtube.com/watch?v=px6U2n74q3g'},{'cassandra','data model','relational','instruction'},
 '2013-05-02 12:30:29’) USING TTL = 2592000 Expire Data: 30 Days
  • 57.
    Thank you! Bring thequestions Follow me on twitter @PatrickMcFadin