#java #scala #learn #fun

poor man's ETL using apache camel

Tue, 15 Mar 2011 03:28:00 -0700

If you haven't read about apache camel; here is the short introduction. You can also get free chapter of "Camel in Action" book. Buy it if you are working on enterprise integration project and using java/scala.

Problem: OLTP system's database schema is not good for generating reports and data mining /analytics. Moving data from OLTP database to reporting schema is tricky and not straightforward. You need to

get rid of unnecessary data
figure out contextual grouping of data
take care of correctness of data
shouldn't hamper performance of source as its also used for OLTP
graceful recovery on failure of such transformation

ETL is appropriate term for such scenarios and there are many toos available in market but they are costly(considering learning curve vs productivity),cumbersome and bloated with unnecessary features. Ok enough of rant. Lets solve the problem.

Narrowing down the problem: Am assuming source and destination are mysql databases. And this is common scenario. I'll write down how it can be extended to other source/targets.

What one needs:

ETL Job is single job which can be repeated if it fails. It copies only the new data when it runs. It rollbacks all changes on failure
Scheduling of such a job
Transformation/Processing is required on source data before it is stored at destination.

Before going further read this article. So basically apache camel is routing engine. You can route information from source to destination through some processor(optional).

So here is the sample route

 @Override
    protected RouteBuilder createRouteBuilder()
    {
        return new SpringRouteBuilder()
        {
            public void configure()
            {
                from("direct:input").to("bean:inputSql").to("jdbc:sourceDataSource").process(new ResultSetProcessor()).split(body()).to(
                        "jpa:com.abc.entity.MyJPAEntity");
            }
        };
    }

In nutshell 1. am firing some query on source database; 2.processing its results(using camel processor; basically it is just a pojo with utility method) 3. saving it in destination database (using jpa)

inputSql is a spring bean registered in camel-context xml file. camel-context is regular spring beans xml file having entries for all required beans.e.g. sourceDataSource,destinationDataSource,inputSql One can create springCamelContext by passing path to camel-context.xml.
Processor does the role of Message Translator
Since we are using jpa in our example; we'll need persistence xml with all required entities declared for each persistence unit. You can refer above mentioned ETL article from camel website.

Now details: InputSql bean and jpa is pretty straightforward. Camel's DSL is so powerful you need not do anything other than providing your sourceQuery,destination entity, correct translation and it does its job really well under the hood.

Below is the way one can use polymorphism and inheritence for creating multiple processors as and when required.

Common processor when you are writing etl jobs for data transformation from source database to target database(jdbc-->process-->jpa) as below. You can see its receiving array of resultsets for processing.

public abstract class CamelProcessor<T> implements Processor
{
    protected void process(Exchange exchange, ResultMapper<T> mapper) throws Exception
    {
        try
        {
            ArrayList<T> payloads = new ArrayList<T>();
            ArrayList<HashMap<String, Object>> data = exchange.getIn().getBody(ArrayList.class);
            if (data != null)
            {
                for (HashMap<String, Object> obj : data)
                {
                    payloads.add(mapper.valueOf(obj));
                }
            }
            exchange.getIn().setBody(payloads);
        }
        catch (Exception e)
        {
            // TODO common error handling
            e.printStackTrace();
        }
    }

}

Specific Message Translator aka Processor can be written as below.Array of resultsets can be mapped to jpa entity by passing custom rowmapper in processor implementation. See next code snippet for this.

public class ResultSeProcessor extends CamelProcessor<MyJPAEntity>
{
    public void process(Exchange exchange) throws Exception
    {
        process(exchange, getRowMapper());
    }

    private static ResultMapper<MyJPAEntity> getRowMapper()
    {
        ResultMapper<MyJPAEntity> mapper = new ResultMapper<MyJPAEntity>()
        {
            public MyJPAEntityvalueOf(HashMap<String, Object> data)
            {
                MyJPAEntity txn = new MyJPAEntity();
                // from resultset set the values to jpa entity and return it 
                //txn.setXXX((String)data.get("xxx"))
                return txn ;
            }
        };
        return mapper;
    }
}

Workflow of custom ETL

Write a quartz job and pass the spring-config(camel-context.xml) path to it.
Create a camel context
optional Enable tracing for debug information
Get the source query from quatz job datamap
optional: Modify the query prior to job by appending where clause info so that we select only new data from source
Add route to camel context
Create a producer template
Pass the source query(could be updated in preETL hook mentioned above) to producer template
Start the camel context
optional postETL job hook

This workflow suffices because

