SYS-CON MEDIA Authors: Liz McMillan, Carmen Gonzalez, Zakia Bouachraoui, Roger Strukhoff, David Linthicum

Blog Feed Post

Cassandra’s Data Model

As we prepare to implement our Market Data repository to facilitate algo development and back-testing, you should have downloaded Cassandra and installed it by now.  What, you haven’t?  Well, click here, get it done and then come back for some fun.  To get things up and running once you’ve downloaded Cassandra, click here for some guidance (this assumes you’re running Linux but should point you in the right direction if you’re running Windoze).

CONFUSION

Most of the explanations I’ve read about Cassandra’s data model first extol the virtues of NoSQL and the evils of Relational Databases.  And so while getting the reader caught up in this mythic struggle that summons images from Tolkien’s middle earth, the point is lost.  And that point is?

IT’S ALL ACTUALLY QUITE EASY

Cassandra thinks about data the way we think about data.  Most of us think about data in rows and columns.  So does Cassandra.  But it also alleviates some extra stuff we don’t need while adding some stuff that we do need.  And that can be a little disconcerting initially.  To make things easier, let’s first describe a goal for our exercise.  We’d like to get a day’s worth of market data, by symbol, in ascending time order.  Also, we might like to get the data for a slice of time within that day.  Like, “give me all the BBO’s for American Airlines for May 20th, 2010,” or, “I’d like to see the BBO’s for American Airlines for May 20th, 2010 between 1 and 2pm.”  Let’s jump right in.

LET’S GET OUR DATA

As we subscribe to our favorite market data feed, we receive something like:

  • Symbol,
  • Bid,
  • Offer,
  • Bid Size,
  • Offer Size.
  • Time Stamp, and
  • Seq # (most quote vendors provide a Sequence # because multiple quotes can occur for any given Time Stamp)

We’re going to call this a column family.  Cassandra’s analog for a table is a Column Family.  You can see why this fits so well above – the columns that belong to the symbol AA comprise a family of related information.  I’d like to store this data by symbol, so later, I can retrieve it.  Using the Cassandra client (cassandra-cli – it’s in the bin directory where you installed Cassandra), let’s create the BBO Column Family.  It looks like this:

create column family bbo with comparator = UTF8Type
and column_metadata = [
{column_name: symbol, validation_class:UTF8Type},
{column_name: bb, validation_class: UTF8Type},
{column_name: bo, validation_class: UTF8Type},
{column_name: bbSize, validation_class: UTF8Type},
{column_name: bSize, validation_class: UTF8Type},
{column_name: timeStamp, validation_class: LongType},
{column_name: seqNum, validation_class: LongType},
];

And now that we’ve created the schema, let’s insert some quotes.

