Cubegeek

A Zynga Story

I know something about this. You should too.

MPP Database Update

I noticed some folks reading through the Redshift category and noticed that I haven't written anything new for a while. So here's what's new:

1. We see that Redshift has improved its vacuum capabilities and added more functionality all around. It has improved its performance all around, but it hasn't changed its overall performance characteristics. Redshift is not fundamentally different after two years. It still behaves like Redshift when compared to Vertica, the other MPP columnar database we support at Full360.
2. We have been able to learn quite a bit more about tuning Redshift. Full360 will be offering this service soon. It's called Upshift and it is surely the most comprehensive performance evaluation available in the industry. 

I have to qualify all of this by saying that I personally work a lot more with Vertica than I do with Redshift. These two products may seem very similar but the details are often overwhelming. Fortunately we are developing a methodology that expresses the rules for optimization very well. So while the characterization I've made still holds true, there will be a growing number of exceptions and interesting circumstances. I call Vertica a magical sword. It is powerful, precise and it sharpens itself. I can cut intricate and delicate patterns, and chop hundreds of large heads. I call Redshift an ogre's club. It is massively powerful, brain dead simple to use and relatively inexpensive. So you basically have to look at your application and know whether or not it is a job for a club or a sword. Our methodology will tell you exactly which, but like I said, the devil is in the details and we are wrangling dozens of demons. 

The good news is that both products are improving at a good clip. Still, I confess I'm paying more attention to Vertica. I'm really impressed with the overview I got yesterday on Vertica 8. They've optimized some of their geospatial algorithms. They've incorporated several ML features directly into the core product. They've dramatically improved their integration with Hadoop, Spark and Kafka. They're claiming to perform 160% of Impala. So that's superb. Most importantly there is enthusiasm for the creation of the new company, which is less like Vertica getting sold to Microfocus and more like a rebirth of Microfocus itself who is, by the way, the owner of Suse Linux. The Vertica guys are thrilled that they'll be working for a software company. That means the upcoming integration with S3 is serious as is their priority on cloud implementations. All good. 

Vertica Idempotent Set Transformational Denormalized Design Standards

 At Full360, we have developed a best practice around our design of greenfield and re-engineered DW applications. The following is a high level guide to how we accomplish this in Vertica. Vertica optimization is something we have pursued with vigor at Full360. There are several different levels at which this can be pursued. Implicit is the modularization of the applications so that the major functions of our data management philosophy can be expressed discretely. But let's get to the $10 words, shall we?

Idempotency
Both I and JDub could go on at to absurd lengths about how important this is to the modularizationf DW design. I will simply, which characteristic casualness, tell you that it makes all of our stuff idiot proof, in that it makes our data provision dependencies kind of go away. The basic idea is that in application units(which basically means the chunks at which the data to be consumed makes process and business sense)  you make your input streams discrete.  So when your input streams are discretely chunked, you can run your process over and over without concern about whether it has been done once, twice or never. You just run that independent data provision job and it creates the right sized bucket of data.

Set Transformational
VLDB folks are probably familiar with why you do ELT rather than ETL. The simple way of saying it is that database developers are more stingy and efficient with data than ETL developers. I developed a taste for hand-crafted 'ETL' back in the days when Informatica was a baby, and having my Unix biases, I always loved moving files around. At the time, my focus was on Essbase which had not ETL hooks, even though Arbor should have purchased an ETL company cheap. Interestingly tangential, Wall Street has never been very long in ETL companies. Anyway, I expect that Informatica and Talend will not like to hear that their days are numbered, but then neither did Carleton and DataStage, and they used to rule the world. The bottom line is that moving data from table to table using SQL rather than in a GUI that does not is going to be, in certain databases, much faster and more human readable than in third party tools. So we do set transformation, and even regex stuff, inside Vertica. One day we may even benchmark UDFs against external programs.