source query is optimized and returns me only the new rows( using preETL hook which add where clause to select new rows)
on any exception (camel/quartz/logic/runtime) destination remains untouched. So on failure I can restart the job and it'll copy new rows only
Can add batch-job behavior by adding LIMIT clause in where condition of source-query. It will copy only N rows in on run and re-run if there are still some new data at the source. Running such job in batch of 5k/10k is really performant.
How I use destination data for reporting is totally decoupled to my ETL jobs application. I can run a portal using jasper reports/BIRT or simply flot for charting purpose.

note: This post is work in progress.

Permalink | Leave a comment »

Scala Map Basics

Sun, 06 Mar 2011 06:44:41 -0800

World Cup Cricket fever is the rage! So added small gist. It shows you how much readable this code is compared to java collections code.

object MapTests extends Application {
  override def main(args: Array[String]) {
    //reference http://www.scala-lang.org/api/current/scala/collection/immutable/Map.html
    val worldCupWinners = Map(1975 -> "West Indies",
      1979 -> "West Indies",
      1983 -> "India",
      1987 -> "Australia",
      1992 -> "Pakistan",
      1996 -> "Sri Lanka",
      1999 -> "Australia",
      2003 -> "Australia",
      2007 -> "Australia")
    val evenYears = worldCupWinners filterKeys (_ % 2 == 0)
    println("evenYears: " + evenYears)

    val ausTotalWins = worldCupWinners.filter {
      element =>
        val (year, country) = element
        country contains "Aus"
    }
    println("ausTotalWins: " + ausTotalWins)

    val countAfter2k = ausTotalWins.filterKeys(_ >= 2000).size
    val countBefore2k = ausTotalWins.filterKeys(_ < 2000).size
    println("countAfter2k:" + countAfter2k)
    println("countBefore2k:" + countBefore2k)
    // some and none goodies
    println(worldCupWinners.get(1983))
    println(worldCupWinners.get(2000))

    //scala map is immutable so need to use update for 2011 winner as below
    worldCupWinners.updated(2011, "Winner of India vs Pakistan")
    // its immutable so it wont add the new value
    println(worldCupWinners.mkString(", "))
    // another try
    val updWorldCupWinners = worldCupWinners.updated(2011, "Winner of India vs Pakistan")
    println(updWorldCupWinners.mkString(", "))

    //head vs tail
    println("head :" + worldCupWinners.head)
    println("tail :" + worldCupWinners.tail)

  }
}

Permalink | Leave a comment »

Adding audit functionality in spring mvc web applicaion

Thu, 03 Mar 2011 00:09:00 -0800

Many a times one needs to know "who" accessed some critical functionality of your web app at "what" time. Keeping record of such information is called as Auditing. There are lot of things to consider before you implement full-fledged audit-trails in your web application.

Now for a simpler scenarios you can get it done with simple annotation wherever you need "audit" record in your code as shown below. No more code!

    @Auditable(operation = "Save/Update Login Configuration")
    public void save(Config loginConfig)
    {
        logger.debug("saving Login Config for terminalId=" + loginConfig.getTerminalId());
        getSession().merge(loginConfig);
        getSession().flush();
        return;
    }

Now lets see how it works under the hood. When a method annotated is accessed it transparently saves the audit-information. This is good use-case for AOP and implemented using aspect as below

@Configurable
@Aspect
public class LoggingInterceptor
{
    Log log = LogFactory.getLog(LoggingInterceptor.class);
    @Autowired
    private AuditData auditData;

    @Around("execution(* *(..)) && @annotation(com.xyz.portal.util.audit.Auditable)")
    public Object profile(ProceedingJoinPoint pjp) throws Throwable
    {
        final String currentUser = SecurityContextHolder.getContext().getAuthentication().getName();
        try
        {
            Object resultValue = pjp.proceed();
            logAudit(currentUser, pjp);
            return resultValue;
        }
        finally
        {
        }
    }

    private void logAudit(String currentUser, ProceedingJoinPoint pjp)
    {
        AuditInformation auditInformation = new AuditInformation();
        StringBuffer sbfDetails = new StringBuffer();
        auditInformation.setDate(new Date());
        auditInformation.setUserName(currentUser);
        auditInformation.setOperation(this.getMethod(pjp).getAnnotation(Auditable.class).operation());
        for (Object o : pjp.getArgs())
        {
            if (o != null)
            {
                sbfDetails.append(" PARAMETER " + o.getClass().getSimpleName() + " : " + o.toString());
            }
        }
        auditInformation.setMethodName(pjp.getSourceLocation().getWithinType().getSimpleName() + " : " + this.getMethod(pjp).getName());
        auditData.save(auditInformation);
    }

