Transcript Data Streams, Message Brokers, Sensor Nets, and Other Strange Places to Run Database Queries Michael Franklin UC Berkeley July 2003

Data Streams,
Message Brokers,
Sensor Nets, and Other
Strange Places to Run
Database Queries
Michael Franklin
UC Berkeley
July 2003
Data Everywhere

Increasingly ubiquitous networking at all
scales.
 ad hoc sensor nets, wireless, global Internet

Explosion in number, types, and locations of
data sources and sinks.
 mobile devices, P2P networks, data centers

Emerging software infrastructure to put it all
together.
 pub/sub, XML, web services, …
Data Management in a
Networked World
“When processing, storage, and transmission cost
micro-dollars, the the only real value is the data and its
organization.” (Jim Gray’s 1998 Turing Award Paper)

Data is the crucial resource for emerging
networked applications.

Database techniques are all about data
organization and access.
 They can be adapted for network-centric environments.
 In particular, query processing can play a central role in
a number of non-traditional settings.
Networked Data Management
Projects @UCB-DB Group






GridDB - Relational interaction model for
Scientific Grid Computing. [SIGMOD 03 Demo]
MobiScope - Distributed processing for
Location-based Services [MDM 03]
PIER - P2P Data Management [VLDB 03]
TelegraphCQ - Adaptive Dataflow Processing for
Data Streams. [CIDR 03; SIGMOD 03 Demo]
TinyDB - Sensor Networks for environmental
monitoring [OSDI 02;SIGMOD 03]
YFilter - XML Message Brokering
[ICDE 02 Demo; VLDB 03]
Why Database Queries?

Declarative approach.
 Programmer productivity.
 Robustness to change.
 Let the system manage efficiency.

Semantics and High-level operators.
 Framework for correctness criteria.
 Pushing semantics down enables smarter
implementations, code re-use.

These
are the
traditional
arguments
Here’s
why the
 Query plans are networks of operators.
 Query/Data duality enables intelligent routing. techniques
carry over
Natural mapping of dataflow processing.
Query Plans and Operators
SELECT
SELECT eid,
E.loc,
ename,
COUNT DISTINCT (E.eid)
title
AVG(E.sal)
FROM
E, Proj
FROMEmp
Emp
E P, Asgn A
WHERE E.eid = A.eid
GROUP E.sal
BY E.loc
WHERE
> $50K
AND P.pid = A.pid
HAVING
Count(*)
AND E.loc
<> P.loc> 5


System handles
query plan generation
& optimization;
ensures correct
execution.

Count
Having
distinct

Group(agg)
Join
Select

Join
Emp
Proj
Emp
Emp
Asgn
Employees
Projects
Assignments
Issues: Operator ordering, physical operator choice,
caching, access path (index) use, …
“Traditional” Distributed Queries

Transparency
 Query writers can be
oblivious to
distribution.
 System does plan
generation and
optimization; ensures
correct execution.
©1998 Ozsu and Valduriez

Issues: operator placement, data placement, physical
operators, caching, replication, synchronization,…
Beyond Emps and Depts

In emerging networked data environments,
queries can also be used for:







Monitoring
Real-time Analysis
Actuation
Routing
Transformation
Service Composition
Definition,Naming, and Access Rights
New QP Scenarios




Sensor Networks
Message Brokers
Data Streams
Information/Application Integration
New QP Scenarios




Sensor Networks
Message Brokers
Data Streams
Information/Application Integration
Monitoring (1) - Sensor Nets

Tiny devices monitor the
physical environment.
 Berkeley “motes”, Smart Dust,
RFid, …
 Apps: Transportation,
Environmental, Energy, NBC,…

Form ad hoc networks that aggregate and
communicate streams of values.

E.g., Mica
Mote
4Mhz, 8 bit Atmel RISC uProc, 40 kbit Radio,4 K RAM, 128 K Program
Flash, 512 K Data Flash, AA battery pack
e.g., TinyOS http://webs.cs.berkeley.edu/tos/
TinyDB http://telegraph.cs.berkeley.edu/tinydb
Sensor Net Sample Apps
Habitat Monitoring: Storm
petrels on great duck island,
microclimates on James
Reserve.
Earthquake shake-tests.
Vehicle detection: sensors along a
road, collect data about passing
vehicles.
Traditional
monitoring apparatus.
Declarative Queries in Sensor Nets

