Blaine's Blog

tech_ramblings.random()

Phase 3: Making the decision to use Hadoop + Azkaban + Appworx

· · Leave a comment

Phase 3 of 7!  Here we go!

This is the start of high level technical discussions.  This is phase 3 of 7 for this project (see diagram below).

This phase is broken up into 3 sections:

  • Technical Architecture Design
    Goal is to define the architecture of the new system. What servers are involved to store data?  What servers are involved to consume data?  What servers are involved to manage data?
  • Dimensional Modeling
    Goal is to define the fact table and dimension tables.
  • Analytical Application Specification
    Goal is to define the reporting layer for the new datamart.

Technical Architecture Design

The database:

One of the goals of this project was to migrate to a database that was more commonly used at the new company, in this case LinkedIn/Microsoft. As such we moved from SQL Server to Hadoop. It is worth noting that other databases are used at LinkedIn, such as Teradata and Oracle, but the data team uses Hadoop and this new datamart would sit on Hadoop. SQL Server, Teradata & Oracle are more reasonable solutions to hosting a star schema but the direction of my department was to move as much as possible to Hadoop. Not easy but where there is a will, there is a way.

I made a conscious decision to use hive instead of pig. Most of my peers were in the pig camp. SQL Server and hive are closely related to T-SQL, so making the decision easy for me. In fact, a month into this project I had several python scripts that could port code for me. After spending way too much time fixing things by hand, I put a python script together to parse a SQL Server file and output a hive hql file, complete with HDFS locations, formats, CTAS operations, type definitions and formatting. Pretty silly to have to go that far BUT migrating 10k+ lines of code was a ridiculous chore. I was constantly doing refactoring, and having the ability to easily move from one database to another was _amazingly_ valuable. I had operations joining 6+ tables and Hadoop chokes on those type of operations. If I found a bug in hive, I could port my code to SQL Server, debug it, then port it back to hive. Pretty awesome…pretty lame having to resort to that…but pretty awesome too.

Data Ingestion:

The old tools for data ingestion for this project were added haphazardly. As a result, a Frankenstein system was built using a combination of Informatica, Powershell and SSIS. This was done for time to market purposes. The powers that be wanted results and engineers cut corners to meet deadlines. It happens, nothing wrong with that, but I had to clean it up. There was no real desire to keep any of the old tech so all data ingestion would be refactored. The new tech stack was built using tools common at LinkedIn. Azkaban is an open source tool developed by LinkedIn. Azkaban was already supported, which was good. It did not include APIs to communicate with some third party billing systems, which was bad. Not having those APIs meant scope creep. We needed to pull an engineer off another project to solve this API problem. Ultimately, the project got what it needed, all source data delivered to HDFS, but the cost to get there was higher than expected.

Scheduling and Dependency Management:

The old system was using a combination of Informatica and SSIS to manage processes. It also used ‘semaphore’ files to communicate from one system to another. If a process upstream were to finish, it would create a file. A process downstream would wait for that file, kick off when it was found, then delete the file when finished. May sound strange but it worked. This clumsy system was a feature of not having the right tools available for the right job. The solution was to embrace the tech stack at LinkedIn. Appworx is a GUI xterm tool used to manage processes.  Thus, Appworx was used to stand up the new datamart. (More on this later.  This datamart got a little crazy with Appworx dependencies.)

Dimensional Modeling:

Another goal of this project was to create a new star schema. The old system was more or less a denormalized flat table with every attribute and measure imaginable in one record (yuck!). Putting that data into a dimensional model was done by analyzing the target data and carefully dissecting how it was created. Ultimately you want to create as few dimension as possible, but enough such that everything is in third normal form. Anything less is sloppy. With those requirements in mind, and given enough time to reverse engineer code, the output was simple. My documentation and use of lucidchart for diagrams was extensive. It allowed me to look at attributes from a high level, related ETLs, source systems, drill down to specific behavior, then debate design decisions with peers, the team, and myself. This process was also quite time consuming.