    protected Method getMethod(JoinPoint jp)
    {
        Method invoked = null;
        try
        {
            MethodSignature met = (MethodSignature)jp.getSignature();
            invoked = jp.getSourceLocation().getWithinType().getMethod(met.getMethod().getName(), met.getMethod().getParameterTypes());
        }
        finally
        {
            return invoked;
        }
    }

}

Also the Auditable annotation as below. You can check the usage in the very first snippet of this post.

@Documented
@Retention(value = RetentionPolicy.RUNTIME)
@Target(value ={ ElementType.METHOD })
public @interface Auditable
{
    String operation();
}

Permalink | Leave a comment »

Scala Traits : Decorator pattern

Sat, 26 Feb 2011 22:44:00 -0800

Here is the code from the pragmatic programmer scala book to understand how decorator patter is implemented in scala.

package com.example.chapter7.traits

class DecoratorTraits

abstract class Check {
  def check():String ="Checked application details"
}

trait employmentCheck extends Check {
  override def check():String="Checked employment details " +super.check()
}

trait creditCheck extends Check {
  override def check():String="Checked credit details " +super.check()
}

trait crimeCheck extends Check {
  override def check():String="Checked crime details " +super.check()
}

object DecoratorTraits extends Application
{
  override def main(args:Array[String])
  {
    println("======================================================")
    println(notes())
    println("======================================================")
    println("Logic execution strace below")
    println("======================================================")
    val newEmpCheck = new Check with employmentCheck with crimeCheck
    val newTenantCheck = new Check with employmentCheck with crimeCheck with creditCheck
    println(newEmpCheck check)
    println(newTenantCheck check)
  }

  def notes() =
    {
      """
      Wikipedia says "The decorator pattern can be used to make it possible to extend (decorate) the functionality of a
      certain object at runtime, independently of other instances of the same class, provided some groundwork is done at design time."

      In example above multiple checks are added e.g. employment,credit,crime.
      newEmpCheck though uses decorator pattern you can see at design time we need to add super.check() in each trait to achieve
      expected result.

     Points to Note:
      1. Also each trait extends abstract class so these traits can ONLY be mixed with the class which extends same abstract class.
      2. Also if abstract class Check has some concrete utility methods those can be utilized in trait's concrete check implementation.
      3. straight from pragprog book to understand execution( late method bnding for this particular example )

      "The rightmost trait picked up the call to check(). It then, upon the call to super.check(), passed the call over to the trait
      on its left. The leftmost traits invoked the check() on the actual instance."

      """
    }
}

Permalink | Leave a comment »

Scala Traits : basics

Fri, 25 Feb 2011 22:04:00 -0800

I've been reading scala whenever I get a free time. Here is the simple explaination of 'traits in scala'. You can run this class from scala commandline or from ide. Also keep checking my scalaLearning github repository for more examples.

class UsingTraits

//refer notes 1,2,3

trait Friend{
  val name:String
  def listen()=println("Your friend "+name+" is listening")
}

class Human(val name:String) extends Friend

class Man(override val name:String) extends Human(name)

class Woman(override val name:String) extends Human(name)

// refer notes 3 and 4
class Animal

class Dog(val name:String) extends Animal with Friend


object UsingTraits extends Application{
  override def main(args:Array[String])
  {
    println("======================================================")
    println(traitNotes)
    println("======================================================")
    println("Logic execution strace below")
    println("======================================================")
    val ketan = new Human("ketan")
    ketan.listen
    val dog = new Dog("pluto")
    dog.listen


  }

  def traitNotes() =
  {
"""
  1. A trait is a behavior that can be mixed into or assimilated into class hierarchy.
  2. A trait is like an interface with partial implementation
  3. The vals and vars you define and initialize in a trait get internally implemented in the classes that mix the trait in.
  Any vals and vars defined but not initialized are considered abstract, and the classes that mix in these traits are
  required to implement them
  4. When class is not extending any other class; one can mix trait using "extends"  keyword; otherwise use "with" keyword.
  Check above example. 'Dog extends Animal with Friend'
  5. "Selective mixins" : Not all pets are friendly. So we can mix trait 'Friend' ONLY with the instance of 'friendly' pet.
   e.g. Lets assume cat with the name 'ruby' is friendly. so add 'Friend' trait for cat instance with
   name 'ruby' It can be done as  => new Cat('ruby') with Friend
"""

  }

}

