Community Articles

Find and share helpful community-sourced technical articles.
Labels (1)
Super Guru

There are two great additions you can make to your current Hive. The first is HPL/SQL that brings stored procedure programming to the Hadoop world. The second is Hive Mall which brings advanced functions and machine learning to your Hive queries.


HPL/SQL is included in Hive 2.0 and will be included in Hive 2.1 on HDP 2.6. You can manually download and install it now. It is Hybrid Procedural SQL on Hadoop. For developers coming from Oracle and SQL Server, these procedures will feel very familiar and will allow you to port a lot of your existing PL/SQL and TSQL code over to Hive.

This gives you another interface to Hive and Hadoop, it will be included in future Hadoop and be tied into the very fast Hive LLAP 2.1.


To Run A Stored Procedure

cd hplsql-0.3.17 
./hplsql -f 

HP/SQL Stored Procedure Example

create procedure fn_test1 (VarOne char(25))    
SET plhql
execute immediate 'set hive.exec.dynamic.partition.mode=nonstrict'; 
execute immediate 'set hive.exec.dynamic.partition=true';    
execute immediate 'SET hive.execution.engine=tez';    
print VarOne;  
set VarOne = Upper(VarOne);  
if (VarOne not in ('STUFF', 'STUFF2'))  
print 'Bad Data';  
print 'Good Data';
print call fn_test1('STUFF');
./hplsql -f
17/03/09 20:04:03 INFO jdbc.Utils: Supplied authorities: localhost:10000
17/03/09 20:04:03 INFO jdbc.Utils: Resolved authority: 
Open connection: jdbc:hive2://localhost:10000 (266 ms)
Starting SQL statementSQL statement executed successfully (2 ms)
Starting SQL statementSQL statement executed successfully (2 ms)
Starting SQL statementSQL statement executed successfully (1 ms)
Good Data 


Apache HiveMall

HiveMall was developed by developers from Treasure Data, NTT and Hortonworks.

set hivevar:hivemall_jar=hdfs:///apps/hivemall/hivemall-with-dependencies.jar; 
source /opt/demo/define-all-as-permanent.hive; 

HiveMall is a scalable machine learning library built as a collection of Hive UDFs that you can run through Hive, Spark and Pig. It brings very cool processing to your Hive queries, Zeppelin, Pig and Spark code. You will be able to combine Hive Mall machine learning with stored procedures on in-memory fast LLAP Hive. This is revolutionary. You can run this via near real-time Apache NiFi streams.


Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.
Version history
Last update:
‎08-17-2019 01:51 PM
Updated by:
Top Kudoed Authors