Another feature worth noting is that every dimension would be Type 1. Creating type 2 SCDs in SQL Server, Teradata or Oracle is trivial. Doing it in hive requires some overhead. Given you can use MR functions (some were available) to create surrogate keys, using anything other than natural keys was overkill for my needs. I was already reverse engineering 10k+ lines of code, it was taking much longer than expected, so minimizing any cost was beneficial. Type 2 SCDs were one of those things that was nice to have but didn’t matter for this datamart. I was creating an event fact table, most attributes were immutable, thus natural keys were just fine.  If an aggregate fact table were created on top of my event fact table then surely surrogate keys would be introduced.  But that’s another project for another time.

Analytical Application Specification:

The reporting layer ultimately drives what it is you do. In my case my original reporting layer was QlikView and my new reporting layer would be some proprietary internal tool at LinkedIn.

Side story: An interesting feature of this project is QlikView has its own columnar database. This allows QlikView to offer low response times to users (good). It also increases the cost to ingest data from a normalized data set into QlikView (bad). The old datamart was created to support QlikView and design decisions were made specifically to embrace QlikView (very bad). In doing so, many typical design decisions were disregarded. First & second normal form target tables were created in the old database (huh?). One goal of this project would to put everything into a pretty event table star schema with convenient third normal form tables. This would serve as the foundation for reporting data such that aggregate tables could be built on top.

Conclusion:

For this project, I asked the following questions and the answers determined the path forward:

  • How will data be consumed? Azkaban
  • How will dependencies be managed? Appworx
  • How will transformations be applied to the data? Hadoop/hive
  • How will data be stored? HDFS

The technology decisions were largely made for me because my technical options were limited. If the new company was predominantly using X technology, then so would I.

=> The event fact table determined the grain of my fact.
  => The start schema determined how the event fact table would be managed.
    => Attributes in dimensions were immutable.
      => Azkaban determined how data would flow between servers.
        => Hadoop determined where the datamart would be kept.
          => Appworx determined how it would be glued together.

In retrospect I can say phase 3 of this project organized itself. At the time, it wasn’t so simple.  It was a lot of asking myself if something would work, validating with tests, then moving to the next step. I wasted a few days testing presto, which is an example of testing that did not work out in my favor.  I had to quickly educate myself on new technologies in order to validate my approach to solving these architecture problems.

Phase 2: Hadoop Migration Business Requirements Definitions

· · Leave a comment

Phase 2: Hadoop Migration Business Requirements Definitions

The purpose of this phase is to come to an agreement on defining business requirements. This process can be difficult if the output is not clearly defined. This blog post is related to a large Hadoop migration that I did at Microsoft so I will use that project as an example.

The goal of this project was to move from one technology stack to another. Whatever target tables were being created in the original system should be duplicated in the new system. At least that was the goal. For a large migration, this becomes non-trivial.

What makes defining business requirements difficult?

  • Owners do not exist for business definitions
    For example, consider a situation where someone defined a metric, did a poor job documenting the metric, then left the company. These are difficult problems to solve.
  • Business definitions not exist
    For example, most engineers at small companies will be happy saying “the documentation is in the code.” This is unfortunate and something overlooked at small companies because code is less likely to be shared. In large corporations, this becomes an issue because you’re more likely to work in teams and share code. As such, you’re more likely to find good documentation at large companies. My solution to that problem was to get a consensus on ambiguous definitions and make good educated guesses.
  • The business logic in the new system is different from the new system.
    For example, what happens when you find a bug in the original system? Can you fix the bug in the original system? If not, can you fix the bug in the new system? If you do fix the bug in the new system, how will you show it has been fixed and how will you prepare analysts for the new data? My solution to that problem was to provide analysis on why the original data was wrong, fix it in the new system and show how much the data changed.

To illustrate the problem of business logic changing, consider these two rough ER diagrams below. The diagram on the left shows reporting tables and sources for those reporting tables. The diagram on the right shows the new system. The new system was distinctly different from the old system. Business logic was drastically refactored. When I was at the business requirements phase of this project, I looked at these two diagrams and told myself it was technically possible to achieve this outcome. What I did not do was accurately estimate how hard it would be to achieve this outcome. Underestimating the effort to do the refactoring resulted in a lot of back and forth between myself and manager to convince them that the cost was worth it.