Many sensor network applications can be described using
query language primitives.
 Potential for tremendous reductions in development and
debugging effort.
“Report the light intensities of the bright nests.”
Sensors
SELECT nestNo, light
FROM sensors
WHERE light > 400
EPOCH DURATION 1s
Epoch
nestNo
Light
Temp
Accel
Sound
0
1
455
x
x
x
0
2
389
x
x
x
1
1
422
x
x
x
1
2
405
x
x
x
Aggregation Query Example
“Count the number occupied
nests in each loud region of
the island.”
SELECT region, CNT(occupied)
AVG(sound)
Epoch
region
CNT(…)
AVG(…)
0
North
3
360
0
South
3
520
1
North
3
370
1
South
3
520
FROM sensors
GROUP BY region
HAVING AVG(sound) > 200
EPOCH DURATION 10s
Regions w/ AVG(sound) > 200
Sensor Queries @ 10000 Ft
Query
{A,B,C,D,E,F}
A
{B,D,E,F}
B
C
•Sample rate
{D,E,F} D
E
(Almost) All Queries are
Continuous and Periodic
Written in SQL
With Extensions For :
F
•Offline delivery
•Temporal Aggregation
TAG: Tiny AGgregation
(Sam Madden)

In-network processing
 Reduces costs depending on type of aggregates
 Supports “spatial aggregation”


Exploitation of operator, functional semantics
Part of “TinyDB” system
available at http://telegraph.cs.berkeley.edu/tinydb
Tiny AGgregation (TAG), Madden, Franklin, Hellerstein, Hong. OSDI 2002.
Aggregation Framework
• As in extensible databases, we support any
aggregation function conforming to:
Aggn={fmerge, finit, fevaluate}
Fmerge{<a1>,<a2>}  <a12>
Partial Aggregation
finit{a0}
 <a0>
Fevaluate{<a1>}
 aggregate value
(Merge: associative, commutative!)
Example: Average
AVGmerge
{<S1, C1>, <S2, C2>}  < S1 + S2 , C1 + C2>
AVGinit{v}
 <v,1>
AVGevaluate{<S1, C1>}
 S1/C1
TAG: Pipelined Aggregates



After query propagates, during each epoch:
 Each sensor samples local sensors once
 Combines them with Partial State
Records (PSRs) from children
 Outputs PSR representing aggregate
state in the previous epoch.
After (d-1) epochs, PSR for the whole tree
output at root
 d = Depth of the routing tree
 If desired, partial state from top k levels
could be output in kth epoch
To avoid combining PSRs from different
epochs, sensors must cache values from
children
Value from 2
produced at time
t arrives at 1 at
time (t+1)
1
2
3
4
5
Value from 5
produced at time
t arrives at 1 at
time (t+3)
Illustration: Pipelined Aggregation
SELECT COUNT(*)
FROM sensors
1
2
3
4
5
Depth = d
Illustration: Pipelined Aggregation
SELECT COUNT(*)
FROM sensors
Sensor #
1
Epoch #
1
1
2
1
3
1
1
1
4
1
Epoch 1
1
5
1
1
2
3
1
4
1
5
Illustration: Pipelined Aggregation
SELECT COUNT(*)
FROM sensors
Sensor #
Epoch #
1
2
3
3
Epoch 2
1
4
1
5
1
1
1
1
1
1
2
3
1
2
2
1
2
2
3
2
4
1
5
Illustration: Pipelined Aggregation
SELECT COUNT(*)
FROM sensors
Sensor #
Epoch #
1
2
3
4
Epoch 3
1
4
1
5
1
1
1
1
1
1
2
3
1
2
2
1
3
4
1
3
2
1
3
2
3
2
4
1
5
Illustration: Pipelined Aggregation
SELECT COUNT(*)
FROM sensors
Sensor #
Epoch #
1
2
3
5
Epoch 4
1
4
1
5
1
1
1
1
1
1
2
3
1
2
2
1
3
4
1
3
2
1
4
5
1
3
2
1
3
2
3
2
4
1
5
Illustration: Pipelined Aggregation
SELECT COUNT(*)
FROM sensors
Sensor #
Epoch #
1
2
3
5
Epoch 5
1
4
1
5
1
1
1
1
1
1
2
3
1
2
2
1
3
4
1
3
2
1
4
5
1
3
2
1
5
5
1
3
2
1
3
2
3
2
4
1
5
Bytes Transmitted
Simulation Results
Total Bytes Xmitted vs. Aggregation Function
2500 Nodes
50x50 Grid
100000
Neighbors = ~20
Total Bytes Xmitted
Depth = ~10
90000
80000
70000
60000
50000
40000
30000
20000
10000
0
EXTERNAL
MAX
AVERAGE
Aggregation Function
COUNT
MEDIAN
Optimization: “Snooping”


