Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Time Series and Events: Storage and Querying Strategies with Cassandra

Time Series and Events: Storage and Querying Strategies with Cassandra

Cassandra IoT meetup, London

Tareq Abedrabbo

October 23, 2014
Tweet

More Decks by Tareq Abedrabbo

Other Decks in Programming

Transcript

  1. Time Series and Events:
    Storage and Querying
    Strategies
    David Borsos & Tareq Abedrabbo
    C* London Meetup, 23 October 2014

    View full-size slide

  2. About Us
    - Software consultancy and delivery company
    - We’ve been building systems with
    Cassandra for 3 years
    - DataStax Solutions Partner

    View full-size slide

  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

    View full-size slide

  4. Essential C* concepts
    - Assuming CQL
    - Partition
    - Partition key
    - Clustering column
    - (Primary key = partition key + clustering
    columns)

    View full-size slide

  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

    View full-size slide

  6. Pros
    - Simple
    - Works well for simple data structures
    - E.g. measurements
    - Good read and write performance

    View full-size slide

  7. Cons
    - Hard limit on partition size (2 billion cells)
    - Limited flexibility
    - Limited querying

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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”

    View full-size slide

  16. There is no generic optimal
    solution!

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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
    }
    }

    View full-size slide

  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
    }
    }

    View full-size slide

  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

    View full-size slide

  22. Go ReSTful - query events
    GET /api/events?
    - 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

    View full-size slide

  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?...”
    }

    View full-size slide

  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

    View full-size slide

  25. Data model: Main Event Table
    create table events (
    id timeuuid primary key,
    source text,
    type text,
    meta_t map,
    meta_v map,
    payload_t map,
    payload_v map
    );

    View full-size slide

  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);

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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”
    }

    View full-size slide

  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”
    }

    View full-size slide

  39. Future improvements
    - User defined types
    - Solve cluster hotspots for bucket indexes
    - Solr integration
    - Hadoop or Spark for analytics
    - Testing with Stubbed Cassandra

    View full-size slide

  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

    View full-size slide

  41. Thank you for listening, any
    questions?
    - OpenCredo: http://www.opencredo.com/blog
    - @tareq_abedrabbo
    - @davib0

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide