DEV Community: Alexander Radzin

Extending the Aerospike JDBC driver with UDFs

Alexander Radzin — Tue, 02 Jun 2020 19:25:59 +0000

Preface

This post continues series of articles that present the Aerosplike SQL driver.

Introduction

Previous post 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:

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

This is example of function that receives one argument:

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:

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 and SquirreL 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:

select first_name, now() from people

Conclusions

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.

What's next

Next article of this series will explain how to work with complex types.

Functions supported by the Aerospike SQL driver

Alexander Radzin — Fri, 29 May 2020 05:29:31 +0000

Preface

This post continues series of articles that present the Aerosplike SQL driver

Introduction

There are two types of SQL functions, aggregate functions, and scalar(non-aggregate) functions. Aggregate functions operate on many records and produce a summary, works with GROUP BY whereas non-aggregate functions operate on each record independently.[1]

This statement is correct for the most of relational databases that can store only simple type in one cell. Aerospike supports built-in lists and maps. Aerospike JDBC driver provides several functions that operate these types. Since lists and maps are not scalars we can define 3 types of functions provided by the Aerospike JDBC driver:

scalar functions (that operate scalar types into one record)
collection functions (that operate lists and maps into one record)
aggregate functions that operate many records

Built-in scalar and collection functions

Name	Description
`len(x)`, `length(x)`	returns length of given string, list, map
`ascii(c)`	returns ASCII code of given character
`char(c)`	returns char corresponding to the given ASCII code
`locate(subStr, str, [offset=1])`	returns position of `subStr` into `str` starting from `offset` (that is 1 if omitted)
`instr(subStr, str)`	returns position of `subStr` into `str`
`trim(s)`	trims string (removes spaces from both sides)
`ltrim(s)`	removes leading spaces from the string
`rtrim(s)`	removes trailing spaces from the string
`strcmp(s1, s2)`	compares given strings
`left(s, n)`	returns substring of length `n` starting from the beginning of the given string
`lower(s)`, `lcase(s)`	converts a string to lower-case
`upper(s)`, `ucase(s)`	converts a string to upper-case
`str(x)`	returns string representation of given value (like `toString()` in java)
`space(n)`	returns string that contains `n` spcaces
`reverse(s)`	reverses given string
`to_base64(bytes)`	generates Base64 representation of given byte array
`from_base64(str)`	returns byte array from given Base64
`substr(str, from, to)`	returns substring of given string
`concat(str1, str2, ...)`	concatenates given strings
`concat_ws(separator, str1, str2, ...)`	concatenates given strings using separator
`date([x])`	creates `java.util.Date` from either string or epoch. If argument is not provided this function returns date that represents current time.
`calendar([x])`	similar to `date([x]) but returns`java.util.Calendar`
`now()`	returns epoch (ms)
`year([x])`, `month([x])`, `dayofmonth([x])`, `hour([x])`, `minute([x])` ,`second([x])`, `millisecond([x])`	return year, month, etc respectively. Optional argument may be either formatted date or epoch in milliseconds. If it is not provided current time is used.
`map(s)`, `list(s)`, `array(s)`	create map, list and array respectively from their string representation

Built-in aggregate functions

The aggregate functions allow you to perform a calculation on a set of values to return a single scalar value. The following functions are supported:

count()
sum()
max()
min()
avg()
sumsqs() - sum of squares of values

Distinct is considered a modifier but syntactically looks like are function and is supported as well.

For performance and better memory consumption reasons aggregate functions are implemented in Lua and run on server side (into Aerospike DB).

Project home

The project is available in GitHub.

What's next

Next article of this series will explain how to add custom function to the driver.

Aerospike JDBC driver: SQL DML compliance

Alexander Radzin — Wed, 27 May 2020 16:35:03 +0000

Introduction

My previous post explained SQL DDL commands supported by the Aerospike JDBC driver. This post will discuss the DML commands.

DML - data manipulation language

Data manipulation language consists of the following commands:

INSERT
SELECT
UPDATE
DELETE

Aerospike JDBC driver treats Aerospoike sets as tables, and bins as columns. However Aerospike is not relational database. It treats key and value differently. Key is not a regular column. Aerospike JDBC driver uses special identifier PK to refer the primary key.

`INSERT`

Insert for Aerospike is more than insert. Since Aerospike is schemaless, schema is actually defined by the data. Table (set) is created when data is written there. The actual data "defines" the table schema, i.e. types and name of table columns (or set bins using Aerospike terms.)

Insert syntax is not differ from syntax of regular SQL. You can either insert one row:

insert into people (PK, first_name, last_name, year_of_birth) values (1, 'John', 'Lennon', 1940)

or several rows:

insert into people (PK, first_name, last_name, year_of_birth) 
values 
(1, 1, 'John', 'Lennon', 1940), 
(2, 'Paul', 'McCartney', 1942)

Important: primary key (PK) is required in INSERT statement.

`SELECT`

Select statement is used in order to retrieve data from the table. Either explicit definition of columns to be retrieved or * are supported:

select first_name, last_name from people
select * from people

Data can be filtered using WHERE statement:

select * from people where name = 'John'

Unlike AQL where clause here can use expressions, calculations, functions, parentheses and refer PK or any bin name (column) either indexed or not. Logical operations <, <. >=, <=, =, !=, IN operator and LIKE statement for textual fields are supported as well. BETWEEN statement is supported for numeric values only.

select * from people where name in ('John', 'Bill')
select * from people where PK in (1, 2, 3)
select * from people where PK BETWEEN (1, 42)
select * from people where id + 5 < 10

Rows returned by select can be sorted using ORDER BY and sliced using LIMIT and OFFSET. Inner SELECT statements are supported.

JOIN

Select can be used with join. Inner and left joins are supported. The join expression can use equality expression on any column. PK can be used for the right table. Using PK on the left table is possible if PK is stored in the DB (that does not happen by default). One of the next posts will be dedicated to details of using PK in queries.

`UPDATE`

Update and insert in AQL are similar. Both commands add new record if specified PK does not exist and update record if PK exists. Update implemented by Aersoplike SQL driver behave like the standard SQL update. It updates records found by criteria specified in WHERE clause. WHERE expression can be complex (see SELECT).

`DELETE`

Unlike DELETE implemented by AQL that can delete entries only identified by PK this delete works with regular where clause (see update).

Project home

The project is available in GitHub.

What's next

Next article of this series will explain how to use built-in functions.

Aerospike JDBC driver: SQL DDL compliance

Alexander Radzin — Tue, 26 May 2020 18:37:09 +0000

Introduction

My previous post has introduced to reader the Aerospike JDBC driver. This post explains one of its feature - DDL.

DDL - data definition language

Typical relational database requires schema definition. DDL commands can create schemas, tables, indexes etc. Aerospike is a schemaless database. Namespace cannot be created dynamically: it must be configured using configuration file. Aerospike set (similar to table in a relational database) should not be created explicitly: it is created automatically when data is being written.

Secondary indices exist in Aerospike as well as relational databases and can be created and dropped dynamically. Aerospike JDBC driver supports commands CREATE INDEX and DROP INDEX.

Relational database "knows" type of indexed field, so it should not be mentioned in "CREATE INDEX" statement. Aerospike is schemaless DB, so the index type is defined when index is being created:

CREATE STRING INDEX INDEX1 ON MYTABLE (SOME_FIELD)

Generally syntax of create index statement looks like

CREATE <type> INDEX <index_name> ON <set_name> (<bin_name>)

The following index types are supported:

STRING
NUMERIC
GEO2DSPHERE
STRING MAPKEYS
STRING MAPVALUES
NUMERIC LIST

Dropping index is simpler:

DROP INDEX <set_name>.<bin_name>

For example:

DROP INDEX MYTABLE.INDEX1

Project home

The project is available in GitHub.

What's next

Next article of this series will explain the SQL DML compliance.

Introduction to the Aerospike JDBC driver

Alexander Radzin — Mon, 25 May 2020 15:36:23 +0000

Introduction

Once upon a time when all databases were relational and supported SQL, Java programmers (almost) did not care about the database type they were going to use. This was achieved by using JDBC drivers - software that talks to the specific database exposing standard API to the application layer.

The world has changed after the invention of NoSQL databases. These databases have some advantages comparing to the traditional relational databases but they do not have common interface. Therefore, using them requires writing specific application code for each database.

Aerospike is a popular, fast, mature NoSQL database. It provides client libraries for different systems and languages including Java. These libraries help to implement applications that use Aerospike DB. However, application code which uses this library has to be specific for Aerospike.

The Aerospike JDBC Driver enables users to connect with Aerospike DB directly from any application that supports standard JDBC connectivity.

Key features

This is a JDBC driver. It can be used by any JDBC complaint application, e.g. UI, monitoring, querying and other general purpose tools. It supports standard SQL statements and functions and can be used by developers, QA, data analysts and DevOps engineers. It can help if somebody wants to migrate his application from relational DB to Aerospike.

This article will briefly enumerate the key features of the driver. The next articles of this series will explain particular features in depth.

SQL compliance

DDL

CREATE INDEX
DROP INDEX

DML

INSERT
SELECT
UPDATE
DELETE

WHERE - using PK or any other column and the combination of them including calculations, parentheses etc.
JOIN, LEFT JOINGROUP BY`

Data types

The driver supports all basic Aerospike types (integer, string, bytes,
double) mapping them to JDBC types (INTEGER, VARCHAR, BLOB, DOUBLE) respectively and corresponding java types (int, String, byte[], double). Aerospike lists and maps are supported as well. They are mapped to the JDBC java.sql.Array and java.util.Map respectively.

Additionally driver can store and retrieve java objects using either standard java serialization mechanism (for Serializable or Externalizable classes) or using custom serialization/deserialization) enabled by user defined functions.

Expressions and functions

Mathematical expressions can be used in the queries:

select first_name, last_name, year()-year_of_birth as age from people

Built-in Functions

aggregation functions: count(), max(), min(), sum(), avg()
string functions: str(), concat(), ucase(), lcase(), len(), reverse() etc.
date/time functions: now(), year(), month(), day() etc.

User-defined functions

The driver provides a lot of built-in functions. Moreover, it can be easily extended. User can implement his own functions in Java and register them via JDBC connection parameters.

Project home

The project is available in GitHub.

What's next

Next article of this series will explain the SQL compliance of the driver.