Insight: Shared channel enables optimizations
Suppress messages that won’t affect aggregate
 E.g., in a MAX query, sensor with value v hears a neighbor with
value ≥ v, so it doesn’t report
 Applies to all exemplary, monotonic aggregates

Learn about query advertisements it missed
 If a sensor shows up in a new environment, it can learn about
queries by looking at neighbors messages.

Root doesn’t have to explicitly rebroadcast query!
Optimization: Hypothesis Testing

Insight: Root can provide information that will
suppress readings that cannot affect the final
aggregate value.
 E.g. Tell all the nodes that the MIN is definitely < 50;
nodes with value ≥ 50 need not participate.
 Depends on monotonicity

How is hypothesis computed?
 Blind guess
 Statistically informed guess
 Observation over first few levels of tree / rounds of aggregate
Experiment: Hypothesis Testing
Messages/ Epoch vs. Network Diameter
(SELECT MAX(attr), R(attr) = [0,100])
3000
Messages / Epoch
2500
2000
No Guess
Guess = 50
Guess = 90
Snooping
1500
1000
500
0
10
20
30
40
50
Network Diameter
Uniform Value Distribution, Dense Packing, Ideal
Communication
Taxonomy of Aggregates

TAG insight: classify aggregates according to
various functional properties
 Yields a general set of optimizations that can automatically
be applied
Property
Examples
Affects
Partial State
MEDIAN : unbounded,
MAX : 1 record
Effectiveness of TAG
Duplicate Sensitivity
MIN : dup. insensitive,
AVG : dup. sensitive
MAX : exemplary
COUNT: summary
COUNT : monotonic
AVG : non-monotonic
Routing Redundancy
Exemplary vs.
Summary
Monotonic
Applicability of Sampling, Effect of
Loss
Hypothesis Testing, Snooping
ACQP

Data collection aware query processing
 “acquisitional query processing”
Issues addressed:
 How does the user control acquisition?
 Rates
or lifetimes
 Event-based triggers
 How should the query be processed?
 Sampling
as a first class operation
 Events – join duality
 Which nodes have relevant data?
 Which samples should be transmitted?
Madden, Franklin, Hellerstein, and Hong. The Design of An
Acqusitional Query Processor. SIGMOD 2003.
Sensor Query Processing Summary

Higher-level programming abstractions for
sensor networks are necessary.
 Aggregation is a fundamental operation
 Semantically aware optimizations
 Close integration with network

ACQP: Languages, indices, approximations that
give user control over which data enters the
system.

Wealth of open research problems:
 Error tolerance, topologies, heterogeneity, spatial
processing, routing strategies, operators, actuation,..

Combines database, network, and device issues
New QP Scenarios




Sensor Networks
Message Brokers
Data Streams
Information/Application Integration
New QP Scenarios




