Hive

What's HIVE?

Hive is a data warehouse infrastructure tool to process structured data in Hadoop. It resides on top of Hadoop to summarize Big Data, and makes querying and analyzing easy.

Although Pig can be quite a powerful and simple language to use, the downside is that it’s something new to learn and master. Some folks at Facebook developed by a run time Hadoop support structure that allows anyone who is already fluent with SQL (which is commonplace for relational data-base developers) to leverage the Hadoop platform right out of the gate.

Their creation, called Hive, allows SQL developers to write Hive Query Language (HQL) statements that are similar to standard SQL statements; now you should be aware that HQL is limited in the commands it understands, but it is still pretty useful. HQL statements are broken down by the Hive service into MapReduce jobs and executed across a Hadoop cluster.

For anyone with a SQL or relational database background, this section will look very familiar to you. As with any database management system (DBMS), you can run your Hive queries in many ways. You can run them from a command line interface (known as the Hive shell), from a Java Database Connectivity (JDBC) or Open Database Connectivity (ODBC) application leveraging the Hive JDBC/ODBC drivers, or from what is called a Hive Thrift Client. The Hive Thrift Client is much like any database client that gets installed on a user’s client machine (or in a middle tier of a three-tier architecture): it communicates with the Hive services running on the server. You can use the Hive Thrift Client within applications written in C++, Java, PHP, Python, or Ruby (much like you can use these client-side languages with embedded SQL to access a database such as DB2 or Informix).

Hive looks very much like traditional database code with SQL access. However, because Hive is based on Hadoop and MapReduce operations, there are several key differences. The first is that Hadoop is intended for long sequential scans, and because Hive is based on Hadoop, you can expect queries to have a very high latency (many minutes). This means that Hive would not be appropriate for applications that need very fast response times, as you would expect with a database such as DB2. Finally, Hive is read-based and therefore not appropriate for transaction processing that typically involves a high percentage of write operations.


Functions of HIVE:

Hive has three main functions: data summarization, query and analysis. It supports queries expressed in a language called HiveQL, which automatically translates SQL-like queries into MapReduce jobs executed on Hadoop. In addition, HiveQL supports custom MapReduce scripts to be plugged into queries. Hive also enables data serialization/deserialization and increases flexibility in schema design by including a system catalog called Hive-Metastore.

According to the Apache Hive wiki, "Hive is not designed for OLTP workloads and does not offer real-time queries or row-level updates. It is best used for batch jobs over large sets of append-only data (like web logs)."

Hive supports text files (also called flat files), SequenceFiles (flat files consisting of binary key/value pairs) and RCFiles (Record Columnar Files which store columns of a table in a columnar database way.)

Other features of Hive:


>Indexing to provide acceleration, index type including compaction and Bitmap index as of 0.10, more index types are planned.
>Different storage types such as plain text, RCFile, HBase, ORC, and others.
>Metadata storage in an RDBMS, significantly reducing the time to perform semantic checks during query execution.
>Operating on compressed data stored into the Hadoop ecosystem using algorithms including DEFLATE, BWT, snappy, etc.
>Built-in user defined functions (UDFs) to manipulate dates, strings, and other data-mining tools. Hive supports extending the UDF set to handle use-cases not supported by built-in functions.
>SQL-like queries (HiveQL), which are implicitly converted into MapReduce or Tez, or Spark jobs.

HIVE - Hands ON:

1) Download the data from - http://seanlahman.com/files/database/lahman591-csv.zip

2) The HDFS Files view shows you the files in the HDP file store. In this case the file store resides in the Hortonworks Sandbox VM. Clicking on browse will open a dialog box. Upload Batting.csv file and Master.csv file.

3) Open the Hive View by clicking on the Hive button. The Hive view provides a user interface to the Hive data warehouse system for Hadoop. On right is a query editor. A query may span multiple lines. At the bottom there are buttons to Execute the query, Explain the query, Save the query with a name and to open a new Worksheet window for another query.

The first task we will do is create a table to hold the data. We will type the query into the composition area on the right handside. Once you have typed in the query hit the Execute button at the bottom.

 create table temp_batting (col_value STRING);  

The query does not return any results because at this point we just created an empty table and we have not copied any data in it.