Set bbo[‘symbol’=’AA’;
Set bbo[‘bb’]=’123.34’;
Set bbo[‘bo’]=’123.84’;
Set bbo[‘bbSize’]=’100’;
Set bbo[‘boSize’]=’200’;
Set bbo[‘timeStamp’]=1234;

What happens when you execute a list bbo command now?  So, that’s easy enough.   So what happens as we get the next quote?  Well, we go to insert our data like this:

Set bbo[‘symbol’=’AA’;
Set bbo[‘bb’]=’125.34’;
Set bbo[‘bo’]=’125.84’;
Set bbo[‘bbSize’]=’100’;
Set bbo[‘boSize’]=’200’;
Set bbo[‘timeStamp’]=1235;

And then to see our data, enter this command (again):

List bbo;

When we use the ‘list bbo’ command, we’re only go see that data last inserted for that row key.  What happened to the previous data?  It was over-written with the new data.  So if we wanted to save each quote, we could combine the timestamp with the column name and then we’d be inserting unique columns each time and we’d be fine.  But there’s a different way to do this.

BIG DEAL, I DON’T SEE ANYTHING DIFFERENT HERE

And you don’t, because we haven’t started introducing the special sauce yet.  Well, we kind of did.  In the schema definitions above, you’ll notice we didn’t say that much about what we could or couldn’t insert into a row.  We just started adding columns dynamically.  So, each row, which is identified by a key, can have different columns in it and even a different number of columns.

WELL, THAT’S NOT GOING TO WORK

So, how do we keep track of all the quotes for our symbol?  First a little clarification, the Column Family above is really BBO, and we’ve inserted a row identified by the key, ‘AA” and some associated tag/data value pairs.  Think of this as a map of maps.  So now, we need to insert the bits that change for a given symbol over time.  How could we do that?  We create a Super Column Family of course.  A Super Column Family contains Super Columns.  A Super Column is kind of like another row of data – so using our example above, the Super Column we’ll be inserting consists of the BB, BO, BB Size, etc. The data above gets inserted using [AA] as our row key, and we need to pick a key for the Super Column that contains the quote data.    Let’s pick Seq# as our Super Column key.  Our row key is still Symbol, and I’ve prepended the date to it.  This way, all the data for a day’s worth of AA will be in the same row.  This is called a compound, or aggregate, key.  It looks like this:

create column family sbbo with column_type = 'Super' and comparator = ‘BytesType’
and column_metadata = [
{column_name: bb, validation_class: UTF8Type},
{column_name: bo, validation_class: UTF8Type},
{column_name: bbSize, validation_class: UTF8Type},
{column_name: bSize, validation_class: UTF8Type},
{column_name: timeStamp, validation_class: LongType},
];

And the insert statements look like this (we’re using the Seq# as the key – that’s the Super Column key right after the row key or, ’20100124:AA’ below):

Set sbbo[‘20100124:AA’][1234][‘bb’]=’100.00’;
Set sbbo[‘20100124:AA’][1234][‘bo’]=’101.00’;
Set sbbo[‘20100124:AA’][1235][‘bb’]=’101.00’;
Set sbbo[‘20100124:AA’][1235][‘bo’]=’102.00’;
Set sbbo[‘20100125:AA’][1234][‘bb’]=’100.00’;
Set sbbo[‘20100125:AA’][1234][‘bo’]=’101.00’;
Set sbbo[‘20100125:AA’][1235][‘bb’]=’101.00’;
Set sbbo[‘20100125:AA’][1235][‘bo’]=’102.00’;

Now let’s see what’s in the column family:

List sbbo;

So now it looks like we’re able to store a set of quotes for a symbol for any given day.  Bingo.

All we’ve really done here is add another map – so we now have a map (Date, Symbol) that contains a map (Symbol, Quote) that contains another map (Quote, QuoteField).  Or, what we’ve done is figured out a way to represent the potentially sparse fact tables resulting from large data analysis (OLAP) projects in a concise and easily addressable fashion.  Told you it wasn’t that hard.

GIVE ME MY DATA

So, now that we’ve inserted a couple of rows of data, let’s see how to get our data.  From above, we want to:

  1. Get all the data for a day’s worth of a symbol, and
  2. Get all the data for a slice of time during a day for a symbol

Assuming you’ve entered the statements above to insert the data, we can retrieve an entire day’s worth of AA with this simple statement:

List sbbo[‘20100124:AA’];

Now that we’ve gone over some of Cassandra’s basics, we’ll get a little more into it in upcoming posts.  That’s where we’ll cover the goal in #2.

THANKS FOR READING

PrintFriendly

Read the original blog entry...

More Stories By Colin Clark

Colin Clark is the CTO for Cloud Event Processing, Inc. and is widely regarded as a thought leader and pioneer in both Complex Event Processing and its application within Capital Markets.

Follow Colin on Twitter at http:\\twitter.com\EventCloudPro to learn more about cloud based event processing using map/reduce, complex event processing, and event driven pattern matching agents. You can also send topic suggestions or questions to [email protected]

Latest Stories
The platform combines the strengths of Singtel's extensive, intelligent network capabilities with Microsoft's cloud expertise to create a unique solution that sets new standards for IoT applications," said Mr Diomedes Kastanis, Head of IoT at Singtel. "Our solution provides speed, transparency and flexibility, paving the way for a more pervasive use of IoT to accelerate enterprises' digitalisation efforts. AI-powered intelligent connectivity over Microsoft Azure will be the fastest connected pat...
There are many examples of disruption in consumer space – Uber disrupting the cab industry, Airbnb disrupting the hospitality industry and so on; but have you wondered who is disrupting support and operations? AISERA helps make businesses and customers successful by offering consumer-like user experience for support and operations. We have built the world’s first AI-driven IT / HR / Cloud / Customer Support and Operations solution.
ScaleMP is presenting at CloudEXPO 2019, held June 24-26 in Santa Clara, and we’d love to see you there. At the conference, we’ll demonstrate how ScaleMP is solving one of the most vexing challenges for cloud — memory cost and limit of scale — and how our innovative vSMP MemoryONE solution provides affordable larger server memory for the private and public cloud. Please visit us at Booth No. 519 to connect with our experts and learn more about vSMP MemoryONE and how it is already serving some of...
Darktrace is the world's leading AI company for cyber security. Created by mathematicians from the University of Cambridge, Darktrace's Enterprise Immune System is the first non-consumer application of machine learning to work at scale, across all network types, from physical, virtualized, and cloud, through to IoT and industrial control systems. Installed as a self-configuring cyber defense platform, Darktrace continuously learns what is ‘normal' for all devices and users, updating its understa...
Codete accelerates their clients growth through technological expertise and experience. Codite team works with organizations to meet the challenges that digitalization presents. Their clients include digital start-ups as well as established enterprises in the IT industry. To stay competitive in a highly innovative IT industry, strong R&D departments and bold spin-off initiatives is a must. Codete Data Science and Software Architects teams help corporate clients to stay up to date with the mod...
As you know, enterprise IT conversation over the past year have often centered upon the open-source Kubernetes container orchestration system. In fact, Kubernetes has emerged as the key technology -- and even primary platform -- of cloud migrations for a wide variety of organizations. Kubernetes is critical to forward-looking enterprises that continue to push their IT infrastructures toward maximum functionality, scalability, and flexibility. As they do so, IT professionals are also embr...
Platform9, the leader in SaaS-managed hybrid cloud, has announced it will present five sessions at four upcoming industry conferences in June: BCS in London, DevOpsCon in Berlin, HPE Discover and Cloud Computing Expo 2019.
At CloudEXPO Silicon Valley, June 24-26, 2019, Digital Transformation (DX) is a major focus with expanded DevOpsSUMMIT and FinTechEXPO programs within the DXWorldEXPO agenda. Successful transformation requires a laser focus on being data-driven and on using all the tools available that enable transformation if they plan to survive over the long term. A total of 88% of Fortune 500 companies from a generation ago are now out of business. Only 12% still survive. Similar percentages are found throug...
When you're operating multiple services in production, building out forensics tools such as monitoring and observability becomes essential. Unfortunately, it is a real challenge balancing priorities between building new features and tools to help pinpoint root causes. Linkerd provides many of the tools you need to tame the chaos of operating microservices in a cloud native world. Because Linkerd is a transparent proxy that runs alongside your application, there are no code changes required. I...
In his general session at 21st Cloud Expo, Greg Dumas, Calligo’s Vice President and G.M. of US operations, discussed the new Global Data Protection Regulation and how Calligo can help business stay compliant in digitally globalized world. Greg Dumas is Calligo's Vice President and G.M. of US operations. Calligo is an established service provider that provides an innovative platform for trusted cloud solutions. Calligo’s customers are typically most concerned about GDPR compliance, application p...
Modern software design has fundamentally changed how we manage applications, causing many to turn to containers as the new virtual machine for resource management. As container adoption grows beyond stateless applications to stateful workloads, the need for persistent storage is foundational - something customers routinely cite as a top pain point. In his session at @DevOpsSummit at 21st Cloud Expo, Bill Borsari, Head of Systems Engineering at Datera, explored how organizations can reap the bene...
"NetApp's vision is how we help organizations manage data - delivering the right data in the right place, in the right time, to the people who need it, and doing it agnostic to what the platform is," explained Josh Atwell, Developer Advocate for NetApp, in this SYS-CON.tv interview at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
Druva is the global leader in Cloud Data Protection and Management, delivering the industry's first data management-as-a-service solution that aggregates data from endpoints, servers and cloud applications and leverages the public cloud to offer a single pane of glass to enable data protection, governance and intelligence-dramatically increasing the availability and visibility of business critical information, while reducing the risk, cost and complexity of managing and protecting it. Druva's...
Kubernetes as a Container Platform is becoming a de facto for every enterprise. In my interactions with enterprises adopting container platform, I come across common questions: - How does application security work on this platform? What all do I need to secure? - How do I implement security in pipelines? - What about vulnerabilities discovered at a later point in time? - What are newer technologies like Istio Service Mesh bring to table?In this session, I will be addressing these commonly asked ...
BMC has unmatched experience in IT management, supporting 92 of the Forbes Global 100, and earning recognition as an ITSM Gartner Magic Quadrant Leader for five years running. Our solutions offer speed, agility, and efficiency to tackle business challenges in the areas of service management, automation, operations, and the mainframe.