Implementing stored procedures in Hive with HPL/SQL is very inconvenient. The open source tool has many restrictions on the use of cursor, which makes it impossible to implement a lot of features. It imposes such strict rules on the use of variables that gives rise to compatibility problems. Both are not fatal flaws if basic debugging functionality functions properly after error in the code reports. The biggest problem is that it is “undebuggable”. That is awkward.

To make things worse, HPL/SQL doesn’t provide the JDBC interface. To embed it into a Java program, you need to run command line to execute the HPL/SQL script and write the result back to a Hive temporary table and then read it through Hive’s JDBC in the Java program.

An alternative, indirect and not better, is Java UDF. Java lacks class libraries for processing structured data, so it implements all algorithms through hardcoding. For example, it writes a two-dimensional table using the combination of “ArrayList+HashMap”, achieves group and aggregate with dozens of lines of code and implements joins with much too lengthy code. There’s no uniform phrasing with hardcoding even for similar business logic. The various solutions make code difficult to read and maintain.

Another problem with Java stored procedures is the tight coupling. Java classes are not hot-deployable, making it impossible to change what’s currently deployed without redeploying it and restarting Hive service. This affects the development environment in a bad way. Though we can choose to design a smart structure to loosen the coupling, we are sure to see an increase in the cost.

There would be no such problems if we had use esProc to write stored procedures. All will be much easier.

esProc provides a rich variety of class libraries for structured data processing. There are embedded functions for performing retrieval, sorting, aggregation, group & aggregation and joins. The computing tool is also equipped with branch statement, loop statement and agile syntax to implement complicated business logic effortlessly. It allows setting a breakpoint and using trace & debug to facilitate quick error correction; and offers the standard JDBC interface through which the stored procedure, which is saved as a separate script file, can be invoked by the up-level Java program. You can edit the script file as needed and won’t affect the Java code and Hive service. To bring the interface down, esProc supports standard Hive JDBC as well as high-performance proprietary interface to execute HSQL statements.

Here’s an example. The Hive table sales is grouped and summarized by seller, year and month as follows:

A query with stored procedure could be this. We need to adjust the amounts for each seller by transferring 1000 from April’s amount to March’s for each seller in each year. If the amount in March is null, append an empty record with amount -1000 to it to balance the account; if the amount in April is null, append an empty record with amount 1000 to it; if both months have nulls, no balancing will be made.

Below is the expected result:

esProc script for implementing the stored procedure:

Read How to Call an SPL Script in Java to learn how to deploy esProc JDBC to call the SPL script.

About esProc installation, free license download and relevant documentation, see Getting Started with esProc.