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