Introducing Aerospike JDBC Driver

#aerospike #nosql #jdbc #java

One of the most common ways to interact with databases on JVM is the JDBC API.

JDBC stands for Java Database Connectivity, which is a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases.

Aerospike is a fast and durable No-SQL database. It has its own Java client, but this article will introduce you to a supplementary way of interacting with it using SQL.

Get yourself a hot cup of tea or coffee (for the true Java look and feel) and we will dive into the details of the Aerospike journey into the JDBC world.

Introduction

The Aerospike JDBC driver requires Java 8 and is compliant with JDBC 4.2. Also, Aerospike server 5.2+ is required, because the driver uses the new Filter expressions.

The first release of the JDBC driver supports the following SQL statements:

SELECT
INSERT
UPDATE
DELETE

You can also add WHERE conditions and LIMIT your queries. JOINS, ORDER BY and GROUP BY are not supported.
From the very beginning, the driver was designed to support operations that could be done using the regular Java client, without UDFs and other compute and memory hungry components. So, the original intention is to keep it small and easy to start, without workarounds to support features that aren’t native to the Aerospike Database.

The driver doesn’t support SQL functions as well as the Aerospike collection data types (CDTs).

Getting Started

Install the Aerospike JDBC driver and add the location of it to your classpath.
You can take the JAR file from the releases, add a Maven dependency, or build it from the sources.

The Aerospike JDBC driver is statically registered in the AerospikeDriver class. So the only thing required is to load this class.

Class.forName("com.aerospike.jdbc.AerospikeDriver").newInstance();

The next thing you’ll need to do is to specify the JDBC URL. The URL template is:

jdbc:aerospike:HOST[:PORT][/NAMESPACE][?PARAM1=VALUE1[&PARAM2=VALUE2]

For example the jdbc:aerospike:localhost URL will connect to the Aerospike database running on a local machine and listening on the default port (3000). The jdbc:aerospike:172.17.0.5:3300/test URL connects to the test namespace on the Aerospike database running on 172.17.0.5:3300.

After the initial setup let’s see a simple usage example of it:

try {
    String url = "jdbc:aerospike:localhost:3000/test";
    Connection connection = DriverManager.getConnection(url);

    String query = "select * from ns1 limit 10";
    ResultSet resultSet = connection.createStatement().executeQuery(query);
    while (resultSet.next()) {
        String bin1 = resultSet.getString("bin1");
        System.out.println(bin1);
    }
} catch (Exception e) {
    System.err.println(e.getMessage());
}

JDBC Client tools

You can browse and manipulate data in Aerospike with any of the available SQL client tools using the JDBC driver.

There are a number of multiplatform and free database tools available like DBeaver, SQuirreL, and others.

Here are the steps to configure the DBeaver SQL Browser with the Aerospike JDBC driver:

Database -> Driver Manager -> New Fill in settings:
- Driver Name: Aerospike
- Driver Type: Generic
- Class Name: com.aerospike.jdbc.AerospikeDriver
- URL Template: jdbc:aerospike:{host}[:{port}]/[{database}]
- Default Port: 3000
Click the Add File button and add the JDBC jar file.
Click the Find Class button.
Click OK.

Create a connection:

Database -> New Database Connection
Select Aerospike and click Next.
Fill in the connection settings
- Host and Port
- Database/Schema: the namespace you are connecting to
- Username and Password if you have security turned on in Aerospike Database Enterprise Edition
Click Finish.

Now you can open an SQL editor and query your Aerospike cluster:

Summary

The Aerospike JDBC driver is in its very early stages. It will be great if you could try it and give us some feedback. Any contributions to the project are very welcome.

Check out my previous Aerospike SQL series if you haven’t done this yet.

And don’t forget to subscribe to the Aerospike developer blog to get updated with our latest news.