Sensor Networks
Message Brokers
Data Streams
Information/Application Integration
Web Services/Message Brokers
•A platform for dynamic, loosely-coupled integration of
enterprise applications and data.
•Interaction accomplished through exchange of messages in
the wide area.
(e.g., Adam Bosworth’s VLDB 02 keynote:
http://www.cs.ust.hk/vldb2002/VLDB2002-proceedings/slides/S01P01slides.pdf)
Underlying Technology: Filtering
User Profiles
Filtered
Data
XML
XML
Conversion Documents
Filter
Engine
Users
Data Sources
The challenge is to efficiently and quickly match
incoming XML documents against the potentially huge
set of user profiles.
Message Brokers

Message Brokers perform three main tasks:
 Filtering - matching of interests.
 Transformation - format conversion for app
integration and preferences.
 Delivery - moving bits through the overlay
network

Must be lightweight and scalable.
 Effectively they are high-function routers.
 Large-scale deployments may entail handling
10’s or 100’s of thousands of queries
(subscriptions)

XML is a natural substrate.
YFilter Message Broker
(Yanlei Diao [VLDB 03])
queries
XML messages
message
listener
XML
parser
nodelabled
tree
customized
XML messages
message
factory
message
sender
results in groupSequencelistSequence format
Customization
module
tuple
streams
XQuery
parser
parsed
queries
shared path
matching engine
Query Processor
Figure 1: XML message broker architecture
XQuery-based Subscriptions
A query consists of a constant tag and an FLWR
expression
 A for clause: a variable and a path expression
 An optional where clause: conjunctive predicates
 A return clause: interleaved constant tags and path
expressions
 where and return clause paths are relative
<sections>
{for
$s in document(“doc.xml”)//section
where $s//figure/title = “XML processing”
return
<section>
{ $s/title }
{ $s//figure }
</section>
}
</sections>
YFilter:Shared Path Matching


For large-scale systems, shared processing
is essential.
YFilter uses an NFA-based approach to
share path matching work among queries.
a
/a
//a
/*
//*
Location steps

*
a
*

*
*
NFA fragments
Constructing a Query NFA
Concatenate NFA fragments for location steps in a
path expression.
/a
a
//b

*a

*b
Query
“/a//b”
a
Constructing the Combined NFA
Q1=/a/b
Q2=/a/c
Q3=/a/b/c
Q4=/a//b/c
Q5=/a/*/b
Q6=/a//c
Q7=/a/*/*/c
Q8=/a/b/c
{Q1}
c
a
b
c

{Q2}
*b
c
*
c
*
{Q3,
Q8}
{Q3}
c
{Q4}
{Q6}
{Q5}
{Q7}
c
NFA Execution
{Q1}
NFA
c
b
c
a
1
2
3
{Q3, Q8}
{Q2}
4
c
*b

*
5
6
11
9
8
7
c
c
*
10
{Q4}
1
initial
{Q6}
3976
{Q5}
2
1
{Q7}
c
12
Runtime
Stack
13
An XML fragment
read
</c>
6
39 7
5 1012
8 11 6
2
1
2
1
read
<a>
read
<b>
3976
2
1
match Q1
2
1
1
read
<c>
match Q3 Q8
Q5 Q4 Q6
read </b> read
</a>
<a> <b> <c> </c> </b> </a>
Performance Evaluation
800
YFilter: prefix sharing
xfilter(lb)
hybrid
MQPT (ms)
600
XFilter (list balance): no sharing
yfilter
Hybrid approach: share substrings
containing ‘/’ only
400
200
0
0
50
100
Number of Queries (x1000)
Varying number of distinct queries
(NITF, D=6, W=0.2, //=0.2)
150
• YFilter is significantly faster
(around 30 ms for 150K queries)
• Parsing not included:
Xerces (168 ms)
Java XML Pack (141 ms)
Saxon (86 ms).
With YFilter, path matching is no longer the dominant cost!
Message Transformation

Change YFilter to output streams of “path tuples”.
 Each path tuple contains a sequence of node ids
representing the elements that matched the path.

This output is post-processed using relational-style
operators to produce customized messages.

Three approaches (differ in the extent to which they
push work to the engine)
 PathSharing-F: For clause paths only
 PathSharing-FW: For & Where clause paths
 PathSharing-FWR: For, Where & Return

Inherent tension between path sharing and result
customization!
Message Broker – Wrap Up
Sharing is the key to performance
 NFA provides excellent scalability/performance
 PathSharing-FWR performs best, when combined with
optimizations based on the queries and DTD.
 When the post-processing is shared, even more scalability
can be achieved.
 This
sharing is facilitated by using relational-like query plans.
On-going work - How to deploy in the wide area?:
 Distributed Filtering and Content Delivery Network
 Combining distributed query processing and state-ofthe-art application-level multicast protocols.
 What semantics can/should be provided?
For more information see: www.cs.berkeley.edu/~daioyl/yfilter
New QP Scenarios




Sensor Networks
Message Brokers
Data Streams
Information/Application Integration
New QP Scenarios




Sensor Networks
Message Brokers
Data Streams
Information/Application Integration
Monitoring (2) : Data Streams

Streaming Data
 Network monitors
 news feeds
 stock tickers

B2B and Enterprise apps
 Supply-Chain, CRM
 Trade Reconciliation, Order Processing etc.


(Quasi) real-time flow of events and data
Must manage these flows to drive business (and
other) processes.
 Mine flows to create and adjust business rules.
 Can also “tap into” flows for on-line analysis.
TelegraphCQ Overview

An adaptive system for large-scale shared dataflow
processing.

Based on an extensible set of operators:
1) Ingress (data access) operators
 Screen Scraper, Napster/Gnutella readers,
 File readers, Sensor Proxies
2) Non-Blocking Data processing operators
 Selections (filters), XJoins, …
3) Adaptive Routing Operators
 Eddies, STeMs, FLuX, etc.

