DEV Community

Cover image for Aerospike JDBC driver: SQL DDL compliance
Alexander Radzin
Alexander Radzin

Posted on • Updated on

Aerospike JDBC driver: SQL DDL compliance

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.

Previous

Introduction to the Aerospike JDBC driver

Top comments (1)

Collapse
 
alexradzin profile image
Alexander Radzin
  1. Thank you for your comment about the drop index example. I've fixed it.
  2. Indeed my version of create index does not follow SQL standard. Unfortunately discovery of types from data when creating index is not always possible: you can create index when data is not populated yet. However you are right: if index is created when data is already populated, shorter (standard) SQL syntax may be supported.