---

Aurora's Stream Query Algebra (SQuAl)

---

• Data Model

  A stream is an append-only sequence of tuples Each tuple conforms to the same schema Each tuple has a time stamp which specifies the time that the time of origin within the Aurora system: (tp = tuple timestamp) NOTE: operator boxes "create" new tuples and these new tuples will receive a time stamp that equal to the oldest of the source tuples Example:: A stream relation type has the form: (TS, A1, A2, ..., An) TS is the time stamp of the "stream relation type" A1 is the ith attribute of the "stream relation type" A stream tuple has the form: (ts, v1, v2, ..., vn) Often, the time stamp is omitted in the discussion, because it is rarely used

  ---

  ---

• SQuAl operators
  • Aurora's SQuAl has 7 operators:

    Filter Map Union BSort (buffered-based sort) Aggregate Join Resample

    ---

    ---

  • The first 3 operators (Filter, Map, Union) are order-agnostic

    An order-agnostic operator will always produce the same output for a stream of input - regardless of the ordering of the tuples in the input

  • The last 4 operators (BSort, Aggregate, Join, Resample) are order-sensitive

    An order-sensitive operator will produce different output when the ordering of the tuples in the input is changed

  ---

  ---

• Filter: select tuples based on condition
  • The Filter operator will output those tuples that satisfy a given condition

  • Syntax:

    Filter(P1, P2, ..., Pm)(S) Pi = ith predicate S = the input stream

    ---

  • Example: "Filter(A3 >= 30)(S)"

  ---

  ---

• Map: the projection operator
  • Map is a generalized projection operator

  • Syntax:

    Map( B1 = F1, B2 = F2, ... Bm = Fm)(S) Fi = function i (applied on the input tuples) Bi = name of attribute i S = input stream

    ---

  • Example: Map( B1=2*A1, B2=2*A2, B3=2*A3)

    (To used the "standard" projection operation, just use the identity map)

    (To remove attributes, just omit the attribute from the map operation).

  ---

  ---

• Union: merging 2 streams
  • The union operation is used to merge 2 or more streams into a single stream

  • Syntax:

    Union( S1, S2, ..., Sm) Si = input stream i NOTE: the value of the time stamp of the output tuple is equal to the time stamp of the input tuple

    ---

  • Example: Union(S1, S2)

  ---

  ---

• Specifying order for Order-sensitive operators
  • The other operators in SQuAl are order-sensitive

    Order-sensitive operators can only be guaranteed to execute using finite buffer space and finite time if the operators can assume some ordering over their input streams

    ---

  • In SQuAl, you can specify an order on a stream.

    ---

  • Out of order:

    A tuple, t in stream S, is out of order by n with respect to attribute A in stream S if there are more than n tuples, u, preceding t in stream S such that u.A > t.A

    Example:

    1, 3, 1, 2, 4, 4, 8, 3, 4, 4 1 is out of order by 1 - (the value 3 preceeds it) 3 is out of order by 3 - (the values 4, 4 and 8 preceed it) 4 is out of order by 1 - (the value 8 preceeds it) 4 is out of order by 1 - (the value 8 preceeds it)

    ---

    ---

  • An order specification in Aurora has the following form:

    Order(    On A,    Slack n,    GroupBy B1, B1, ... Bm   ) A: attribute values of this attribute will be ascending n is a non-negative value This is the number of out of order tuples that will be tolerated This parameter will reserve n + 1 buffer entries to process the flow. (See an example later) Bi: grouping attribute i The input tuples are partitioned according to the GroupBy attributes. The order of the tuples in each partition is considered independently (from other partitions)

    ---

  • Effect of the order specification:

    A order specification works like a filter:

    An arriving tuple that conforms to the order specification is allowed to pass through An arriving tuple that violates to the order specification is discarded This is according to the paper (page 12):

    ---

  • Example: Order(On A, Slack 2, GroupBy NULL)

    Time: | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10| --+---+---+---+---+---+---+---+---+---+---+--------- sample --+---+---+---+---+---+---+---+---+---+---+--------- input stream: | 1 | 3 | 1 | 2 | 4 | 4 | 8 | 3 | 4 | 4 | --+---+---+---+---+---+---+---+---+---+---+--------- Buffer content: --+---+---+---+---+---+---+---+---+---+---+-------- | 1 | 1 | 1 | 1 | 2 | 3 | 4 | 4 | 4 | 4 | --+---+---+---+---+---+---+---+---+---+---+-------- | - | 3 | 1 | 2 | 3 | 4 | 4 | 4 | 4 | 4 | --+---+---+---+---+---+---+---+---+---+---+-------- | - | - | 3 | 3 | 4 | 4 | 8 | 8 | 8 | 8 --+---+---+---+---+---+---+---+---+---+---+-------- | V 3 violates order specification Discarded ?? (page 131, line 6) Output stream: (= first item in buffer) --+---+---+---+---+---+---+---+---+---+---+-------- | - | - | 1 | 1 | 2 | 3 | 4 | 3 | 4 | 4 | --+---+---+---+---+---+---+---+---+---+---+--------

    NOTE: GroupBy NULL means use no grouping attributes

    It will put all tuples in the same group

    ---

  • Example: Order(On A, Slack 0, GroupBy NULL)
    • When slack = 0, then the tuples in a stream are expected to arrive in (ascending) order

  ---

  ---

• BSort: Buffered Sort
  • The BSort operator is an approximate sort operator

  • Syntax:

    BSort( Assuming O ) (S) O is an order specification (see above) S is the input stream

    ---

  • Example:

    O = Order( On A , Slack 2 , GroupBy NULL ); BSort( O ) (S)

    Input stream: 1, 3, 1, 2, 4, 4, 8, 3, 4, 4

    Output:

    Time: | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10| --+---+---+---+---+---+---+---+---+---+---+--------- sample --+---+---+---+---+---+---+---+---+---+---+--------- input stream: | 1 | 3 | 1 | 2 | 4 | 4 | 8 | 3 | 4 | 4 | --+---+---+---+---+---+---+---+---+---+---+--------- Buffer content: --+---+---+---+---+---+---+---+---+---+---+-------- | 1 | 1 | 1 | 1 | 2 | 3 | 4 | 4 | 4 | 4 | --+---+---+---+---+---+---+---+---+---+---+-------- | - | 3 | 1 | 2 | 3 | 4 | 4 | 4 | 4 | 4 | --+---+---+---+---+---+---+---+---+---+---+-------- | - | - | 3 | 3 | 4 | 4 | 8 | 8 | 8 | 8 | --+---+---+---+---+---+---+---+---+---+---+-------- | V 3 violates order specification It's not discarded ???????? (Contradicts page 131, line 6) Output stream: (= first item in buffer) --+---+---+---+---+---+---+---+---+---+---+-------- | - | - | 1 | 1 | 2 | 3 | 4 | 3 | 4 | 4 | --+---+---+---+---+---+---+---+---+---+---+--------

    ---

    I think there is an error - according to the definition of Order, the system should discard the value 3 that is 3 places out of order...

    Then again, it could be that the BSort operator never discard tuples but output out of order tuples as soon as possible..

    I did not write the paper; so I can't tell you if this is a real error. Here is the actual example in the paper:

  ---

  ---

• Aggregate: applying a set function on a window of tuples
  • The Aggregate operator applies a (window) function on a window of tuples in the input stream

    ---

  • Window:

    A window is a subsequence of tuples in the input stream Example: Input stream: 1 4 5 8 3 1 3 4 6 ... | | +-------------+ "window"

    ---

  • Syntax:

    Aggregate( F, Assuming 0, Size s, Advance i) (S) F: function O: the ordering on the input stream s = size of the window (number of elements used in the function) i: an integer or a predicate that specify how (far) to advance (silde) the window S = input stream Output format: the output format of Aggregate is similar to a Group BY query in SQL The output format consists of the grouping attributes and the aggregate function value The time stamp of the output tuple is equal to the smallest time stamp in the window of tuples

    ---

  • Example: tracking average hourly prices of stocks
    Input stream schema: (Sid, Time, Price)

    Sid = stock ID Time = time that the stock price is valid Price = price of the stock at that time

    SQuAl aggregate function:

    Aggregate( Avg(Price), Assuming Order(On Time, GroupBy Sid), Size 1hour, Advance 1hour) Tuples are first partitioned by Sid and ordered according to Time Then each substream is sampled for 1 hour (Size 1hour) The function Average is applied to the tuples in this window (per group) After each sample the window is advanced to the next hour (Advance 1hour)

    Sample input data:

  ---

  ---

