Handling data with changing schema on Hadoop

User data often is unpredictable, even when we can predict a change coming our way we need to prepare for that. Make changes in our environment to accept the incoming change, accommodate and absorb. 

With that being a fact of life, our design should allow for those changes to happen.  With the structured data warehouses gone (well, mostly), Hadoop based data stores are the norm these days, and would be default in future not so distant.  By definition, Hadoop allows all kinds of data to be stored on the cluster and happily provides various tools to process such data.

However, with different types of data out there on cluster, any given tool would need to "know" what data that is? How to read it, interpret it and process it. That would have been relatively straightforward with the data schema being static, as in, a file comes in from source A, with a certain format, and that’s about it. You can define programs/structures to work with it. 

What if the source system decides to alter the data set schema, add a few columns, and change some column ordering, perhaps data type too, what then? Would our program reading that dataset on Hadoop cluster still work as it? No.. It won’t .. Because it would also require change to reflect the same changes sent from source.

Assume that you did those changes too. So, now our program is version 2, able to handle the dataset sent by source (v2 as well). What about the older data lying around on Hadoop cluster (v1, with old schema)? How is that dataset going to used/usable?  What if that data was used by hive tables, would you have two (or more) set of tables? 

What if that data was exposed to some analytical programs, through R or similar? I am sure we won’t want to rewrite/test the whole solutions just because source system decided to change something?

And this scenario is not purely hypothetical, it happens around us, more often than not, just that we deal with it in our own patchwork styles, or solutions varying on a case to case basis.

Here I propose a somewhat generic solution, one that might work for all, well, nearly all use cases.

Avro is a data serialization system, wherein the schema is built into the file itself. As a result, programs reading the data-file doesn’t have to bother with interpreting the data file through external schema.

Avro was designed by Doug Cutting for similar reasons, as a tool that is language and schema independent and therefore unrelated tools/technologies can deal with data files.

That being said, is not a silver bullet in itself, but it handles the schema change fairly nicely.

Basically, you store the data on hdfs in Avro formatted files. For that, Avro schema needs to be created, which is fairly straightforward json structured file, indicating the record structure. Take a look at an example here - 

{"namespace": "myExample.avro",  "type": "record",  "name": "User",  "fields": [      {"name": "name", "type": "string"},      {"name": "age",  "type": ["int", "null"]},      {"name": "address", "type": ["string", "null"]}  ] }

create external  table ex_users (name     String ,  age     int , address     String) stored as AVRO location '/apps/data/hive/ex_users.tbl';

When files are saved as Avro formatted files, information from the schema is built into them, in plain text, and is parse-able for programs like hive and others to interpret the rest of the file.

For this to work, the tool has to rely on Avro. Look at this hive table definition - 

With the clause "Stored as Avro" we are basically telling hive to match the columns defined in hive with columns in Avro fi

les in that hdfs location and use whatever columns map to hive definition.

  

Avro is compressible, split-able and stores data in binary format, providing some additional features.

When this Avro formatted file is exposed to programs like hive, they don’t have to worry about changes in schema, since they rely on the schema information in the file header. If the hive / R schema asks for 5 columns, but the file only has 3 of them (with names as matching criteria and not order/position of columns), those matching columns are project to the consuming tool, and the rest is ignored. 

Similarly, if the tool asks for data that is not present in a certain data file (remember our example v2 file with additional column as against v1), the column that is not present is reported as null.

Now, look at the following data file (new avro schema - after source system decided to change)

{"namespace": "myExample.avro", "type": "record", "name": "User", "fields": [     {"name": "name", "type": "string"},     {"name": "age",  "type": ["int", "null"]},       {"name": "salary",  "type": ["float", "null"]},     {"name": "address", "type": ["string", "null"]} ]}

And the corresponding hive table definition - 

create external  table ex_users (name     String ,  age     int , address     String, salary float)stored as AVROlocation '/apps/data/hive/ex_users.tbl';

For this table definition, its not a problem to query and report a data file in old schema (v1) wherein the column salary is not present. it would simply know from the avro schema definition in that header that the file doenst have that column, and it would gladly report that as null.

On the other hand, in the new structure has a different order of columns, which is also fine for hive/avro combination since the schema definition helps match up the columns and report the right content in right columns. 

Similar analogies can be derived for other data consumers who need to interpret the schemas for data files on hdfs.

In comparison to some other storage formats, we do lose some points on the performance scale, but for the use case, where schema is changing and we want to be able to handle the changes with little or no effort, this is a better fit than many others.