Denormalized
Vertica like Redshift is not a transaction database. It is columnar and it easily handles 600, 800, 1200 column tables. It was designed to. So there is no reason to do a lot of silly little joins in silly little tables to get juicy fat data. We make all that part of the ingestion process, which gives us what we want. Think about it for a minute. Consider the volatility of lookup tables and dimensions as compared to the volatility of atomic facts and transactions, aggregated or otherwise. The facts will be bigger and more fluid. So why spend join energy on query exhibits over the long haul when you can easily have all the columns you want? You don't have to. There are no table scans from hell, that's what columnar solves. So we go big.

--

Production
I'm not going to talk about the guts of Production other than to mention it briefly here. Production is some of the genius and we have a bag of tricks that is ever-expanding as we deal with realtime, near-realtime, and other odd types of data sources. Yes we lambda with streams and lakes, but we smart lambda. Again with whatever tech makes sense. Right now we're playing with Kinesis and Kafka and our own custom Actor models which we're sure will evolve over time. We're also looking at how to use Redis and other superfast KV stores. So I suspect we will grow many efficient tentacles as we Produce standardized data for ingestion into our columnar DW apps. Nuff said.

Ingestion

_SRC

We ingest data into source tables for each schema as they come to us. No matter how many fields, large or small, we will take them in using a COPY from produced files. Whether in Vertica or Redshift we standardize on UTF-8 with vertical bars delimited and a backslash escape. In some cases, if we've munged up variable length stuff from our own custom regex routines or other JSON, AVRO or semi-structured effluvia, we will have an additional pre-step using Vertica Flex Tables. We are coming up with best practices there too.

These source files should also retain the original names of the fields of the produced data when possible. This assists in debugging with the original developers.

_STG

All of the data that is to be used in the application should then be mapped to a view. This is the staged data. Staged data should be of the increment of ingestion (discretely chunked in application consumption units). That is to say that your _SRC and _STG will carry the same number of records although they are likely to carry a different number of fields once the ingestion is done.

Transformation

_CLN

The clean tables should be materialized tables with human-readable fields that are conceptually discrete. This is generally accomplished through a direct insert from one or more _STG views. Clean tables can be defined with blank fields for further rule application. Clean tables that contain all history for the query space should have the term _HIST_CLN, otherwise one should assume that a clean table is the same size as an ingestion increment. Clean tables should be optimized for the scope of the transactions that take place in their creation. When you are looking at the clean tables, you have all of the data you need from all of the sources presented in the way you as a developer think about the data. There should be very little ambiguity at this point, it's your best look at the details before they are aggregated and rationalized.

_LKP

Lookup tables are straightforward. They should be optimized for their transaction capacity with clean tables.

_IFT

In a complex model, clean tables can be made into Intermediate Fact tables. The intermediate fact tables are materialized tables with all of the necessary dimensions that support measures that can be made across the full query space. These may or may not be exhibited directly, but should be useful in a partial analysis of the particular measures they contain. An intermediate fact table should be the place where window functions are applied. They should be the place where obscure field conditions are made into explicit attribute and status fields. It is important to know that an application may have dependencies of different measures that seem to be dimensionally equivalent, but actually aren't. So by using IFTs we unburden ourselves of the very idea of a single massive star or snowflake that might have holes. Also, we can capture all of the attributes of a set of measures completely without concerning ourselves with the weight of them in the final presentation layer.

So think about this. A clean look of the data will probably not have sensible status fields they will have codes. There may be multiple ways to interpret a certain combination of fields. So whatever you need to support a the full scope of consumable data, even if it means synthetically remastering transactions, you can do with IFTs beforehand. Building these are the real guts of the DW application, and it's where the fun of working with sharp analysts come in, especially when it comes to data integration projects. I will apply all of the reasonable and semi-reasonable business rules here. This is where I have enough detail to point machine learning, because at this point the data is explicit and human readable. It will reveal the more interesting cases and outliers. And here it should be very rich - beyond human comprehension, so yeah maybe you don't go full wide with these tables, only add 4 of the 50 psychographic customer tags you have against their ID, because, security.

_XBT

Exhibit tables are materialized views in some cases but generally views that are presentable to the end user. These should be indexed and optimized for query retrieval. Security access rules are applied to user groups, etc only to exhibit tables. It should be assumed that none other than dbadmin processes will have access to any tables or views but the exhibit tables.

