Cubegeek

It Begins Again

Ah. So many changes, so little time.

I think that when I look at all of the things I've learned about myself and technology over my career it is that there are two things I love the most. The first is having my hands on good technology and building a system everybody is proud of. The second is sitting down with project sponsors and proving to them that the technology I am selling absolutely does what I say it can do. 

Before I do the second thing, I generally need to do the first thing. But the industry has changed. More of these components have goodies built for them or with them in mind to make all of that building so easy that it is almost unnecessary. On the other hand, there is so much more variety, size and shape of data to be integrated into new architectures that what was once simple implementation has become much more complex. This is especially the case with security and privacy concerns. 

What I'm doing these days is taking what I have learned in DevOps and shrink wrapping it around my favorite database, which is Vertica. It has the most powerful engine for data warehousing. Data warehousing is still the king of structured data. And the toolset for structuring up unstructured data is improving all the time. So I'm going to go with structure. No surprise there. What it surprising is the extent to which the speed of data transformation can take place. I'm talking about Kafka, which is still a bit clumsy, but capable of great things. 

Of course you know I have to talk about LLMs. Well all I'm going to say is the following. I find it difficult to believe that LLMs as a service will not pop up everywhere. For me, the trick is to find a way for individual companies, especially large ones, to enable private LLMs to become referential to every pool of data they own. In other words, this thing I'm coming to understand as Prompt Engineering will soon become the most extraordinary UI ever, and a lot of interactive prompting and responding with become the SQL of the future. So I'm looking at the probabilities of architecting NLP interactive stuff to bring back references to curated structured data. 

Data Mining & Easter Eggs

Q: Why do they call it data mining? Isn't it knowledge mining?

A: No, it's data mining. Knowledge is two steps up the added value chain. Once something becomes knowledge, you don't really need data mining. So let's first talk about the added value chain.

Data is less valuable than information. You can have bad data. It’s still data. You cleanse the data and organize it then you have information. Encrypted data is still data. It’s not information until you decrypt it. That’s the difference between data and information.

Good, clean, organized data = information. It’s not knowledge until it tells a story. That means a human can understand what it is. Knowledge is information in context. If I say “35 Units are in Department 7” and “27 Units are in Department 8” I have information. But it’s not knowledge until I have complete information. I don’t know the date. I don’t know how many departments there are.

Intelligence is actionable knowledge with appropriate qualifications. We could get into what make makes something actionable. But that has been done here[1].

All of these elevations in value come from human organizing data into information into knowledge into intelligence. But humans have a particular way of organizing and thinking about where to find intelligence. Think of how children search on an easter egg hunt. They don’t brute force search every square inch for things that look like eggs, they try to emulate the thinking of ‘Easter Bunnies’, the parents who hide them. Machines, on the other, search more exhaustively and find patterns that humans don’t consider relevant. A machine might notice for example that light colored eggs were hidden an average of 2 inches from tall vertical surfaces. Children would not notice, they just count the eggs. But machine can surface patterns in the DATA that create a different kind of information, knowledge and intelligence. For example, data mining an easter egg hunt might show based on proximity of egg colors and the color of occluding objects, that some of the people who hid the eggs might be colorblind. Their inability to distinguish certain colors made the hunt easier for non-colorblind children to find the eggs, and therefore the hunt rewarded faster running children without sophisticated hunting tactics. That is surfacing information in the data that humans would never have the inclination or patience to discover, which is the point of having machines do the mining rather than humans.

As soon as you have a machine-sensed pattern in the data, it changes how you organize the data into information. That is what adds value and it changes the way you think about analyzing easter egg hunts in the future.

Footnotes

[1] Barriers to Added Value in Data Analysis

 

Supercomputer Blues

from the archives October 2005

I named the big one Potter and the little one Hagid. We're running benchmarks from Snape. Potter is a temperamental one, doesn't like my 32bit text editor and won't synch IDs from my database to the Deployment Server, so that when the applets go a-calling, they get rejected out of hand. But Potter hasn't given me a real fit like Hagid did this evening. Three tests. One gives 40% errors I can't explain. The next one goes flawlessly. Then I change one little parameter and the whole craptard goes zombie. This, ladies and gentlemen is why NT sucks, no matter how hefty the hardware. I've got 300 databases running simultaneously and the agent freezes. Can I kill the zombies all at once? Noooooo. NT has no such animal as a kill -9 (the lung ripper). It just sits there and divvies up the full weight of 8 3GHz Xeons burning 100% across 300 processes that I've got to kill one by frikken one. With a mouse and four clicks. Warning, killing this process may cause system instability. Are you sure?