Once the query has executed we can refresh the Database Explorer at the left of the composition area and when folding down the default database we will see we have a new table called temp_batting.

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

4) The next thing we want to do extract the data. So first we will type in a query to create a new table called batting to hold the data. That table will have three columns for player_id, year and the number of runs.

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

5) Then we extract the data we want from temp_batting and copy it into batting. We will do this with a regexp pattern. To do this we are going to build up a multi-line query. The first line of the query create the table batting. The three regexp_extract calls are going to extract the player_id, year and run fields from the table temp_batting. When you are done typing the query it will look like this. Be careful as there are no spaces in the regular expression pattern.

 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;  

6) The next step is to group the data by year so we can find the highest score for each year. This query first groups all the records by year and then selects the player with the highest runs from each year.

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

7) To go back and get the player_id(s) so we know who the player(s) was. We know that for a given year we can use the runs to find the player(s) for that year. So we can take the previous query and join it with the batting records to get the final table.

 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) ;  

PIG - An Overview

PIG

Pig is a high-level platform for creating MapReduce programs used with Hadoop. The language for this platform is called Pig Latin. Pig Latin abstracts the programming from the Java MapReduce idiom into a notation which makes MapReduce programming high level, similar to that of SQL for RDBMS systems. Pig Latin can be extended using UDF (User Defined Functions) which the user can write in Java, Python, JavaScript, Ruby or Groovy and then call directly from the language.

Pig was originally developed at Yahoo Research around 2006 for researchers to have an ad-hoc way of creating and executing map-reduce jobs on very large data sets. In 2007, it was moved into the Apache Software Foundation.

It is a platform for analyzing large data sets that consists of a high-level language for expressing data analysis programs, coupled with infrastructure for evaluating these programs. The salient property of Pig programs is that their structure is amenable to substantial parallelization, which in turns enables them to handle very large data sets.

Pig is made up of two components: the first is the language itself, which is called PigLatin and the second is a runtime environment where PigLatin programs are executed.

Why Is It Called Pig?

One question that is frequently asked is, “Why is it named Pig?” People also want to know whether Pig is an acronym. It is not. The story goes that the researchers working on the project initially referred to it simply as “the language.” Eventually they needed to call it something. Off the top of his head, one researcher suggested Pig, and the name stuck. It is quirky yet memorable and easy to spell. While some have hinted that the name sounds coy or silly, it has provided us with an entertaining nomenclature, such as Pig Latin for a language, Grunt for a shell, and Piggybank for a CPAN-like shared repository.


The programming language:
1. The first step in a Pig program is to LOAD the data you want to manipulate from HDFS.
2. Then you run the data through a set of transformations (which, under the covers, are translated into a set of mapper and reducer tasks).
3. Finally, you DUMP the data to the screen or you STORE the results in a file somewhere.

LOAD
As is the case with all the Hadoop features, the objects that are being worked on by Hadoop are stored in HDFS. In order for a Pig program to access this data, the program must first tell Pig what file (or files) it will use, and that’s done through the LOAD 'data_file' command (where 'data_file' specifies either an HDFS file or directory). If a directory is specified, all the files in that directory will be loaded into the program. If the data is stored in a file format that is not natively accessible to Pig, you can optionally add the USING function to the LOAD statement to specify a user-defined function that can read in and interpret the data.

TRANSFORM
The transformation logic is where all the data manipulation happens. Here you can FILTER out rows that are not of interest, JOIN two sets of data files, GROUP data to build aggregations, ORDER results, and much more.

DUMP and STORE
If you don’t specify the DUMP or STORE command, the results of a Pig pro¬gram are not generated. You would typically use the DUMP command, which sends the output to the screen, when you are debugging your Pig programs. When you go into production, you simply change the DUMP call to a STORE call so that any resHow Pig differs from MapReduce

Why is Pig necessary?

Pig provides users with several advantages over using MapReduce directly. Pig Latin provides all of the standard data-processing operations, such as join, filter, group by, order by, union, etc. MapReduce provides the group by operation directly (that is what the shuffle plus reduce phases are), and it provides the order by operation indirectly through the way it implements the grouping. Filter and projection can be implemented trivially in the map phase. But other operators, particularly join, are not provided and must instead be written by the user.

