PSoup A System for streaming queries over streaming

PSoup: A System for streaming queries over streaming data Sirish Chandrasekaran Michael J. Franklin The VLDB Journal (2003) 12: 140 -156 Study presented by: Hasmukh Patel CS 561: Advance Databases (Spring-2004), WPI 12/26/2021 1

Plan of presentation • Why PSoup? • Applications for PSoup • PSoup Architecture • PSoup data structures • PSoup in action • Eddy in Psoup • Summary CS 561: Advance Databases (Spring-2004), WPI 12/26/2021 2

Why one more database system like PSoup? Conventional database system: • Queries are streamed over static data • Results are generated for a distinct point in time data Continuous query system: • Data is streamed over static queries • Results are generated continuously How to handle stream of queries over streams of data • This is the problem domain where PSoup helps CS 561: Advance Databases (Spring-2004), WPI 12/26/2021 3

Candidate applications for PSoup Data Recharging: • PDA like devices periodically connect to the network to refresh their data content Monitoring: • Periodically getting health status (various stats) of network switches CS 561: Advance Databases (Spring-2004), WPI 12/26/2021 4

PSoup Architecture (QPE) PSoup is memory based and part of Telegraph. CQ built at UC Berkeley. (Source: Original paper) CS 561: Advance Databases (Spring-2004), WPI 12/26/2021 5

PSoup Architecture (QPE) Extends SQL by adding window clause ‘Begin. End’ e. g. Select r. c, s. y From data_stream r, data_stream s Where (r. a = 5 and r. b > s. x) Begin (Now – 20) End (Now) CS 561: Advance Databases (Spring-2004), WPI Standing Query Clause (SQC) Window Clause 12/26/2021 6

PSoup Data structures used Stem: STat. E Module • Keeps state of data and queries • Provides insert and probe methods over their • One Stem for each stream and one Stem for all queries Query Stem Data Stem Tupple-id R. a R. b Query-id Predicate 71 0 1 11 R. a = 5 72 5 4 12 R. a > 2 and R. a <= 10 73 9 1 13 R. a = 4 and R. b > 5 74 4 8 75 12 6 CS 561: Advance Databases (Spring-2004), WPI 12/26/2021 7

PSoup Data structures used Data index: • Index tree on each search attribute of a stream R. a 10 4 1 2 20 3 4 6 8 10 40 70 30 50 60 CS 561: Advance Databases (Spring-2004), WPI 12/26/2021 8

PSoup Data structures used Predicate index: • Red-Black tree • Balanced binary search tree on values of predicate attribute. • Keeps query ids in link list (for each relational op) attached to each node. (I still do not understand how this tree helps!!) • For complex predicates keeps link list instead of tree 6 8 2 1 <= < = <= > 13 5 <= < = 11 <= > 12 7 10 11 R. a = 5 12 R. a > 5 and R. a <= 10 13 R. a > 8 <= 12 < = <= > CS 561: Advance Databases (Spring-2004), WPI 12/26/2021 9

PSoup Data structures used Result structure: • Indicates which tuples satisfy which queries • Can be a sparse matrix (bit map, shown here) or link list assigned to each query • In bitmap rows are ordered by the timestamp (physical tupleid), columns are ordered by query-id 11 12 T T 13 71 72 73 T 74 T T 75 CS 561: Advance Databases (Spring-2004), WPI 12/26/2021 10

PSoup in action (Current state) Query Stem Query-id Predicate 11 R. a = 5 12 R. a > 2 and R. a <= 10 13 R. a = 4 and R. b > 5 Data Stem 11 12 T T Tupple-id R. a R. b 71 0 1 72 5 4 72 73 9 1 73 T 74 4 8 74 T 75 12 6 75 13 71 CS 561: Advance Databases (Spring-2004), WPI T 12/26/2021 11

PSoup in action (New Query) Query Stem Query-id Predicate 11 R. a = 5 12 R. a > 2 and R. a <= 10 13 R. a = 4 and R. b > 5 14 R. A < 5 and R. b = 8 Data Stem 11 12 T T Tupple-id R. a R. b 71 0 1 72 5 4 72 73 9 1 73 T 74 4 8 74 T 75 12 6 75 13 14 T T 71 CS 561: Advance Databases (Spring-2004), WPI 12/26/2021 12