Anyway. Next time I'll keep Load Runner throttled and not try to launch 300 databases all in the same second. Lesson learned.

The Columnar Stadium

Q: What is the difference, in layman's terms, between a relational database and a columnar database?

A: I have a great analogy for you.

Imagine that you are at a football stadium. There are 100,000 fans in the seats. Obviously today is somebody's birthday. So if you asked everybody whose birthday is today, about 1/366 people will stand up.  Go through the calendar, and you'll eventually get everyone to stand up.

 

In a relational database, you use the entire stadium to 'store' the actual people by birthday. Everybody has their own seat. In a column database, you only have 366 actual people 'stored' and then you have 100,000 pointers to the other seats where those people would be stored.

 

In this analogy, you are the end user standing in the parking lot asking for all of the people born today. That is your query. When your answer for today is 273, you will see 273 people come out of the stadium with the birthday attribute of today. What you don't know is that the columnar database has only one person stored in the whole stadium that represents all of those 273, whereas the relational database is actually using 273 seats. So that makes the columnar database 273x as efficient as the relational database.

 

And guess what, the larger the number of total people 'stored' the greater the relative efficiency of the columnar store.

Related articles

Three Databases

Stepping Out of the Elephant's Shadow

Essbase Capacity Planning (1997)

Back to the Future + Batman

I just came across this information on the interwebz. The past's vision of the future always looks awkward from the POV of the actual current future. These are all applications I could pretty much build single-handedly on Full360's Data Platform in AWS.

The Winter Corporation, a consulting firm that specializes in large-scale databases, data warehousing, and strategic information management, conducts a yearly survey of large corporations and then announces winners in a number of VLDB categories. In 1997, the largest database was Knight Ridder's DIALOG, a text database, with 7 terabytes (seven thousand gigabytes) of storage. Replications of databases present in redundant array of independent disks (RAID) systems and mirror sites were discounted in the survey.

Our table summarizes their results for 1997:

Category Winner	Application Type	Company	Type of System
Largest decision support system database: 7 terabytes	Text search (DIALOG)	Knight Ridder Information	Proprietary system and Hitachi Data Systems hardware
Largest transaction database: 3.5 terabytes	Package transit and delivery tracking	United Parcel Service	IBM DB2 database and IBM System/390 mainframe
Largest decision support UNIX database 2.4 terabytes	Decision support	Wal-Mart Stores	NCR Teradata DBMS and NCR Worldmark
Largest transaction processing database on a UNIX system: 750 gigabytes	Credit card, accounting and loan, and customer information services	Shin Han Bank	Informix XPS DBMS software and Hewlett-Packard 9000 hardware
Most rows in a database on a traditional platform: 100 billion rows	Text search (DIALOG)	Knight Ridder Information	Proprietary system and Hitachi Data Systems hardware
Most rows in a transaction processing database on a traditional platform: 50 billion rows	Customer billing system	Telstra	IBM DB2 database and IBM System/390 9021 processors
Most concurrent users in a traditional operating system: 2,500 users	Decision support	K-Mart Corporation	NCR Teradata DBMS and a system 3600
Most concurrent users in a UNIX database: 650 users	Decision support	Wal-Mart Stores	NCR Teradata DBMS and NCR Worldmark
Largest federated database (databases that contain independently operated databases that own their own data): 822 gigabytes	Audit trail for retail financial transactions	National Processing Company	Oracle on a Sun SPARC, HP 9000, and IBM RS/6000 servers

 

I ran like hell away from raised floors and mainframe computers when I came out of college in the mid 80s, but they held dominance over UNIX systems for a dozen years after when it came to big data. I was always impressed with Britton Lee, which later became Teradata, but there was always a huge gap between the having of such data and the presentation of it to the minds that would make sense of it. Nothing is so foolish as Batman in his cave waiting for a printout from the Bat Computer, no matter how much the Boy Wonder assents. Caped data crusaders always get snagged in the real world when faced with Riddler and Jokers. 