Before After

Advice for defining business requirements

  • Prioritize which metrics and reports are important
    If time becomes a problem, then you will want to deliver the most important reports and metrics first.
  • The more refactoring you do, the more you should prepare for scope creep.
    This is particularly true for business logic. Refactoring business logic becomes a tedious process that leads to you digging into the weeds of data and processes. It leads to scope creep and unexpected meetings. Solving complicated problems almost always take more time than you originally thought.
  • Don’t be afraid to advertise expensive costs.
    Complex problems should not be undersold. It is better for everyone to know up front that an easy problem is actually difficult to solve.

Conclusion

Defining business requirements is a necessary evil that may save you some headaches down the road. Do not be afraid to oversell the cost of some problems and undersell your ability to fix them.  Most engineers probably hate underselling their abilities but the people your speaking to may not be as technically literate about the project as yourself.  If they hold you to unrealistic expectations then you will be paying for it later.  Complicated migration jobs generally have land mines and scope creep that you will not predict.

 

Migrating from SQL Server to Hadoop: Project Planning

· · 2 Comments

Phase 1: Project Planning

This is Phase 1 of 7 that I used to migrate a datamart to Hadoop.

You don’t know what you don’t know, so my first goal was to begin getting answers.  I honestly didn’t know what questions to ask at the time.  I knew this datamart created data, I had some rough idea how it happened, but I didn’t know nearly enough to migrate it from SQL Server to Hadoop.  I did my best to figure this out by using the following questions below…

Questions to ask when beginning a Hadoop migration project:

  • What systems exist in this project?
  • Within each system, where is the code located?
  • How complicated is the code?
  • What does the data pipeline look like?
  • What will the end state of this project look like?
  • How am I going to segment this project into multiple phases?

Here is a question I wish I had asked at the time:

  • What are my expectations for myself? What are expectations for my company?
  • What is the cost to migrate non T-SQL standard code?

What systems exist in this project?

  • In my case I was working with somewhat of a Frankenstein of a data pipeline and my first goal was to define the major components. To do that I held meeting with some of the original developers and picked their brains about this data pipeline. After those interviews I learned the following systems existed:
    • Informatica Cloud Services
      This system was deprecated. There were references to it in documentation, therefor I had to mention it.
    • A custom data integration tool
      This was built in PowerShell, used a SOAP API, was controlled with SSIS, and there was some SQL in it.
    • Informatica
      This was controlled with SSIS and semaphore files. Also interesting, was Informatia was being used a a dependency management tool. For this project, it was kicking off SQL Server code. It was not doing transformations in Informatica.
    • SQL Server
      This is where the business logic was located. This is where the meat of the project would be, but before then I would need to deconstruct everything else. If business logic were located elsewhere (which it was) then I would need to know about it.

Within each system, where is the code located?

  • Git?
    Most of the code I needed to migrate was located in git.  Good news for me.
  • Somewhere else?
    Some of the code I needed to migrate was not located in git.  Bad news for me.  Answering this question required setting up interviews with various developers.  Not a big deal but there were times where I thought I had all my code defined, only to learn in another meeting that there was new code I had not yet found.

