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