Join in pig

Requirement

You have two table named as A and B. and you want to perform all types of join in pig Latin. It will help you to understand, how join works in pig.

Solution

Step 1: Input Files

Download file  Aand B from here. And place them into a local directory.

File A and B are the comma delimited file, please refer below :-

I am placing these files into local directory ‘/root/local_bdp/posts/join-in-pig’

Step 2: Enter into Pig Grunt shell.

Enter into grunt shell, type pig –x local.
Now we are using local mode because we have placed a file in a local directory. Load these two files using below commands. Replace the location of the file as per your local location.

 
 
  1. A = LOAD '/root/local_bdp/posts/join-in-pig/A' using PigStorage(',') AS (id :int,type:chararray);
  2. B = LOAD '/root/local_bdp/posts/join-in-pig/B' using PigStorage(',') AS (id :int,type:chararray);

Let’s understand join one by one

A. Inner Join:

Sometimes it is required to have only common records out of two datasets. Now we have to table A & B, we are joining based on a key which is id.
So in output, only those records which match id with another dataset will come. Rest will be discarded.

Use below command to perform the inner join in pig.

 
 
  1. INNER_JOIN = JOIN A BY id, B BY id;

 

Expected output:


Use below command to see the output set.

 
 
  1. DUMP INNER_JOIN

Please refer below screen shot for reference.


As you can see only records which have the same id such as 1, 3, 4 are present in the output, rest has been discarded.

B. Left Join

this type of join is performed when we want to look up something from other datasets, the best example would be fetching a phone no of an employee from other datasets based on employee code.
Use below command to perform left join.

 
 
  1. LEFT_JOIN = JOIN A BY id LEFT, B BY id;

Expected output

Use below command to see the output set.

 
 
  1. DUMP LEFT_JOIN

Now we have all the records of left table A and matched records of table B.

C. Right Join

This type of join is performed when we want to get all the data of look-up table with only matching records of left table.

Use below command to perform right join.

 
 
  1. RIGHT_JOIN = JOIN A BY id RIGHT ,B BY id;

Expected output

Use below command to see the output set.

 
 
  1. DUMP LEFT_JOIN

Now we have all the records of right table B and matched records of table A.

D.Full Join

When it is needed to get all the matched and unmatched records out of two datasets, we can use full join. All data from left as well as from right datasets will appear in result set. Nonmatching records will have null have values in respective columns.
Use below command to perform full join.

 
 
  1. FULL_JOIN = JOIN A BY id FULL, B BY id;

Expected output

Use below command to see the output set.

 
 
  1. DUMP FULL_JOIN

Now we have all matched and unmatched records in output as shown below.

Wrapping Up

Joins are important when you have to deal with data which are present in more than a table. In real time we get files from many sources which have a relation between them, so to get meaningful information from these data-sets it needs to perform join to get combined result.

We are happy to help you, don’t forget to subscribe us. Keep learning.

Load CSV file into hive AVRO table

Requirement You have comma separated(CSV) file and you want to create Avro table in hive on top of it, then ...
Read More

Load CSV file into hive PARQUET table

Requirement You have comma separated(CSV) file and you want to create Parquet table in hive on top of it, then ...
Read More

Hive Most Asked Interview Questions With Answers – Part II

What is bucketing and what is the use of it? Answer: Bucket is an optimisation technique which is used to ...
Read More
/ hive, hive interview, interview-qa

Spark Interview Questions Part-1

Suppose you have a spark dataframe which contains millions of records. You need to perform multiple actions on it. How ...
Read More

Leave a Reply