APACHE HIVE

APACHE HIVE

The Apache Hive data warehouse software facilitates querying and managing large datasets residing in distributed storage. Hive provides a mechanism to project structure onto this data and query the data using a SQL-like language called HiveQL. At the same time this language also allows traditional map/reduce programmers to plug in their custom mappers and reducers when it is inconvenient or inefficient to express this logic in HiveQL.

GETTING STARTED WITH HIVE

Here we will try to give a simple demonstration of how hive works.

SOURCE

http://seanlahman.com/files/database/lahman591-csv.zip

Hue can be accessed from HortonWorks using url:  127.0.0.1:8000

Login ID: Hue

Password: 1111

UPLOAD DATA

Data can be uploaded into the directory user/hue from the HDFS file system. 

The steps are available on the previous blogs.

Once done with this step, we will get something like this:

Now click on the top left corner of the page and click on the 2nd icon -- Beeswax(Hive UI).

FEEDING QUERIES

Click on the query editor to go to the query page.

QUERIES

Create a temp_batting table with the query

create table temp_batting (col_value STRING);

In order to check the code has executed correctly or not , we can click on Tables and then see the temp_batting table. 

Then tick the checkbox next to it and click on View as per the screenshot below.

LOADING Batting.csv INTO temp_batting

Here we load the baseball scores in our temp table using the below mentioned queriy:

LOAD DATA INPATH '/user/admin/Batting.csv' OVERWRITE INTO TABLE temp_batting;

Once the data is loaded, we can check the samples that are recorded in temp_batting table using below query.

SELECT * FROM temp_batting LIMIT 100;

Once  we execute the query, we can see the result as given below:

Now we have tocreate a new table called BATTING that will contain three columns:

player_id

year

number of runs

create table batting (player_id STRING, year INT, runs INT);

TABLE BATTING

Now we have to extract the data that we want from temp_batting and copy it into batting.  

We do it using regexp pattern and build a multi line query.

First line will overwrite the blank data into the batting table. 

Next 3 lines will extract player_id, year and runs fields form the temp_batting table.

Here is the query

insert overwrite table batting SELECT regexp_extract(col_value, '^(?:([^,]*)\,?){1}', 1) player_id, regexp_extract(col_value, '^(?:([^,]*)\,?){2}', 1) year, regexp_extract(col_value, '^(?:([^,]*)\,?){9}', 1) run from temp_batting;







Once the query is executed we can see the job status by entering the below address in the web browser 127.0.0.1:8088 and  get the below status.




Now we group the data by year using the query given below:








SELECT year, max(runs) FROM batting GROUP BY year;

OUTPUT

Now we need to check which player has scored how many runs for that given year. 




We can do this using the below query.




SELECT a.year, a.player_id, a.runs from batting a JOIN (SELECT year, max(runs) runs FROM batting GROUP BY year ) b ON (a.year = b.year AND a.runs = b.runs) ;

FINAL OUTPUT

Hope this demonstration helps. Thank you




Process Data with Apache Pig

What is Pig?

Pig is a high level scripting language that is used with Apache Hadoop. Pig excels at describing data analysis problems as data flows. Pig is complete in that you can do all the required data manipulations in Apache Hadoop with Pig. In addition through the User Defined Functions(UDF) facility in Pig you can have Pig invoke code in many languages like JRuby, Jython and Java. Conversely you can execute Pig scripts in other languages. The result is that you can use Pig as a component to build larger and more complex applications that tackle real business problems.

A good example of a Pig application is the ETL transaction model that describes how a process will extract data from a source, transform it according to a rule set and then load it into a datastore. Pig can ingest data from files, streams or other sources using the User Defined Functions(UDF). Once it has the data it can perform select, iteration, and other transforms over the data. Again the UDF feature allows passing the data to more complex algorithms for the transform. Finally Pig can store the results into the Hadoop Data File System.

Pig scripts are translated into a series of MapReduce jobs that are run on the Apache Hadoop cluster. As part of the translation the Pig interpreter does perform optimizations to speed execution on Apache Hadoop. We are going to write a Pig script that will do our data analysis task.

Lets take a look at how pig works using a problem statement.

Problem Statement

Compute the highest runs by a player for each year.

Data processing task

We are going to read in a baseball statistics file. We are going to compute the highest runs by a player for each year. This file has all the statistics from 1871–2011 and it contains over 90,000 rows. Once we have the highest runs we will extend the script to translate a player id field into the first and last names of the players.

Batting.csv - will give player with highest runs

Mster.csv - will give the player's personal information (common element : palyer id)

Steps

• Download the zip file and extract it to get the master.csv and batting.csv files

• Upload these two files into file browser of hue.
  

• Go to Pig script console and give title to the script

Steps to write the pig script : 

1. Load the data

2. Then we will filter the first row of the data

3. We use FOREACH statement to iterate through the batting data object and generate selected fields and assgin them names. The new data object we are creating is named as runs.

4. We use GROUP statement to group elements in runs by the year field.So, we created new data 

object grp_data

5. We will again use FOREACH statement to find maximum runs for each year.

6. Now we have maximum runs. So, we join this with runs data object so we can pick up player Id.

7. The result will be a dataset with "year","playerId" and "max 

runs".

8. At the end we use dump statement to generate the output.

We can check the progress of the job initiated in the job browser

The job is successfully completed

We have received the desired result.

We can check the history for confirmation.