Stream SQL, Rules, Subscriptions: It’s All The Same

1. MIDDLEWARE SYSTEMS RESEARCH GROUP Stream SQL, Rules, Subscriptions: It’s All The Same http://www.padres.msrg.utoronto.ca Hans-Arno Jacobsen Bell University Laboratory Chair Middleware Systems Research Group University of Toronto Go PSQL !

2. Event Processing with PADRES • PADRES Event Language • A1: [class = item], [shipID = *], [weight < 10] • P1: [class, item],[shipID, ACME123],[weight, 4] • S1: [class = item], [weight > 3] • Initially implemented with JESS (i.e., as rules) • LHS statements encode atomic subscriptions • LHS encode composite subscriptions • RHS encode notification semantics (e.g., forwarding logic) • IFLHS op LHS op LHS THENRHS • PADRES SQL (PSQL) modeled after SQL for event and event stream processing

3. PSQL Schema Definition CREATE TABLE (attr op val [, attr op val]* ) CREATE TABLE (class = invoke, service = *, item = *, id = *) Advertisement: [class, =, invoke],[service, = ,*],[item, =, *],[id, =, *] CREATE TABLE (class = result, service = *, item = *, id = *, state = *) Advertisement: [class, =, result],[service, = ,*],[item, =, *], [id, =, *],[state,=,*]

4. Publishing INSERT ( attr [, attr]* ) VALUES ( val [, val]* ) INSERT (class, service, id) VALUES (invoke, ItemView, a0012) Publication: [class,invoke],[service,ItemView],[id, a0012]

5. Subscribing Querying and subscribing to data SELECT [ attr | function]* [ FROM event pattern ] WHERE [ attr op val ]* [ GROUP BY attr* ] [ HAVING function* ] [ … ] clause is optional We support AVG, MIN, MAX, COUNT as AVG(attr,N)

6. Example Atomic and hybrid subscription: SELECT * WHERE class = invoke, service = ItemView, item = T-Shirt, start_time = NOW – 1h, end_time = NOW + 1h [class, =, invoke], [service, =, ItemView],[item, =, T-Shirt], [start_time, =, NOW – 1h], [end_time, =, NOW + 1h]

7. Example Composite and hybridsubscription SELECT e2.service, e2.item, e2.id FROM e1 AND e2 WHERE e1.class = result, e1.service = ItemShipped, e1.state = 2, e2.class = invoke, e2.service = ItemView, e1.start_time = NOW – 2months, e1.item = e2.item [class,=,result],[service, =, ItemShipped],[item,=,$X], [state, =, 2],[start_time, =, NOW – 2 months], AND [class,=, invoke],[service, =, ItemView],[item,=,$X]

8. P1 P2 P3 P4 P5 Historic Event Query • Partition the publication space • Assign partitions Publication space P(A) Subscriber DB Admin 6 1 S 3 1 3 4 2 Publisher 2 P 5 5 4 Partition CANOE Summer School, Norway, 2009

9. 4 1 1 3 2 2 5 Assigning A Partition [class,DB_CONTROL],[command,STORE],[db,B3-DB], [partition_spec,” [class,=,reading],[level,>,0] ”] Subscriber DB Admin • Partitioning • Partial replication • Full replication 6 S 1 3 4 1 Publisher [class,=,reading],[level,>,0] [class,DB_CONTROL],[command,STORE],[db,B3-DB], [partition_spec,`[class,=,reading],[level,>,0]`] 2 P 5 Advertisement Subscription Publication Partition CANOE Summer School, Norway, 2009

10. Challenges & Obstacles Remaining • Stream SQL ≈ Rules ≈ Subscriptions • A common formal framework • A query algebra or calculus • Simple (i.e., can be described in 2 pages or less) • Algorithmic issues in processing expressions

11. The PSQL Language Schema or type definition CREATE TABLE (attr op val [, attr op val]* ) CREATE TABLE (class = invoke, service = *, item = *, id = *) Adv: [class, =, invoke],[service, = ,*],[item, =, *],[id, =, *] CREATE TABLE (class = result, service = *, item = *, id = *, state = *) Adv: [class, =, invoke],[service, = ,*],[item, =, *],[id, =, *],[state,=,*] Produce data INSERT ( attr [, attr]* ) VALUES ( val [, val]* ) INSERT (class, service, id) VALUES (invoke, ItemView, a0012) Pub: [class,invoke],[service,ItemView],[id, a0012]

12. Retail Scenario Web Services Customer B B Delivery Services B Customer B B Web Services Manager Customer