Permalink | Leave a comment »

Sahyadri fort markers on google map

Sun, 21 Nov 2010 23:02:00 -0800

I've ported Sahyadri fort markers google sites page to github-pages. You can refer my code on github (https://github.com/ketankhairnar/map). This is very old static page though; I'll need to move it to newest available google maps library.

Permalink | Leave a comment »

Roman Number Conversion

Sat, 23 Oct 2010 04:24:00 -0700

I got some simple programming problems which help you to do some practice of TDD. I am posting my first version of solution of Roman number conversion

package dojo1_Roman;

public class RomanConverter
{

    public enum Digit
    {
        UNIT, DECIMAL, HUNDRED, THOUSAND
    };

    private static String[] units =
        { "I", "II", "III", "IV", "V", "VI", "VII", "VIII", "IX", "X" };
    private static String[] tens =
        { "X", "XX", "XXX", "XL", "L", "LX", "LXX", "LXXX", "XC", "C" };
    private static String[] hundreds =
        { "C", "CC", "CCC", "CD", "D", "DC", "DCC", "DCCC", "DM", "M" };
    private static String[] thousands =
        { "M", "MM", "MMM", "IV", "V", "VI", "VII", "VIII", "IX", "X" };// can't display bar on numbers higher than 3K

    public static int unitCount = 0;
    public static int tenCount = 0;
    public static int hundredCount = 0;
    public static int thousandCount = 0;


    public static void processInput(int input) throws Exception
    {
        try
        {
            System.out.println("input is : " + input);
            thousandCount = input / 1000;
            hundredCount = input / 100 - (thousandCount * 10);
            tenCount = input / 10 - (thousandCount * 100 + hundredCount * 10);
            int minus = thousandCount * 1000 + hundredCount * 100 + tenCount * 10;
            unitCount = input - minus;
            System.out.println("thousand: " + thousandCount);
            System.out.println("hundred: " + hundredCount);
            System.out.println("ten: " + tenCount);
            System.out.println("unit: " + unitCount);
        }
        catch (Exception e)
        {
            if (e instanceof NumberFormatException)
            {
                System.out.println("Improper Input : Not a Number");
            }
            else
            {
                System.out.println("Improper Input : No argument passed");
            }
            throw e;
        }
    }

    public static String getDisplayString(int input) throws Exception
    {
        StringBuffer sbfResult = new StringBuffer();
        processInput(input);
        append(sbfResult, Digit.THOUSAND);
        append(sbfResult, Digit.HUNDRED);
        append(sbfResult, Digit.DECIMAL);
        append(sbfResult, Digit.UNIT);
        return sbfResult.toString();
    }

    private static void append(StringBuffer sbfResult, Digit digit)
    {
        switch (digit)
        {
            case UNIT:
                sbfResult.append(units[unitCount - 1]);
                break;
            case DECIMAL:
                sbfResult.append(tens[tenCount - 1]);
                break;
            case HUNDRED:
                sbfResult.append(hundreds[hundredCount - 1]);
                break;
            case THOUSAND:
                sbfResult.append(thousands[thousandCount - 1]);
                break;
        }
    }

}

Permalink | Leave a comment »

Gems from "The Mythical Man-Month" part 1

Fri, 30 Jul 2010 21:52:00 -0700

The Tar Pit

The Joys of the craft
- Sheer joy of making things
- pleasure of making things which are useful to other people
- fascination of fashioning complex puzzles
- joy of always learning
Woes of craft
- must perform perfectly
- others provide objectives/resources/information on which you may not have control
- dependence on other people's programs(hence side-effects of their maldesign,incompleteness and flawed implementation)
- tedious labor for ironing out working product( though design of grand things is fun)
- wrong estimation on part of debugging/testing
- obsoleteness of product when it is complete

Challenge is to avoid obsolescence of implementation with available resources. (sidenote: Whoever says programming is easy has never porgrammed a full system )

Mythical Man-Month

Optimism
- Perhaps, it is merely that computers are young, programmers are younger, and the young are always optimists.
- For the human makers of things, the incompleteness and incosistencies of our ideas become clear only during implementation
The Man-Month
- Cost does indeed vary as the product of the number of men and the number of months. Progress does not.
Gutless estimating
- We need to develop and publicize productivity figures, bug-incidence figures, estimating rules and so on. The whole profession can only benfit from sharing such data.
- Adding manpower to later software project makes it later

The Surgical Team