How complicated is the code?

  • To do this I parsed the code I had to migrate.
    More specifically I used a python script to calculate the cost of each ETL. In my case there were ~35 ETLs that were written in SQL Server stored procedures. I previously created a python script to parse SQL so I leveraged that code.With some modifications, I could use this script to:

    • recursively look through directories for SQL Server stored procedures in git.
    • find the number lines in each ETL, number of lines of code, spaces and comments.
    • find which tables were used in each ETL.
    • for each ETL, find the number of JOIN operations. Is is a JOIN between 2 tables, 10 tables, etc?
    • for each ETL, define each join between each table and column.  I used this data to get a high level view of what I was looking at.  There were something like ~500 joins going on throughout the entire datamart.  As crazy as this may sound, it was understandable because the ETLs contained a lot of business logic.
  • Given some rough statistics on each ETL file, give it a cost to migrate.
    For each ETL file, I assigned a cost to migrate that file. I drastically underestimated that cost, but at the very least I had some idea which ETLs would be difficult to migrate. Knowing which ETLs were difficult allowed me to prioritize which ETLs to migrate first.  Easy ETLs were migrated first of course so I could demonstrate progress…the difficult ETLs were extremely complicated.  I used a fudge factor of difficult ETLs being 25-50% more difficult to migrate than easy ETLs.  That should have been 200-500%.  It is what it is.

What does the data pipeline look like?

  • Knowing the systems and the code involved, it was time to construct a rough data pipeline. In my case that looked like so:

  • There a couple interesting things that stand out at this point:
    • EDW Step 2 has dependencies on the original source data. Not good.  Part of my goal would be to eliminate these strange dependencies. By doing so, the data pipeline would be less confusing, easier to maintain, easier to restart, and reduce the number of reads. All good things.
    • The process for Downstream Tables looks suspiciously easy.  It’s just an icon right? That is deceiving.  I did not realize this till late in the project, but the downstream tables includes a lot of business logic.  This lead to a lot of scope creep. Underestimating the cost to manage this business logic added to the overall cost of the project.  Shit happens when you migrate legacy code here.  Best advice to mitigate this problem is to explain the technical complexity and hope it resonates when you adjust your deadlines.

What will the end state of this project look like?

  • This is a conversation I had with business owners of this data. I point this out because was a major requirement that was underserved. In my case, I was with a company that was going through acquisitions, people were coming and going, so finding out who owned my target data was not a simple process. If I were going to do this again, then I would pause to do more project planning. If you own this reporting table, are you aware of the business logic in it? Are you aware of the cost to migrate then support that logic? Are you aware that this business logic even exists?  You should put these questions to the report owners so they know the cost of the migration.

How am I going to segment this project into multiple phases?

Lastly, what questions did I wish I asked?

  • What are my expectations for myself? What are expectations for my company?
    In my case I was dealing with a large code base that was developed by people other than myself. I knew this but no one in my company knew this. The result was according to other people, I was spending an extraordinary amount of time reverse engineering code. This was settled months later in the project with difficult meetings where I tried to explain scope creep. It’s something I wish I had originally brought up.  Point being, try to do some diligence on your end to find these problems if you can.
  • What is the cost to migrate non T-SQL standard code?
    This is something that caught me by surprise as well. 90% of SQL Server can be moved to hive.  It’s that 10% that became a problem. Trying to reproduce SQL Server specific functions and operations like loops and cross joins difficult in hive. The solutions required creative SQL yoga. In retrospect I wish I could have defined the cost to fix these problems in advance.

Conclusions on project planning for a Hadoop migration

  • Define the major components of each system.
  • Define how the major components are tracked.
  • Create a data pipeline workflow.
  • Red flag those areas that may be difficult to migrate.

 

Next up, business requirements!  Every engineers favorite subject, next to documentation.

 

First steps to migrating a large datamart from SQL Server to hadoop

· · Leave a comment

The purpose of this post is to show the approach I took to migrating a large datamart from SQL Server to hadoop. My blog has been neglected over the past year because of this project. The year before I built a walking bike and got it into museums. This past year I refactored a ton of legacy code.  Moving 15k lines of a relational database code to hadoop/hive was interesting. The end result was actually nice because the data pipeline became easier to manage.

How time consuming was this project?

After Lynda.com was purchased by LinkedIn, it was decided to move the tech stack from Lynda to LinkedIn.  LinkedIn does have Teradata, which is much more comparable to SQL Server, but my peers were working on Hadoop/hive, therefor I migrated code to Hadoop/hive.  I once did one a year long Hadoop migration at Chegg.com where I moved the clickstream data pipeline from AsterData to Amazon EMR but this Lynda project was drastically different.  While the Chegg.com project was complicated by mimicking AsterData MPP processes in MapReduce, this Lynda project was complicated by having too much business logic in the datamart and clumsy legacy code.

