HIVE

In this blog post we will discuss about how to execute a HQL task using hue environment.

HQL was developed by facebook engineers as a hadoop support structure that allows fluent SQL developers to leverage the hadoop platform because HQL is similar to SQL. Those who are good with SQL can easily get adapted to HQL.This blog post will be continuation to our previous task. If you remember we have executed pig script to calculate the player wise scores sorted according to years.Like we accessed pig script window we should now access to HIVE UI (User Interface) this is located in hue page with a bee wax symbol.

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.

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 editor should  look like this:

Lets execute our first query

create table temp_batting (col_value STRING);

if you observe this is similar to SQL query. This query will create a table called temp_batting.

Now we need to load the data from to our hql table temp_batting from a csv file which is already uploaded from window environment in our last task.

So lets move on and create a new table called batting with three variables player_id, year and No. of runs scored.

we shall use the following code for doing the same.

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

We will now transfer our data from temp_batting to our new table batting.

The code which we will use for the same is as follows:-

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;

The job status can be seen in our all applications window.

Lets group the data with Maximum runs.

Lets get to know which player scored highest runs in a given particular year. We will use the following code.

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

So with this we successfully executed the HQL queries and performed the task with easily understandable codes.

If you have any queries please comment on this post.

Working of PIG LATIN using HADOOP

What is 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.



Why use PIG at all?

Pig was initially developed at Yahoo! to allow people using Hadoop® to focus more on analyzing large data sets and spend less time having to write mapper and reducer programs. Like actual pigs, who eat almost anything, the Pig programming language is designed to handle any kind of data—hence the name!

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.
Running a program in PIG:

The objective of the program is to compute highest run scored by a baseball player for each year. The file we are referring to has all statistics from 1871-2011 and over 90000 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.

Data can be downloaded from the following link.

http://hortonassets.s3.amazonaws.com/pig/lahman591-csv.zip

This link opens a zip folder containing csv files. We will upload batting.csv and masters.csv

Once the hortonworks sandbox is running log into HUE using the address 127.0.0.1:8000 on your web browser

Credentials :

Login : hue

password : 1111

Now its time to upload our data to hue using interactive option in file browser tab.

CODES:

Below are the codes which are needed to execute our objective from the data. Along with the code I will attempt to explain each line to aid you in your understanding.

batting = load 'Batting.csv' using PigStorage(',');

PigStorage function loads the data, comma as the data delimiter.

raw_runs = FILTER batting BY $1>0;

Filtering the first row of data

runs = FOREACH raw_runs GENERATE $0 as playerID, $1 as year, $8 as runs;

FOREACH statement will iterate through the batting data object and GENERATE pulls out selected fields and assings them names.

grp_data = GROUP runs by (year);

Groups the elements in runs by the year field.

max_runs = FOREACH grp_data GENERATE group as grp,MAX(runs.runs) as max_runs;

Using FOREACH command to find maximum runs for each year

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;

We join maximum runs with joins this with the runs data so that we can pick up the player id.  The result will be a dataset containing Year, Player ID and Run.  The last line dumps the data to the output.

Your final script will look as below

OUTPUT:

Thank you