Hi All,
I am trying to do a regex query on the primary index and get matching records to process further. I am new to aerospike and have been trying to use filter expressions but unable to figure out the correct usage. I am using the golang client library
Example:
Namespace: “namespace1”
Set: “set1”
keys like **apple**orange**grapes**
I need to query for all the keys containing “apple”
Please help me out with the usage to achieve this
Thanks and Regards Sriteja
I actually don’t think Aerospike supports Expressions against the primary key at this point (the keys are actually not stored by default and the sendKey policy has to be used for the keys to be stored, but even then, I don’t think Expressions can target the keys).
You could leverage Expressions against a bin if the key is stored in a bin. Let’s see if others have alternate / potentially better suggestions.
With Expressions, it is possible to do regex search on Key, but as @meher said, you must store the key on the server. You can either store it via sendKey set to true, or you can also store in a record bin.
Code below shows example if you do sendKey true. (similar code will be applicable if the string was in the bin with exp build call modified to use string in bin instead that of the stored key).
I populated the test data (using aql) as follows:
INSERT INTO test.testset (PK, name, age) VALUES ('jacksapplekey1', 'Jack', 26)
INSERT INTO test.testset (PK, name, age) VALUES ('jillgrapekey2', 'Jill', 20)
INSERT INTO test.testset (PK, name, age) VALUES ('mangokey3', 'James', 38)
INSERT INTO test.testset (PK, name, age) VALUES ('Jimkey4apple', 'Jim', 46)
INSERT INTO test.testset (PK, name, age) VALUES ('JuliaGrapekey5', 'Julia', 62)
INSERT INTO test.testset (PK, name, age) VALUES ('SallyMANGOkey6', 'Sally', 32)
INSERT INTO test.testset (PK, name, age) VALUES ('SeanaPPlekey7', 'Sean', 24)
INSERT INTO test.testset (PK, name, age) VALUES ('SamGRAPEkey8', 'Sam', 12)
INSERT INTO test.testset (PK, name, age) VALUES ('Susankey9', 'Susan', 42)
INSERT INTO test.testset (PK, name, age) VALUES ('SandraPeachkey0', 'Sandra', 34)
Then I added a secondary index on age bin (just for fun). So I will select records based on an age range AND that have apple (with case ignored flag) in their primary key.
Here is the code in Java: (Aerospike Golang client … should have similar APIs available.)
//Needed imports
import com.aerospike.client.query.Statement;
import com.aerospike.client.query.Filter;
import com.aerospike.client.query.RecordSet;
import com.aerospike.client.policy.QueryPolicy;
import com.aerospike.client.exp.Exp;
import com.aerospike.client.exp.Exp.Type;
import com.aerospike.client.query.RegexFlag;
//Run SI query
Statement stmt = new Statement();
stmt.setNamespace("test");
stmt.setSetName("testset");
stmt.setFilter(Filter.range("age", 20,30));
QueryPolicy qp = new QueryPolicy();
Exp exp = Exp.val(false); //init to false
exp = Exp.regexCompare("^.*apple.*",RegexFlag.ICASE,Exp.key(Type.STRING));
qp.filterExp = Exp.build(exp);
RecordSet rs = client.query(qp, stmt);
while (rs.next()){
Record r = rs.getRecord();
Key k = rs.getKey(); //Double check the key retrireved
System.out.println(r);
System.out.println(k);
}
And the output: (Jim has apple in key, but age is out of SI filter range)
(gen:1),(exp:409022737),(bins:(name:Sean),(age:24))
test:testset:SeanaPPlekey7:ef8ea2cf3c6f526b567acdae097ba99e6f9def2d
(gen:1),(exp:409022737),(bins:(name:Jack),(age:26))
test:testset:jacksapplekey1:acbb041d06610ece7dc1e3bea2fd0c3916cde7bb
My colleagues replied to you in general, but of course that does not answer your question specifically for Go. Here is that answer:
package main
import (
"log"
"strings"
as "github.com/aerospike/aerospike-client-go/v6"
)
const (
namespace = "test"
set = "testset"
)
var (
client *as.Client
)
func main() {
var err error
client, err = as.NewClient("localhost", 3000)
if err != nil {
log.Fatalln(err.Error())
}
wp := as.NewWritePolicy(0, 0)
wp.SendKey = true
err = client.Truncate(nil, namespace, set, nil)
if err != nil {
log.Fatalln(err.Error())
}
putBins(wp, "jacksapplekey1", as.BinMap{"Jack": 26})
putBins(wp, "jillgrapekey2", as.BinMap{"Jill": 20})
putBins(wp, "mangokey3", as.BinMap{"James": 38})
putBins(wp, "Jimkey4apple", as.BinMap{"Jim": 46})
putBins(wp, "JuliaGrapekey5", as.BinMap{"Julia": 62})
putBins(wp, "SallyMANGOkey6", as.BinMap{"Sally": 32})
putBins(wp, "SeanaPPlekey7", as.BinMap{"Sean": 24})
putBins(wp, "SamGRAPEkey8", as.BinMap{"Sam": 12})
putBins(wp, "Susankey9", as.BinMap{"Susan": 42})
putBins(wp, "SandraPeachkey0", as.BinMap{"Sandra": 34})
stmt := as.NewStatement(namespace, set)
exp := as.ExpRegexCompare("^.*apple.*", as.ExpRegexFlagICASE, as.ExpKey(as.ExpTypeSTRING))
qp := as.NewQueryPolicy()
qp.FilterExpression = exp
rs, err := client.Query(qp, stmt)
if err != nil {
log.Fatalln(err.Error())
}
for res := range rs.Results() {
if res.Err != nil {
log.Fatalln(err.Error())
}
log.Println(res.Record)
if !strings.Contains(strings.ToLower(res.Record.Key.Value().GetObject().(string)), "apple") {
log.Fatalf("Wrong key returned: %s. Expected to include 'apple' (case-insensitive)\n", res.Record.Key.Value())
}
}
log.Println("Finished successafully.")
}
func putBins(wp *as.WritePolicy, akey string, bins as.BinMap) {
key, err := as.NewKey(namespace, set, akey)
if err != nil {
log.Fatalln(err.Error())
}
if err = client.Put(wp, key, bins); err != nil {
log.Fatalln(err.Error())
}
}
Thanks a lot for your responses @Khosrow_Afroozeh , @pgupta. Will try the suggested method out
Regards, Sriteja
Thanks for your response @meher
Hi All,
I have proceeded further with the solution suggested by @Khosrow_Afroozeh , But when I try to test my API with a load of around 8-10 requests( involving a regex search ) per sec, The requests stop responding after 100-120 requests. Would like to know how costly of an operation is regex search in aerospike and if there are any things to consider from the database’s point of view.
Thanks in advance, Sriteja