Skip to main content

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 KeyDescription
      namebin name
      typedatatype NUMERIC or STRING
      valuevalue of the bin
    • ExamplesEquivalent 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 KeyDescription
      namebin name
      typedatatype NUMERIC or STRING
      beginstart of the range
      endend of the range
    • ExamplesEquivalent 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 KeyDescription
      nameBin name
      typeDatatype NUMERIC or STRING
      colTypecolumn type or its equivalent numerical value i.e. 0,1,2,3
      valueValue of the bin
    • ExamplesIndex 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 listFind all records whose bin1 (of ArrayType) containing 10 as one of the values.
      { "name": "bin1", "type": "NUMERIC", "colType": 2, "value" : 1}Indexed map keysFind all records whose bin1 (of MapType) containing 10 as map keys.
      { "name": "bin1", "type": "NUMERIC", "colType": 3, "value" : 1}Indexed map valuesFind all records whose bin1 (of MapType) containing 10 as map values.