Introduction to Microsoft SQL Server 2008 R2 StreamInsight

1. PDC09-SVR07 Introduction to Microsoft SQL Server 2008 R2 StreamInsight Torsten Grabs Senior Program Manager Lead Microsoft Corporation

2. What are Event-Driven Applications? Event Complex Event Processing (CEP) is the continuous and incremental processing of event streams from multiple sources based on declarative query and pattern specifications with near-zero latency. request output stream input stream response

3. Latency Scenarios for Event-Driven Applications Relational Database Applications CEP Target Scenarios Operational Analytics Applications, e.g., Logistics, etc. Data Warehousing Applications Web Analytics Applications Manufacturing Applications Financial trading Applications Monitoring Applications Aggregate Data Rate (Events/sec.)

4. Example Scenarios • Manufacturing: • Sensor on plant floor • React through device controllers • Aggregated data • 10,000 events/sec • Power, Utilities: • Energy consumption • Outages • Smart grids • 100,000 events/sec • Web Analytics: • Click-stream data • Online customer behavior • Page layout • 100,000 events /sec • Financial Services: • Stock & news feeds • Algorithmic trading • Patterns over time • Super-low latency • 100,000 events /sec Asset Instrumentation for Data Acquisition, Subscriptions to Data Feeds Data Stream Data Stream Visual trend-line and KPI monitoring Batch & product management Automated anomaly detection Real-time customer segmentation Algorithmic trading Proactive condition-based maintenance Stream Data Store & Archive Asset Specs & Parameters Event Processing Engine • Threshold queries • Event correlation from multiple sources • Pattern queries Lookup

5. Virtuous Cycle: Monitor, Manage, Mine

6. Solutions with StreamInsight CEP Data Sources, Operations, Assets, Feeds, Sensors, Devices Input Data Streams OutputData Streams Input Data Streams CEP Engine Monitor & Record Mine & Design Manage & Benefit f(x) f'(x) g(y) h(x,y) Results Deploy History Operational Data Store & Archive CEP Engine f(x) g(y) f'(x) h(x,y)

7. Overview: Microsoft StreamInsight Event Event Event Event Event Event Event Event Event .NET C# LINQ CEP Application Development IDE Event targets Event sources CEP Application at Runtime CEP Engine Devices, Sensors Pagers & Monitoring devices Standing Queries Output Adapters Input Adapters Web servers KPI Dashboards, SharePoint UI Trading stations Event stores & Databases Stock tickers & News feeds Event stores & Databases Static reference data

8. Events • Events expose different temporal characteristics • Point in time events • Interval events with fixed duration • Interval events with initially unknown duration • Rich payloads capture all properties of an event d c e Payload/ value  b a t5 t3 t4 t1 t2 Time 

9. Event Types • Events in Microsoft’s CEP platform use the .NET type system • Events are structured and can have multiple fields • Fields are typed using the .NET framework types • CEP engine provisioned timestamp fields capture all the different temporal event characteristics • Event sources populate time stamp fields

10. Event Streams & Adapters • A stream is a possibly infinite sequence of events • Insertions of new events • Changes to event durations • Stream characteristics: • Event/data arrival patterns • Steady rate with end-of-stream indication • Intermittent, random, or in bursts • Out of order events: Order of arrival of events does not match the order of their application timestamps • Adapters • Receive/get events from the data source • Enqueue events for processing in the engine

11. Typical CEP Queries • Typical CEP queries require combination of functionality • Complex type describes event properties • Calculations introduce additional event properties • Grouping by one or more event properties • Aggregation for each event group over a pre-defined period of time, typically a window • Multiple event groups monitored by the same query • Correlate event streams • Check for absence of activity with a data source • Enrich events with reference data • Collection of assets may change over time • We want to make writing and maintaining those queries easy or even effortless

12. StreamInsight Query Features • Operators over streams • Calculations (PROJECT) • Correlation of streams from different data sources (JOIN) • Check for absence of activity with a data source (EXISTS) • Selection of events from streams (FILTER) • Stream partitioning (GROUP & APPLY) • Aggregation (SUM, COUNT, …) • Ranking and heavy hitters (TOP-K) • Temporal operations: hopping window, sliding window • Extensibility – to add new domain-specific operators

13. LINQ Query Examples LINQ Example – JOIN, PROJECT, FILTER: from e1 in MyStream1 join e2 in MyStream2 on e1.ID equals e2.ID where e1.f2 == “foo” select new { e1.f1, e2.f4 }; Filter Project &Aggregate Project Window Grouping Join LINQ Example – GROUP&APPLY, WINDOW: from e3 in MyStream3 group e3 by e3.i intoSubStream fromwin inSubStream.HoppingWindow( FiveMinutes,ThreeSeconds) selectnew { i = SubStream.Key, a = win.Avg(e => e.f) };

