My previous post explained SQL DDL commands supported by the Aerospike JDBC driver. This post will discuss the DML commands.
Data manipulation language consists of the following commands:
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 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
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
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
SELECT statements are supported.
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.
insert in AQL are similar. Both commands add new record if specified
PK does not exist and update record if
Update implemented by Aersoplike SQL driver behave like the standard SQL update. It updates records found by criteria specified in
WHERE expression can be complex (see
DELETE implemented by AQL that can delete entries only identified by
delete works with regular
where clause (see
The project is available in GitHub.
Next article of this series will explain how to use built-in functions.
Aerospike JDBC driver: SQL DDL compliance