Popular programming languages lack special class libraries for processing structured data. They hardcode all basic structured algorithms, such as data retrieval, sorting and aggregation, from scratch; have to write lengthy chunks of code for commonly-used algorithms, such as group & aggregate and joins; and take great trouble to create custom classes to achieve a complex algorithm. Such an example is Java.

The tightly coupled language is able to achieve any algorithm if performance and efficiency is not your priority. Take stored procedures for example. A stored procedure written in Java is tightly coupled to the Java program rather than in its natural independent status. Any change to it will result in a re-package of the whole project, which means very high maintenance costs. But it shouldn’t have been like that and could have been much easier if you had used esProc to implement the stored procedure for Vertica.

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 offers the standard JDBC interface through which the stored procedure, which is saved as a separate script file, can be invoked by the Java program. You can edit the script file as needed and won’t affect the Java code.

For instance, the sales table in Vertica stores orders data for each salesperson:

Suppose I need to program such logic. For each salesperson find their top 3 and bottom 3 orders in terms amount and mark them with “top3” and “bottom3”. Since our goal requires a certain number of orders, we won’t find top3 orders for a salesperson who has less than 3 orders and bottom3 orders for one who has less than 6 orders. Below is the expected result:

SPL script:

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.