The Problem
- Sheer no of minds to be coordinated affects the cost of the effort, for a major part of the cost is communication and correcting the ill-effects of miscommunication(system debugging)
- Dilemma: few good minds for efficiency and conceptual integrity vs. timely appearance of large systems product
Proposal
- Transformation of programming "from private art to public practice"
How It works
- No differences of interest
- Unilateral settlement of differences of judgement
- works because 1. lack of division of problem 2. superior-subordinate relationship

Aristocracy,Democracy and System Design

Conceptual integrity
- Purpose = ease of use = function / conceptual complexity ; this is ultimate test of system design
Aristocracy and Democracy
- Where architecture tells what happens; implementation tells how it is made to happen

Permalink | Leave a comment »

Books to finish till July 31

Wed, 30 Jun 2010 23:49:00 -0700

Programming and related books:

rolling out releases

Sun, 07 Mar 2010 01:12:00 -0800

I have been part of rolling out 1.0 of our application to end customer and this has been fulfilling experience and it brought me lot of shameful moments. Here is the summary.

Manage Change : This is I would mark the number 1 thing every developer should be really good at. This means you have to really look farther and figure out when this thing changes because of business requirements; how are you going to manage that.
Reject complexities : If you think something is complex; reject that option immediately. 99% there is easy way out. Use online forums/communities often and align your application to what seems to be most recommended approach. Business is different. Technical solutions are more rigorously ironed out. One needs to smart enough to seek help from right people/communities. And in this era I guess that's what people call crowd-sourcing.
Technical Debt: I am already buried deep. Trust none or delegate completely some parts of your application to your colleagues. Each person delivers artifacts their own way and that adds to technical debt of team. More rigid measures you have before it gets added to your repository; better. Cleaning mess later is itself a big task. Also everybody on your team is dev+qa combo. There is no other option.
Deployment : This is my favorite theme for last couple of weeks. Rinse and repeat. Outlining steps to deploy your software is challenging job if you have multiple systems and multiple point of failures. If developers don't get involved in it; they are going to screw up things in future too. Once you figured out smooth procedure; get it repeated multiple times on your test environment by somebody else on your team. That removes some of the assumptions. Your assumptions and point of failures should be on some whiteboard and make sure everybody understands it completely. Everybody I mean even you customer support team and your managers.
Production Issues : Things go wrong. Fixing fast and dirty is the last resort and your stakeholders should approve it. Being a dev/dev manager its not your responsibility to take that decision. Your task is to be ready with options and trade offs for each of them in plain honest way. If you screw things here for short term a** covering; you have already taken few strides back and had set the wrong example. More people are going to follow that in future. Wary of decisions taken in cave.

Permalink | Leave a comment »

Build Mania

Tue, 02 Mar 2010 04:12:00 -0800

I have worked on small projects with couple of developers as well as with team of 10-15 people. Automated build is very important in many ways.

Making sure you have build in place makes easier for other people to start working on things immediately
You manage dependencies pro-actively.
Multiple inter-dependent project builds can be automated easily.
Your system can grow easily when builds are in place and everybody saves time on this monotonous activity of setting up workspace.

These are no longer "good to have" but prerequisite of any new java project these days.
I have primarily used ant for building java projects and that has worked really well so far. In recent past I had tried to mavenize all ant projects but that exercise was so cumbersome that I left it half way. The main problem with the maven is I have to do things the maven way. This is not the case with "ant". I can have my build script the way I want it and hence its easy for me to setup. In order to setup maven projects I have to use archetype and understand maven build lifecycle i.e. compile,build,test,package,deploy, install etc etc.

Maven is really helpful for projects where collaboration with lot of developers is required. You just need to share the pom.xml and source and everybody is ready to go. This works really well for small project or multiple independent projects. But I had a tough time in setting up pom structure for projects where there was dependency hierarchy. Hence the build hierarchy. Setting up build for such projects was pain. I know the real reason. My limited knowledge and inertia of moving from working ant system to maven made me biased towards ant.

We are moving to git and revamping release management as a side-effect of the whole exercise. Being a small team and periodic releases never made me think that we need to change the release management. Going forward we'll be having rapid releases and supporting multiple features/customizations would need lot of branching and merging. Quick reading and listening to some demos helped me to understand how good git fits in busy developer's daily workflow. I am really excited to learn this. As part of the build we release lot of artifacts i.e. jar/war files and storing them in git repository is not a good idea. So we'll be using git for source revision and nexus/artifactory as artifact repository. I've been reading about ivy too.

I will be sharing my learning of this exercise later.

Permalink | Leave a comment »