So I'm saying that it is the collaboration of minds with access to large data sets that make the big difference, not just data scientists sitting in their expensive hideouts plotting world domination. That's what's new and possible now that the cost of computing has come down and the ability to spread seven terabytes among hundreds or even thousands of analysts make for a new dynamic in decision making. 

 

Stepping Out of the Elephant's Shadow

Hadoop.

Where do I begin to tell the story? How about we go from the epiphany backwards? This morning I read this from Dave Sisk. 

 Hadoop is not even in the same ballpark as any of the CDBMS's that have been mentioned. It's not even a database, for that matter...it's a giant MPP ETL process...which is a great thing IF you use it as a giant MPP ETL process instead of trying to use it as a database. I've examined HBase closely, and looked at a colleague's implementation...it has to be the only key/value store that I can think of that is a worse piece of crap than MongoDB. (At least MongoDB is better than something.) My colleague's company's 60-node implementation of HBase (on big honkin' enterprise hardware) struggles to insert 2000 rows of data second (I can insert at 10-20 times that into PostgreSQL running in a VM on my friggin' laptop), and reports run for hours (sometimes days). You can do the same work in a good columnar RDBMS 2-3 orders of magnitude faster...as in milliseconds or seconds (minutes at worst), instead of hour or days.

At a prior company, we used Vertica to consume hundreds of thousands of rows per second, and could return results from billion row high-reduction queries in a few hundred milliseconds (from about 5 hefty nodes)...Hadoop/Hive/HBase with hundreds of nodes could not come within 2 orders of magnitude of that kind of performance, no matter how much hardware you throw at it.

That is what I've been waiting to hear for years. So I wrote back.

Finally thank you for helping me understand what I didn't, that there is a massively huge performance gap between Vertica and Hadoop. I've been wondering why I never meet Hadoop folks in my space (Business Intelligence) and I've been listening to the guys at MAPR tell me theories of why their Amazon-approved version of EMR is going to be a world beater. I have assumed that all Hadoop is simply a very large scalable file system (and I've started to call it HDFS) + some clunky tools that lack a semantic layer. But I assumed that a reasonably competent Java programmer (who wants to be that?) could make it perform at a good clip. No matter what the performance, I expected that it was mostly ETL class tech. BUT I figured sooner or later somebody was going to build a semantic layer of SQL onto it and then it would be serious competition for columnar DBs, primarily because of mind and market share. 

However, if Hadoop is little more than a glorified giant file system and map reduce is to HDFS as regex grep sed & awk + perl is to ordinary file systems, then there's no way it will ever compete on performance and cost efficiency to columnar tech. 

I'm going to assume HDFS is a data lake from here on out, unsuitable for BI queries.

That assumption means a great deal in my world and it erases a class of insecurity I've been having over every discussion of 'big data' I've been a part of for several years. You see none of my customers have Hadoop or ask for Hadoop. All of them can form the syllables. They know about Hadoop. Hadoop *is* big data, as far as the layman's world (and another world that is not BI). So I haven't had a *reason* outside of great curiosity to build anything with Hadoop. 

First of all, I have S3. There's no bigger data lake I've ever needed. Terabytes are not a problem. I mean I've got terabytes at home. But under ElasticBI, I've got S3 whipped into shape and smartly integrated to every database I care about. (Vertica, Redshift, Essbase, VoltDB). So I never have to concern myself with running out of space for staging or moving massive amounts of data.  It's always about database optimization. That requires structure, structure requires purpose, and I know how to get that from my customers. Hadoop is about unstructured data.

Now when I start talking about unstructured data, I mean web data that has a volatile structure. And in that space I see tools like MongoDB, Cassandra and CouchDB with Couch as the winner. I've heard horror stories about Mongo, and that Cassandra is a big tease that never quite gets all of her drama together. There's also Riak which is heavyweight champion in the space, so I hear and believe. But I'm not building ecommerce websites and I don't need to manage volatile content and serve up XML to be rendered, so I have no need for that class of data management. Not right now anyway. I want to be the master of all data management, data store and database worlds, but I have to deal with one continent at a time, and I can accept that Riak and Couch are on another planet. Planet Unstructure. 

But when they say they're going to put on SQL clothing and take their big data + analytics into the Data Warehouse realm, that's war of the worlds. I don't like the prospects of that imminent invasion. Because, really, websites are the masters of handling 10,000 simultaneous concurrent users. I can't do anything like that. Those guys must know something. 