• Join: joining 2 windows of tuples
  • The Join operator is a binary (using 2 streams) operation that joins tuples in 2 streams with each other.

    The Join operation is apply on windows of tuples

    (Ideally you would wait for a streams to drain completely before performing the join operation... but that is not possible with stream queries).

    ---

  • Syntax:

    Join( Predicate, Size s, Left Assuming O1, Right Assuming O1) (S1, S2) Predicate: the join condition O1 is an ordering on the input stream S1 O2 is an ordering on the input stream S2 Size s is a "size constraint" on the difference in ordering: Let u be a tuple in S1 that satisfies the ordering O1 And assume the ordering O1 is on the attribute A in S1 Let v be a tuple in S2 that satisfies the ordering O2 And assume the ordering O2 is on the attribute B in S2 The size constraint Size s requires that the tuples must satisfy: | u.A - v.B | < s

    ---

  • Example: find soldiers occupying the same position within 10 minutes of each other
    Input stream schema: (Sid, Time, Position)

    Sid = soldier's ID Time = time of the position report Position = position of the soldier at the given time

    Join predicate: S1.Position = S2.Position

    Sample input data:

    In addition to the predicate S1.Position = S2.Position, the tuple must also satisfy:

    | S1.Time - S2.Time | < 10 min

  ---

  ---

• Resample: aligning pairs
  • The Resample operation is like a join operation using a generalized window join condition

    A set function (window function) is applied on the matching set of tuples to produce a single tuple

    ---

  • Syntax:

    Resample( F, Size s, Left Assuming O1, Right Assuming O2) (S1, S2) F: the window function that is applied in the matching set of tuples (How the tuples are matched is discussed next) O1 is the ordering on some attribute S1.A in input stream S1 O2 is the ordering on some attribute S2.B in input stream S2 Size s is again a "size constraint" on the difference in ordering: Let u be a tuple in S1 that satisfies the ordering O1 And assume the ordering O1 is on the attribute A in S1 Let v be a tuple in S2 that satisfies the ordering O2 And assume the ordering O2 is on the attribute B in S2 The size constraint Size s requires that the tuples must satisfy: | u.A - v.B | < s

    ---

  • The output of Resample is driven by the data in the input stream S₁

    For each tuple in S₁, the aggregate (set) function F is applied on the tuples in stream S₂ that satisfy:

    | u.A - v.B | < s

    ---

  • Example on using Resample:
    • Given a stream of data on the location of platoons

    • The format of the data stream is: (ID, Time, X-Location)

    Query:

    Find the approximate location of a platoon at certain time (e.g., 2:00, 2:15, 2:30, etc...)

  • Solution:
    • Generate an artificial heart beat time stream

      E.g.: 2:00, 2:15, 2:30, etc....

    • Resample the location data stream driven by the heart beat stream:

      S1 = heart beat stream O1 = Order(On S1.Time) Ss = location data stream O1 = Order(On S2.Time) Size s = pick... 10 min F: avg(S2.X-Location)

    ---

  • Example worked out with sample input data:

    • Example:
      • to compute the 3rd item (1, 2.15, 2.33), the Resample operation uses tuple Heartbeat (2:15)
      • then look for all tuples in the location stream on Platoon 1 that are within 10 min from the given time.
      • There are 3 tuples: (1, 2:08, 3), (1, 2:12, 1), (1, 2:21, 3)
      • The average of the location value is: (3 + 1 + 3)/3 = 2.33

  ---

  ---

• Example SQuAl
  • Data stream:

    (ID, Time, Position)

    The data represents the position of a soldier ID at a given time Time

    ---

  • Query:

    Find the times that m or more soldier pass a certain border at the same time

    ---

  • Sample input stream processing:

    Find the times that 3 or more soldier pass a certain position 30 at the same time

    Concrete data:

    ---

  • SQuAl query:

  ---

  ---

• Constructing the SQuAl query network
  • Users use an GUI interface to define operator (boxes) and make the connections between the operators:

    ---

  • The GUI has a queue monitor tool that allows the user to observe the content of the queues:

    That can be a helpful tool in debugging