Principles of Cassandra Data Modeling

Slide 1

Slide 1 text

Slide 2

Slide 2 text

About this Talk This is about getting the basics right. Friday, May 16, 14

Slide 3

Slide 3 text

About this Talk This is about getting the basics right. Fancy tricks can come later, once you know how to evaluate them. Friday, May 16, 14

Slide 4

Slide 4 text

General Principles 1. Spread data evenly around the cluster Friday, May 16, 14

Slide 5

Slide 5 text

Primary key determines placement* Partitioning jim carol johnny suzy age: 36 car: camaro gender: M age: 37 car: subaru gender: F age:12 gender: M age:10 gender: F Friday, May 16, 14

Slide 6

Slide 6 text

jim carol johnny suzy PK 5e02739678... a9a0198010... f4eb27cea7... 78b421309e... Murmur Hash Murmur* hash operation yields a 64-bit number for keys of any size. Friday, May 16, 14

Slide 7

Slide 7 text

Node D Node C Node B Node A The “token ring” 0xc000000000..0 0x0000000000..0 0x4000000000..0 0x8000000000..0 Friday, May 16, 14

Slide 8

Slide 8 text

jim 5e02739678... carol a9a0198010... johnny f4eb27cea7... suzy 78b421309e... Start End A 0x0000000000..1 0x4000000000..0 B 0x4000000000..1 0x8000000000..0 C 0x8000000000..1 0xc000000000..0 D 0xc000000000..1 0x0000000000..0 Friday, May 16, 14

Slide 9

Slide 9 text

Slide 10

Slide 10 text

Slide 11

Slide 11 text

Slide 12

Slide 12 text

Slide 13

Slide 13 text

Node D Node C Node B Node A carol a9a0198010... Replication Friday, May 16, 14

Slide 14

Slide 14 text

Node D Node C Node B Node A carol a9a0198010... Friday, May 16, 14

Slide 15

Slide 15 text

Node D Node C Node B Node A carol a9a0198010... Friday, May 16, 14

Slide 16

Slide 16 text

C’’ A’’ D’ C’ A’ D A B’ C B Virtual nodes Node D Node C Node B Node A Without vnodes With vnodes Friday, May 16, 14

Slide 17

Slide 17 text

General Principles 1. Spread data evenly around the cluster Pick a good primary key. Friday, May 16, 14

Slide 18

Slide 18 text

General Principles 1. Spread data evenly around the cluster 2. Minimize the number of partitions read Friday, May 16, 14 Why minimize? Network cost of fan-out, disk seeks.

Slide 19

Slide 19 text

General Principles 1. Spread data evenly around the cluster 2. Minimize the number of partitions read Sometimes these conflict. It’s a balance. Friday, May 16, 14

Slide 20

Slide 20 text

Non-Goals 1. Minimize the number of writes Friday, May 16, 14

Slide 21

Slide 21 text

Non-Goals 1. Minimize the number of writes Writes aren’t free, but they’re cheap. Friday, May 16, 14

Slide 22

Slide 22 text

Non-Goals 1. Minimize the number of writes 2. Minimize data duplication Friday, May 16, 14

Slide 23

Slide 23 text

Non-Goals 1. Minimize the number of writes 2. Minimize data duplication There are no joins. (Nor do you really want them in a distributed database.) Friday, May 16, 14 Also, disk space is a cheap resource.

Slide 24

Slide 24 text

General Principles 1. Spread data evenly around the cluster 2. Minimize the number of partitions read Minimize partition reads by modeling to fit your queries. Friday, May 16, 14

Slide 25

Slide 25 text

Model Your Queries 1. Don’t model around relations Friday, May 16, 14

Slide 26

Slide 26 text

Model Your Queries 1. Don’t model around relations 2. Don’t model around objects Friday, May 16, 14

Slide 27

Slide 27 text

Model Your Queries Steps to pick a data model: 1. Determine what specific queries to support Friday, May 16, 14

Slide 28

Slide 28 text

Model Your Queries Consider grouping, ordering, filtering, uniqueness, etc. Steps to pick a data model: 1. Determine what specific queries to support Friday, May 16, 14

Slide 29

Slide 29 text

Model Your Queries Steps to pick a data model: 1. Determine what specific queries to support 2. Try to create a table where you can satisfy your query by reading (roughly) one partition Friday, May 16, 14

Slide 30

Slide 30 text

Slide 31

Slide 31 text

Example: User Lookup We have users and want to look them up. Friday, May 16, 14

Slide 32

Slide 32 text

Slide 33

Slide 33 text

Example: User Lookup We have users and want to look them up: • by username • by email • want full user details for both types of lookups Friday, May 16, 14

