Support for secondary index

Aerospike Database Enterprise Edition 6.0 introduces extensive support for secondary indexes. The Spark connector leverages the secondary index to reduce query latency.

Prerequisites

• Aerospike Database Enterprise Edition 6.0 and later
• Aerospike Connect for Spark 3.4.0 and later

Notes on filter string aerospike.sindex-filter:

• Connector supports equal, range, contains filters. These filters can be specified as a JSON string.

• Filters support NUMERIC and STRING types. NUMERIC types can be Long, Int, Short, Date, or TimeStamp. Filters do not support Double and Float data types. null values are not indexed by Aerospike database. If an implicitly-constructed filter contains null when aerospike.sindex-filter is not set, the query does not use the provided secondary index.

• aerospike.sindex must be set whenever you use aerospike.sindex-filter. If it is not, the connector throws an error before the query reaches the database. If the aerospike.sindex-filter JSON is malformed or unsupported, the connector does not throw an error. Instead, it logs an error and a warning, then runs the query without the secondary index.

• equal filter

  • Only supports scalar types (collection data types not supported)

  • JSON Key	Description
    name	bin name
    type	datatype NUMERIC or STRING
    value	value of the bin

  • Examples	Equivalent SQL
    { "name": "bin1", "type": "NUMERIC", "value": 10}	select * from namespace where col("bin1") == 10
    { "name": "bin1", "type": "NUMERIC", "value": "2012-10-03"}	select * from namespace where col("bin1") == java.sql.Date.valueOf("2012-10-03")
    { "name": "bin1", "type": "NUMERIC", "value": "2020-12-12 01:24:23"}	select * from namespace where col("bin1") == java.sql.Timestamp.valueOf("2020-12-12 01:24:23")
    { "name": "bin1", "type": "STRING", "value": "McDonalds"}	select * from namespace where col("bin1") == "McDonalds"

• range filter

  • Only supports NUMERIC scalar types (collection data types not supported).

  • | JSON Key | Description | | ------------- |:-------------: | | name | bin name| | type | datatype NUMERIC | | begin| start of the range | | end| end of the range |

  • | Examples | Equivalent SQL | | ------- |:------: | { "name": "bin1", "type": "NUMERIC", "begin": 10, "end" : 20}| select * from namespace where col("bin1") >= 10 and col("bin1") <= 20 | { "name": "bin1", "type": "NUMERIC", "begin": "2012-10-03", "end" : "2013-10-03" }| select * from namespace where col("bin1") >= java.sql.Date.valueOf("2012-10-03") and col("bin1") <= java.sql.Date.valueOf("2013-10-03") | { "name": "bin1", "type": "NUMERIC", "begin": "2020-12-12 01:24:23", "end" : "2021-12-12 01:24:23"}| select * from namespace where col("bin1") >= java.sql.Timestamp.valueOf("2020-12-12 01:24:23") and col("bin1") <= java.sql.Timestamp.valueOf("2021-12-12 01:24:23")|

• contains filter

  • Supports scalar and collection data type of NUMERIC and STRING types.

  • JSON Key	Description
    name	bin name
    type	datatype NUMERIC or STRING
    colType	column type, or its equivalent numerical value (0, 1, 2, or 3)
    value	value of the bin

  • Examples	Index Type	(Not So) Equivalent SQL
    { "name": "bin1", "type": "NUMERIC", "colType": "DEFAULT", "value" : 1}		select * from namespace where col("bin1") ==1
    { "name": "bin1", "type": "NUMERIC", "colType": 1, "value" : 10}	Indexed list	Find all records whose bin1 (of ArrayType) contain 10 as one of the values.
    { "name": "bin1", "type": "NUMERIC", "colType": 2, "value" : 10}	Indexed map keys	Find all records whose bin1 (of MapType) contain 10 as map keys.
    { "name": "bin1", "type": "NUMERIC", "colType": 3, "value" : 10}	Indexed map values	Find all records whose bin1 (of MapType) contain 10 as map values.