We added support for scalar user-defined functions in our recent release, VoltDB V7.6. With this feature, users can write their own SQL functions in Java and load them into VoltDB. Those functions, once loaded, can be used in all kinds of SQL queries just like any other built-in SQL functions. User-defined functions can give you great flexibility in customizing your business logic inside the database. In this blog post, we will use a specific example to show how user-defined functions can benefit machine learning applications.

For example, classification is a fundamental problem in machine learning and statistics. Given an input data set, a classification model can make a prediction and assign each data sample to one of several predefined classes. Commonly used classification models include logistic regression, naive Bayesian classifiers, support vector machines, decision trees, and neural networks. Here, we will choose the naive Bayesian classifier and the k-nearest neighbors algorithm as two examples to illustrate how user-defined functions can simplify business applications.

The dataset we are going to use in our examples is the iris data set. The iris data set is a multivariate data set introduced by the British statistician and biologist Ronald Fisher. It contains in total 150 data samples selected evenly from three species of iris (Iris setosa, Iris virginica, and Iris versicolor). Four features were measured for each sample: the length and the width of both the sepals and petals, in centimeters.

The original data set comes in the CSV format. To store it in the database, we first define the iris table as follows:

Table iris includes four columns for different feature values and a target class column which records the real species of each sample. To load the data, VoltDB provides a command line utility, CSVLoader, which can import the contents of a CSV file and insert it into a VoltDB table.

A typical machine learning application involves two parts: learning and scoring. VoltDB strives for providing solid support for fast data applications, where the fast incoming data stream is summarized and processed in real-time. That is, VoltDB can apply machine learning models on the incoming data stream for real-time decision making with high throughput and low latency. Building the models on the historical data, however, is normally done on a back-end large-scale big data analytics platform. VoltDB can export the live transaction data into those platforms for incremental model updates and sync up with the back-end system to get the updated model for future data processing.

In our example, we include the learning part for completeness. So we first build the classification models (the naive Bayesian classifier and the k-nearest neighbor classifier) from the iris data set. The naive Bayesian classifier is a simple probabilistic classifier based on applying the Bayes' theorem with strong (naive) independence assumptions between the features. While the k-nearest neighbor classifier classifies the samples based on the target class of their nearest neighbors. The technical discussion about how those models can be computed from the training data set is out of the scope of this blog post. We encourage the audience to refer to numerous resources that are available on the Internet to get more familiarized with those models if you are interested. Here, we create a Java stored procedure in VoltDB to scan the input table and learn the models leveraging the algorithm implementations provided by the Java-ML library.

Please note that the input data table in this example is replicated, i.e., no partitioning column is defined and each partition will contain a full copy of the table data. This is only for the simplicity of the example because every partition will run the same learning method on the same data, no model synchronization between partitions is required. The data set can be partitioned in order to achieve maximum performance if VoltDB is only used for real-time decision making, i.e. data scoring.

The code for the VoltDB Java stored procedure to train the k-nearest neighbor classification model is shown below (the code for the naive Bayesian classifier is almost the same):

The classifier knnc is a static member of the IrisKNNClassifier. When the stored procedure is run on all partitions, it learns the model and stores it with the class. In a real-world use case, this procedure should get its model from an external analytics system.

To apply the model, we create a Java method in the same class as the stored procedure method in order to access the model data. The code for the Java method is shown below:

The Java method takes four double type parameters. VoltDB supports both boxed types and primitive types in the UDF method. it automatically maps the Java types to the VoltDB SQL data types. For more information, see our documentation. Basically, what this function does is it gets the data that is passed in as parameters, applies the model, and returns the class prediction.

The compilation process of a UDF is the same as that of a stored procedure. It is also perfectly fine to define a stored procedure and UDFs together. After the JAR file is compiled, we can easily load the procedures and the UDFs by executing the following commands:

In this example, we show the potential of Java user-defined functions with a brief use case. User-defined functions unlock the full power and flexibility of the Java programming language and environment. With tons of external libraries that UDFs can link to, there is a whole bunch of things you can do inside the database. The source code for this example is available on GitHub. We hope you find the feature helpful in designing your fast data application, and please let us know what you think!

Consuming AI in byte sized applications is the best way to transform digitally. #BuiltOnAI, EdgeVerve’s business application, provides you with everything you need to plug & play AI into your enterprise. Learn more.