Slide 34

Slide 34 text

Slide 35

Slide 35 text

Example: User Lookup CREATE TABLE users_by_username ( username text PRIMARY KEY, email text, age int ) We have users and want to look them up: • by username • by email Friday, May 16, 14

Slide 36

Slide 36 text

Example: User Lookup CREATE TABLE users_by_username ( username text PRIMARY KEY, email text, age int ) CREATE TABLE users_by_email ( email text PRIMARY KEY, username text, age int ) We have users and want to look them up: • by username • by email Friday, May 16, 14

Slide 37

Slide 37 text

General Principles 1. Spread data evenly around the cluster 2. Minimize the number of partitions read Friday, May 16, 14

Slide 38

Slide 38 text

Slide 39

Slide 39 text

General Principles 1. Spread data evenly around the cluster 2. Minimize the number of partitions read Friday, May 16, 14

Slide 40

Slide 40 text

Example: User Lookup CREATE TABLE users_by_username ( username text PRIMARY KEY, id uuid ) CREATE TABLE users_by_email ( email text PRIMARY KEY, id uuid ) What if we had done: CREATE TABLE users ( id uuid PRIMARY KEY username text, email text, age int, ) Friday, May 16, 14

Slide 41

Slide 41 text

Example: User Lookup What if we had done: CREATE TABLE users ( id uuid PRIMARY KEY username text, email text, age int, ) We reduced data duplication! Friday, May 16, 14

Slide 42

Slide 42 text

Example: User Lookup What if we had done: CREATE TABLE users ( id uuid PRIMARY KEY username text, email text, age int, ) We reduced data duplication! Need to read twice as many partitions. Friday, May 16, 14 Same problem with secondary indexes Consider cost/frequency of updates vs reads. Worse for other patterns.

Slide 43

Slide 43 text

Example: User Groups Users are in groups. Friday, May 16, 14

Slide 44

Slide 44 text

Slide 45

Slide 45 text

Example: User Groups Users are in groups. We want to get all users in a group. Friday, May 16, 14

Slide 46

Slide 46 text

Slide 47

Slide 47 text

Slide 48

Slide 48 text

Example: User Groups CREATE TABLE groups ( groupname text, username text, email text, age int, PRIMARY KEY (groupname, username) ) Friday, May 16, 14

Slide 49

Slide 49 text

Example: User Groups groupname is the partitioning key username is the clustering key CREATE TABLE groups ( groupname text, username text, email text, age int, PRIMARY KEY (groupname, username) ) Friday, May 16, 14

Slide 50

Slide 50 text

Example: User Groups One partition per group. Within a partition, order rows by username. CREATE TABLE groups ( groupname text, username text, email text, age int, PRIMARY KEY (groupname, username) ) Friday, May 16, 14

Slide 51

Slide 51 text

Slide 52

Slide 52 text

General Principles 1. Spread data evenly around the cluster 2. Minimize the number of partitions read Friday, May 16, 14

Slide 53

Slide 53 text

Example: User Groups One partition per group. Not spread perfectly, but reads are great. CREATE TABLE groups ( groupname text, username text, email text, age int, PRIMARY KEY (groupname, username) ) Friday, May 16, 14 How to break up big groups? Split by time, username preﬁx, or preﬁx of a hash of the username.

Slide 54

Slide 54 text

Example: User Groups We’re duplicating user data many times. CREATE TABLE groups ( groupname text, username text, email text, age int, PRIMARY KEY (groupname, username) ) Friday, May 16, 14

Slide 55

Slide 55 text

Example: User Lookup What if we had done: CREATE TABLE users ( id uuid PRIMARY KEY username text, email text, age int ) CREATE TABLE groups ( groupname text, user_id uuid, PRIMARY KEY (groupname, user_id) ) Friday, May 16, 14 What’s wrong with this?

Slide 56

Slide 56 text

Slide 57

Slide 57 text

Example: New Users Users are in groups. Get the N newest users in a group. Friday, May 16, 14

Slide 58

Slide 58 text

Example: User Groups CREATE TABLE group_join_dates ( groupname text, joined timeuuid, username text, email text, age int, PRIMARY KEY (groupname, joined) ) Friday, May 16, 14

Slide 59

Slide 59 text

Example: User Groups CREATE TABLE group_join_dates ( groupname text, joined timeuuid, username text, email text, age int, PRIMARY KEY (groupname, joined) ) Use timeuuid instead of timestamp to avoid collisions. Friday, May 16, 14

Slide 60

Slide 60 text