Operators connected through “Fjords” [MF02]
 queue-based framework unifying push&pull.
SteMs:“State Modules”
[Raman & Hellerstein ICDE 03]
HashA
HashB
HashC
A A BB C CD
HashD
D
•A generalization of the symmetric hash join (n-way)
•SteMs maintain intermediate state for multiple joins.
•Use Eddy to route tuples through the necessary modules.
•SteMs + Eddy reduce need for optimizer, increasing
adaptivity in volatile streaming environments.
Telegraph CQ Architecture
Query Plan Queue
TelegraphCQ
Back End
6
7
5
4
Eddy Control Queue
Modules
8
CQEddy
Query Result Queues
}
Split
Scans
9
1
TelegraphCQ
Front End
Planner
Parser
Listener
Mini-Executor
3
2
Proxy
Catalog
Shared Memory Buffer Pool
Wrappers
Legend
TelegraphCQ
Wrapper
ClearingHouse
Disk
Data Tuples
Query + Control
Data + Query
Semantics of data streams

Different notions of data streams
Time
 Ordered sequence of tuples
 Bag of tuple/timestamp pairs [STREAM]
 Mapping from time to sets of tuples

Data streams are unbounded
 Windows: restrict data for a query

A stream can be transformed by:
 Moving a window across it
 A window can be moved by
 Shifting
its extremities
 Changing its size
5
4
3
2
1
Tuple sets
{t5,t6,t7
{t4,t5,t6
{t3,t4,t5
{t2,t3,t4
{t1,t2,t3
The StreaQuel Language




An extension of SQL
Operates exclusively on streams
Is closed under streams
Supports different ways to “create” streams
 Infinite time-stamped tuple sequence
 Traditional stable relations




Flexible windows: sliding, landmark, and more
Supports logical and physical time
When used with a cursor mechanism, allows clients
to do their own window-based processing.
Target language for TelegraphCQ
Example – Landmark query
NOW = 40 = t
0
5
10
15
20
25
30
35
Window
40
45
50
55
60
Timeline
ST
NOW = 41 = t
...
...
Window
Timeline
ST
NOW = 45 = t
Window ST
Window
ST
NOW = 50 = t
Timeline
Timeline
Current Status - TelegraphCQ

System has been developed by modifying
PostgreSQL:
 Re-used a lot of code:
 Expression



evaluator, semaphores, parser, planner
Sucessfully Demonstrated at SIGMOD 2003.
Performance studies underway.
Beta Version to be released Aug 03
 Open Source (PostgreSQL license)
 Shared joins with windows and aggregates
 Archived/unarchived streams

A “hot” area: Several major streaming systems
under development in the database community
Beyond Emps and Depts

Monitoring
 TinyDB, TelegraphCQ, YFilter

Real-time Analysis
 TinyDB and TelegraphCQ

Actuation
 TinyDB, GridDB

Routing (queries and/or data),
Transformation, Service Composition
 all of the projects

Definition,Naming, and Access Rights
 TelegraphCQ, but all should
Conclusions

Data is the crucial resource in emerging
networked environments.

Database query processing techniques and
insights can provide tremendous leverage.

Huge research opportunities for database,
networking, and distributed systems
researchers.

Breakthroughs will come from projects that
span these areas.