Sorted Results from a Secondary Index Query in Aerospike — Part I
Aerospike, a distributed NoSQL database, stores records distributed evenly across all nodes of the cluster using a hash value derived from the user’s key. While this scheme gives excellent performance at scale for reading and writing records, when running secondary index queries on a set of records in a namespace, it presents the challenge of getting sorted results to the client.
For many use cases, users need the query results sorted. For example, in a data set of residents of cities, where each resident’s information is an individual record stored in a persistent namespace on disks, we may want to find all residents on a particular street, sorted by their house numbers.
Natively, while you can run this kind of Secondary Index query combined with predicate filtering in Aerospike, as of version 4.6, you will get the matching records in no particular order. The onus then falls on the client to accumulate the results and sort, and optionally paginate. This requires both compute and memory resources on the client side.
However, Aerospike has two neat options at our disposal to let Aerospike do this work for us. First, Aerospike offers in-memory namespaces where a record can grow literally to whatever size allowed by the available memory. There are ingress and egress limitations of 128MiB in the network socket buffers, but for our purpose, we will update the record incrementally and not retrieve the entire record if it exceeds 128MiB (we can paginate on retrieval). Second, the Map data type in Aerospike takes key:value inserts and can be configured to store and index them by key order. Map Operation APIs are available to extract the associated value in key sort order, either all or paginated by count.
Example: We have data on people — name, house#, street name, city, county — millions of records. We want to search for all the people on a given street by doing a partial match on street name. However, we want the result set sorted and paginated by house number. Multiple people may live in the same house, so multiple record entries may be present for the same house number.
We will take each record on disk extracted using a predicate filtered query and store in a scratch record, in an in-memory namespace as a map data type (key-value pair) where the key is carefully constructed
//Select key for desired sort order
k = record.getString(“housenum”)+ record.getString(“name”); to yield the sort order that we need.
The scratch record (in-memory) is deleted prior to, and can also be deleted after, each run.
The queryId passed by the user is used as the primary key for the scratch record to keep each query’s scratch space separate from others. Users can devise efficient ways to create unique queryIds based on their use case.
Using Map APIs, we can easily retrieve results, from the scratch record, sorted by index and we can also limit by count (paginate).
With fail on cluster change and Strong Consistency mode guarantees, this can be enhanced with code exceptions for timeouts and deliver consistent performance even under cluster network events.
The key idea is to utilize an in-memory namespace without record size limitation, move the storage and compute to the server and exploit the capabilities of the Map and List APIs. Alternatively, one can also use sorted lists data type to achieve a similar implementation, whatever works better for the data model at hand.
In Part II, we will explore the List data type implementation for sorting the result set.
Sample implementation is shown below:
public ArrayList<String> getPeopleStrings(String ns, String sn, String street, String queryId) { ArrayList<String> lPeople = new ArrayList<String>();
if(street.isEmpty()) return lPeople;
if(queryId.isEmpty()) return lPeople;Record record = null;
Statement stmt = new Statement();
stmt.setSetName(sn); // "people"
stmt.setNamespace(ns); // "test", a disk storage namespace
// If SI filtering is needed, add here.
stmt.setIndexName("county_idx");
stmt.setFilter(Filter.equal("county","Santa Clara"));
stmt.setPredExp(
//match part of the string (street name) passed
//by the user on record bin "street"
PredExp.stringBin("street"),
PredExp.stringValue(".*"+street+".*"),
PredExp.stringRegex(RegexFlag.ICASE | RegexFlag.NEWLINE)
); QueryPolicy queryPolicy = null;
RecordSet recordSet = client.query(queryPolicy, stmt);
Key tempKey = new Key("scratch", "temp", queryId);
//scratch is an in-memory namespace MapPolicy mPolicy = new MapPolicy(MapOrder.KEY_ORDERED,
MapWriteFlags.DEFAULT);
//Must use KEY_ORDERED to retrieve values in sorted key order. int size = 0;
Record tempRec = null;
String k;
String v;
client.delete(null, tempKey); while (recordSet.next()) {
record = recordSet.getRecord();
v = record.getString("housenum")+" "+
record.getString("street") +" "+
record.getString("city")+" "+
record.getString("county")+" [" +
record.getString("name") +"]"; //We reduced v to a string for simplicity -
//could be kept as a list of items. //Select key for desired sort order
k = record.getString("housenum")+ record.getString("name"); tempRec = client.operate(null, tempKey,
MapOperation.put(mPolicy, "tempBin",Value.get(k),
Value.get(v))); //save values to scratch space in key order
//mPolicy is KEY_ORDERED
} if(tempRec!=null) {
size = tempRec.getInt("tempBin"); //Use size to paginate if desired. If expected size
//is less than 128MiB, entire record can be retrieved.
//Sorted page data is fetched in a single read operation. tempRec = client.operate(null, tempKey,
MapOperation.getByIndexRange("tempBin",0,size,
MapReturnType.VALUE)); List<?> ll = tempRec.getList("tempBin"); for(int i=0; i<size; i++) {
lPeople.add((String)ll.get(i));
}
}
return lPeople;
}
