Support for secondary index

Aerospike 6.0 EE introduces extensive support for secondary index. The Spark connector leverages the secondary index to reduce query latency.

Prerequisites

• Aerospike database 6.0 or later
• Spark connector 3.4.0 or 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.

• In the case of an incompatible combination of aerospike.sindex and aerospike.sindex.filter, the database will throw an error.

• 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 or STRING | | 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", "value": "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("2020-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,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" : 1}	Indexed list	Find all records whose bin1 (of ArrayType) containing 10 as one of the values.
    { "name": "bin1", "type": "NUMERIC", "colType": 2, "value" : 1}	Indexed map keys	Find all records whose bin1 (of MapType) containing 10 as map keys.
    { "name": "bin1", "type": "NUMERIC", "colType": 3, "value" : 1}	Indexed map values	Find all records whose bin1 (of MapType) containing 10 as map values.