Extending the Aerospike JDBC driver with UDFs
Tags: aerospike, java, sql, driver
Published: 2020-06-02 19:25:59

#Preface
This post continues series of articles that present the [Aerosplike SQL driver](https://dev.to/alexradzin/introduction-to-the-aerospike-jdbc-driver-51m4).

#Introduction
Previous [post](https://dev.to/alexradzin/functions-supported-by-the-aerospike-sql-driver-2l0p) explained the built-in functions provided by the Aerospike JDBC Driver. Here I am going to explain how to extend the functionality of the driver by implementing User Defined Functions (UDF).

#Implementation of UDF
Right now aggregate functions cannot be customized, however you can implement scalar and collection functions using Java programming language. 

Function is a named block of reusable code that used to perform single action. Typically functions return result. Functions may accept arguments. 

Java 8 and higher provides interfaces `Supplier`, `Function`, `BiFunction`. Implementations of these interfaces can be defined as UDF for the Aerospike JDBC driver. For example here is definition of function `now()` that returns epoch in milliseconds:

```java
public class Now implements new Supplier<Long>() {
    @Override
    public Long get() {
        return System.currentTimeMillis();
    }
};
``` 

This is example of function that receives one argument:
```java
public class Lower implements new Function<String, String>() {
    @Override
    public String apply(String s) {
        return s == null ? null : s.toLowerCase();
    }
};
```

Implementation of function that accepts 2 arguments is similar. Create class that implements `BiFunction`. Unfortunately JDK does not define interface for function that accepts 3, 4 or more arguments as well as function that accepts any number of arguments. Such interface is defined by the driver:
```java
package com.nosqldriver.util;
@FunctionalInterface
public interface VarargsFunction<T, R> {
    R apply(T ... t);
}
```
If you want to implement UDF that accepts more than 2 arguments you have to implement this interface. In this case you have to add the driver to the compilation class path. Implementation of other types of functions does not require this dependency. Implementation of `VarargsFunction` has yet another complexity: the programmer is responsible on casting and verification of accepted arguments. 

#Deploying of UDF
Once UDF is implemented and verified it should be packed into jar file that should be added to the classpath of the driver. Various tools allow this. For example [Dbeaver](https://dbeaver.io/) and [SquirreL](http://squirrel-sql.sourceforge.net/) allow defining driver packaged in several jar files. Once this is done the the function should be registered using connection parameter:
```
custom.function.NAME=FULLY_QUALIFIED_CLASS_NAME
```
e.g.
```
custom.function.now=com.mycompany.Now
```

The connection parameter can be supplied as a parameter of JDBC URL or as a connection property. 
```
jdbc:aeropspike:myhost?custom.function.now=com.mycompany.Now
```

Once function is registered it can be used in SQL query:
```sql
select first_name, now() from people
```

#Conclusions
[Aerospike JDBC Driver](https://github.com/alexradzin/aerospike-jdbc-driver) provides a comprehensive set of built-in functions. Moreover it is extendable using User-Defined Functions that can be easily implemented using Java programming language. 

#Project home
The project is available in [GitHub](https://github.com/alexradzin/aerospike-jdbc-driver).

#What's next
Next article of this series will explain how to work with complex types.