[SQL] Implement SESSION windows - #6782
Conversation
mythical-fred
left a comment
There was a problem hiding this comment.
LGTM. The Calcite-level rewrite is clean and well-documented — the Javadoc on SessionRewriteRule makes the LAG → break → SUM(break) → self-join plan immediately clear. The RemoveIdentityOperators refactor properly handles the GC-conflict edge case that SESSION surfaces, and StrayGC now catches duplicate retainers. Test coverage is thorough: keyed and keyless sessions, NULL timestamps and keys, surrounded columns, negative cases, streaming with LATENESS/compaction, deletions, and late merges across the waterline.
| @@ -0,0 +1,227 @@ | |||
| package org.dbsp.sqlCompiler.compiler.sql.quidem; | |||
There was a problem hiding this comment.
This is how Calcite names some tests, I am reusing them whenever I can. It's Latin for "in fact".
| FROM TABLE(SESSION(TABLE events, DESCRIPTOR(ts), DESCRIPTOR(uid), INTERVAL 10 MINUTES)) | ||
| GROUP BY uid, window_start, window_end;"""; | ||
| CompilerCircuitStream ccs = this.getCCS(sql).compactAfterEachStep(); | ||
| // TODO: window_start cannot have a waterline (a session can be unbounded) |
There was a problem hiding this comment.
so does it throw an error in this case?
There was a problem hiding this comment.
WATERLINES are the main mechanism used for GC when a collection has LATENESS: a waterline is computed at runtime for each collection where old elements can never be updated. The point here is that a session window start window_start can always change, no matter how old, by deleting the last row in the session, which can be recent. So you may see updates to very old values in the column window_start. This statement means that the compiler should not build a circuit to compute the waterline for this column.
Our waterline computing algorithm is conservative, we sometimes fail to compute a waterline where it exists. That's the case for window_end. There is a separate issue for that: #2805
There was a problem hiding this comment.
There are quite a few other cases where we do not compute a waterline, which in practice means that we do not GC some queries we could GC: #1850
|
|
||
| `SESSION` groups rows into sessions based on a timestamp column. Two | ||
| rows belong to the same session when their timestamps are less than | ||
| `size` (the inactivity gap) apart. Unlike `TUMBLE` and `HOP` windows, |
There was a problem hiding this comment.
size(the inactivity gap) apart. UnlikeTUMBLEandHOPwindows,
what is the inactivity gap
There was a problem hiding this comment.
the distance between two consecutive events.
A session starts after a gap and is extended as long as there are events no farther than the specified distance from each other. Think web browser sessions.
| session windows are not fixed in absolute time: each session starts at | ||
| the timestamp of its first row and ends `size` after the timestamp of | ||
| its last row. The optional `key` descriptor partitions the rows; | ||
| sessions are formed separately within each key. |
There was a problem hiding this comment.
it would be nice to have a small example with data so I can quickly parse what this does
There was a problem hiding this comment.
I will copy an example from the tests
Signed-off-by: Mihai Budiu <mbudiu@feldera.com>
mythical-fred
left a comment
There was a problem hiding this comment.
Rebase refresh of the prior APPROVE. Force-pushed onto today's main; the single commit's diff against its parent is identical to the previously reviewed diff except for a 15-line docs addition to docs.feldera.com/docs/sql/table.md describing the SESSION window function (syntax, example, window_start/window_end semantics, NULL timestamp/key behaviour). That belongs in the same PR and reads well. Re-approving.
Fixes #6778
The issue describes how this is done. SESSION is a "table function" in SQL, there is a Calcite-level transformation which rewrites it into some window aggregates using LAG and some self-joins. After this rewrite there is nothing really special about session.
The PR is a bit more involved because it uncovered a bug in the handling GC operators in the graph, and it also contains the fix.
(The PR also uncovered 3 bugs in Calcite, which I have sent PRs for, but we don't depend on them.)
Describe Manual Test Plan
Ran lots of tests.
Checklist