Well, so did LAMP stackers at one time. I'm going to take a gamble and commit HDFS and all that manipulation to the back porch. It's not competition in BI, and it never really was, but that was really in my mind a matter of market focus. Now I understand technically that there are hard limits to what all of that can ever do and strong technical reasons why what I do with my database tech is not threatened by these other systems.

I still want to know. I still want to spend some hands on time and eyeball some NoSQL tech in the context of their own systems. I wish I knew a guy, personally. But maybe our paths haven't crossed for good reasons. Either way, I am stepping out of the shadow of the elephant.  Hadoop is just a data lake fallback for collecting a lot of stuff that may or may not be map reduced into something coherent. It's a specialty ETL transform that we at Full360 will do with realtime streams.  So maybe if my Colorado River data pipeline fails, it will form a Hadoopified Salton Sea while we fix the levee. What it most definitely is not, is Fast Data. Meaning it has no place in the IoT future and is an artifact of what we will inevitably call something along the lines of JBOD. Lakes are meant to be drained. And now I've completely introduced heterogeneous metaphors into this. But that's kind of how epiphanies work, neh?

I'm out of the shadow of the elephant who drinks up lakes with that weird trunk of his. Yay circus tricks!

Big Data Analytics vs Data Warehousing

Problem: Determine how many people are at the beach on the 4th of July.

Solution 1: Big Data Analytics - minded
Take a fleet of quadcopter drones and have them take thousands of snapshots of the people on the beach. Download many gigabytes of jpegs. Use fancy facial recognition software to identify individuals and take a best guess estimation by de-duplicating the result set. Give a number within a confidence interval.

Solution 2: Data Warehouse - minded
Block off all access to the beach. Don't let anyone in until they swipe their driver's license or photo ID. Query the database, get an exact count.

Big data analytics is what it is because it makes guesses from data that is not structured to answer specific questions. Data warehousing is what it is because you absolutely structure the domain for data collection according to a purpose. In an ideal world, all big data analysis guessing evolves to data warehouse structure. 

So 'big data analytics' essentially means inefficient unstructured data + smart guessing. All of the credit card transactions in the world are data warehouse structured, and have always been. But that's not 'small data'.

To get a longer explanation of some the details of data warehousing that would bore, but satisfy, an expert like me, read this article. But do so with the following understanding. Everything that 'big data analytics' is, will become a data management subset of future data warehousing. In other words, SQL has mastered all we need to know about set logic and transformations in database tech. It *is* the standard semantic level, as permanent as HTML is to web development. Everything the unstructured database tech now do in 'NoSQL' will be assimilated. Resistance is futile. At the core of the technology, what's too fast today will not be tomorrow. What's too voluminous today will not be in the future. 

At Full360, we already combine appropriate technologies in our DW + BI Elastic framework to bridge the gap. There's no data to fast, too wide or too big for us. 

Barriers to Added Value in Data Analysis

There are also Five Primary Barriers to value add.

NR - Not Relevant
NA - Not Actionable
NS - Not Sufficient
NE - Not Efficient
NI - Not Interested

Imagine, for example, that your automobile has 100 sensors and each sensor creates a datastream of 1GB per mile. That's going to generate BIG data. But what do you really care about when it comes to your car? What you might care about is how many chicks you can pickup. In that case, none of the big data adds value. = NR

You might care about how quickly you can stop in the rain. You have brake sensors and you have wet weather sensors. So you can use your big data to calculate your braking distance in wet or sunny weather. The problem is that you are 500 miles away from anyplace (or anybody) that can install new brakes or tires. = NA

You might care about trunk capacity. You have sensors on all four wheels but they all combine into one number. You can't disambiguate between weight in the front seat from weight in the back seat or shifting weight in the car.  = NS

You might care about gas mileage on steep roads as compared to straight roads. However to get an adequate sample size, you have to drive so many steep roads (and you live in Kansas) that it costs more to find the steep roads and then calculate the savings, (and add sensor 101, an inclinometer), than you could possibly save. = NE.

