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.
