Pig Schema – Part 2

In my previous post, we learned Pig scalar data types and complex data types. We have seen examples to load a tuple. We have seen another example where we loaded a tuple which contains a Map as a field. That was an example of nested complex data type.
We are left with an example to load an inner bag into a pig relation. What is inner bag? We understand bag, it’s a collection of tuples. So whenever we load data from our files, it creates several tuples and all of them are loaded in an outer bag. Look at the example below. Line 1 & 2 loads my file test-data.tsv into a relation named D. In this case D is an outer bag. Line number 3 creates a new relation X by grouping state column from D. In this case X is again an outer bag. At line number 5, when we dump X, content of X is displayed on screen. Look at the last line in output.
First column value is Maharashtra and second column is enclosed into {}. That entire second column is an inner bag. This inner bag holds two tuples which are separated by comma. I have saved it into a file and we will load it back using a new script.

Look at the example below to learn how we load an inner bag from our file. It’s simple and need not any explanation.

I will summarize Complex data types in below table.

We have seen that when we load data from a source file, data format in source format is also important. When we loaded map data, we had data encoded in [key#value] format. When we loaded an inner bag, we had data encoded in {(f1,f2,…),(f1,f2,…)} format.

You must have a doubt that do we really get data in such a very well encoded pig schema notations? Real life data comes in all varieties. You may get data files encoded in pig schema notations but you may get completely unstructured data or semi structured data or you may get very well structured data but not in pig schema notations, it may be some other schema notation like JSON or XML or an HBASE table or any other format. That’s one of the top challenges in data analysis and any tool claiming to be a data analysis tool must have to deal with those challenges. So the question is how pig handles this challenge.

This role is played by LOAD and STORE function. Load/Store functions determine how data goes into Pig and comes out of Pig. If you remember examples which we used to load a comma separated file, we used PigStotage function to inform pig that my data file is a comma separated file. PigStorage is default load function for pig. As per Pig 0.12 documentation, there are 8 such functions to support various source data formats. We will see some examples on their usage but what if you have data which is in a format other than supported by these 8 functions. You can write your own function and use it in your pig script to load your custom formatted data. But if your data is in any of the industry accepted standard formats or your schema is stored in HCATLOG, you will find such Load/Store functions already developed and shared by someone in open source community. I will write about HCATLOG separately but if you need you can get information about HCatLoader here.

Now it’s time for an example of using load function other than default PigStorage. I will show you an example on how to use JsonLoader to load data from a JSON data file.

For this example, we need a simple Json data file shown as below.

JsonLoader expects a schema definition file named as .pig_schema in the input directory. Screen below shows content of the file for our example.

Finally, below script shows my script for loading Json formatted data.

Keep reading, keep learning.

Good Luck.

Pig – Data Types and Schema– Part 1

In previous posts, we learned how to load data into Pig relation (outer bag). When you load data into a relation, you have an option to specify schema for your data. Specifying a schema is letting pig know about organization of your data like field names and data types. Specifying schema is optional, if you don’t specify, pig will make guesses with best possible way. But if you specify schema for you data, Pig will make use of it in error checking and optimization. Let’s take an example.

I am loading tuples from hr-data.txt in three relations one after other. First relation MYDATA is loaded without specifying any schema about my data. I can still use MYDATA bag. When I need to refer any field, I can use positional notation which starts from 0. If I use $0 in my pig script, it points to first column in MYDATA which is id column. If I use $2 in my pig scripts, it points to last_name. We will see examples later how to use positional notations.
Second bag MYDATA1 is loaded with partial schema information. I informed pig about column names but nothing about data types. I specified only 4 columns but actual file contains more than that. Pig will ignore them and will load only 4 columns.
Third bag, MYDATA2 is loaded with much more detailed schema information. I specified field names and their data types. Before we get into more details about schema, let’s understand supported data types in Pig.

Scalar Data Types

Pig supports scalar and complex data types. We will focus on scalar data types first. Below table shows list of all supported scalar data types.

There is nothing much to discuss about these data types except they are implemented internally using java classes. This makes them easy to be used with user defined functions and their behavior resembles Java to their original Java counterpart.

Complex Data Types

Pig has three complex data types Map, Tuple and Bag. Tuple is the simplest one amongst other complex data types in Pig. We have already seen tuples in above example where we loaded a tuple of four fields. Most of the time you will start with loading data from your source as tuples and then working on it to transform them. Most important property of tuple is that it is an ordered list of fields so you can refer them using positional notation. When you load data as tuples, absence of data or some error in data makes field set to NULL. While defining schema for the tuple we use parentheses i.e. () to represent a tuple and fields in tuple are separated by comma. See example below.
Next complex type is Bag which is a collection of tuple. Bag is nothing but a collection of tuples. When you load data, you get a bag which holds multiple tuples. Bag is like table in database and tuples are like rows in database tables however there are several differences which you will understand slowly. You should note that tuples are not ordered in bag and it may contain duplicate tuples also. That’s why bags do not support any positional notation.
Last complex type is Map. It’s a key-value pair. These key value pairs are separated by # and data type for key must be chararray. We use [key#value] to represent a key value pair in pig. We will see an example below.
We will see some examples for all of these complex data types to get a better understanding.

Let’s come back to schema. Schemas enable you to assign names to fields and declare types of fields. They are optional but we should use them whenever possible. We get first opportunity to define schema when we load data using LOAD statement. Later we can redefine schema with FOREACH statements. Let’s take an example for both the methods. Let me show you my data before I load it.

It’s a tab separated file with four fields as name, city, state and country. Let’s load it without specifying schema. We will use DESCRIBE command to show its schema and then we will use DUMP command to display it on screen. First three lines in below screen is my pig script and rest is output.

So pig reported that schema is unknown. A parenthesis in output represents tuple and we can see that fields are separated by comma. Let’s define schema for this same data using AS clause of LOAD statement. We will use same parenthesis and comma notations to define it.

We can see that Pig has responded back with the schema of our bag D. {} represent bag.

Let’s look at another example. In this example, I want to demonstrate two things. How to load Map data type and how to redefine schema using FOREACH statement? For this example, I have slightly changed by data file. Check below screen and you will notice that I have modified city field. I encoded it using format which is used by pig to represent a key values pair i.e. [Key#value].

Check below script and output. Hope you get it else read explanation below.

I loaded data specifying schema for my data. Schema is almost same as defined in earlier example except one change. Let’s look at m: map[chararray], m is filed name. map is a keyword informing pig that it’s a map type. chararray is data type for value. We can’t define data type for key as pig always assumes it to be a chararray.
Output generated by DESCRIBE command confirms same schema which I specified along with LOAD.
FOREACH statement extracts only city name from relation D and redefines schema for that field as CityName:chararry. When we again describe schema for relation X, we can see what we defined. Finally I displayed content of relation X.

I believe it’s too much for a beginner to grab about schemas, you try it yourself, digest it and we will cover more about schemas later.

Apache Pig Latin – A Paradigm shift

What is Pig? You ask this question to anyone and answer will be somthing as “It is a parallel data flow language”. Such a flat answer does not give me a proper picture. In this post we will try to answer this question and learn some other Pig fundamentals. Let’s start.

There are at least three paradigms of computer programing techniques. Most of us are already familiar with two of them. Programming languages and Query languages.

In programing languages like Java and C, we control execution. When we write program, we define execution steps.

In query languages we ask questions.

A Java or C programer who doesn’t know query language will find SQL a difficult thing during early learning phases. It takes some time for him to get hold of this paradigm shift.
Pig Latin is another shift. It uses third programming technique known as data flow language or sometimes termed as data stream language. If you are not familiar with any data flow language, it’s another paradigm shift for you and it might take some extra effort to get familiar and comfortable with this method.

In this post, we will cover some basics about Pig Latin. Let’s Start.

1. Pig is a mixed case language. Keywords in Pig are case insensitive but names in pig are case sensitive. When I say names are case sensitive that means variable names, function names etc. When I say keywords are case insensitive that means LOAD, USING, FOREACH, GROUP BY etc.
2. Like any other scripting language, Pig Latin script support commenting. Single line comments are given using double hyphen (- -) and multiline comments are Java style i.e. pair of starting /* and ending */.
3. Pig is a data flow language. Before you do anything with data, you have to load it. Almost every pig script will begin with a load statement. An example is given below.
                    MYDATA = load ‘/user/pkp/test.txt’;

For simplicity, you can think of MYDATA as a variable. Above statement will load data from test.txt file and assign it to a variable MYDATA.

MYDATA is not a simple variable. It is similar to a table in any database which contains multiple rows of data. In our example, MYDATA holds multiple rows from test.txt.

Pig Latin uses different terminology for Table and Rows. Table in Pig terminology is a Bag. Row in Pig terminology is a Tuple. In this example MYDATA is a Bag which holds multiple Tuples from test.txt file.

1. When Pig executes load statement, it loads tuples from file into a Bag. By default load statement assumes that your tuples are separated by new line character. 
2. If you are visualizing Tuple as a row, you must see fields there. By default, load statement assumes that your fields are tab delimited. 
3. Once you loaded data into Pig, what do you want to do with it?
   Answer will be processing. But I don’t want to call it processing. I will call it “data flow”. Because I will flow data from one bag to another bag. That’s all we do in Pig and that’s what a paradigm shift you have to understand. Pig is a data flow language and we don’t process data. We flow data from one bag to another bag. Bag and Relation are almost synonymous in Pig. You may think, what will I achieve moving data from one bag to another bag? Doubt is genuine. I will transform data from one format to another format in each step and new bag will hold data into a new format. Isn’t great? No? Let me ask, what is ETL process?
   E- We Extract data from a source, that’s what we did in load statement.
   T- We Transform data into a new format, that’s what I am saying that we will do in each step of our Pig Latin.
   L- That’s a simple part. We will load our final Bag wherever we want, most of the time into another file.
   Pig Latin is essentially an ETL scripting language. it is the most simple and powerful ETL tool as of today coupled with the power of hadoop.
4. When you are done with your transformations or a series of transformations, you have your final data into a Bag. Most of the time, you might want to store it into a new file. You will use below command for this.

             store BAG_NAME into ‘FILE_NAME’;

             if you just want to display your bag content on the screen instead of storing it into a file, 
             you can use dump BAG_NAME;

We will run a simple example. I will load a tab separated file test.txt and dump it on screen. Let me show you my file before we start.  In below screen, my data file has 5 rows. Each row has 4 columns or fields and they are separated by a tab character.

Let’s load it into Pig Bag and dump it back on screen. My Bag name is MYDATA. Grunt throws so many messages on screen and at the end of processing, it shows output. I have eliminated all those intermediate messages and kept only output in the screen for better clarity.

In above screen, you notice that each tuple is enclosed by braces. If you remember, my original data file did not have these braces. It’s pig who is adding these braces. You can try storing it into a file (store BAG_NAME into ‘FILE_NAME’;)instead of dumping on screen and you will notice that pig does not add these braces into file. Which clarifies that pig does not enclose top level tuples into braces when store them back in file. What is top level tuple? I said so because we can nest tuples i.e. tuples inside a tuple. We will see nesting below in next step. Next thing to notice here is my fields in a tuple are separated by comma but my original file was separated by tab. That’s again a Pig behavior which does not happen when we store it back into file.

Now let’s perform a transformation, I will group it on third column and dump it on screen. Pig starts column count from 0 so I gave $2 for the third column.

Great! you saw first transformation and a new bag of your tuples. That’s why I mentioned in the beginning that, in pig we move data from one bag to another bag and perform some transformation at each movement. If you understand this concept, you will easily master data flow language. If you keep thinking like Java, C, SQL or any other language you know, you will have difficulties in understanding and developing Pig Latin scripts.

Let’s discuss some important observations from the output.

1. You can again see that each top level tuple is covered inside a pair of top level braces so what’s my first tuple? (HR,{(105,Ravi,HR,Pune)})
   And what is second one? (IT,{(101,Prashant,IT,Bangalore),(102,Prakash,IT,Bangalore)})
2. Next question for you. In my second tuple, how many fields are there? If you answer two? You are right. My first column value is IT and rest of the string is second column.
3. You should have question for me. Why second field is enclosed by {}? Let me answer. Curly braces represents and inner Bag. Nesting has happened here. We have an outer Bag named MYGROUP. This bag contains three tuple. Each tuple contains two fields. First field is a string and second field is an inner Bag. You may ask why we need this inner Bag. Answer is simple. We need a Bag because we need to store multiple tuples for each group for example, for group IT, we have two tuples separated by comma.
4. Let’s store results into a file, check below screen. You will notice that Pig has again removed outer braces from top level tuples and fields are separated by tab instead of comma. This is default behavior of output function which actually writes data into file. We will again visit this behavior when we discuss output function in Pig.

My objective in this post was to help you cope up with paradigm shift from a programming language or query language to a data flow language. Once you start thinking in terms of data flow from one Bag to another Bag and from one form to another form, you will be able to speed up your Pig learning. Hope I thrived in my attempt. Your feedback is valuable.

I

Getting Started with PIG

The power of hadoop is its distributed file system and MapReduce processing framework. We already know that most flexible and powerful method for developing MapReduce jobs is using Java. But we may not need that level of control and power for doing most of our ETL tasks and Java turns out to be too much low level for doing simple tasks. This thought has given birth to Hive and Pig. These two were designed and developed by two different teams in two different companies. They both are given to open source and became famous due to their wide acceptance. Now a day, most of the companies using hadoop is using both of them. I have explained Hive in my several posts and will continue going deeper into Hive. By now we understand that Hive is a query like language and inherit most of SQL features and syntaxes. What is PIG then and how it differs from Hive?

It is officially known as Pig Latin and a Parallel data flow language. So it’s not a query language, its data flow language. What does that mean? In a query language, we tell what result do we want and let query engine decide how to get those results.
For example when we write a SQLSELECT DEPARTMENT, AVG (SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT; we are just asking a question “What is average salary for each department?” Query engine will decide what to do to get this answered. But when we have to do it procedurally, it’s completely different approach. Think of writing a function in C or Java language to get this answered, that’s a procedural example. You have to open file and load data, divide it into groups, calculate average and finally write averages somewhere. Scared? PIG is not as low level as C or JAVA but yes, it is procedural. If you know HQL (Read Hive related Posts on this blog) or at least SQL, as you learn more on PIG you will realize differences so let’s not waste time discussing differences and start learning PIG Latin.

We know pig is a scripting language and like any other, we have two methods to execute our scripts. You can place all of your Pig code into a file and execute that file. Second method is to start pig shell and enter all your pig commands interactively. We will start with second method however first one is used in real solutions. We are choosing second method i.e. pig shell to start learning pig because it immediately compiles each command I enter and let me know if there is any error. In case of error, I correct it and enter my command again. Pig remembers only latest one so I need not to worry what I entered earlier. This feature makes it easy to work for starters. Once we are comfortable with pig syntax, we will move to executing out pig files.

I am assuming you have access to pig environment, if you don’t have, go back to my post here and get a virtual box running. You may need to read this post to be well versed with HDFS and virtual box.
Let’ login to you virtual machine and type below command which will start pig shell. Pig shell is known as Grunt. We will refer grunt instead of pig shell. pig –x mapreduce

Once you are in grunt, we will execute one simple data flow which will produce same results as you can expect from below SQL.SELECT DEPARTMENT, AVG (SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT;

It’s a hello world for is in PIG. You will need a data file. I already have a data file which I used in some of my Hive tutorials which is shared here. You need to download this file and place it into you HDFS directory. If you are not aware how to move files into your hadoop file system, you should read this article.

Let’s do it. If you look at below screen, you will see that I am able to execute my HDFS shell commands from grunt. I used –ls command to show you that my data file is named hql-data.txt and it is placed at /user/pkp/hql directory in my hadoop file system. Commands inside red box are actually my Pig Latin commands.

Let me explain. First three lines starting with mydata till semicolon is a single pig command. There are three parts of it, load, using and as. Load is main command and it takes filename so I gave full path for my data filename. Using is a clause of load command. Since my file is a comma separated file I used this clause to specify that. By default, if I don’t specify using clause, pig will assume a tab separated file. Finally I have as clause where I specified column names. By default pig will assume all columns as bytearray which is fine for this example hence I have not specified any data type for my columns.
Fourth line created a group on department column.
Fifth line generates results by looping for each group and calculating salary averages for each group. We will learn more about all these things sometime later. In this post my objective is to begin with simple pig script.
Finally, sixth line will create a file and place result into it. Grunt will compile all pig statements as you enter it on grunt shell but it will not execute anything until it finds a store command. As soon as you press enter in above screen, grunt will execute entire flow and create a directory “dept_sal_avg” into my home directory and place a file loaded with results into that directory.

It’s time to check results. Check below screen.

Do you need any explanation? I don’t think.

Keep reading and keep learning.