CQ-what? A look at under the hood of Cassandra's CQL.

Internal tech talk given at FullContact on Jan 15th, 2015

Transcript

1. CQ-what? A look at under the hood of Cassandra’s CQL January 15th, 2015
2. “Thrift” internal storage format CQL: partition key Graphic gleefully taken from Datastax
3. CREATE TABLE user_profiles ( user_id text PRIMARY KEY, first_name text, last_name text, year_of_birth int ) WITH COMPACT STORAGE == Tells Cassandra to store less metadata and to store rows exactly like Thrift
4. CQL vs. Thrift • Same datatypes (UTF8Type, LongType, etc.), cool new clothes • Normal CQL tables wrap all types in a CompositeType • UTF8Type -> CompositeType(UTF8Type) • Allows for altering tables • Also to support features like clustering
5. Person API CREATE TABLE profile_identities.results ( storage_type text, storage_value text, version text, indexed_value text, last_update timestamp, PRIMARY KEY ((storage_type, storage_value), version) ) Partition key Clustering key partition key (row) cell name cell value e:michael@fullcontact.com 1:indexed_value { … } 1:last_update 1421342467000 e:bart@fullcontact.com 1:indexed_value { … } 1:last_update 1391421409000 Primary key (duh)
6. CQL lies. SELECT * FROM profile_identities.results LIMIT 2 storage_type storage_value version indexed_value last_updated e michael@fullcontact.com 1 { … } 1421342467000 e bart@fullcontact.com 1 { … } 1391421409000 Gives you a row-oriented view based on your clustering key partition key (row) cell name cell value e:michael@fullcontact.com 1:indexed_value { … } 1:last_update 1421342467000 e:bart@fullcontact.com 1:indexed_value { … } 1:last_update 1391421409000
7. Person API 2: When I learned about partition keys CREATE TABLE profile_identities.results ( storage_type text, storage_value text, version text, indexed_value text, last_update timestamp, PRIMARY KEY (storage_type, storage_value, version) ) Partition key Clustering key partition key (row) cell name cell value e bart@fullcontact.com:1:indexed_value { … } bart@fullcontact.com:1:last_update 1391421409000 michael@fullcontact.com:1:indexed_value { … } michael@fullcontact.com:1:last_update 1421342467000 Don’t drink and DDL. ~5 partitions total. Cassandra was unhappy.
8. Sherlock Cache CREATE TABLE searchtarget.results ( searched_value text, searched_type text, st_name text, version text, last_update timestamp, rawdata text, result text, statuscode text, statustext text, uri text, PRIMARY KEY (searched_value, searched_type, st_name, version)) Partition key Clustering key • Friendly to Cassandra row cache • Works w/ Sherlock’s access pattern • Cluster’s a user’s data
9. Other clustering fun CREATE TABLE activity_log ( userid bigint, ts timestamp, event_name text, event_meta map<text, text>, PRIMARY KEY (userid, ts) WITH CLUSTERING ORDER BY (ts DESC) partition key (row) cell name cell value 1 1234:event_name … 1233:event_name … 1000:event_name … 985:event_name … ts One row per userid, stored in time descending order on disk
10. Collections • map<t,t>, set<t>, list<t> • Just syntactic sugar on top of CQL • No really, I’ll show you • Also handy: set, map are CRDTs by nature of C*’s design • As of 2.1: your own custom embedded types. Whoa.
11. map<t, t> partition key (row) cell name cell value 1 event_meta:a hello event_meta:b goodbye … … CREATE TABLE ghetto_activity_log ( userid bigint, event_meta map<text, text>, PRIMARY KEY (userid)); event_meta: a -> hello b -> goodbye
12. set<t> partition key (row) cell name cell value 1 event_meta:a event_meta:b CREATE TABLE ghetto_activity_log ( userid bigint, event_meta set<text>, PRIMARY KEY (userid));
13. list<t> partition key (row) cell name cell value 1 event_meta:5a27c2809ce011e48edf4f403ad60db0 a event_meta:5a27c2819ce011e48edf4f403ad60db0 b CREATE TABLE ghetto_activity_log ( userid bigint, event_meta list<text>, PRIMARY KEY (userid)); Not a CRDT. I think. Weird because it adds an ordering part to your key
14. Last thing… SELECT COUNT(*) is a complete scan. Don’t use it. It’s limited to 10000 “rows” by default.
15. Questions?