Aerospike multiple filter query?

Question

Reading the documentation did not help me much.

1)As I understood, there is no ability to use multiple filters at one query. If so, how with Aerospike java client API, I write such as query:

SELECT * FROM TABLE_NAME WHERE COLUMN1 = 1 AND COLUMN2 = 2

2) If there is no possibility to use JAVA CLIENT API for that purpose, must I write own UDF for filtering the data?

3) If I write own UDF (that filteres data), would it be fast or should I use secondary-index in order to make it's execution faster?

Answer 1

Predicate filtering was added in release 3.12 on March 15. You can use the PredExp class of the Java client to build complex filters such as the one you mentioned. It also currently exists for the C, C# and Go clients.

Predicate filtering can be used to build as complex of a filtering logic as you need, and its executed natively, not through a UDF. You can apply a record UDF to any record matched by your filter, if you need to. If you're just trying to get all the records that fit a specific complex predicate criteria don't use a UDF, just the predicate filter itself.

A predicate filter can run faster on top of a secondary index, but that's not required. Without one it'll run against a scan which returns all the records in a namespace/set, applying the filter to each. If you do have a secondary index, make sure to order the WHERE clause to use it. As currently there is no server-side query optimizer, you should optimize the query manually by considering the order of the predicates.

For example, assume you have a set of users with bins gender, age, and name, with the first two indexed, and are looking for all female users between 35 and 44 whose name starts with 'S'. You want to set the Filter for the Statement to use the index which returns the fewest records from a query. In this case, using the index on age would return fewer records than gender, and execute faster. Within the PredExp you also want to order the predicates to short-circuit and skip a record being matched as soon as possible. In this case you likely want to compare the gender bin value before the more expensive string regular expression on name.

Answer 2

Strictly speaking, you cannot get a set of records back directly from an equivalent implementation of the multiple filter query you list. But there is a round about way. You can create SI (secondary-index) on column1 to get the first subset of records, feed that recordset to a stream udf where you can only read records, filter them by column2, return a map of return values. In UDFs, you cannot return a record or set of records or stream subset. However, if you store your record key as a bin in each record, you could return a map of keys and then have access to that subset of records in your application.