Secondary Index Support

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. Filter do not support Double and Float data types. null values are not index by Aerospike database. Hence, if an implicitly-constructed filter (i.e., when aerospike.sindex.filter is not set) contains null, the query will 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 i.e. 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.