14. Writing StreamInsight Queries demo

15. Extensibility SDK • Built-in operators do not cover all functionality • Need for domain-specific extensions • Integrate with functionality from existing libraries • Support for extensions in the CEP platform: • User-defined operators, functions, aggregates • Code written in .NET, deployed as .NET assembly • Query operators and LINQ can refer to functionality of the assembly • Temporal snap-shot operator framework • Interface to implement user-defined operators • Manages operator state and snapshot changes • Framework does the heavy lifting to deal with intricate temporal behavior such as out-of-order events

16. User-defined Extensions • Temporal aspects managed by the framework • Access to temporal metadata only if necessary • Incremental state management for aggregates • Easy to invoke from LINQ: • varoutputStream = • from win in • input.TumblingWindow(TimeSpan.FromSeconds(10)) • selectnew • { • v = win.TimeWeightedAverage(e => e.dVal) • };

17. TWAvg Implementation in C# publicclassTimeWeightedAverage : CepTimeSensitiveAggregate<double, double> { publicoverridedoubleGenerateOutput( IEnumerable<IntervalEvent<double>> events, WindowDescriptor wd)       { doubleavg = 0; foreach (IntervalEvent<double> e in events)          { avg += e.Payload * (e.EndTime - e.StartTime).Ticks; } returnavg / (wd.EndTime - wd.StartTime).Ticks;         }     }

18. Managing CEP Data Sources & Queries • Typical CEP Application • Multiple different data sources and adapters • Several queries • Intellectual property is in the queries • Protect investment in queries over time • Re-use existing queries as newer version of an asset, device or data source become available • Re-use existing queries for new data source types • Seamlessly switch between live data feeds and historical data

19. Query Binding Event Event Event Event Event Event Analytics & Queries Data Sources QT1 CEP Engine • Binding a query with data sources yields a query instance • Configure multiple query instances for a query • Similar to data independence: the same query can be bound to different data sources as long as they deliver the required event type AT1 Standing Queries Q1 Input Adapters Output Adapters AT2 Q1’ AT3 Q1’’

20. StreamInsight Query Configuration Query Output Adapter • Query binding: • Coupling adapters with a query • Event types required by the query need to be matched by types delivered by adapters • Query can be re-used for all data sources of the same type • No changes to query necessary for re-use Type Type Query Template Proj. Join Type Type Type Type Input Adapter Input Adapter

21. StreamInsight Deployment Alternatives CEP CEP for lightweight processing and filtering CEP CEP for aggregation and correlation of in-flight events CEP CEP for complex analytics including historical data • Event processing engines are deployed at multiple places on different scales • At the edge – close to the data source • In the mid-tier – consolidate related data sources, • In the data center – historical archive, mining, large scale correlation. Web servers Sensors CEP CEP Feeds Devices CEP CEP CEP CEP CEP CEP CEP • Complex Analytics & • Mining

22. Managing StreamInsightMetadata • Persisted metadata: SQL CE provider • Non-persisted metadata: in-mem provider

23. Financial Market Monitor demo

24. Market Monitor: Architecture • Quotes: • MSFT • IBM StreamInsight StreamInsight StreamInsight Grouping Aggregation Filter Grouping Filter Grouping Output Adapters Output Adapters Output Adapters Input Adapters Input Adapters Input Adapters

25. Recap: Microsoft StreamInsight Event Event Event Event Event Event Event Event Event Development experience with .NET, C#, LINQ and Visual Studio 2008 CEP Application Development CEP platform from Microsoft to build event-driven applications Event targets Event sources Event-driven applications are fundamentally different from traditional database applications: queries are continuous, consume and produce streams, and compute results incrementally CEP Engine Standing Queries Output Adapters Input Adapters Flexible adapter SDK with high performance to connect to different event sources and sinks The CEP platform does the heavy lifting for you to deal with temporal characteristics of event stream data Static reference data

26. Deployment Scenarios Scenario 1: Custom CEP Application Dev Scenario 2: Embed CEP in Application Custom CEP Application .NET, C#, LINQ ISV Application with CEP Engine CEP Engine CEP Engine KPIs KPI mining ETL Pipeline with CEP engine CEP Engine CEP CEP Scenario 4: Operational Intelligence w/ CEP Scenario 3: CEP Enabled Device CEP CEP Reference data Device with Embedded CEP Engine Reference data Madison CEP Engine

27. StreamInsight Roadmap • Focus on • Custom development platform for CEP applications • CEP platform for Microsoft partners • CTP (Community Technology Preview) and TAP (Technology Adopter Program) launched in August 2009 • Product released in first half 2010 with SQL Server 2008 R2 August CTP TAP program November CTP TAP continues TechEdAnnouncement Release time Nov 2009 2010 Aug 2009 May 2009

28. question & answer