Hello, I’ve been looking for a way to query efficiently records and I came up with idea to build an expression that will rely on its metadata since its in the index metadata and hopefully some storage access can be saved however, I am lost in trying to build the proper filter and I need some guidance on what I am doing wrong.
I am using .NET client but I believe client doesn’t matter at this point.
Here is essential part of the code:
(My range builder)
private static readonly DateTime _unixEpoch = new(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
private static (double start, double end) CreateRange(ReportInterval interval)
{
switch (interval)
{
case ReportInterval.Day:
{
return ((DateTime.Today - _unixEpoch).TotalNanoseconds, (DateTime.Today.AddDays(1).AddMilliseconds(-1) - _unixEpoch).TotalNanoseconds);
}
case ReportInterval.Month:
{
var startOfTheWeek = DateTime.Today.AddDays(-(int)DateTime.Today.DayOfWeek + (int)CultureInfo.CurrentCulture.DateTimeFormat.FirstDayOfWeek);
return ((startOfTheWeek - _unixEpoch).TotalNanoseconds, (startOfTheWeek.AddDays(7) - _unixEpoch).TotalNanoseconds);
}
case ReportInterval.Week:
{
var today = DateTime.Today;
var startOfMonth = new DateTime(today.Year, today.Month, 1);
var endOfMonth = startOfMonth.AddMonths(1);
return ((startOfMonth - _unixEpoch).TotalNanoseconds, (endOfMonth - _unixEpoch).TotalNanoseconds);
}
default:
{
throw new NotSupportedException($"{interval} is not supported yet.");
}
}
}
(My range expression builder)
private static Exp CreateLutExpression(ReportInterval interval)
{
var (start, end) = CreateRange(interval);
// start <= record.metadata.lastUpdateDate >= end
return Exp.And(Exp.LE(Exp.Val((long)start), Exp.LastUpdate()), Exp.GE(Exp.Val((long)end), Exp.LastUpdate()));
}
- One thing to notice here is that when I change start with 0 and end with long.MaxValue it returns everything like expected
(How I use it)
var statement = new Statement()
{
SetName = _settings.EventsSet,
Namespace = _settings.Namespace,
BinNames = types.GetTypes()
.SelectMany(type => ReportCodes.Lookup[type])
.Distinct().ToArray(),
};
var policy = new QueryPolicy(_client.QueryPolicyDefault)
{
filterExp = Exp.Build(Exp.And([CreateLutExpression(interval), .. CreateReportExpression(types)])),
};
_client.Query(policy, listener, statement);
(My custom report expression builder that shold fallback to storage access)
private static IEnumerable<Exp> CreateReportExpression(ReportTypes types)
{
if (types.HasFlag(ReportTypes.UniquePlayersThatActivatedChallenge))
{
var expression = Exp.EQ(Exp.Val((int)ActionType.ChallengeActivation), Exp.IntBin(KpiRecord.BinNames.Type));
yield return expression;
}
}
So my questions are:
- What am I doing wrong with the Lut expression because I don’t see any records being returned for day,week or month?
- If I am using AND expression and I put the Lut expression first would that mean that server will try to satisfy the expression left to right, effectively omitting all the storage accesses for the records that are not in the LUT expression criteria (since the LUT expression is left-most condition)?
- I noticed that record_metadata_last_update is documented as “nanoseconds (millisecond resolution) from Unix Epoch (1/1/1970) as an integer.” while in index metadata it was described as “Tracks the last writes to the key (Citrusleaf epoch)” and also in this example of getting LUT through UDF it also mentions that LUT is “expressed in milliseconds since the Citrusleaf epoch”. Which one of those three is most correct?
- Why I dont seem to see the lut value using aql with “SET RECORD_PRINT_METADATA true” option? I only see ttl and gen and no lut.
(Disclaimer) I read somewhere that even if I don’t update my records they will have their lut set to the creation date so I based my logic around this, I hope this is true.
Thanks in advance!