PSoup in action (New Tuple) Query Stem Query-id Predicate 11 R. a = 5 12 R. a > 2 and R. a <= 10 13 R. a = 4 and R. b > 5 14 R. a < 5 and R. b = 8 Data Stem 11 12 T T Tupple-id R. a R. b 71 0 1 72 5 4 72 73 9 1 73 T 74 4 8 74 T 75 12 6 75 76 3 8 76 13 14 T T 71 T CS 561: Advance Databases (Spring-2004), WPI T 12/26/2021 13

PSoup in action (Current State) Join scenario Query Stem Query-id Predicate 11 R. a = S. a and S. b > 2 12 R. a >= 5 and R. b > S. a and S. b = 3 Data Stem - R Data Stem - S Tupple-id R. a R. b Tupple-id S. a S. b 71 5 15 81 5 3 72 1 2 82 3 4 73 7 10 83 6 3 74 2 4 84 6 8 75 5 3 85 2 3 Result – Q 11 Result – Q 12 81 82 83 84 85 71 T 72 73 74 75 81 82 83 84 85 71 T 72 73 T 74 75 T CS 561: Advance Databases (Spring-2004), WPI 12/26/2021 14

PSoup in action (New Query) Join scenario Query Stem S-Tuple-id Predicate 81 R. a = 8 and R. b = 3 Query-id Predicate 82 R. a =6 and R. b = 4 11 R. a = S. a and S. b > 2 83 R. a =9 and R. b = 3 12 R. a >= 5 and R. b > S. a and S. b = 3 84 R. a = 9 and R. b = 8 13 R. a = S. a+3 and R. b = S. b and S. a < 10 85 R. a = 5 and R. b = 3 75 2 1 3 Data Stem - R Data Stem - S Tupple-id R. a R. b Tupple-id S. a S. b 71 5 15 81 5 3 72 1 2 82 3 4 73 7 10 83 6 3 74 2 4 84 6 8 75 5 3 85 2 3 Result – Q 11 Result – Q 12 81 82 83 84 85 71 T 72 73 74 75 81 82 83 84 85 71 T 72 73 T 74 75 T Result – Q 13 81 82 83 84 85 4 CS 561: Advance Databases (Spring-2004), WPI 71 72 73 74 75 T 12/26/2021 15

PSoup in action (New Tuple) Join scenario Query Stem R-Tuple-id Predicate 76 6 =S. a and S. b > 2 83, 84 76 7 > S. a and S. b = 3 81, 83 76 6 = S. a+3 and 7 = S. b and S. a < 10 Query-id Predicate 11 R. a = S. a and S. b > 2 12 R. a >= 5 and R. b > S. a and S. b = 3 13 R. a = S. a+3 and R. b = S. b and S. a < 10 3 2 1 Data Stem - R Data Stem - S Tupple-id R. a R. b Tupple-id S. a S. b 71 5 15 81 5 3 72 1 2 82 3 4 73 7 10 83 6 3 74 2 4 84 6 8 75 5 3 85 2 3 76 6 7 Result – Q 11 Result – Q 12 81 82 83 84 85 71 T 72 73 74 75 76 T T 81 82 83 84 85 71 T 72 73 T 74 75 T 76 T T Result – Q 13 81 82 83 84 85 4 CS 561: Advance Databases (Spring-2004), WPI 71 72 73 74 75 76 T 12/26/2021 16

Eddy in PSoup • Provides Stream prefix consistent state (put tuples in result structure in the order in which they enter) • Stores new tuples in New Tuple Pool (NTP) • Stores temporary tuples in TTP • Begins by picking tuple from NTP and then processes all tuples in TTP, before it picks another new tuple from NTP • Serializes the effects of new tuples on the Result structure in the order in which they enter the system CS 561: Advance Databases (Spring-2004), WPI 12/26/2021 17

Summary: • Treats Query and Data streams analogously • Performs multi-query evaluation by joining query-stem with data-stems • Supports queries that requires access to both data that arrived prior to the query and also data that enter later • Separates computation of the result from the delivery by materializing the results CS 561: Advance Databases (Spring-2004), WPI 12/26/2021 18

Questions? ? ? CS 561: Advance Databases (Spring-2004), WPI 12/26/2021 19

My sincere thanks to Prof. Elke for giving me this opportunity of study and Thanks to you friends for listening me CS 561: Advance Databases (Spring-2004), WPI 12/26/2021 20