Example: User Groups CREATE TABLE group_join_dates ( groupname text, joined timeuuid, username text, email text, age int, PRIMARY KEY (groupname, joined) ) WITH CLUSTERING ORDER BY (joined DESC) Reverse clustering order to optimize for N latest. Friday, May 16, 14

Slide 61

Slide 61 text

Example: User Groups CREATE TABLE group_join_dates ( groupname text, joined timeuuid, username text, email text, age int, PRIMARY KEY (groupname, joined) ) WITH CLUSTERING ORDER BY (joined DESC) SELECT * FROM group_join_dates WHERE groupname=? LIMIT ? Friday, May 16, 14

Slide 62

Slide 62 text

General Principles 1. Spread data evenly around the cluster 2. Minimize the number of partitions read Friday, May 16, 14

Slide 63

Slide 63 text

Example: User Groups CREATE TABLE group_join_dates ( groupname text, joined timeuuid, username text, email text, age int, PRIMARY KEY (groupname, joined) ) How to spread data more evenly? Friday, May 16, 14

Slide 64

Slide 64 text

Example: User Groups CREATE TABLE group_join_dates ( groupname text, join_day text, joined timeuuid, username text, email text, age int, PRIMARY KEY ((groupname, join_day), joined) ) WITH CLUSTERING ORDER BY (joined DESC) Use a compound partition key. Friday, May 16, 14

Slide 65

Slide 65 text

Slide 66

Slide 66 text

General Principles 1. Spread data evenly around the cluster 2. Minimize the number of partitions read We might a few read more partitions. It’s a balance. Friday, May 16, 14

Slide 67

Slide 67 text

Getting Fancy Use fancy features to help satisfy queries by reading from single partition. Friday, May 16, 14

Slide 68

Slide 68 text

Getting Fancy Use fancy features to help satisfy queries by reading from single partition. • List, Set, and Map collections Friday, May 16, 14

Slide 69

Slide 69 text

Getting Fancy Use fancy features to help satisfy queries by reading from single partition. • List, Set, and Map collections CREATE TABLE songs ( artist text, album text, song text, tags set, PRIMARY KEY ((artist, album, song)) ) Friday, May 16, 14 Alternative model would be song <-> tags, requires more reads.

Slide 70

Slide 70 text

Getting Fancy • Alternative to set of tags: CREATE TABLE songs ( artist text, album text, song text, PRIMARY KEY ((artist, album, song)) ) CREATE TABLE tags ( artist text, album text, song text, tag text, PRIMARY KEY ((artist, album, song), tag) ) Friday, May 16, 14 Requires more partition reads

Slide 71

Slide 71 text

Getting Fancy Use fancy features to help satisfy queries by reading from single partition. • List, Set, and Map collections • User defined types Friday, May 16, 14 New in 2.1

Slide 72

Slide 72 text

Getting Fancy Use fancy features to help satisfy queries by reading from single partition. • List, Set, and Map collections • User defined types CREATE TYPE address ( street text, city text, zip int ) CREATE TABLE users ( username text PRIMARY KEY, email text, addresses map ) Friday, May 16, 14 Support “residential”, “business”, and other addresses. Alternative is separate table for user addresses

Slide 73

Slide 73 text

Getting Fancy Use fancy features to help satisfy queries by reading from single partition. • List, Set, and Map collections • User defined types • Static Columns Friday, May 16, 14 New in 2.1

Slide 74

Slide 74 text

Getting Fancy Use fancy features to help satisfy queries by reading from single partition. • List, Set, and Map collections • User defined types • Static Columns CREATE TABLE post_and_comments ( post_id text, post_body text static, comment_time timeuuid, commenter text, comment_body text, PRIMARY KEY (post_id, comment_time) ) Friday, May 16, 14 Always get post body with comments

Slide 75

Slide 75 text

Slide 76

Slide 76 text

Getting Fancy • Static Columns post_id | post_body | comment_time | comment ------------------------------------------------- 3 | Lorem ... | 1 | Hey, ... 3 | | 2 | What’s ... 3 | | 3 | Foobar 1 | How to ... | 2 | Barfoo 2 | Four score | 1 | I <3 C* 2 | | 2 | How does ... Friday, May 16, 14 Always get post body with comments

Slide 77

Slide 77 text

Questions? @tylhobbs [email protected] Friday, May 16, 14

Slide 78

Slide 78 text

Bonus: Transactions INSERT INTO users (username, email, age) VALUES (?, ?, ?) IF NOT EXISTS Friday, May 16, 14

Slide 79

Slide 79 text

Bonus: Transactions INSERT INTO users (username, email, age) VALUES (?, ?, ?) IF NOT EXISTS Use for the 1% of your app that requires them. Friday, May 16, 14