Pig provides some complex, nontrivial implementations of these standard data operations. For example, because the number of records per key in a dataset is rarely evenly distributed, the data sent to the reducers is often skewed. That is, one reducer will get 10 or more times the data than other reducers. Pig has join and order by operators that will handle this case and (in some cases) rebalance the reducers. But these took the Pig team months to write, and rewriting these in MapReduce would be time consuming.

In MapReduce, the data processing inside the map and reduce phases is opaque to the system. This means that MapReduce has no opportunity to optimize or check the user’s code. Pig, on the other hand, can analyze a Pig Latin script and understand the data flow that the user is describing. That means it can do early error checking and optimizations.

MapReduce does not have a type system. This is intentional, and it gives users the flexibility to use their own data types and serialization frameworks. But the downside is that this further limits the system’s ability to check users’ code for errors both before and during runtime.

All of these points mean that Pig Latin is much lower cost to write and maintain than Java code for MapReduce. In one very unscientific experiment, I wrote the same operation in Pig Latin and MapReduce. Given one file with user data and one with click data for a website, the Pig Latin script in Example 1-4 will find the five pages most visited by users between the ages of 18 and 25.ults from running your programs are stored in a file for further processing or analysis. Note that you can use the DUMP command anywhere in your program to dump intermediate result sets to the screen, which is very useful for debugging purposes.

Ex 1:

 batting = load 'Batting.csv' using PigStorage(',')  
 raw_runs = FILTER batting BY $1>0;  
 runs = FOREACH raw_runs GENERATE $0 as playerID, $1 as year, $8 as runs;  
 grp_data = GROUP runs by (year);  
 max_runs = FOREACH grp_data GENERATE group as grp,MAX(runs.runs) as max_runs;  
 join_max_run = JOIN max_runs by ($0, max_runs), runs by (year,runs);   
 join_data = FOREACH join_max_run GENERATE $0 as year, $2 as playerID, $1 as runs;   
 DUMP join_data;  

Result

Ex 2:

 Users = load 'users' as (name, age);  
 Fltrd = filter Users by age >= 18 and age <= 25;  
 Pages = load 'pages' as (user, url);  
 Jnd  = join Fltrd by name, Pages by user;  
 Grpd = group Jnd by url;  
 Smmd = foreach Grpd generate group, COUNT(Jnd) as clicks;  
 Srtd = order Smmd by clicks desc;  
 Top5 = limit Srtd 5;  
 store Top5 into 'top5sites';  

Explanation

The first line of this program loads the file users and declares that this data has two fields: name and age. It assigns the name of Users to the input. The second line applies a filter to Users that passes through records with an age between 18 and 25, inclusive. All other records are discarded. Now the data has only records of users in the age range we are interested in. The results of this filter are named Fltrd.

The second load statement loads pages and names it Pages. It declares its schema to have two fields, user and url.

The line Jnd = join joins together Fltrd and Pages using Fltrd.name and Pages.user as the key. After this join we have found all the URLs each user has visited.

The line Grpd = group collects records together by URL. So for each value of url, such as pignews.com/frontpage, there will be one record with a collection of all records that have that value in the url field. The next line then counts how many records are collected together for each URL. So after this line we now know, for each URL, how many times it was visited by users aged 18–25.

The next thing to do is to sort this from most visits to least. The line Srtd = order sorts on the count value from the previous line and places it in desc (descending) order. Thus the largest value will be first. Finally, we need only the top five pages, so the last line limits the sorted results to only five records. The results of this are then stored back to HDFS in the file top5sites.

In Pig Latin this comes to nine lines of code and took about 15 minutes to write and debug. The same code in MapReduce (omitted here for brevity) came out to about 170 lines of code and took me four hours to get working. The Pig Latin will similarly be easier to maintain, as future developers can easily understand and modify this code.

There is, of course, a cost to all this. It is possible to develop algorithms in MapReduce that cannot be done easily in Pig. And the developer gives up a level of control. A good engineer can always, given enough time, write code that will out perform a generic system. So for less common algorithms or extremely performance-sensitive ones, MapReduce is still the right choice. Basically this is the same situation as choosing to code in Java versus a scripting language such as Python. Java has more power, but due to its lower-level nature, it requires more development time than scripting languages. Developers will need to choose the right tool for each job.



REFERNCES:

To Download - https://pig.apache.org/releases.html