Hadoop Meetup on the sidelines of Strata Hadoop Conference - Part 2

Read part 1 of this here

Day 2 of the meetup was equally exciting, if not better.  Lined up were talks from Qubit and Google, William Hill (a surprise for me - more later on that) and then PostCodeAnywhere, all very exciting from the synopsis.

Google & Qubit showcased basically a stream processing engine, with pluggable components, many of them can be written in different technologies and programming languages.

Of course Google Cloud Data flow is much more than just a stream processing engine, however, for real time data ingestion perspective, that feature is pretty significant.  

A completely managed system, it woks on the publish-subscribe (pub-sub) model.  As Reza put it, “pub-sub is not just data delivery mechanism, its used as a glue to hold the complete system together”.  Pluggable components is another differentiator for Google’s offering, in today’s demo they showcased bigtable as one of the consumers at the end.

From my own knowledge of stream processing, which is not significant in anyway, i could relate to many similarities with IBM’s info sphere streams and some with apache kafka.  However, a question around comparisons with these sites remained unanswered from Google (though in very good spirit, in a chat with the speaker Reza later on, it came out as more of a philosophical question avoidance than anything else).

The william hill talk (by Peter Morgan, their head of engineering), was a genuine surprise, at least for me.  Perhaps due to my ignorance, due to which i didn't realize, their systems are far more sophisticated and load bearing than I would have imagined.  As an example, they process 160TB of data through their systems on a daily basis.

Including many complexities managed through their system are their main components, the betting engine, the settlement engine among others. 

William Hill supports an open API as well, enabling app developers to pick up data elements and innovate. However, for obvious reasons, very limited data is thrown open in the public domain.  Would that be a deterrent for app developers ? not having enough data ?   For example, if i would want to report in an app, who’s betting on a  certain game, cross referenced with geo location data .. I cant do that, since William hill doesn't publish demographic data.  I personally feel alright with it, there are possibilities that many of those data elements can be used in ways to influence the betting system itself, becoming counter-productive.

I would imagine their IT systems to be one of the top notch systems around the place, to be able to manage such data volumes, with such speeds and accuracy. Commendable job.  I would probably write exclusively on their architecture once i get my hands on the presentation slides (couple of days may be).

The talk from PostCodeAnywhere was more educative to me, personally.  Got to understand a bit about Markov Models, chains and how they can be used for machine Learning.  Very interesting stuff there too.

Apache Spark is being seen more and more as the tool to be perform analytics on the fly, specially on large volumes of data.  It would be very interesting to see how R and python analytical capabilities compare with what spark offers.

Speaking to another attendee today, it came out the people prefer to use R more and more for massaging and cleansing purposes, however, its not seen as fit for heavy lifting required for performing real analytic and/or predictive pieces. For these areas, people still prefer to use Python.

IBM’s bigR is a possible contender for the job, where they talk about having optimised R for a hadoop cluster and have enabled it to work on top of hdfs.  However, bigR is not open source and that could be its biggest challenge in adoption.

Trying to become a Data warehousing architect... !!!

If someone wants to become a DW Architect, there is a lot of knowledge required.  Some of the different perspectives (one from MS not specifically for DW, rather enterprise) and another on DW specifically..

Although I think its strange already that someone has to think about the kind of questions being asked in such interview, there are things posted on these lines.  i believe its more of a discussion on thought process and approach rather than question-answer session in such an interview. Still, presenting an excerpt, outlining some of the interesting topics that might turn up in a discussion for this kind of position.

1. Describe advantages of the CIF architecture versus the bus architecture with conformed dimensions. Which would fit best in our environment given [some parameters they give you] and why
2. Describe snow-flaking
3. Describe fact-less fact tables.
4. Draw a star schema of our business
5. Describe common optimization techniques applied at the data model level
6. How do you handle data rejects in a warehouse architecture?
7. Describe common techniques for loading from the staging area to the warehouse when you only have a small window.
8. How do you load type 1 dimensions
9. How do you load type 2 dimensions, and how would you load it given our [insert business particularity]
10. How would you model unbalanced hierarchies
11. How would you model cyclic relations
12. What major elements would you include in an audit model?
13. How would you implement trace-ability ?

Questions picked up from http://arstechnica.com/civis/viewtopic.php?f=20&t=320772

One of the Microsoft guys wrote about possible feathers in the cap of an Enterprise Architect -  http://blogs.msdn.com/b/nickmalik/archive/2006/09/09/ea-interviews.aspx