Requirements:

  • Migrate from
    • Informatica
    • Informatica Cloud
    • Powershell
    • SSIS
    • SQL Agent
    • SQL Server Stored Procedures
  • Migrate to
    • hadoop / hive with Appworx as a scheduler
    • Gobblin with Azkaban as a scheduler
  • Apply coding standards.
  • Simplify business logic (in an ideal world, business logic should not exist in a DW but it is what it is).
  • Refactor 15,000+ lines of code from SQL Server stored procedures to hive.
  • Create a dimensional model where one previously did not exist
  • Recreate downstream aggregate tables.
  • Migrate data ingestion processes to Gobblin (https://github.com/apache/incubator-gobblin).

One of the major problems with this project was the original datamart was a Frankenstein of technology. It used Informatica to kick off SQL Server Stored Procedures. It used semaphore files to do dependency tracking. (A semaphore file is a file used to track completion of a process. Another process checks to see if the file exists, if so then it runs then deletes the semaphore file. Pretty clumsy but it is what it is.) There was powershell code with SQL embedded in it. Tables and key value pairs were renamed with no identifiable pattern. Imagine user_id being renamed to account_id, to source_account_id then to lynda_user_id. Pretty weird stuff.  To the credit of the original designers, it did server a valuable purpose and it did produce accurate data.

The solutions to this hadoop migration project I will describe in a series of posts. In additional making the process simple, coding standards were universally applied, redundancy was removed, and atomic restartable processes were added, among other things. Here is what was built in the end.  It’s much less confusing.

The start of the project was extremely confusing and I began by asking a few quesitons.

  • How do you deconstruct legacy code?
  • How do you estimate the cost to replace it?
  • How do you organize your time?

My answer to deconstructing legacy code was to do the following:

  • Define each server that exists
  • Define each application/database that exists
  • Define each process within each application/database
  • Define relationships between each process
  • Define how those relationships are tracked

Basically I had to understand the scope of the project.

My answer to estimating the cost to refactor legacy code was to be systematic. Break the code up into pieces that could be defined, calculate the complexity of the code, then create an estimate.

  • Defined all relationships between all ETL jobs
    • To do this I created a python script to parse SQL Server ETL files. The python script I had already created to show a colleague that the ETLs were complicated. Minor modification were done to make the script extensible. The python script would reformat each ETL file, parse JOIN conditions, then output various stats depending on how the python script was configured. It answered the following questions: How many ETLs existed? How many lines of code existed? For each ETL, how lines of blanks, comments and code existed? What tables were used? How many of each type of join was done? What tables were joined together? What columns were used to join each table? For each ETL, how many JOINS were done and what was their complexity?
  • Estimated the complexity of each ETL
  • Categorized the ETLs for different purposes
    For example, some ETLs had complicated business logic while others existed to transform data for dimensional modeling purposes.

If I were to do these estimates again then I would reconsider the following:

  • Anything that is not a T-SQL standard in your source or target database is a new cost.
  • Migrating from one data migration application to another application is a new cost.
  • Complicated business logic should be moved out of the data warehouse.
  • Cosmetic transformations should be moved out of the data warehouse.

My answer to how I organized my time was to look at old Kimball books to break the project up into phases. Here’s a bit about that process and I’ll go into details about what the phases mean in the next post.

Strandbeest bike in the Exploratorium and on Tosh.o

· · 2 Comments

Tosh.o picked up the Strandbeest Bike and put it in an episode, check it out here!

FWIW, I did not get permission to put this video up on youtube but Tosh.o didn’t seek permission to put our video up on their TV show so…we are even. The bike is also on display at the Exploratorium until mid September 2016.  The bike is a bamboo enclosure with a TV showing the above youtube clip of JP riding the bike.  #coolshit

strandbeest bike exploratorium

Strandbeest Bike at the Exploratorium