HQL (Hive Query Language) Task using hue

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.

 

Hive editor window should look like this.

 

creating-table-query

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.

 

Advertisements

One thought on “HQL (Hive Query Language) Task using hue

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s