Multiple FilterExp query with aggregation

Client Libraries C# Client
, , ,
1

Hello, I am not sure on how to query a setname based on multiple filters and aggregate the results count based on some values.

For the sake of the argument, let’s suppose that the dataset has this structure:
CUSTOMER_ID; TIMESTAMP; STATUS

  • CUSTOMER_ID and TIMESTAMP can be optional input values via web api. They are the fields to be filtered.
  • STATUS is an enum with 7 different values. It is the field to run the aggregation on. The output should give the total count for each of those values.

Reading the docs/forum, this is the approach I am using:

  1. Set filter on most selective criteria
var stmt = new Statement();
stmt.SetFilter(Filter.Equal(mainFilterBin, customerId));
  1. Define a UDF via Lua script that additionally filters the records based on TIMESTAMP and aggregate the count, returning a map. The invocation is something like:
var rs = client.QueryAggregate(null, stmt, luaPackageName, luaFunctionName,
            Value.Get(startDtUnixTs), Value.Get(endDtUnixTs));

The approach works, I am able to query the dataset as I want to, but I would like to know if there is a better a approach for applying multiple filtering before running LUA.
So, I want to know how to apply multiple filters via c# code and then using LUA for aggregation of the resulting record set on some field.

Some additional info:

  • I am using Aerospike Client v 4.2.7, I do not want to use PredExp because it is obsolete.

  • I read about Expressions and how to set them in a Policy (e.g.; multiFilterPolicy). But then running var rs = client.QueryAggregate(multiFilerPolicy, stmt, luaPackageName, luaFunctionName) won’t work, throwing Query Failed: Error 16 Unsupported server feature.
    Also about this, I must use the synchronous client since the asnyc doesn’t support QueryAggregate (discussion).
    I could skip LUA scripting but is applying something like this fine? I’m not sure because it seems that in this case i would need to count the different STATUS records in the while loop.

stmt.SetFilter(Filter.Range(binName, begin, end));
// Predicates are applied on query results on server side.
// Predicates can reference any bin.
QueryPolicy policy = new QueryPolicy(client.queryPolicyDefault);
policy.filterExp = Exp.Build(
	Exp.Or(
		Exp.And(
			Exp.EQ(Exp.StringBin("customerId"), Exp.Val(customerId)),
			Exp.GE(Exp.IntBin("timestamp"), Exp.Val(timestamp)))
           ));

RecordSet rs = client.Query(policy, stmt);

try
{
	while (rs.Next())
	{
		Record record = rs.Record;
		console.Info("Record: " + record.ToString());
	}
}
finally
{
	rs.Close();
}

What is the best approach in this case? Do you have any suggestion?
Thank you very much
2

I am not an expert on Expressions but I would definitely suggest leveraging them as you already guessed. It seems you would just have to upgrade your server to version 5.6 or above (which is where the Expressions are introduced on the server side).

3

Actually it seems that our current aggregations wouldn’t support running on top of query with extra expressions… and that may be the reason for the ‘feature not supported’ rather than the expressions themselves not supported.

4

Hi @meher, thanks for your time.

Regarding the Query Failed: Error 16 Unsupported server feature message:

  • server side version is 5.7.
  • I also think it is related to running a query with both the Expression policy (meaning that policy.filterExp is set) and the aggregation via lua scripting.

What do you mean with “I would definitely suggest leveraging them as you already guessed” ?

To run the query as a I’ve shown in the example and then count the results via the while loop?
Just for reference, something like:

RecordSet rs = client.Query(policy, stmt); // policy set with FilterExpression
var count = 0;
while (rs.Next())
	{
		Record record = rs.Record;
		console.Info("Record: " + record.ToString());
        count++;
	}

Would you say this approach is better (= faster/better practice) than filtering+aggregating via Lua?

Thank you

PS: Just to clarify, the query I want to run is like:

SELECT COUNT(STATUS)
WHERE CUSTOMER_ID=customerId AND TIMESTAMP>=timestamp

(actually it is a little more complex because STATUS have different values and I want the count to be aggregated by it, but for the sake of the argument this example is fine. The aggregation can then be performed directly via lua script or inside the while loop, based on the approach I would go for)

5

Seems you are right… I thought that expressions would have worked and that is what I was initially suggesting: leveraging Expressions. What you have gotten to may be the best way currently to do this…

1 Like