--

So there it is. This is rather the state of the art that I have internalized in five years of cloud based columnar data warehousing. Maybe I should write a book.

Producers, Consumers & Dynamic Data Warehousing

So we have a bunch of big ideas around here at Full360 and it seems that I have been doing them rather than talking about them. That's what I like about work. Here's what we're doing now.

If you're going to handle big data, sooner or later you're going to get massive amounts that really are too bothersome to keep on your database server in any other form than IN the database. The cheapest place in the world to keep your data is at the NSA, they'll scrape it and store it for you free, indefinitely if you encrypt it. But the cheapest accessible place to keep your data is in Amazon S3. So our theory of DW says, keep your permanent record on S3, and build dynamic data warehouses as needed. What? Dynamic data warehouses? Yes, we shall explain but we're not there just yet.

The massive amounts of data on permanent record generally comes in a stream or in multiple streams. In fact, we have abstracted all of our dataflows essentially to streams and have build standard operations around those streams. There is some precise language that I have around here but the purpose of this blog is to just give an overview. Suffice it to say that there are timing and chunking issues between the way data is produced at the source and the way it is consumed into the data warehouse. Therefore, producers and consumers.

A Producer is a chunk of code that aims itself at a source of a datastream, periodically scrapes off a chunk and then stores an {compressed, encrypted, UTF-8'd, versioned} file set onto a time-hierarchally organized S3 bucket. Let's say that your stream was POS transactions from McDonalds. Every hour, you would make a smart query of the transactions based on a time window (which may or may not be redundant) and then send your results to S3. At the end of the day, you'll have 24 flatfiles in the path mickeyd-bucket-name/POS/2014/2014-01/2014-01-09_0100_MickeyDPOS.txt or some such. Easy to get, can handle millions of files, no problem. But the point of the producer is that it is synchronized to the actual production of data, whatever the source and chunks it appropriate to the most efficient manner of database ingestion.

Producers have smart sub-features. For example, it is the responsibility of the producer to translate XML or JSON to CSV. We like vertical bars (since we are a Vertica shop, nyuk nyuk) but we call it text file or a flat file anyway. Basically all of the cleansing REGEX, bogus character detection, and internal record consistency stuff takes place in the producer. The producer can be made smarter with regard to time (to avoid redundancy) by tying its query to the DW control table. Most importantly, the producer process itself can be scaled horizontally for scalability and/or redundancy. 

The producers create a text-based data warehouse in S3 which is rationalized to the largest set of data any data warehouse can handle. It is all history, all fields, all streams. The consumers determine what goes live and queryable at what rate. 

The Consumer is a chunk of code which generates smart efficient updates and merges to the data warehouse in synch with the way that data is consumed by the DW users. So it is the more conventional driver of database operations. In fact, you can think of the actual database production lifecycle in terms of the consumer. As my old good habits dictate, each of these are discrete operations that are verbs. 

• Capture
• Ingest
• Cleanse
• Stage
• Merge
• Calc
• Errorfy
• Report
• Distribute

Errorfication is something we've done before but it is a concept that needs a bit of explaining. I'll do that in another post. The rest are basically self-explanatory. Notice that from Ingestion through Merging, we increase the complexity of the operations but reduce the volume of records we ultimately work on. Since we're using columnar shardable database tech, this scales rather well, even though merging will always be a CS nightmare. 

Note that the producers and consumers work together in real-time but you can choke them up or down. This is determined by the commit stamps on your control table. So let me talk about that a little bit.

Each file processed into the S3 buckets is going to be registered into a control table. This control table will let us trace all of our records back to S3. IE it is a key into the DW that gives us the provenance of the ETL process. You might be producing on the daily but consuming over the weekend in a 4 hour batch window. Nothing exists to the DW until it is registered which is done immdiately after a bulk copy of a file is loaded to the appropriate source table. Assuming the source table lives until the next step of ETL, the control table will know everything it needs to know about the content of the file loaded into the system. This control table's information is used to throttle the size of consumption chunks. I could go back 2 days, 30 days or two years on my consumer depending upon how much I want to burden my merge process. I could measure those two days by transaction stamps in the datafiles themselves or by the days they were produced to S3. Or I could just rebuild the entire warehouse from scratch or stream by stream. 

These are the fundamentals of the dynamic data warehouse. Its great efficiency is that database servers are not burdened with local data - transfers from S3 to the VPC being cheap. It allows near-realtime data loads from disparate external sources, sometimes halfway around the world, and it allows for the exciting possibilities of adding open data into corporate DWs with relative ease. Naturally, dumped DWs can be published elsewhere. It's very cool, sez me.

What's New This June

The past couple weeks' geekery has been focused primarily on RSpec and improving the quality of my Ruby objects. So while I've been knocking out some practical stuff with the RightAWS API from a top-down, procedural programming paradigm, I've had to rethink in order to refactor. That means applying some different principles to my coding.

In some ways, RSpec makes that very easy, in other ways it's complicated by the inner and outer loop priorities of using it with Cucumber. One is behavior driven and the other is test driven, rspec being the latter - and when I know exactly what I'm doing in the context of an embedded system, one is pretty much the same as the other. What I'm missing, of course is the vocabulary of Web based programming with its object models and its brokers, controllers and whatnot - abstracts for generality. My bias, of course, goes to having the damned code do what I want it to do, and I have generally known how to make that happen, living off of code from Christmas Past.

But since I'm not familiar with all the objecty things I can do with Ruby or how marvelously it abstracts up with elegant constructions, all this Cucumbering and RSpecing can be pretty useful. Now just combine that with not only git but git-flow, and you've got something. Well, I've got something - a handful of tools which are starting to feel natural in my hands. So now while I'm still a sophomore journeyman, I'm starting to use more vocabulary, and getting even a bit wonky perhaps. We'll see. I've got code reviews coming. Still, I did get a chance to eyeball some code that was totally perplexing just nine months ago, and I have the nerve to be haughty about how it ought to go. What's clear is that we're starting to gel as we generate more code, common language and recognize each others' coding style.

In the meantime, I just concluded the Vertica Bootcamp. And I forgot to ask for stickers. 

Everybody has seen the Wikibon (or was it Gartner) report that Vertica is number one for big data market share. Right about now, as they are newly on version six, I would say that they are about as advanced as Essbase was - technology-wise, at its version six a dozen years ago. But Vertica is a much smaller company and big data seems to be a newer market. So that's all to the good. Their technology is baked and almost nobody is pushing their limits. I predict, of course, that the big data market will swallow the rest and not much of what lives in todays relational stores will remain relevant for very long. The technology is being tested, by the likes of Zynga as everybody knows, but will do much more as people wrap their heads around what is possible. 

That leaves me thinking about a number of things. The first is the availability of cheap vizualization tools. So I'll add that little task onto my part-time plate. The second is a review of what is actually possible. And since I'm cool with the open sourceable, that means looking at NASA's new technology transfer portal. But there's also the obvious.

What's obviously possible is the upgrade path of the mediocre. I shouldn't care as much as I do, but I don't like the idea that Microstrategy might just now finally have a backend that makes all of its promises actually work. Sucking up that back-end business ought to be a no-brainer, and if HP or anyone here in the States can make heads or tails of exactly what it is that Autonomy is supposed to be accomplishing (The company reminds me so much of ePiphany.com, that it's creepy) then maybe it will get sucked into that marketing. But I try not to worry about that, because I still have my own ideas.

So next I'm off to master a bit more resource management for Vertica and then bring some of my old models along and see how well they work. I'll tell you this for sure in a heartbeat, Vertica is perfect, and I do mean scary perfect for Essbase drill-through. In fact, I'm fairly certain that I could make it work in a hybrid cube. So maybe if I have some time, I might try that out with the latest Essbase Studio.  I just love the idea that for Vertica, entry-level means a three server database cluster with 5TB of data.

So that's what's new, Magoo. 

By the way, Cringely is right about Microsoft Surface. The new MacBook Pro is not worth my money but still brilliant. Google Plus is fading into my background but remaining high quality. And I think Evernote is going to remain a winner.