Notice in all of these, I am talking about what you care about. If you don't care, then you certainly aren't going to pay somebody to care for you. If your answer to all of this is 'so what?', or 'maybe later', then you clearly are not going to add value. This happens more often than you might expect. A BI project might just be seen as something tangential to the business, or as a special gift to the dorks in accounting and IT. 'Buy him an iPad to shut him up'. Such systems do not contribute value. = NI

---
Understand the goals of the business. Find out who has responsibility for what that can be expressed in dollars. Everybody has to know *something* to make the organization work. Determine what the computer can know.

 

Packer-Docker & Things

So I've finished my course in V&O 101 - Virtualization & Orchestration that is. Somehow on the working end of this stuff it doesn't seem so very exciting. Then again, we're going for the reliability. So here's the news, it works.

I now have a docker machine which is a vagrant based vm, some ubuntu precision 64 or some such, sitting on my Mac. I spin it up and pull down a git repo into its belly. It spits out a nice plump docker image weighing in around 1.5GB. I can then tag it properly and send it on its way to one of our docker registries, some sitting on customer sites, some in our own little cloud. Then I can pull it, almost like a git repo from one of those registries and boom. There's my application in its own self-contained environment, nicely synched up with the release version of the docker container and the application in concert. It takes about an hour end to end for me to fix a bug an move code flawlessly into production.

Now part of the coolness is the way we have wrapped these docker containers into our process. All of the logs that are generated by the application are encrypted and sent to S3. From there they can be downloaded and decrypted from a command line instruction. Also, they burp out AWS SNS notifications on cue, and other metadata about them are piped out to DynamoDB. So we have pretty much got our container act together.

In other news I just made an interesting sort of breakthrough. I figured out an implementation vulnerability with AES. Now it's not that I'm a genius, it's a fairly well known vulnerability, but I figured it out on my own and fixed it with an alternative. Now I still depend upon a cipher coded into OpenSSL, but I am that more confident and my customer's data is that more secure, and that makes me feel good.

What else is new is that we have extended our ability to automate dumps of massive files and work with full and incremental streams. This is a new feature of the ElasticBI Producer framework. Where we used to crap out our JVM around 80 or 90 MB per query, we are regularly doing 300 to 400MB per single query. I have it on good authority that we are essentially unlimited but we're not hitting any large such requirements yet. 

We are expanding our ability to handle odd data. These days I'm parsing standard reports involving securities trading and generally boning up on my conditional logic. I.e if you read header type 4, then the next data line will be type 3 that you scrape to file X which gets put in database B, but the following line (if is stards with GNMA) is of data type 5 that you push to file Y that loads into data table R. 

We're also implementing the new key value pair XML and JSON capabilities of Vertica. I will have done that up by the end of this month, and will report on that kind of fun stuff and as I do more integrations with DynamoDB I'll have some better rules of thumb about which data best goes which way. 

That is all for now.

The Curator

We already have the concept of the Producer and the Consumer. Now we add the concept of the Curator. 

The purpose of this is to regularize the idea of dynamic resizing of the query space. The Curator would thus be responsible to know which data are permanent and unable to be further modified. Let's take the example of a financial data warehouse. Generally, we allow modification and restatements of data past the close of a fiscal period. At some point however, there needs to be a cutoff. This may be one period, two period or perhaps 6 or 12. The longer the period, the more transactions we would likely process in any refresh.  For the sake of our example, let us allow that cutoff to be three months, with a grain of one month. 

So every day we get a restatement of the current month. This is to be expected. But we may also get data which include transactions that are up to three months old. However on June 1, we would close off the trailing month of our three month window and accept no further updates to March. At this point, the Curator would engage the spinning database and push out the final version of March. This should be done in the most efficient manner for the sake of a database load. So the Curator has a kind of producer itself. This Final of March will go into its own S3 bucket and be registered with a Curator table.  Obviously this is a job that can vary in size with the desired grain of curation.  I would recommend a month or a quarter. Lets call this operation a Curator::SetFinal(2014-03). This makes a permanent copy of the last official update for that month of data and stores it out to the Curator's store.

Now depending on how we want to handle it, we can either spin up a new size database in parallel, or drop and rebuild the production database overnight to the new size. Lets say we want to do that operation to expand the history window from 3 years to 4 years. We could call that Curator::Exhibit(4 years), and that would pull the appropriate data from the Curator's store. Bottom line is that the Curator can dynamically resize the query space by using data certified by the application. 

Not bad eh?