Since Hive v2.0.0, the Hadoop Procedure Language SQL (HPL/SQL) (http://www.hplsql.org/) available to provide store procedure programming in Hive. HPL/SQL supports Hive, Spark SQL, and Impala, and is compatible with Oracle, DB2, MySQL, and TSQL standard. One of its benefits is making the migration of existing database-stored procedures to Hive easy and efficient. Using HPL/SQL does not require Java skills to implement what can be done through UDF mentioned. Compared with UDF, HPL/SQL's performance is a little slower and it is still new for production usage.

The following is an example of creating a stored procedure. HPL/SQL supports the creation of both Function and Procedure:

$ cat getEmpCnt.pl
CREATE PROCEDURE getCount()
BEGIN
DECLARE cnt INT = 0;
SELECT COUNT(*) INTO cnt FROM employee;
PRINT 'Users cnt: ' || cnt;
END;

call getCount(); -- Call a procedure

In order to run a procedure, we need to set up the database connection in hplsql-site.xml by providing the hiveserver2 connection URL, as follows. After that, HPL/SQL can use the default connection to submit the procedure statement or file:

SQL hplsql command, is in the same folder as the hive command, with the -f option, as follows:

$ cat /opt/hive2/conf/hplsql-site.xml
 
 <configuration>
 <property>
 <name>hplsql.conn.default</name>
 <value>hive2conn</value>
 </property>
 <property>
 <name>hplsql.conn.hive2conn</name>
 <value>org.apache.hive.jdbc.HiveDriver;jdbc:hive2://localhost:10500</value>
 </property>
 </configuration>

Then, we can call the HPL:

$cd /opt/hive2/bin
$ ./hplsql -f getEmpCnt.pl
SLF4J: Class path contains multiple SLF4J bindings.
...
Open connection: jdbc:hive2://localhost:10500 (1.02 sec)
Starting query
Query executed successfully (569 ms)
Users cnt: 4