Apache Pig over Hadoop

In the last 3 blog posts we looked

   

• Hadoop and HDFS setup
     
• Hive installation and example
     
• Use Jasper Hive plugin and  generate Jasper reports

Pig is another such tool to expose
Structured language which run over hadoop and HDFS

In the blog below we will try
installing and running the same sample example , where will be
extracting out the the mobile phone number and name of the persons
who's id is less then equal to 10 . We will be writing pig scripts
for the same.

We start with installing pig.

• Download and install the  pig  debian package.
         
  • dpkg -i pig_0.10.0-1_i386.deb

   
• Start  the dfs server and mapred service
     
    
  • start-all.sh

• If pig has to run as local mode, then no need to perform above step

• Connect to pig  shell (we will connect here locally)
       
  • pig -x local

 
• Once we are  into the pig shell (Prompt name is grunt :) .. funny .. ) . We now  will load the file from local file system to HDFS using pig.
  
  
      
  • copyFromLocal export.csv person
        

• We will now  load the the data from HDFS to a pig relation (Similar to a table in  Hive)
     
  • person = LOAD 'export.csv' USING PigStorage(',') AS      (PERSON_ID:int,NAME:chararray,FIRST_NAME:chararray,LAST_NAME:chararray,MIDDLE_NAMES:chararray,TITLE:chararray,STREET_ADDRESS:chararray,CITY:chararray,COUNTRY:chararray,POST_CODE:chararray,HOME_PHONE:chararray,WORK_PHONE:chararray,MOBILE_PHONE:chararray,NI_NUMBER:chararray,CREDITLIMIT:chararray,CREDIT_CARD:chararray,CREDITCARD_START_DATE:chararray,CREDITCARD_END_DATE:chararray,CREDITCARD_CVC:chararray,DOB:chararray);

 
• We can see the
      output of the person using dump command
            
  • dump person;

• Run a script to filter out persons  who's person id is less then or equal to 10
         
  • top_ten=FILTER person BY person_id<=10

    Dump top_ten to see the output

   

• Run a script to extract out the  name and the mobile number of that list
  
  
  • mobile_numbers = FOREACH top_ten
            GENERATE NAME , MOBILE_PHONE;

    Dump the mobile_number to see the output

   This is the output we desire.

Develop Jasper report with Hive

In last 2 blog posts we learned

• Setup Hadoop and writing simple map-reduce jobs
• Setup hive and firing sql queries over it

In this blog we will use Jasper Report to generate a report which will use Hive as the data store.

We will generate report form the list of customers who have mobile phone

It is assumed that you have Jaspersoft iReport Designer pre installed.

• Start Hive in server mode so that we can connect it using jdbc client

• hive --service hiveserver

• Create table and load the data in the have table from the hive shell . This is done so that we can query it. Hadoop map reduce programs will be called internally to fetch data from this table. The data will be distributed over HDFS and will be collected and returned according to the query
  • hive -p 10000 -h localhost
  • CREATE TABLE person (PERSON_ID INT, NAME STRING, FIRST_NAME STRING, LAST_NAME STRING, MIDDLE_NAMES STRING, TITLE STRING, STREET_ADDRESS STRING, CITY STRING, COUNTRY STRING, POST_CODE STRING, HOME_PHONE STRING, WORK_PHONE STRING, MOBILE_PHONE STRING, NI_NUMBER STRING, CREDITLIMIT STRING, CREDIT_CARD STRING, CREDITCARD_START_DATE STRING, CREDITCARD_END_DATE STRING, CREDITCARD_CVC STRING, DOB STRING) row format delimited fields terminated by ',';
  • load data inpath 'export.csv' overwrite into table person;

• Start the iReport Designer
  • Create a new datasource to connect to Hive Database. This is the first step which will add a hive database.

• Create a new report. Refer to the screenshots for more details. An query is given to fetch appropriate data from the hive.

 

This way we now have a distributed file system (HDFS). A map-reduce engine above it(Hadoop). Datawarehousing tool over these framework (Hive) and then used a reporting tool to extract out menaingful data out of it and displaying it. Jasper report has built-in capabilities to communicate with Hive (via JDBC).




Peace.
Sanket Raut