Time Series and Events: Storage and Querying Strategies with Cassandra

Cassandra IoT meetup, London

Transcript

1. Time Series and Events: Storage and Querying Strategies David Borsos & Tareq Abedrabbo C* London Meetup, 23 October 2014
2. About Us - Software consultancy and delivery company - We’ve been building systems with Cassandra for 3 years - DataStax Solutions Partner
3. Events and Time Series - Data streams (events, measurements) from a variety of sources - Use data effectively i.e. querying and analytics - Events can be more complex than simple measurements - Event based architecture - a natural fit
4. Essential C* concepts - Assuming CQL - Partition - Partition key - Clustering column - (Primary key = partition key + clustering columns)
5. Simple Time Series Modeling Simplest case: using timestamps as clustering columns key timestamps / values id1 ts11 ts12 ts13 ts14 ts15 v11 v12 v13 v14 v15 id2 ts21 ts22 ts23 ts24 ts25 v21 v22 v23 v24 v25
6. Pros - Simple - Works well for simple data structures - E.g. measurements - Good read and write performance
7. Cons - Hard limit on partition size (2 billion cells) - Limited flexibility - Limited querying
8. Time bucketing Adding a time bucket to the partition key key timestamps / values ts11 ts12 ts13 ts14 ts15 id1 bucket1 v11 v12 id1 bucket2 v12 v14 v15 ts21 ts22 ts23 ts24 ts25 id2 bucket1 v21 v22 id2 bucket2 v23 v25 v25
9. Time bucketing - Mitigates the partition size issue - Queries become slightly more complicated - Write performance is not affected - Reads may be slower - Potentially hitting multiple buckets
10. Denormalise for each new query query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 bucket1 v11 qp2 bucket1 v12 v21 qp2 bucket2 v22
11. Denormalise for each new query query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22 query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22 query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22
12. Denormalise for each new query query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22 query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22 query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22 query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22
13. Denormalise for each new query query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22 query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22 query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22 query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22 query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22 query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22 query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22
14. Denormalise for each new query query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22 query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22 query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22 query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22 query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22 query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22 query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22 query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22 query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22 query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22 query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22 query key id1 ts11 id1 ts12 id2 ts21 id2 ts22 qp1 b1 v11 qp2 b1 v12 v21 qp2 b2 v22
15. Denormalise for each new query - Allow richer queries - Higher disk usage - Disk space is cheap, but not free - Write latency may be impacted - Writing to the denormalised indexes - Time-bucket indexes may create hotspots - “Hot shards”
16. There is no generic optimal solution!
17. Motivation - Capture (more) structured events - Support complex querying - Support analytics - Changes in the data model - Allow for extension in the architecture - Guarantees vs. performance - Consistency vs. latency - Should be tunable
18. Go ReSTful - Event Store Event Service - Simple HTTP / ReST interface - Create events: POST - Query events: GET with parameters - Clear service contract, easy to use - Accessible anywhere you have http! - Decouples interface from implementation
19. Go ReSTful - create an event POST /api/events { "type" : "SOME.EVENT.TYPE", "source" : "some-component:instance", "metadata" : { "anyMetaKey" : "someMetaValue", "tags" : [ "tag1", "tag2" ] }, "payload" : { "anyKey1" : "someValue", "anyKey2" : 3 } }
20. Go ReSTful - read a single event GET /api/events/{id} { "type" : "SOME.EVENT.TYPE", "source" : "some-component:instance", "metadata" : { "anyMetaKey" : "someMetaValue", "tags" : [ "tag1", "tag2" ] }, "payload" : { "anyKey1" : "someValue", "anyKey2" : 3 } }
21. Go ReSTful - read a single event GET /api/events/{id} { "type" : "SOME.EVENT.TYPE", "source" : "some-component:instance", "metadata" : { "anyMetaKey" : "someMetaValue", "tags" : [ "tag1", "tag2" ] }, "payload" : { "anyKey1" : "someValue", "anyKey2" : 3 } } Not extremely useful
22. Go ReSTful - query events GET /api/events?<parameters> - start absolute start time (range) - startOffset X milliseconds ago (range) - end absolute end time (range) - limit number of items returned - order ASC or DESC - tag indexed values - type indexed values
23. Go ReSTful - query events GET /api/events?start=141..&type=X&limit=10 { “events” : [ { “type” : “SOME.EVENT.TYPE”, “source: “some-component:instance”, “metadata” : { … }, “payload” : { … } }, { … } ], “continuation” : “/api/events?...” }
24. Sync/async indexing ReST API Write AMQP Primary event store Index1 / asc Index1 / desc Index2 / asc Index2 / desc Publish IndexN / desc IndexN / asc Write Write Write …. Async
25. Data model: Main Event Table create table events ( id timeuuid primary key, source text, type text, meta_t map<text, text>, meta_v map<text, blob>, payload_t map<text, text>, payload_v map<text, blob> );
26. Data model: Query Indexes events_by_type_asc ( tbucket text, type text, eventid timeuuid, primary key ((type, tbucket), eventid) ) with clustering order by (eventid asc);
27. Data model: Query Indexes events_by_type_asc ( tbucket text, type text, eventid timeuuid, primary key ((type, tbucket), eventid) ) with clustering order by (eventid asc); - No actual values, timeuuid of event is enough - There are multiple index tables; one for each query type - Ascending/descending versions
28. Pros - Client is unaware of the implementation - We could use MySQL or MongoDB (or txt files) - Client only needs to be able to - Understand JSON data structures - Follow URIs - GET parameters work in an intuitive way - Disk consumption is reasonable - Extensible - More than just a data store - Driving a pub/sub mechanism on top of events
29. Cons - Not optimized for latency only - Don’t always need that - Still quick enough for our use-cases - More complex service code - Needs to execute multiple CQL queries in sequence - Using CQL batches when possible - Cluster hotspots can still occur
30. Implementing pagination Want to query - Events - From a specified start time - Until now - Of type X - In ascending order - Each “page” to return 5 items - With a continuation URL to iterate
31. Implementing pagination Want to query - Events - From a specified start time - Until now (*) - Of type X - In ascending order (*) - Each “page” to return 5 items - With a continuation URL to iterate GET /api/events?start=141..&type=X&limit=5
32. Implementing pagination GET /api/events?start=141..&type=X&limit=5 i_type time → type1 bucket1 type1 bucket2 uuid1 uuid2 type1 bucket3 uuid3 uuid4 type1 bucket4 uuid5 uuid6 uuid7 uuid8 type1 bucket5
33. Implementing pagination GET /api/events?start=141..&type=X&limit=5 i_type time → type1 bucket1 ↑ query range ↓ type1 bucket2 uuid1 uuid2 type1 bucket3 uuid3 uuid4 type1 bucket4 uuid5 uuid6 uuid7 uuid8 type1 bucket5
34. Implementing pagination GET /api/events?start=141..&type=X&limit=5 i_type time → ← query range → type1 bucket1 ↑ query range ↓ type1 bucket2 uuid1 uuid2 type1 bucket3 uuid3 uuid4 type1 bucket4 uuid5 uuid6 uuid7 uuid8 type1 bucket5
35. Implementing pagination GET /api/events?start=141..&type=X&limit=5 i_type time → ← query range → type1 bucket1 ↑ query range ↓ type1 bucket2 uuid1 uuid2 type1 bucket3 uuid3 uuid4 type1 bucket4 uuid5 uuid6 uuid7 uuid8 type1 bucket5
36. Implementing pagination GET /api/events?start=141..&type=X&limit=5 i_type time → ← query range → type1 bucket1 ↑ query range ↓ type1 bucket2 uuid1 uuid2 type1 bucket3 uuid3 uuid4 type1 bucket4 uuid5 uuid6 uuid7 uuid8 type1 bucket5 Lookup results in primary event table Continuation URL
37. Implementing pagination GET /api/events?start=141..&type=X&limit=5 { “events” : [ { “id” : “uuid1”, “type” : “X”, “metadata” : { … }, “payload” : { … } }, { … } ], “continuation” : “/api/events?continueFrom=uuid7&type=X&limit=5” }
38. Implementing pagination GET /api/events?start=141..&type=X&limit=5 { “events” : [ { “id” : “uuid1”, “type” : “X”, “metadata” : { … }, “payload” : { … } }, { … } ], “continuation” : “/api/events?continueFrom=uuid7&type=X&limit=5” }
39. Future improvements - User defined types - Solve cluster hotspots for bucket indexes - Solr integration - Hadoop or Spark for analytics - Testing with Stubbed Cassandra
40. Lessons learnt - Scalability is not only about raw performance - Experiment to determine the best fit for your use case - Simplify when you can - Understand Thrift, use CQL
41. Thank you for listening, any questions? - OpenCredo: http://www.opencredo.com/blog - @tareq_abedrabbo - @davib0
42. Pushing the data model further - Still prone for cluster hotspots (buckets) - Sharding within a time bucket - Pay with read-time latency - Writes are not affected
43. Sharded/bucketed index events_by_type_asc ( tbucket text, shard text, type text, eventid timeuuid, primary key ((type, shard, tbucket), eventid) ) with clustering order by (eventid asc); where shard = eventid % n